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/22.214.171.124 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.
14 Responses to “11G R2 Cluster: AVOID using sqlplus & lsnrctl for Oracle”
Leave a Reply