Automating the Oracle Database and Listener Startup

In most cases I'll always start with a scenario. Why? I believe it's a great way to paint a picture of the task at hand and establish what issues I'm trying to provide a solution for. It also sets a basis for setting user expectations and making sure that the solution provided to resolve the issue is exactly what is needed.

Suppose you have an environment that contains several Linux servers hosting multiple Oracle databases and to make this more interesting, suppose some of these oracle databases are Container Databases (CDB) with multiple Pluggable databases (PDB). How much time and energy will you spend lets say during a monthly Linux server maintenance to shutdown or startup all these databases to bring them fully back up and online??

Oracle database automatic startup and shutdown feature allows me to be able to successfully shutdown gracefully multiple databases and also startup all these databases and listeners successfully when there is a server maintenance of some sort. It's easily configurable and you only have to do it once, test to confirm it's working and it's all set.

NOTE:

Before modifying or changing any file it's always a great idea to make a backup of the original file before proceeding. That way if you make a mistake you can always restore the original file from the backup copy of the file.

Step 1:

Edit the "/etc/oratab" file. Set the restart flag for each instance from 'N' to 'Y'.?

:~]$ vi /etc/oratab        

For example

oamdb:/u01/app/oracle/product/12.2/db_1:Y

Step 2:

I created a file called "/etc/init.d/oracle" as the root user or user with sudo privileges, containing the following code.

:~]$ sudo vi /etc/init.d/oracle


#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the?
# Oracle database in ORA_HOME.


ORA_HOME=/u01/app/oracle/product/12.2/db_1
ORA_OWNER=oracle


if [ ! -f $ORA_HOME/bin/dbstart ]
then
? ? echo "Oracle startup: cannot start"
? ? exit
fi


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 "$ORA_HOME/bin/lsnrctl start LISTENER_USAC-CDEV2381" &
? ? ? ? su - $ORA_OWNER -c $ORA_HOME/bin/dbstart &
? ? ? ? touch /var/lock/subsys/oracle
? ? ? ? ;;
? ? '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 $ORA_HOME/bin/dbshut
? ? ? ? Su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop LISTENER_USAC-CDEV2381"
? ? ? ? rm -f /var/lock/subsys/oracle
? ? ? ? ;;
esac        

Step 3: Use the chmod command to set the privileges to 750.

:~]$ sudo chmod 750 /etc/init.d/oracle        

Step 4:

Associate the "oracle" service with the appropriate run levels and set it to auto-start using the following command.

:~]$ sudo chkconfig --add oracle        

Step 5: Test and verify

:~]$ service oracle start
:~]$ service oracle stop        

Proper test reboot the Linux server. Log in and?verify that the database and the listener are started after the server reboots.

Troubleshooting

Log files for startup and shutdown process are generated in $ORACLE_HOME. In an event of a problem you can check these files to troubleshoot. You can also monitor these files to generate an alert and send out a notification of any error messages. That way you can focus on more important tasks during a maintenance.

oemserver01.usat.com-/> cd $ORACLE_HOME
oemserver01.usat.com-/> ls -lrt
-rw-r--r--.? 1 oracle oinstall 13614 Apr 24 00:42 listener.log
drwxr-xr-x.? 2 oracle oinstall? ?102 Apr 24 00:42 dbs
-rw-r--r--.? 1 oracle oinstall? 5584 Apr 24 07:38 startup.log        

Container Databases CDB

For CDB's further configuration is required. There are basically two options

1. Create a startup trigger
2. Enable save state for the pluggable database. (Only available for versions 12.2 and above)

Create a startup trigger for oracle database versions lower than 12.2

CREATE TRIGGER open_all_pdbs
	AFTER STARTUP ON DATABASE
	BEGIN
	EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
	END ;
	/        

Enable save state

SQL> sqlplus / as sysdba

SQL> show pdbs? 

? ? CON_ID CON_NAME? ? ? ? ? ?OPEN MODE? RESTRICTED
	---------- -------------- ---------- ----------
			2 PDB$SEED? ? ? ? READ ONLY? NO
			3 OEMPDB? ? ? ? ? MOUNTED? ---------->>>> Note pdb is mounted after reboot
	SQL>
	SQL> alter pluggable database all open;
	
	Pluggable database altered.
	
	SQL>
	SQL>
	SQL> show pdbs
						
		CON_ID CON_NAME? ? ? ?OPEN MODE? RESTRICTED
	---------- -------------- ---------- ----------
			2 PDB$SEED? ? ? ? READ ONLY? NO
			3 OEMPDB? ? ? ? ? READ WRITE NO
	SQL>
	SQL> select con_name, state from dba_pdb_saved_states; ---> To verify
	
	no rows selected
	
	SQL>
	SQL> alter pluggable database OEMPDB save state; ---->> Preserve the state of the?
	? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? pdb across server restarts.
	
	Pluggable database altered.
	
	SQL>
	
	SQL>
	SQL> col con_name for a8
	SQL> select con_name, state from dba_pdb_saved_states;
	
	CON_NAME STATE
	-------- --------------
	OEMPDB? ?OPEN
	
	SQL>
	SQL>
	
Additional options:


	SQL> alter pluggable database all save state; ---> To save the state for all pdbs
	
	SQL> alter pluggable database all save state instances=all; ---> RAC?
	
	SQL> column con_name format a10;
	SQL> column instance_name format a10;
	SQL> select instance_name, con_id, con_name from dba_pdb_saved_states order by 1;

?        

Conclusion

This is a very easy and effective way to manage multiple Oracle database during a Linux server maintenance without the add stress of manual intervention. With time and effort saved, my priorities can be shifted to other tasks during a Linux server maintenance. I hope this helps you increase your productivity.