Listing Databases on an Oracle DB node

Wednesday, December 11, 2024

Listing Databases on an Oracle DB node

In this blog post I am sharing a script that I wrote that will give you the list of databases running on a DB node. The information provided by the script is

DB_UNIQUE_NAME
ORACLE_SID
DB_HOME

WHY

I have been working on a script to automatically configure OKV for all of the Oracle Databases running on a DB host. In order to install OKV in a RAC cluster, I want to ensure the unique OKV software files are in the same location on every host when I set the WALLET_ROOT variable for my database. The optimal location is to put the software under $ORACLE_BASE/admin/${DB_NAME} which should exist on single instance nodes, and RAC nodes.

Easy right?

I thought it would be easy to determine the name of all of the databases on a host so that I could make sure the install goes into $ORACLE_BASE/admin/{DB_NAME}/okv directory on each DB node.

The first item I realized is that the directory structure under $ORACLE_BASE/admin is actually the DB_UNIQUE_NAME rather than DB_NAME. This allows for 2 different instances of the same DB_NAME (primary and standby) to be running on the same DB node without any conflicts.

Along with determining the DB_UNIQUE_NAME, I wanted to take the following items into account

A RAC environment with, or without srvctl properly configured
A non-RAC environment
Exclude directories that are under $ORACLE_BASE/admin that are not a DB_UNQUE_NAME running on the host.
Don't match on ORACLE_SID. The ORACLE_SID name on a DB node can be completely different from the DB_UNIQUE_NAME.

Answer:

After searching around Google and not finding a good answer I checked with my colleagues. Still no good answer.. There were just suggestions like "srvctl config", which would only work on a RAC node where all databases are properly registered.

The way I decided to this was to

Identify the possible DB_UNIQUE_NAME entries by looking in $ORACLE_BASE/admin
Match the possible DB_UNIQUE_NAME with ORACLE_SIDs by looking in $ORACLE_BASE/diag/rdbms/${DB_UNIQUE_NAME} to find the ORACLE_SID name. I would only include DB_UNIQUE_NAMEs that exist in this directory structure and have a subdirectory.
Find the possible ORACLE_HOME by matching the ORACLE_SID to the /etc/oratab. If there is no entry in /etc/oratab still include it.

Script:

Below is the script I came up with, and it displays a report of the database on the host. This can be changed to store the output in a temporary file and read it into a script that loops through the databases.

####################################################################
##  Make sure that $ORACLE_BASE is set and exists before continuing
####################################################################

if [ -z ${ORACLE_BASE+x} ]
  then
       echo "\$ORACLE_BASE must be set"
       exit 0
fi

####################################################################
##  Make sure that $ORACLE_BASE/admin  exists before continuing
####################################################################


if [ -d ${ORACLE_BASE}/admin ]
  then
       echo "\$ORACLE_BASE  is set to       : ${ORACLE_BASE}"
  else
       echo "\$ORACLE_BASE is set to ${ORACLE_BASE} but ${ORACLE_BASE}/admin does not exist"
       exit 0
fi


########################################################################
## Loop through $ORACLE_BASE/admin to find the list of DB_UNIQUE_NAMEs
#########################################################################

for dbdir in $ORACLE_BASE/admin/*
do
   DB_UNIQUE_NAME=${dbdir##*/}
##   echo "possible DB_UNIQUE_NAME :  ${DB_UNIQUE_NAME}"



########################################################################
## Loop through $ORACLE_BASE/diag/rdbms/${DB_UNIQUE_NAME} to validate
#########################################################################



   for siddir in ${ORACLE_BASE}/diag/rdbms/${DB_UNIQUE_NAME}/*
    do
      db_sid=${siddir##*/}
      if [ -d ${siddir} ]
          then
              echo " "
              echo "DB_UNIQUE_NAME : ${DB_UNIQUE_NAME}"
              echo "ORACLE_SID     : ${db_sid}"
              db_home=`cat /etc/oratab | grep ${db_sid} | cut -d ':' -f 2`
              if [ -z "${db_home}" ]
                   then
                       echo "ORACLE_HOME    :  ******  NOT IN /etc/oratab **** Cannot determine ORACLE_HOME *****"
                   else
                       echo "ORACLE_HOME    : ${db_home}"
              fi
              echo " "
     fi
    done
done

Output:

Below is the sample output from the script.. You can see that it doesn't require the DB to exist in the /etc/oratab file.


DB_UNIQUE_NAME : cdb1db1
ORACLE_SID     : cdb1db11
ORACLE_HOME    :  ******  NOT IN /etc/oratab **** Cannot determine ORACLE_HOME *****


DB_UNIQUE_NAME : daver
ORACLE_SID     : daver1
ORACLE_HOME    : /u01/app/oracle/product/19.0.0.0/dbhome_1


DB_UNIQUE_NAME : dbsgadat
ORACLE_SID     : dbsgadat1
ORACLE_HOME    : /u01/app/oracle/product/19.0.0.0/dbhome_1


DB_UNIQUE_NAME : dbsgprd
ORACLE_SID     : dbsgprd1
ORACLE_HOME    : /u01/app/oracle/product/19.0.0.0/dbhome_1

Finally:

If you are also trying to get a list of databases that are running on a DB node I hope this helps you.

Bryan's Oracle Blog

Wednesday, December 11, 2024