For last few months I was part of the Pythian’s team helping implement Oracle 11gR2 Clusters for different Oracle customers. All the implementations had different requirements and configurations, however, in all cases, the client’s DBAs made the same mistake over and over again. They used sqlplus and lsnrctl utilities to manage (start/stop) databases and listeners. This is totally wrong in the 11gR2 Cluster world. In fact this is wrong even starting from 10g. The 11gR2 way of managing processes makes it even worse.
Just before explaining why it is a bad idea I would like to mention the right way of doing things. The following commands are just few examples on how you should start/stop Oracle processes:
srvctl start instance -d MEGADB -n carhost02 -o open srvctl stop instance -d MEGADB -n carhost02 -o immediate srvctl stop scan_listener -i 3 srvctl stop listener
The good news is that you do not need to remember the syntax. Just type “srvctl” and hit Enter. You will see a good overview of the commands available. To get an extensive help you add “-h” at the end. For example if you would like to see what mandatory parameters and additional options available for Oracle instance starting process just enter the following command:
srvctl start instance -h
Let’s get back to the topic of why it is bad to use sqlplus or lsnrctl commands in 11gR2 Clusters configuration. There are several reasons why you should avoid using the “old” way of managing Oracle processes:
– A – Listeners configuration in 11gR2 is dynamic and managed by cluster. There is no need to manage/edit listener.ora file directly anymore. In fact if you do so you will end up with a mess. Oracle Cluster (agents) manages the listeners configuration dynamically adding and dropping different configuration items from/to relative listener.ora file. If you try to start an Oracle listener using lsnrctl utility in one possible scenario you will still be able to start a listener however it may be using default 1521 on all IPs available on the host and it may be in conflict with Listeners configured and managed via cluster.
– B – In 11gR2 Cluster configuration all listeners are running under Infrastructure home and separate (by default) OS user (typically grid user). I have seen it many times when Oracle DBAs start listeners under oracle unix user (DB oracle home). This is wrong again. There are no correct listeners’ configuration files under DB oracle directory. Starting Listener under different OS user may conflict with already running listeners and confuse Oracle Cluster.
– C – In talking about staring database (instances) using sqlplus, the problem is that Oracle Cluster starts/stops an Oracle instance using pre-configured non-default SPFILE location. You can see the location of the SPFILE Cluster uses to start/stop database’s instances using the following command:
/export/home/oragrid>srvctl config database -d MEGADB Database unique name: MEGADB Database name: MEGADB Oracle home: /u01/db/mage/11.2.0.1 Oracle user: oramage Spfile: +DATAG1/MEGADB/spfileMEGADB.ora Domain: WORLD Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: MEGADB Database instances: MEGADB1,MEGADB2 Disk Groups: DATAG1,FRA Services: MEGADB_10g… Database is administrator managed /export/home/oragrid>
As you can easily can figure out if you start Oracle instances using sqlplus Oracle tries to use the default spfile location ($ORACLE_HOME/dbs). If you have a pfile or spfile located there this way of starting the Oracle instance will lead to totally different results than starting it using srvctl Cluster utility.
The main message of this blog post to any Oracle DBA is:
– If you are working in Oracle Cluster environment starting from 10g, start using the srvctl utility. Otherwise you will end up in a messy and unmanageable configuration.
Yury
13 Comments. Leave new
Hi Yury,
I guess the same should be considered for e-Business Suite implementations, right? Do you have any suggestion for eBS DBAs based on your experience and mistakes you’ve seen in configuring 11gR2 Clusters for eBS too?
Maris
>>> I guess the same should be considered for e-Business Suite implementations, right? Do you have any suggestion for eBS DBAs based on your experience and mistakes you’ve seen in configuring 11gR2 Clusters for eBS too?
Hey Maris! I am glad to see you around! How is life there on the other side of the planet?! :)
The suggestion is generic. In fact if I am not mistaken than latest ATG version for R12 supports SCAN and other 11G clustering features.
I would say that it isn’t 100% clear at this stage what would be the final “best practices” design for EBS in that context. This is still ongoing process and we may expect to see the final Oracle’s vision in next 6 months.
We do have a few clients who are “earlier adopters” for 11GR2 Clusters in EBS environments. From what I can see there are plenty of options to configure Oracle Cluster for EBS. Some main points could be as following:
– Main SCAN listener configuration should be significant for all EBS needs. However if necessary you can create a custom Cluster registered (managed) listener for EBS specifically.
– I still would suggest using srvctl for the processes management on DB side (aka spilt configuration where you manage DB separately from Apps)
– Most probably Oracle will utilise services’ concept more on future EBS releases. Therefore it make science to start using those today.
– Keep services count reasonably small. It is hard to manage a configuration after services count reaches 20 :)
– Let Cluster manage processes as much as reasonable in your case. Keep in mind that you can re-configure it to meet almost any requirements. However more away you are from standard (default) setup more luckily you hit some issues (not discovered by others ;). Keep it simple in other words :)
See you around,
Y
Hey Charlie,
>> For sqlplus I disagree.
Thank you for following our blog.
Well I am not saying that you can’t use sqlplus. The only point I am trying to make is that it is good time to start using srvctl instead of previous way of managing Oracle processes. It makes it much easer to manage and support a configuration. In fact to make Oracle use init.ora, listener.ora configuration you need to run through more re-configuration steps (read customize the default configuration) than to use the default SPFILE configuration. On top of that if you don’t know all the details around Oracle 11GR2 Clusterware you at risk to miss something and get unpredictable results.
On the other side it is obvious where Oracle is going in that area and it much easier to start using it and build you mussels (knowledgebase) rather to keep it in the old way and fight related issues.
Anyway there is not universal “best way” there is good way for your circumstances/knowlage/environment/requirements :)
Thank you for sharing your experience once again,
See you around,
Yury
This is good advice all RAC DBA’s should follow. It took me a while to figure out why services would not dynamically register with my listener when I started it. That was because I was starting the listener the old fashioned way, “lsnrctl start” instead of using srvctl. I’ve also seen where starting a database with SQL*Plus results in services not registering with the listener. As Yury says…start it with srvctl and you shouldn’t have these problems.
Cheers,
Brian
Hi Yury,
Really nice post and we feel lucky that we found this post.
I think we are in similar problem, Please see below and help us.
We have following configuration:
11gR2 Grid Infrastructure: we used port#1448 for scan listener during installation.
Then created 10gR2 RAC database with default configuration
Initially after database creation, database was able to register with local listeners but similar mess up happend that you mentioned in this post.
The current status: we restarted the nodes one by one with hope that database will register itself with listener but currently when we check crsctl stat res -t, it shows all services, listeners are up and running but database is not registering to any listener so we are unable to connect to database.
will be thankful if you please connect us at [email protected]
Please advice and help us
Hey Adam,
Thanks for following the blog.
In regards of SCAN infrastructure troubleshooting may I suggest you to have a look on the following related Presentation and White paper?
https://www.slideshare.net/yvelikanov/oracle-11g-scanconcepts-and-implementation-experience-sharing-12088418
https://www.slideshare.net/yvelikanov/oracle-11g-scanconcepts-and-implementation-experience-sharing-whitepaper
Those have a troubleshooting section where I mention main things to check. In case of 10G DB under 11G Infrastructure you may want to exclude “listener_remote” part (imho it should work if you decide to use SCAN).
If it doesn’t help or you are looking for faster resolution you can involve me (via Pythian) in the troubleshooting process.
Yury
Hello Yury,
for standalone 11gR2 installations do you prefer using srvctl for managing components or old-fashioned way with sqlplus/lsnrctl?
With Grid Infrastructure and Oracle Restart I think that using srvctl utility is recommended but I don’t have an idea how widely is used among DBAs.
What are your experiences?
Little off-topic question – sorry for that :)
Thanks for answer!
Regards,
Marko
Hello Marko,
Thanks for the question
>> for standalone 11gR2 installations do you prefer using srvctl for managing components or old-fashioned way with sqlplus/lsnrctl?
My answer would be: Keep it simple :) … unless it complicates things
If you don’t use ASM and GI in your stand alone 11GR2 implementation then I would go with the old fashion standard way (in fact you can’t do other way as GI isn’t there ;)
As soon as you have 11GR2 ASM and GI I would suggest to use srvctl for normal operational functions.
PS IMHO: You still should use direct utilities as SQL*Plus for troubleshooting and special cases purposes.
Yury
Hello Yury,
I agree with you that you should use srvctl utility for managing resources if you have GI+ASM.
I’ve asked question wrongly so I should reformulate it a little :)
From your experience do you see ASM+GI widely adopted in standalone configurations? I’m asking because it’s Oracle recommended solution.
My humble opinion:
For standalone installations, configurations with GI+ASM are more complicated solutions then configurations without GI+ASM. Besides that GI takes additional resources (RAM/disk).
Nevertheless I think that installations with GI+ASM would become “best practice” for all installations (RAC and standalone) in short time.
Can you share your opinion on that.
Regards,
Marko
I completely agree with your opinion Marko. I think that GI+ASM may be overkill for many stand alone systems. File system is a vary good way to keep things simple. However saying that there is always “it depends …” :) Use your judgement and don’t take any advice (even Oracle’s, well especially Oracle’s) for granted.
Answering your direct question: No I don’t see too many ASM+GI in many stand alone systems. In fact I didn’t see one yet :)
PS DBAs tends to take a complex solutions just to keep them entertained :) Don’t judge them to hard :)
#monday_mood
I’m not using GI+ASM in standalone installations.
Currently, I’m playing with GI+ASM in test environment – to entertain myself :-)
It is nice to be prepared – just in case.
Thank you again for info from your experience.
Regards,
Marko
I think, rac one-node is the future trend of standalone installations for many shops.
Any preferences on RELOADING a net8 listener without using lsnrctl in a RAC environment?