Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

Script to automatically startup and shutdown the Oracle database 11gR2

Posted by Mir Sayeed Hassan on February 17th, 2018

Script to automatically startup and shutdown the Oracle database 11gR2

Fallow the below step by step procedure as shown below:

Create the directory as shown below:

[oracle@ogg-test1 ~]$ pwd
/home/oracle
[root@oracleTestServer ~]# mkdir -p /home/oracle/scripts
[root@oracleTestServer ~]# chown oracle.oinstall /home/oracle/scripts

Check the hostname:

[oracle@ogg-test1 scripts]$ hostname
test-db

Startup Script:

[oracle@ogg-test1 scripts]$ vi startup.sh
#!/bin/bash
export TMP=/tmp
export TMPDIR=$TMP
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
export PATH=/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin:$PATH
export ORACLE_HOSTNAME=test-db
export ORACLE_UNQNAME=testdb
export ORACLE_SID=testdb
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES
# Start Listener                   ---- (In case if you have multiple listeners, specify the no of listener names)
lsnrctl start
# Start Database
sqlplus / as sysdba << EOF
STARTUP;
EXIT;
EOF

Shutdown Script:

[oracle@ogg-test1 scripts]$ cat shutdown.sh
#!/bin/bash
export TMP=/tmp
export TMPDIR=$TMP
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME 
export PATH=/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin:$PATH
export ORACLE_HOSTNAME=test-db
export ORACLE_UNQNAME=testdb
export ORACLE_SID=testdb
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES
# Stop Database
sqlplus / as sysdba << EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF
# Stop Listener         ---- (In case if you have multiple listeners, specify the no of listener names)
lsnrctl stop
:wq!
[root@oracleTestServer ~]# chmod u+x /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh
[root@oracleTestServer ~]# chown oracle.oinstall /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh

Create a below file as a “root” login:

[root@oracleTestServer ~]# vi /etc/init.d/dbora
#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database software.
ORA_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
# Remove "&" if you don't want startup as a background process.
su $ORA_OWNER -c "/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1" &
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 "/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
rm -f /var/lock/subsys/dbora
;;
esac
:wq!

Assign the require privilege:

[root@oracleTestServer ~]# chmod 750 /etc/init.d/dbora

Associate “dbora” service with the appropriate run level & set it auto start with below command

[root@oracleTestServer ~]# chkconfig --add dbora

Test the shutdown process:

Verify the database is open to start the shutdown process:

[root@oracleTestServer ~]# su – oracle
Last login: Sat Feb 17 15:49:13 IRST 2018 on pts/0
[oracle@ogg-test1 ~]$ !sq
sqlplus
sys@TESTDB>;select status from v$instance;
STATUS
------------
OPEN
sys@TESTDB>;exit
[oracle@ogg-test1 ~]$ exit
Logout

Start the shutdown database process by executing below command:

[root@oracleTestServer ~]# service dbora stop
[root@oracleTestServer ~]# su - oracle
Last login: Sat Feb 17 15:53:51 IRST 2018 on pts/0
[oracle@ogg-test1 ~]$ !sq
sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 17 15:54:17 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance
testdb>;

Test the startup process:

Verify the database is open to start the shutdown process:

[oracle@ogg-test1 ~]$ !sq
sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 17 15:54:17 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance
testdb>;

(The database is in shutdown status)

Start the startup database process by executing below command:

[root@oracleTestServer ~]# service dbora start
[root@oracleTestServer ~]# su - oracle
Last login: Sat Feb 17 16:00:15 IRST 2018
[oracle@ogg-test1 ~]$ !sq
sqlplus
sys@TESTDB>;select status from V$instance;
STATUS
-----------
OPEN

And listener started status:

[oracle@ogg-test1 scripts]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-FEB-2018 16:06:00
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.123)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-FEB-2018 16:05:46
Uptime                    0 days 0 hr. 0 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ogg-test1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.120.123)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-db)(PORT=8087))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "testXDB" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

Note:

If the server is rebooted, The database will start automatically in open mode

=======Hence startup & shutdown script is tested & verified in our env=======