Oracle Labs by Yuri Khazin, Oracle DBA

Linux and Oracle – automatic DB startup and shutdown


Linux and Oracle – automatic DB startup and shutdown

If you are reading this article you are likely to have encountered a problem with either startup or shutdown of your database on the server.  Is there indeed a problem? There is. If you followed post installation instructions your database on UNIX-like platforms  would start automatically (in most cases) but would not properly shut down when the server goes down. What do we mean by proper database shutdown? The database needs to be told to “shutdown immediate” so that all database files are in sync with control files. What happens when your server is shut down? Well, the OS tries to stop all the known services and processes it can think of, then it sends “term” and “kill all” signals to all the processes that are still around. Among those happened to be your database, so it quietly dies while its files are all in disarray. The database is in quite a messy state when it is a time to start up again. Read your alert logs. Have you noticed that database is doing instance recovery almost every time it starts? It has to reconcile all the files before it can start up. Oracle does a good job in recovery and most of the time database starts just fine. So where is the problem? The real problem starts when the database becomes corrupted during server shutdown. What next? It can not be recovered at the startup and so you will need to do a backup restore first and, probably, lose some of your data.

Is this a proper way to handle your precious (hiss) database? I do not think so. Let’s look at the details.

What causes the problem? Oracle installer does not always follow specific rules of a particular OS platform. Starting up is straight forward: the OS executes a script in /etc/rc.d unconditionally.  Shutting down is more tricky. When stopping processes and services the host OS wishes to make sure the service has actually been started before it tries to stop it. This is achieved by checking for a special file in the /var/lock/subsys directory (on Linux and some UNIX platforms). If the file is there the OS will proceed to execute an appropriate /etc/rc.d script. If the file is not found the OS will bypass shutdown script. Since Installer did no take care of this part, on most installations the proper shutdown is bypassed.

This problem does not exist on Windows platforms as Oracle is properly configures itself as a service. Surprise!

How to overcome the trouble? The details of setting up shutdown script for Oracle database are described in this article. The explanation applies to Oracle 10.2 (10g Enterprise Edition) on Linux platform (Red Hat and alike).

Oracle database can be treated as a service on Linux as well and as such, being properly controlled when server is moving from one runlevel to another. Unfortunately, there is still no cure for server being unplugged from power or some hardware failure (we are not talking here about standby, RAC or other high availability solutions so do not try to catch me).

Red Hat, as well as most of its derivatives, uses run levels listed below:

  • 0 – Halt
  • 1 – Single user
  • 2 – Not used/User definable
  • 3 – Full multi-user, console logins only
  • 4 – Not used/User definable
  • 5 – Full multi-user, with display manager as well as console logins
  • 6 – Reboot

Oracle can be started on run level 3 and remains up at 4 and 5. It should go down when server is moving to levels 0,1,2 and 6.

Which services are started at which run levels can be managed with the chkconfig tool, which keeps its configuration files in /etc/rc.d directory.

Use following command to list all the services controlled by chkconfig and whether they are on/off for each runlevel:

/sbin/chkconfig –list

You will notice that usually Oracle is not listed among the services. We are going to fix that.

Here is how to configure your server to take care of Oracle (10g):

1. As user “oracle” mark Oracle SID entry at /etc/oratab as auto start (change N to Y).

2. Skip this section if you are on Oracle 11g or 12c. Keep reading if you are on Oracle 10g. As user “oracle” modify $ORACLE_HOME/bin/dbstart in the following manner:

Find the line with “ORACLE_HOME_LISTNER=”(somewhere around line 78)

If it reads like this:

ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle

then change it to read:

ORACLE_HOME_LISTNER=$ORACLE_HOME

Do not ask me who vikrkuma is and why he is so important that Oracle ships installation media for years with his name engraved in the core of a major script. Just take time to thank people that spent much time debugging the stuffy stuff.

3. As root, place the script listed below in /etc/init.d/dbora (yes, it is a new file you are creating)

Modify this script if you need to (you may not have OEM installed or you may have several differently named listeners). See next block of text for versions specific to Oracle 10g and 11g.

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle 12c auto start-stop script.
#
# There is a mentioning of ORACLE_HOME here as this script relies
# on .profile of oracle’s user
#
# Set ORA_OWNER to the user id of the owner of the Oracle databaseORA_OWNER=oracle

