service handler(s) in listeners
reader, April 10, 2003 - 8:12 am UTC
Tom,
Can you please explain me the Services Summary...(seen below) in oracle listeners ... i was thinking till now that, once the client connects to the server, the oracle creates an handlers to service the request and once the service is processed the listener will wait for further request... will the handlers remain till the instance or the listener is shutdown....
please clarify me. ...pls explain me on the actual process of client to server connection on your words
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HYD280R.)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 8.1.7.1.0 - Production
Start Date 31-MAR-2003 04:30:39
Uptime 10 days 2 hr. 59 min. 26 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /u01/app/oracle/product/8.1.5/network/admin/listener.o
ra
Listener Log File /u01/app/oracle/product/8.1.5/network/log/listener.log
Services Summary...
DEVEENGC has 1 service handler(s)
DEVESCLC has 1 service handler(s)
DEVETMCT has 1 service handler(s)
DEVETMCT has 1 service handler(s)
PLSExtProc has 1 service handler(s)
TSTEENWL has 1 service handler(s)
TSTEENWS has 1 service handler(s)
TSTEENWS has 1 service handler(s)
TSTETMCT has 1 service handler(s)
TSTETMCT has 1 service handler(s)
The command completed successfully
Thanks for your great work,..
April 10, 2003 - 8:21 am UTC
when a client connects, they talk to the listener for a brief instant in time -- the listeners job is to hand you off to the server. The server might setup a dedicated server process for you or let you use a shared server....
Here, you are just see that many instances have registered themselves with this listener - this listener has an instance per service here to handle requests. These are the service names you could use in a tns names entry to connect to a given instance.
How to set multiple listeners.
SUBBARAO, April 11, 2003 - 7:19 am UTC
Dear Tom,
I have a database with 700 users accessing. I have only one listener process running. How to check whether I need to increase the process? If so can you give us the sample listener file with multiple listeners configured.
Regds
SUBBARAO.
April 11, 2003 - 8:57 am UTC
the listener is only involved during login.
do you have problems logging in?
you would use net assistant to set up such a configuration.
A reader, April 11, 2003 - 4:56 pm UTC
How about using 'sevice' of lsnrctl to see if there are any refusal to connection requests. If there are, does it mean either another listener or MTS is needed ?
April 14, 2003 - 7:27 am UTC
The "refused" count is bumped whenever the listener is not successful at
passing a connection to another process. You should look in your listener's
log for specific failure messages. The issues can range from the serious
(eg., out-of-swap space) to the benign (eg., a direct hand-off to a dispatcher
failed because a conduit for the handoff could not be established so the
listener used redirect instead).
So in general, no -- does not mean another listener or shared server is needed, in fact -- there probably aren't enough resources for another listener.
redirect concept
A reader, May 10, 2003 - 12:29 pm UTC
Tom,
Could you please clarify when and what circumstance the listener would redirect a session? Oracle document says, "redirect messages are used, for example, when dispatchers are remote to the listener". My understanding is that PMON would register the dispatchers info with the listener when I specify it in the parameter file. In this context, does remote mean the dispatchers are not configured in the local server?
Thanks for your time in clarifying this.
May 11, 2003 - 9:52 am UTC
redirects are used normally when you use shared server.
you
a) connect to listener
b) listener determines which dispatcher you should connect to
c) listener on SOME platforms can instantly redirect/hand off the connection, on
others it will
d) return the ip/port to connect to the dispatcher to the client
e) stop the connection
f) the client connects to that dispatcher
note that on windows, this happended even with dedicated server connections until shared_sockets were implemented and would be common in 8.0 and before.
redirect concept
A reader, May 11, 2003 - 10:54 am UTC
Thanks. I understand what you say. However, I am still confused with dispatchers being remote to the listener as the doc says,
"redirect messages are used, for example, when
dispatchers are remote to the listener".
Is it like in OPS a listener may have info about dispatchers configured on other server. In this case, can the listener redirect the connection to other server? I am just guessing Tom. I would be greatful if I understand what remote means in this context. Just a curiosity. Thanks.
May 11, 2003 - 11:25 am UTC
a listener can be running on a machine totally separate and distinct from the database itself.
that is what they mean by "are remote to the listener"
common in an OPS/RAC environment where a single listener is servicing MANY instances -- each instance being on a different machine.
Number of listeners required?
Tony, May 12, 2003 - 12:39 am UTC
Tom,
How to decide the number of listeners required for a database?. How many connections should we have per listener?
May 12, 2003 - 7:39 am UTC
one listener.
the connections are transient, you connect to a listener to get to a database. the listener will not have any connections except during the initial login.
automate startup and shut down listerners on sunOS
Leonard Anukam, May 12, 2003 - 5:18 am UTC
But how about windows
Listener Load Balancing
Tony, May 13, 2003 - 5:24 am UTC
Tom,
What is Lister load sharing and when should we go for Listener Load sharing?. Is there any limit on number of concurrent connections Listener handle?
May 13, 2003 - 7:28 am UTC
you switch load balancing and sharing, assume you mean balance.
it is used in a multi-instance setup (like RAC or OPS). There are N instances, all equivalent (all get to the same database). The listener will balance the load across all available instances so that no single instance gets too much work and gets overloaded while the others are sitting idle.
Dynamic Service Registration?
Tony, May 13, 2003 - 5:29 am UTC
Tom,
What is Dynamic Service Registration? How to enable it?
Is Listenr not required if dynamic service registration is enabled?
Service Handler
A reader, May 29, 2003 - 5:08 am UTC
What is Service Handler.
May 29, 2003 - 8:19 am UTC
it is a named service (instance or set of instances) that registered with a listener
it is how you connect to the database (well, you can still use a sid but service is prefered)
re:Service Handler
JD, May 29, 2003 - 12:44 pm UTC
What Is diff. between Service handler
when database is in Dedicated/MTS mode.
May 29, 2003 - 1:54 pm UTC
nothing
a database is never in "dedicated" or "shared server"/MTS mode.
those are methods of connecting, they are not modes the database "runs in". A database can do both at the same time (they would each have their own service handler)
orastart on oracle 9
Andy, September 03, 2003 - 3:18 am UTC
Back to your orastart script Tom - to adapt this to oracle 9.1.2, is it simply a matter of using sqlplus instead of svrmgrl, and logging in as sys instead of internal?
September 03, 2003 - 6:59 am UTC
yup
RE: oracle 9.1.2
Mark A. Williams, September 03, 2003 - 10:39 am UTC
> oracle 9.1.2
Is there such a release as 9.1.2?
- Mark
September 03, 2003 - 12:02 pm UTC
nope.
there was a 9.0 and a 9.2 but there is no 9.1
Automatic Startup of DB and APPS on Reboot..... Good or Bad
Robert, September 04, 2003 - 5:50 pm UTC
Hi Tom,
Can you please help settle a difference of opinion.
Regarding automatic startup scripts to startup the Database and FinApps processes...
Is there a down side to doing this?
One opinion is that this is a bad idea, because you could have problems if the database and Apps (e.g. concurrent managers) started up automatically after being shut down in some kind of crash (e.g. someone trips over the cord to the Sun box).
What is your opinion of this?
Thanks,
Robert.
September 05, 2003 - 3:25 pm UTC
tell me what problems they anticipate?
a well designed startup script should be OK -- i mean, a human will make more mistakes then a script that has been tested.
Potential Problems with Automatic Restart
Robert, September 08, 2003 - 1:09 pm UTC
Tom,
Oracle Applications 11.5.9
Oracle Database 9.2.0.3
Solaris 5.8
Here are the concerns as I understand them.
1. Database. Suppose a radical problem happens to the database such as a corruption, bad disk, deleted datafile, etc. The concern is that when the database is restarted using the auto start scripts, there could be serious problems, which could have been avoided if someone had been alerted beforehand, taken a look at the alert logs, etc., fixed things, then started the database manually.
2. Oracle Applications. specifically Concurrent Manager jobs. Suppose one or more CM jobs are running when the database crashes. Suppose these jobs are (1) in various states of update and/or (2) one in a series of jobs (i.e.: request set) which have dependencies before and after it. The concern is that when the CM processes automatically get restarted, the CM will begin processing these jobs which may either (1) be in an 'unstable' state, or else (2) skip a failed, unfinished job, which could cause the wrong data to be updated or not updated, etc.
Thanks,
Robert
September 08, 2003 - 1:59 pm UTC
tis up to you.... if you don't feel comfortable with it, by all means don't do it.
the cm jobs shouldn't be in an unstable state, but if you think a human can glance at it and say "ok to start", that is what you should do I suppose.
Can I start a remote listener?
TH, September 13, 2003 - 3:52 am UTC
Dear Tom..
1. Can we start a database listener from another machine. (Windows based)?
2. When I connected through a tnsname, listener was required. I tried to connect without host string it get connected even though the listener was not started. What is the magic? The listner is not mandatory in a server?(I tried the connection in the server itself)
Thanx,
Thaha, Bahrain
September 13, 2003 - 9:28 am UTC
1) yes, you can -- using shared server configuration
2) when not connecting over a network, you need no listener. your client process will directly "fork and exec" the dedicated server (unix) or get in touch with the server and have it create a thread (windoze)
Mark, April 20, 2004 - 1:12 pm UTC
Tom, Thanks for your Time.
I know your not a windows guy. But do you, or one of your co-workers, know how to create/delete/modify(change to auto/man) a Listener service via command line? Like ORADIM does for the instance service.
Thanks
April 20, 2004 - 3:30 pm UTC
to create the listener service -- simply
c:\> lsnrctl start <listener name>
the listener will itself create the service if it does not exist.
to modify services (delete/modify) from the command line -- we'll have to ask the windows people out there to comment on tools.
oradim will do it. Right?
Nasir Sarwar, April 20, 2004 - 3:55 pm UTC
tool for command-line service manipulation
Mark A. Williams, April 20, 2004 - 6:33 pm UTC
Download the sc.exe tool from Microsoft. It gives you complete command-line control for services. It is included in XP, but is in the freely downloadable portion of the resource kits for other O/S'es as I recall...
- Mark
A reader, April 20, 2004 - 9:22 pm UTC
Thiru, July 13, 2004 - 7:15 am UTC
I created db in oracle 9.2 on linux. I gave lsnrctl start but I dont see any services started. The listener supports no services is displayed. What does this indicate?
Thanks
July 13, 2004 - 11:48 am UTC
that you are using dynamic registration and after you startup a database (or just wait a short period of time if the database is already running) the services will "appear"
pmon registers it's services with the listener.
Auto startup
Devarshi, September 03, 2004 - 4:07 am UTC
We have unixware 7.1.1 and oracle 8.1.7 installed on our machine. I read this page. I wanted to know
1) in SCO unix ware have to change #!csh to #!ksh
2)what does the line
ctxsrv -user ctxsys/ctxsys>&/export/home/oracle8i/tmp/ctx.log &
achieves
It runs on the background .right? what else ?
3) There is a scrip dbstart in $ORACLE_HOME/bin/
(and a dbstop)
Are they any way diffrent from the simple scripts you have provided.
Devarshi
September 03, 2004 - 10:03 am UTC
1) use whatever scripting language you desire to use. that is entirely your choice
2) it redirects stdout (>) and stderr (>& is stdout and stderr in csh) to that log file and then backgrounds it
3) i do not use dbstart and dbshut. dbstart/shut rely on the /etc/oratab or /var/opt/oracle/oratab or where ever they decided the oratab goes on your OS. I prefer to just "do it myself"
q on listeners
A reader, September 03, 2004 - 4:46 pm UTC
Hi Tom
Consider the case where one listener is serving
multiple servers db1 and db2 on a machine. In this
case they have to use diffeerent ports for listening;
in this case if I do a tnsping, I should be able to get
an "OK" message for both ports, correct? By "OK" I mean
a message to the effect that a listener is serving a db
on this port.
Thanx!
September 03, 2004 - 6:16 pm UTC
no they do not?
that single listener would normally use a single port.
thanx!
A reader, September 03, 2004 - 8:10 pm UTC
That means all users share the same port to connect
to different dbs (e.g. using host/port combination
you would have:
user1 - host1:port:db1
user2 - host1:port:db2
correct?
thanx!
September 04, 2004 - 10:53 am UTC
absolutely, you have one listener, they all goto the same process at the end of the day, they can all use the same ip:port
another q
A reader, September 03, 2004 - 11:33 pm UTC
"that single listener would normally use a single port. "
how common is this practice of having one listener
being shared by multiple dbs? Does it serve any purpose?
thanx!
September 04, 2004 - 11:03 am UTC
it is lots easier then having a port per instance?
let me ask you -- does having a port per instance serve any purpose?
orastart
Andrea, December 01, 2004 - 10:49 am UTC
As a new DB admin, who just installed 9i -- this was good infomation. I'm now at the point where I have to tune everything, and I'm learning what to tune as I go. automating my db starting process is first.
The thing I need to know, Tom is your scripts are written for 8i, can I still use them for 9i?
December 01, 2004 - 11:02 am UTC
same concept works -- it'll be sqlplus instead of svrmgrl
sqlplus "/ as sysdba" <<EOF
whatever
EOF
Different os accounts for oracle versions?
Doug, December 01, 2004 - 4:57 pm UTC
From the top of the thread, it looks like you have a different oracle account for each major version of oracle you are running, i.e, "su - oracle8". Do you find this advantageous when you have multiple installations? 8,9,10 etc., oracle8 oracle9 oracle10 ?
December 01, 2004 - 7:52 pm UTC
yes, I personally do as I run lots and lots of test instances -- i have 7.1 on up. I have a separate oracle account for each install, separate oracle homes, separate everything.
if you are on windows -- ignore everything I just said.
Auto register
A reader, December 10, 2004 - 5:23 pm UTC
Tom,
How do I block auto register off when I have multiple listeners. I want to have each database with its own listener
December 10, 2004 - 8:14 pm UTC
set the init.ora parameter
show parameter listener
listener,tnsnames entry
reader, December 21, 2004 - 3:01 am UTC
Tom,
we have two databases one in 9i and the other in 10g. Can the listener and the port number be the same for both? How it should be?
In the above situation,how the tns entries should be to establish connectivity with both the databases?
Give some examples or some source of information in this regard.
Regards
December 21, 2004 - 8:59 am UTC
if both databases are on the same machine, there should be ONE listener (the 10g) and both databases would register with it.
So, yes the listener and port would be the same for both -- since there should be at most one listener on the machine.
automating oracle 9i startup
Andrea Savage, February 04, 2005 - 1:44 pm UTC
Tom,
after a couple of adjustments to your scipts, I can start oracle via command line, but I am still unable to get the start via boot. any idea's? I'm running oracal 9i and solaris 8.
The scripts are sweet, and just what I needed.
Alexander the ok, June 08, 2007 - 9:19 am UTC
Tom,
Any reason why you think one listener is enough for multiple instances? Don't you think it would be better to have one per instance, so If you lose a listener, people can still connect to the other instances?
June 09, 2007 - 10:30 am UTC
how do you "lose" a listener.
I would lose the other databases, if you have more than one on a server - that is something you've done wrong.
Alexander the ok, June 09, 2007 - 3:00 pm UTC
Wouldn't that be the day. In a perfect world, yeah that would be great, one database per machine. The people who make budgets and design environments would never agree to that in 1000 years. They are trying to cram as much as the can in one place so there's less maintenance for servers.
Frankly you're the only person I've ever heard say they have the freedom to do that. I suspect it's because you aren't an ordinary dba so people will actually listen to you. Are you a manager too? Can you just say "I want x y and z" for a new project? Where I come from dbas are just another piece of the puzzle. If we want something that's expensive (RAC for example), we usually never get it.
Don't forget you work for the #3 software company in the world. Not all of us can clap our hands and say "let there be servers".
June 10, 2007 - 9:26 am UTC
it is common sense.
#2 by the way.
http://en.wikipedia.org/wiki/World's_largest_software_companies And by the way, I clap and say "LET THERE BE FEWER SERVERS". For the fact of the matter is - I would not be asking for a machine per instance that you already have (that would not be smart). I would be consolidating the instances I have into as few as possible.
If you can put five instances with five applications on a single machine and that machine is full - then you could put all five applications into a single instance and have room for at least one or two more applications - at least.
Consolidation - it is something that people actually are doing. It isn't a pipe dream.
A new project does NOT demand brand spanking new hardware. Look at what disk management via SAN/NAS has done. Pool a resource.
Exactly the same thing we've been talking about here at Oracle for many years.
I too try to cram as much as I can into a single machine - and the way to do that is to have as few instances as possible - one being best.
Alexander the ok, June 12, 2007 - 5:08 pm UTC
But on the flip side, now all your eggs are in one basket. When you have to patch, upgrade, whatever, now you're effecting tons of applications instead of a few. Much harder to negotiate downtime or "quiet time" to do so.
Also what if you need to restore the database? You can't without hosing everyone else. (Sure you may get lucky and have enough undo retention to use flashback, maybe not though.)
June 12, 2007 - 5:42 pm UTC
that is AWESOME.
you have to get them all, get them all in one fell swoop. It actually forces the issue.
If you let yourself get 10 instances, all I know if you'll end up with 10 different versions of oracle - 100% sure of that I am.
Much easier to have a draconian policy that says to everyone "hey, we are going - if you want to be there with us, you will plan your upgrade NOW". If you let people stagger it and do it when they feel like it, they never will.
Especially in light of security related issues - all for one, one for all.
tablespace point in time recovery (PITR) has been around for many many years. block level recover, file recovery, tablespace and tablespace PITR - I can recovery whatever I want, as little as I want.
Multiple log writer
Joshi, March 08, 2009 - 1:23 pm UTC
Tom,
I was looking at this healthy discussion on whether to have one/fewer big database with many schemas/apps or have more databases with one/fewer schemas/apps per database. I agree both have their advantages. With multiple databases : they have their separate SGA, background processes and sometimes they could be overhead and wastage instead of one/fewer databases. Advantage could be that they have their separate background processes and less contention on write to disk by log writer etc. Here I am talking heavy update volume. And high log file sync waits. Or high disk i/o. Still I/O is limited by the hardware. However, it could help for contention for CPU by log writer, db writer or other processes : by having multiple log writer, db writer. Am I making sense or off the mark? IS it possible log file sync waits are because : the lone log writer is not able to cope with the heavy write. That the very processes waiting for the log file sync are blocking out log writer from getting cpu. Please comment. Thanks
March 08, 2009 - 5:11 pm UTC
Let me put it this way:
one server per host.
You need, can only support, want to have, need to have - at most one server per host.
And if lgwr was to become a bottleneck - you can use RAC in clusters to get more than one for a single database.
but on a single host (be it a real host, a virtual host, whatever) at most ONE instance should be run, period.
Listener getting overloaded
Shamseer, August 28, 2009 - 3:21 am UTC
Tom,
In our PROD database; which is connecting to Portal-Infranet application (Oracle BRM); the listener is getting overloaded.Because of this data loading is becoming slow.Once we restart the listener the data loading picks up.We can understand the load of the listenr using 'topas' out put where the 'tnslsnr' process comes to the top of the list.
Is there any way to reduce the load for the listener?Is it advisable to configure more than one listener in a database; so that application loading can be routed to seperate listener?
Expecting your reply.
August 28, 2009 - 5:20 pm UTC
... the listener is getting overloaded ...
are you sure? that is almost "not possible"
for you see, a listener is involved in:
a) connect
and nothing after that, the data doesn't flow through the listener, the listener only does work on the connect and then gets out of the loop.
when you stop the listener, you also stop your load - sounds like false cause and effect - why is your load process connecting more than once?
Listener Overloading
Shamseer, August 31, 2009 - 5:38 am UTC
Tom,
Many Thanks for your response.Yes you are right. Listener is only involved in client connections.
In our case per second approx 30+ sessions are getting connected to the database (all client connection).The client application is called IREL - which is nothing but SQLLOADER. So per second 30+ SQL Loader connections are happening.Once loading is done; it will give way for new connections.Please find the TOPAS output below:
Name PID CPU% PgSp Owner
oracle 5361718 2.5 7.6 patrol
oracle 7491640 1.6 4.8 orapin
oracle 5079072 1.2 20.6 orapin
tnslsnr 7524524 1.1 78.9 orapin
..........
..........
From the above we can see the listener process has almost reached the top list resource consuming processes.
Once we restart; it wont appear in the above output for some time; but after a while it start popping up.
Seems to be a strange problem; but we are in a fix without knowing what to do..
Regards
Shamseer
August 31, 2009 - 7:41 am UTC
... So per second 30+ SQL Loader connections are
happening. ...
that does not compute, why would you experience 30 connections per second for any sustained period of time?
why wouldn't it connect 30 times (once) and then load???
... From the above we can see the listener process has almost reached the top list
resource consuming processes.
...
how do you figure that??!?!?! I see something that is taking 1.1% of a cpu?
explain in detail why you believe it is saying what you believe it to be saying. And also, explain in detail what you believe you are saying " has almost reached the top list resource consuming processes" is not very clear.
Alexander, November 13, 2009 - 11:23 am UTC
Hey Tom,
I've just setup an 11.2 database on a server with other 10g's. I'm trying to create a listener for 11g, it seems it automatically registered itself with my 10g one (God only knows why).
I CANNOT get it out of there, I created tnsnames and listener .ora's:
/oracle/product/11g/network/admin
(x6a6kdc:oracle)> cat listener.ora
SID_LIST_LISTENER_11G =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/11g)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = OCP11G)
(ORACLE_HOME = /oracle/product/11g)
)
LISTENER_11G =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x6a6kdc.lmig.com)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
/oracle/product/11g/network/admin
(x6a6kdc:oracle)> cat tnsnames.ora
OCP11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = x6a6kdc.lmig.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = OCP11G.LMIG.COM)
)
)This is what happens:
(x6a6kdc:oracle)> tnsping OCP11G
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 13-NOV-2009 12:13:30
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = x6a6kdc.lmig.com)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = OCP11G.LMIG.COM)))
TNS-12541: TNS:no listener
(x6a6kdc:oracle)> lsnrctl start LISTENER_11G
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-NOV-2009 12:13:40
Copyright (c) 1991, 2009, Oracle. All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been started
I have no listener params set (local, remote). Part of my confusion is the difference between the listener name and alias. This is killing me we have Oracle coming Monday morning for a POC. Thanks so much.
November 15, 2009 - 3:07 pm UTC
lsnrctl start 11g
no listener_
how to startup a database remotely
Pratul, July 09, 2010 - 12:56 am UTC
How to start database remotely and with static registration of services in listener