Twitter

A systemd service to stop/start Oracle databases

Oracle CRS and Restart manage the status of your Oracle resources then your Oracle databases and listeners are automatically restarted at boot and you have an easy way to stop/start all of them. But when you do not have these features, you need to manage it on your own. Oracle has always providing two scripts for doing this: dbstart and dbshut which are located in $ORACLE_HOME/bin and which rely on /etc/oratab to stop/start resources of an ORACLE_HOME.

On some platforms like AWS, a system V stop/start service is setup for you:
[root@ip-1-2-3-4 ~]# cat /etc/init.d/dbora
...
case $1 in
'start')
    echo -n $"Starting Oracle: "
    su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &
    ;;
'stop')
    echo -n $"Shutting down Oracle: "
    su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" &
    ;;
...
[root@ip-1-2-3-4 ~]# chkconfig --list | grep dbora
dbora           0:off   1:off   2:on    3:on    4:on    5:on    6:off
[root@ip-1-2-3-4 ~]#
If you do not have this on your system/platform, you can replicate this but it makes more sense to have a systemd service instead as systemd is default pretty much everywhere since 2015.

To achieve this, you first have to set the databases you want to start with this service to "Y" in /etc/oratab
[root@prodserver ~]# cat /etc/oratab
PRODDB:/psoft/db/oracle-server/19.3.0.0:Y   <=== put a Y here
[root@prodserver ~]#
Find your dbstart and dbshut scripts:
[root@prodserver ~]# ls -ltr /psoft/db/oracle-server/19.3.0.0/bin/dbs*
-rwxr-x---+ 1 oracle oinstall 15737 Jan  1  2000 /psoft/db/oracle-server/19.3.0.0/bin/dbstart
-rwxr-x---+ 1 oracle oinstall  8142 Jan  1  2000 /psoft/db/oracle-server/19.3.0.0/bin/dbshut
[root@prodserver ~]#
Create a systemd service configuration file as below:
[root@prodserver ~]# cat /etc/systemd/system/oracle.service
[Unit]
Description=Oracle databases Stop/Start
After=multi-user.target
[Service]
Type=oneshot
RemainAfterExit=yes
User=oracle
Group=oinstall
ExecStart=/psoft/db/oracle-server/19.3.0.0/bin/dbstart /psoft/db/oracle-server/19.3.0.0
ExecStop=/psoft/db/oracle-server/19.3.0.0/bin/dbshut /psoft/db/oracle-server/19.3.0.0
[Install]
WantedBy=multi-user.target
[root@prodserver ~]#
Now enable your service:
[root@prodserver ~]# systemctl enable oracle
[root@prodserver ~]# systemctl is-enabled oracle
[root@prodserver ~]#
And you are go to go !
[root@prodserver ~]# systemctl stop oracle
[root@prodserver ~]# systemctl start oracle
[root@prodserver ~]#
To have a quick status, I made a small script like this:
[oracle@prodserver ~]# cat /usr/local/bin/db-status.sh
#!/bin/bash
# Fred Denis -- July 15th 2021
# Show simple infos from databases defined in oratab
#
# Variables
#
    TS="date "+%Y-%m-%d_%H:%M:%S""       # A timestamp for a nice output
ORATAB="/etc/oratab"
#
# Checks
#
if [[ ! -f "${ORATAB}" ]]; then
    printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Cannot find ${ORATAB}; cannot continue."
    exit 123
fi
#
# Show databases status
#
for DB in $(cat ${ORATAB} | grep "^[a-zA-Z]" | awk -F ":" '{print $1}'); do
    . oraenv <<< "${DB}" > /dev/null 2>&1
    sqlplus -S / as sysdba << END_SQL
    select instance_name, status, to_char(startup_time, 'DD/MM/YYYY HH24:MI:SS') as Startup_Time, to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as Current_Time from gv\$instance ;
END_SQL
done
[oracle@prodserver ~]#  /usr/local/bin/db-status.sh
INSTANCE_NAME    STATUS          STARTUP_TIME        CURRENT_TIME
---------------- ------------ ------------------- -------------------
PRODDB            OPEN         16/07/2021 12:02:30 22/07/2021 11:49:54
[oracle@prodserver ~]#

Easy and very efficient !

2 comments:

  1. Really cool one.. Thanks for sharing.!!

    ReplyDelete
  2. Nice :)
    Thanks, Fred!

    We've also started using systemctl.
    I might recommend adding TimeoutSec=600 into the unit file -- occasionally the startup of databases takes longer than the default 30 seconds and if it hits the threshold, systemctl kills started processes.
    Also I'd recommend adding the following parameters to the unit file, since systemd apparently doesn't read /etc/sysctl.conf
    I didn't check this one yet, people say it does take into account files in /etc/sysctl.d. but can't confirm this yet:

    LimitMEMLOCK=infinity
    LimitNOFILE=65535
    (big thanks to Tim Hall)

    Thanks again! Your blog is awesome!

    ReplyDelete

Load 2 billion rows with SQL LOADER

I worked on few Extract data / transform data (nor not) / load data into an Oracle database projects during my professional life and SQL ...