case “$1” in
‘start’)
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su – $ORA_OWNER -c “dbstart \$ORACLE_HOME”
touch /var/lock/subsys/dbora
;;
‘stop’)
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su – $ORA_OWNER -c “dbshut \$ORACLE_HOME”
rm -f /var/lock/subsys/dbora
;;
esac

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle 10gand 11g auto start-stop script.
#
# There is no mentioning of ORACLE_HOME here as this script relies
# on .profile of oracle’s user to find dbstart, dbshut and other
# utilities;
#
# Set ORA_OWNER to the user id of the owner of the Oracle databaseORA_OWNER=oraclecase “$1” in
‘start’)
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su – $ORA_OWNER -c “lsnrctl start”
su – $ORA_OWNER -c “dbstart”
su – $ORA_OWNER -c “emctl start dbconsole”
touch /var/lock/subsys/dbora
;;
‘stop’)
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su – $ORA_OWNER -c “emctl stop dbconsole”
su – $ORA_OWNER -c “dbshut”
su – $ORA_OWNER -c “lsnrctl stop”
rm -f /var/lock/subsys/dbora
;;
esac

Side note: You have probably noticed a weird file ““>/var/lock/subsys/dbora” in the script above. Many flavors of UNIX and Linux will not even look at your startup script if this file is not properly handled. Lack of awareness about this behavior is a source of frustration for many DBAs and System Admins. When O/S ignores your script during restart (reboot) this is a most probable cause.

Use the chmod command to set the privileges to 750.

chmod 750 /etc/init.d/dbora

4. As root, test if this script works properly by running it, first to start and then to stop database and listener:

/etc/init.d/dbora start

/etc/init.d/dbora stop

Analyze the output and fix errors, if any.

5. As root, associate the dbora service with the appropriate run levels and set it to auto-start using the following command:

chkconfig –add dbora

Use this command to verify the results:

chkconfig –list dbora

We expect to see this output:

[root@odban1 ~]# chkconfig –list dbora
dbora 0:off 1:off 2:off 3:on 4:on 5:on 6:off

Use this command to verify the creation of the control scripts:

find /etc/rc.d/ -name ‘*dbora’ -print

Check to see that S and K scripts were created. (scripts are in /etc/rc.d/rc*.d directories). We expect to see something like this:

[root@odban1 ~]# find /etc/rc.d/ -name ‘*dbora’ -print
/etc/rc.d/rc5.d/S99dbora
/etc/rc.d/rc2.d/K10dbora
/etc/rc.d/rc4.d/S99dbora
/etc/rc.d/init.d/dbora
/etc/rc.d/rc1.d/K10dbora
/etc/rc.d/rc3.d/S99dbora
/etc/rc.d/rc6.d/K10dbora
/etc/rc.d/rc0.d/K10dbora

“S” scripts are for the boot up time and “K” are for the shut down.

Now do an actual reboot and check if “S” and “K” scripts did work properly. The instances marked with “Y” in /etc/oratab should now startup/shutdown automatically at system startup/shutdown.

Note: Instead of “reboot” command, “init 2” and then “init 3” (or “init 5”) can be issued to test scripts at a smaller scale.

All relevant logs are here (run as “oracle”):

ls -l $ORACLE_HOME/*.log

Check alert log as well, at the server shutdown time the log must show “Shutting down instance (immediate)” and “ALTER DATABASE CLOSE NORMAL” commands.

That’s all to it. Good luck.

Advertisements

4 Comments »

  1. Another excellent article by Mr. Kazin. I have a very minor quibble, though: in both ‘start’ and ‘stop’ sequences I would reverse the order of the listener start/stop and database start/stop commands. To make a (rather poor) analogy with a shopkeeper: (s)he would open the doors to the shop (listener) once everything inside is ready for the customers, and at the end of the day would likely first close the doors and then do any necessary housekeeping chores before actually closing the shop.

    Thanks again for the excellent contributions to the group’s knowledge base !

    Comment by Michael Serbanescu — November 19, 2009 @ 15:29

    • Thanks for commenting, Michael.
      Let me defend the order of starting things up. When listener is present the starting database will register itself with it. Another reason for keeping listener first in the order is that you will be able to connect as sysdba (remotely) even if your database is down (or failed to start).

      Comment by oraclelabs — November 19, 2009 @ 17:14

  2. Good, This script is very much helpfull and is good for knowledge

    Comment by subhash — January 8, 2011 @ 03:03

  3. Hi,
    This is very helpful document.
    It worked fine for me.

    Thanks a lot
    Alex

    http://www.simplora.com

    Comment by Alex Slobidker — December 5, 2013 @ 18:30


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: