recent

Titulo

Automatic Shutdown and Startup of Oracle Database on Linux


Oracle Database runs on an Operating Systems like Linux, Unix, or Windows. What happens when your operating systems goes down?  Have your ever thought of this crazy scenario? Today, with this article, I will show you how to automate Oracle Database Startup and Shutdown when your Linux server is up or down. I will also talk about the reason and advantage behind automatic startup & shutdown. When your Linux OS goes down, it will Shutdown your Oracle database and similarly, it will Startup the Oracle db Instance when the Linux host is rebooted. As a bonus, we will also automate the Oracle Listener service along with Oracle database.

Why do you need to automate it?

  1. You don't need to manually Startup or Shutdown Oracle database when rebooting  Linux Server.
  2. Linux Server may reboot accidentally and there is no need to run Startup command on Oracle.
  3. Don't need to wake up in the middle of the night just to Startup the database.
  4. Prevents Oracle files from corruption and losing valuable data.
When System Administrator sends a reboot or shutdown command on Linux, the OS tries to stop all the active services by killing them. When the database is active, the killing of the database service might leave a database in a messy state. The database does instance recovery every time you startup the database. This isn't a good practice and the instance recovery can fail and may leave you with the corrupted data and files. Therefore you are required to perform a proper shutdown or startup of Oracle database before rebooting the operating system.

Let's automate Oracle (11g) database and Listener on Oracle Linux 6.6. DEV is the SID for my Oracle installation  Are you ready for this fun exercise with me? 

Oracle provides scripts called dbshut and dbstart on $ORACLE_HOME/bin directory. These scripts use /etc/oratab file to find out which database to start and stop. The first task it to edit /etc/oratab file. By default the oratab has entries set to No. You will need to edit oratab file to YES which enables dbshut and dbstart command. Do you remember creating this file at all during installation? This oratab file is used by Oracle utilities which was create by root.sh and updated by the database configuration assistant when creating database. Does that ring your bell?

[root@oracldev etc]# vi /etc/oratab
Before: DEV:/u01/app/oracle/product/11.2.0/dbhome_1:N
After: DEV:/u01/app/oracle/product/11.2.0/dbhome_1:Y
save and quit and verify the change.

Next, as a root user create a file: /etc/init.d/dbora
[root@oracldev etc]#   vi   /etc/init.d/dbora 
Copy the following line of code to dbora file we just created.

#!/bin/sh
# chkconfig: 345 99 10
# description: Service to start and stop Oracle Database and Listener
# processname: oracle
# config: /etc/oratab
# pidfile: /var/run/oracle.pid
# Source function library.
. /etc/init.d/functions
RETVAL=0
ORA_OWNER="oracle"
ORA_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"

# See how we were called.
prog="oracle"

start() {
echo -n $"Starting $prog: "
su $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/dbora

return $RETVAL
}

stop() {
echo -n $"Stopping $prog: "
su $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -r /var/lock/subsys/dbora

return $RETVAL
}
restart() {
stop
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
echo $"Usage: $0 {start|stop|restart}"
exit 1
esac
exit $?

Note: The Listener should be started prior to database startup.

If your Oracle Version and the Owner is different, you will need to modify those line of code to match your Oracle home and the Owner. Now, as a root you will need to change the owner and permission to make this script executable.

[root@oracldev etc]# chgrp  dba /etc/init.d/dbora;
[root@oracldev etc]# chmod 750 /etc/init.d/dbora
[root@oracldev etc]# chkconfig --level 345 dbora on

What did we do here?

  1. Changed the group of a file to dba
  2. Made the file executable
  3. Added the service to start automatically during the system start up.

What is next?


Testing, testing and more testing.  Test your script thoroughly to ensure the setup is doing exactly what you wanted to accomplish. Try rebooting Linux Server and watch the screen. You will see a message on the screen the says: Shutting down Oracle Database. When your Linux Server is back up, try logging into Oracle database using SQLPlus, you should see the database is open.  Do a similar check with the Listener.

In addition to above test, you can verify by digging into OS log files. I recommend going over these log files to ensure there is no error.

cat /var/log/boot.log     # This hold the Linux reboot message that you see on screen
cat //u01/app/oracle/product/11.2.0/dbhome_1/dbstart.log    # oracle db startup message
cat /u01/app/oracle/product/11.2.0/dbhome_1/dbshut.log     # oracle db shutting down message
cat /u01/app/oracle/product/11.2.0/dbhome_1/listener.log    # listener startup & shutting down message

Bazinga!! We just automated the Startup and Shutdown of Oracle database and listener of Oracle 11g on Oracle Linux 6.6. The setup is similar for Oracle 10g and 12c. I hope you enjoyed the process and all your databases automated by now.


Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

No comments

Powered by Blogger.