Systemctl and Oracle Database

Automate Startup or Shutdown of Oracle Database. Illustration by Shashi Kallae.

Automating Database startup and shutdown helps prevent the database from getting corrupted with improper shutdowns and startups. There are several ways to automate the start and shutdown process. Oracle recommends the below method using systemctl in linux env to automate the process.

Step-1: Create a scripts directory in the Oracle user’s home.

mkdir ~/scripts (or) # mkdir /home/oracle/scripts

Here “oracle” user home is /home/oracle

Step 2: Per Oracle’s recommendation, create a file named setEnv.sh using the touch command.

Once the file is created, edit the file using “vi” editor and add the below lines.

$ touch setEnv.sh

$ vi ~/scripts/setEnv.sh

Note: Modify the contents as per your needs. Contact Oracle support if you are stuck with any questions or issues with this process. Using the cat command or vi editor, add the below environment variables. Ensure to mention the FQDN of the Oracle database server, Oracle database name, and Path. Once done, save the file.

$ cat > /home/oracle/scripts/setEnv.sh <<EOF

# Oracle Database Settings
export TMP=/tmp
export TMPDIR=\$TMP

export ORACLE_HOSTNAME=<Oracle database fqdn hostname>
export ORACLE_UNQNAME=oracledatabase_name
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/19.x/db_1
export ORACLE_SID=oracledatabase_name

export PATH=/usr/sbin:/usr/local/bin:\$PATH
export PATH=\$ORACLE_HOME/bin:\$PATH

export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
EOF

-------------------
Make sure you ESC “$” as above.

Step-3: Add the setEnv.sh file to the .bash_profile of the oracle user.

echo ". ~/scripts/setEnv.sh" >> ~/.bash_profile

Step-4: Create two files: start_oradb.sh and stop_oradb.sh

• start_oradb.sh: Starts or opens the Oracle Database.

• stop_oradb.sh: Stops or closes the Oracle Database.

• Open up permissions on the above two files.

• Change the ownersip on the above two files to oracle user.

• Execute the scripts to test.

cat > ~/scripts/start_oradb.sh <<EOF
#!/bin/bash
. ~/scripts/setEnv.sh

export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

oracledbstart \$ORACLE_HOME
EOF
------------------------------------
cat > ~/scripts/stop_oradb.sh <<EOF
#!/bin/bash
. ~/scripts/setEnv.sh

export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

oracledbshut \$ORACLE_HOME
EOF
------------------------------------
$ chown -R oracle:oinstall ~/scripts
$ chmod u+x ~/scripts/*.sh

You should be able to start/stop the database with the following scripts executed from the "oracle" user.
------------------------------------
$ ~/scripts/start_oradb.sh
$ ~/scripts/stop_oradb.sh

Step-5: Edit the file and add the Oracle database and Autostart details as below.

vi /etc/oratab
<oracledatabase_name>:/u01/app/oracle/product/19.x/db_1:Y
:wq
(save the file)

Step-6: Add a service file in the systemd as below.

vi /etc/systemd/system/oracledb.service

[Unit]
Description=The Oracle Database Automated Start and Shutdown Services
After=syslog.target network.target

[Service]
# systemd ignores PAM limits, so set any necessary limits in the service.
# Not really a bug, but a feature.
# https://bugzilla.redhat.com/show_bug.cgi?id=754285
LimitMEMLOCK=infinity
LimitNOFILE=65535

#Type=simple
# idle: similar to simple, the actual execution of the service binary is delayed
# until all jobs are finished, which avoids mixing the status output with shell output of services.
RemainAfterExit=yes
User=oracle
Group=oinstall
ExecStart=/home/oracle/scripts/start_oradb.sh
ExecStop=/home/oracle/scripts/stop_oradb.sh

[Install]
WantedBy=multi-user.target

:wq
(save the file)

$ cat /etc/systemd/system/oracledb.service

Step-7: reload the systemd service/daemon.

$ systemctl daemon-reload

Step-8: Start the Oracle Database service using systemctl and enable it as below.

Start the systemd service and enable the systemd service, so the Oracle database services are automatically restarted on reboot.

$ systemctl start oracledbora.service
$ systemctl enable oracledbora.service

Step-9: Start and Stop the Oracle database using SQLPLUS client.

You can also stop and start the Oracle database instance via the SQLPLUS command.

The below SQL command is used to shutdown the database manually,

$ sqlplus / as sysdba
SQL> shutdown immediate

The below SQL command is used to startup the database manually,

SQL> STARTUP
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;