In the ever-evolving landscape of IT infrastructure and database management efficiency and automation are not just buzzwords—they are necessities. As a seasoned solution architect with over 20 years in the field, I’ve witnessed firsthand how automation can transform complex and time-consuming tasks into streamlined processes. Today, I’m excited to share a recent project where I tackled the challenge of managing multiple Oracle databases in a Linux environment, optimising the start and stop procedures with a custom automation script. 

The Challenges in Database Management 

In our environment, patching Linux systems often necessitates stopping and starting multiple databases. Previously, this involved manually switching between different user sessions on Linux, which was time-consuming and error prone. For instance, managing databases for various clients required separate sessions and scripts for each database. Additionally, for Data Guard standby databases, we needed to run specific SQL commands to manage services, further complicating the process. 

Exploring a career path in SQL?
Get started with our FREE Overview of Oracle SQL Training

The Solution

To address these challenges, I developed two automation scripts to simplify and accelerate the process of starting and stopping databases. Here’s a brief outline of the approach: 

1. Unified Automation Script for Stopping Databases

#!/bin/bash 

   # Function to stop Oracle DB 
   stop_db() { 
       local db_user=$1 
       echo "Stopping database for user: $db_user" 
       runuser -l $db_user -c "cd /home/$db_user/scripts && ./shutdown.sh" 
       if [ $? -eq 0 ]; then 
           echo "$db_user database stopped successfully" 
       else 
           echo "Failed to stop $db_user database" 
       fi 
   } 

  

   # Function to stop OEM Agent 
   stop_agent() { 
       local db_user=$1 
       echo "Stopping OEM Agent for user: $db_user" 
       runuser -l $db_user -c "cd /home/$db_user && ./StopAgent.sh" 
       if [ $? -eq 0 ]; then 
           echo "$db_user OEM Agent stopped successfully" 
       else 
           echo "Failed to stop $db_user OEM Agent" 
       fi 
   } 

  

   # Stop Oracle DBs and OEM Agents 
   stop_db "dbuser1" 
   stop_db "dbuser2" 
   stop_db "dbuser3" 
   stop_agent "oemagent" 
   echo "All specified databases and agents are stopped." 

2. Unified Automation Script for Starting Databases

#!/bin/bash

   # Function to start Oracle DB 
   start_db() { 
       local db_user=$1 
       echo "Starting database for user: $db_user" 
       runuser -l $db_user -c "cd /home/$db_user/scripts && ./startup.sh" 
       if [ $? -eq 0 ]; then 
           echo "$db_user database started successfully" 
       else 
           echo "Failed to start $db_user database" 
       fi 
   } 

  

   # Function to start OEM Agent 
   start_agent() { 
       local db_user=$1 
       echo "Starting OEM Agent for user: $db_user" 
       runuser -l $db_user -c "cd /home/$db_user && ./StartAgent.sh" 
       if [ $? -eq 0 ]; then 
           echo "$db_user OEM Agent started successfully" 
       else 
           echo "Failed to start $db_user OEM Agent" 
       fi 
   } 

  

   # Function to start database with additional SQL commands 
   start_db_with_sql() { 
       local db_user="dbuser4" 
       echo "Starting database and running SQL commands for user: $db_user" 
       runuser -l $db_user -c "cd /home/$db_user/scripts && ./startup.sh" 
       if [ $? -eq 0 ]; then 
           echo "Database started for $db_user. Now running SQL commands." 
           runuser -l $db_user -c " 
               sqlplus / as sysdba <<EOF 
               alter session set container=CONTAINER_NAME; 
               exec dbms_service.start_service('SERVICE_NAME'); 
               exit; 
   EOF 
           " 
           if [ $? -eq 0 ]; then 
               echo "SQL commands for $db_user executed successfully." 
           else 
               echo "Failed to execute SQL commands for $db_user." 
           fi 
       else 
           echo "Failed to start database for $db_user." 
       fi 
   } 

  

   # Start Oracle DBs and OEM Agents 
   start_db "dbuser1" 
   start_db "dbuser2" 
   start_db "dbuser3" 
   start_db_with_sql 
   start_agent "oemagent" 
   echo "All specified databases and agents are started." 

Key Benefits of Database Management

1. Efficiency: The scripts reduce manual intervention by automating the start and stop processes, saving significant time and minimising errors. 
2. Consistency: With standardised scripts, each database and service is managed uniformly, ensuring consistency across the environment. 
3. Flexibility: The scripts can be easily adapted to accommodate new databases or changes in the environment. 

Conclusion

In conclusion, automation is a powerful tool that can enhance productivity and reliability in IT operations. By streamlining the process of managing Oracle databases in a Linux environment, we’ve not only improved operational efficiency but also reduced the risk of human error. I hope this approach can serve as a valuable reference for others facing similar challenges.

Author: Kashif Baksh is a Senior Principal Consultant at Fusion Practices