Half a year ago, my colleague Remco wrote an article on auto starting the listener and the databases after a host reboot. As usual with Oracle, there are several solutions. In a previous job, I learned to appreciate Oracle Grid infrastructure to do the same. And then some more.
Oracle Grid Infrastructure can be downloaded and used for free. It serves many purposes, especially for ASM and RAC,but as it turns out, it can be installed as ‘software only’ and still serve a purpose known as Oracle Restart.
So why not use the old familiar dbstart and dbstop scripts?
Here’s why: Data Guard. Many applications, including Weblogic Connection Pools, use a long connection string that contains host name and service name of both (or more) instances of a Data Guard installation. Suppose host A with instance Prim represents the primary database. Now suppose the application wants to connect to the host A and SID Prim but it can’t get a connection immediately through the listener (this actually might happen more often than you think). The application will behave as expected, which is to look for host B with SID Prim. That might very well exist, but since that instance is a standby instance, it will answer with a connection refused. And that often is worse than not finding an instance at all.
What we want is to connect to a service rather than to a SID. Services are completely customizable per instance. So why not have a service that only exists if the database is a primary database?
This mechanism exists for years already and used to be taken care of by using triggers, activating the service upon opening the database. Remember, a standby database doesn’t open, so that particular service won’t be started.
Doesn’t it? Not in the past, but now we have active standby. Oops, there’s an unwanted service.
Grid Infrastructure allows you to start services depending on the role of the database: Primary, Physical Standby, Logical Standby or Snapshot Standby.
It also enables you to automatically start the database and listener upon host startup.
It even enables you to immediately and automatically start processes when they crash. Try killing process pmon for instance. That’s basically killing your instance. You’d have to discover this happened and then restart your database manually using sqlplus.
With this new software, it will be detected automatically and restart your database before you noticed it was down.
Time to show how it’s done.
Download the Oracle Grid software and have it available at your host.
Login as user oracle and go to the directory where you unzipped the software, called ‘grid’. Type
[oracle@oraclelinux6 grid]$ ./runInstaller
The options are pretty clear. Choose the last one and click Next.
No comment needed..
I still prefer the familiar oinstall group and for this example it’s not important anyway since we won’t be using ASM.
Click Next.
Depending on your choices you might get one or more warnings. You can safely ignore them.
This is the Oracle default. Make sure you at least have /u01/app, owned by oracle:oinstall.
I highly recommend using these Oracle default directories, it makes life easier at so many levels.
Click Next.
Issues will occur. Investigate and fix them, or check Ignore All if you are sure of what you are doing. Personally I don’t need swap on a database server so at least that one I ignore. And in this particular case, I didn’t solve the resolv.conf issue either.
Click Install.
An overview of the choices you made. Check them and Click Install.
Now the software will be installed which might take up anything between 1 and 20 minutes. When it’s finished you’ll see the next screen:
Open an extra terminal as user root and execute the script /u01/app/11.2.0/grid/root.sh
Look at the output. It should contain the lines
To configure Grid Infrastructure for a Stand-Alone Server run the following command as the root user: /u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl
This is the most tricky part: even if you think you’ve met all requirements, you might very well run into errors. Luckily it’s quite well documented on the interweb.
Once done, click Finish in the main screen and the screen will close. You are done installing.
Next, we create a database with dbca. You can also use grid control to do so. Once you filled out all parameters and start creating the database, notice there is a line in the progress window, stating
Registering database with Oracle Restart
And now for the real thing.
The main reason we installed Grid infrastructure is a feature called Oracle Restart. It can be checked and configured using the command ‘srvctl’. Let’s explore its possibilities.
First, check which databases are controlled by Oracle Restart
[oracle@linux63 ~]$ . oraenv ORACLE_SID = [oracle] ? orcl The Oracle base has been set to /u01/app/oracle [oracle@linux63 ~]$ srvctl config orcl [oracle@linux63 ~]$
There’s one database registered by Oracle restart, our just created orcl.
Let’s check out this database:
[oracle@linux63 ~]$ srvctl config database -d orcl Database unique name: orcl Database name: orcl Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1 Oracle user: oracle Spfile: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Database instance: orcl Disk Groups: Services: [oracle@linux63 ~]$
The original goal of installing Grid infrastructure was to automatically start the database upon starting or rebooting the host.
So I rebooted (not visible here) and checked:
[oracle@linux63 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 6 12:05:55 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production SQL> select status from v$instance; STATUS ------------ OPEN SQL>
Good, that worked.
I also claimed that it would start the listener automatically. Has it done so?
[oracle@linux63 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 06-AUG-2014 12:12:46 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused [oracle@linux63 ~]$
That’s disappointing. Or is it? Maybe we should add the listener (default name LISTENER) to the srvctl configuration and start it:
[oracle@linux63 ~]$ srvctl add listener [oracle@linux63 ~]$ srvctl config orcl [oracle@linux63 ~]$
Pity, it doesn’t show the listener. But it might be there:
[oracle@linux63 ~]$ srvctl config listener Name: LISTENER Home: /u01/app/11.2.0/grid End points: TCP:1521 [oracle@linux63 ~]$ srvctl start listener [oracle@linux63 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 06-AUG-2014 12:15:48 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 06-AUG-2014 12:15:38 Uptime 0 days 0 hr. 0 min. 9 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora Listener Log File /u01/app/11.2.0/grid/log/diag/tnslsnr/linux63/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.73)(PORT=1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@linux63 ~]$
I’ve rebooted the host at this time and sure enough, both the listener and the database started automatically.
So, we’ve met our target: both the listener and the database started without any human intervention. But it would be a shame to stop now since there are many more options in Oracle Restart. One in particular I mentioned in my introduction.
As we all know, or should know, one should connect to a database using a service, not to a SID. Let’s take another look at the Oracle Restart configuration of this specific database:
[oracle@linux63 ~]$ srvctl config database -d orcl Database unique name: orcl Database name: orcl Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1 Oracle user: oracle Spfile: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Database instance: orcl Disk Groups: Services: [oracle@linux63 ~] $
The last line, Services, is empty. Now we’ll add a service with the name ‘production’. As you can see we need to tell to which database this service should refer:
[oracle@linux63 ~]$ srvctl add service -s production -d orcl -l PRIMARY [oracle@linux63 ~]$
Look at the last parameter, -l PRIMARY. This tells Oracle Restart that this service should be started only if the database_role is PRIMARY. Redundant for a standalone database but in case of a dataguard configuration this is critical: The service ‘production’ will only be available if the server is the primary server. A standby server will never be reachable through this service name.
At the standby host you can add this same service on the standby database configuration. The service will only be started if that standby database becomes the primary database and that’s exactly what we want.
Okay, start the service and check:
[oracle@linux63 ~]$ srvctl start service -s production -d orcl [oracle@linux63 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 06-AUG-2014 12:00:59 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 06-AUG-2014 11:59:32 Uptime 0 days 0 hr. 1 min. 26 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /u01/app/oracle/diag/tnslsnr/linux63/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux63.local)(PORT=1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "production" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@linux63 ~]$
Again, I rebooted the host and the service was there afterward without human intervention.
And that’s it for this blog. There are many more options and I advise you to go and play with them to get a feeling of all the possibilities.
One last command here, to get you started:
[oracle@linux63 bin]$ srvctl -h
Hi,
I think this is a great feature and I used it to standardize restarting databases across different operating systems. Unfortunately it has been deprecated in Oracle 12c–a fact you should mention when promoting its use.
Regards.