Scripts for setting the environment for Oracle Database Multitenant

Question

When working on shared database servers with several Oracle Container Databases installed in different homes and different versions it can be hard to find the pdb you are looking for quickly. We used a shell script to set the environment and I added a DB query function to display the PDBS. Example output:

Valid Oracle SIDs are:

                DBSXX1 SGA:  4900 MB  PGA:  1350 MB  DBROLE:  PRIMARY
                DBXXX2 SGA:  4900 MB  PGA:  1380 MB  DBROLE:  PHYSICAL STANDBY
                CDBXX001 [ PDB$SEED PDBXXX1 PDBXXX2 ...]  SGA:  64000 MB PGA:  16777 MB DBROLE:  PRIMARY
                CDBXX002 [ PDB$SEED PDBXXX1 PDBXXX2 ...]  SGA:  64000 MB PGA:  4443 MB DBROLE:  PRIMARY
                CDBXX003 [ PDB$SEED PDBXXX1 PDBXXX2 ...]  SGA:  32000 MB PGA:  11211 MB DBROLE:  PRIMARY
                CDBXX004 [ PDB$SEED PDBXXX1 PDBXXX2 ...]  SGA:  32000 MB PGA:  1111 MB DBROLE:  PRIMARY
                CDBXX005: Database offline
                ...
Enter the Oracle SID you require (default: CDBXX001):

Oracle SID is now CDBXX001, Oracle Home is /u01/app/oracle/product/19000/)

The problem is the performance of the script. The query causes a delay. Are there better solutions to set the environment/display for oracle multitenant ? Improvement ideas?

Full script:

if [ -t 0 ]                             

function query_db() {
export ORACLE_SID=$1
export ORACLE_HOME=`grep "^${ORACLE_SID}:" /etc/oratab|cut -d: -f2 -s`
sqlplus -s / as sysdba<<SQL
set feedback off
set lines 1000
set pages 0
$2
SQL
}

then
        ORACLE_SID=""
        while [ -z "${ORACLE_SID}" ]
            do
                    tput clear; tput rev
                    echo "Valid Oracle SIDs are: "
                    tput rmso
                    for SID in `cat /etc/oratab|grep -v "^#"|cut -f1 -d: -s`
                    do
           if [[ $SID == `ps -ef | grep -w "ora_smon_$SID" | grep -v grep | cut -d _ -f3` ]]
then
                        CDB=$(query_db ${SID} "select cdb from v\$database;")
                        PDBS=$(query_db ${SID} "select name from v\$pdbs;")
                        #DBSIZE=$(query_db ${SID} "select sum (bytes) / 1024/1024/1024 AS SIZE from dba_data_files;")
                        SGA=$(query_db ${SID} "select trunc((sum(value)/1024/1024),-1) SGA from v\$sga;")
                        PGA=$(query_db ${SID} "select trunc((sum(pga_max_mem)/1024/1024),-1) PGA from v\$process;")
                        DBROLE=$(query_db ${SID} "select database_role from v\$database;")

                       if [[ "$CDB" == "YES" ]];
                        then
                         echo -e "\t\t${SID} "[" "${PDBS}" "]"  "SGA:" "${SGA} MB" "PGA:" "${PGA} MB" "DBROLE: " ${DBROLE}"
                         else
                         echo -e "\t\t${SID} "SGA:" "${SGA} MB"  "PGA:" "${PGA} MB"  "DBROLE: " ${DBROLE} "
                        fi
else
echo -e "\t\t${SID} : Database offline"
fi
            done
                DEFAULT=`cat /etc/oratab|grep -v "^#"|cut -d: -f1 -s|head -1`
                echo -e "\nEnter the Oracle SID you require (default: $DEFAULT): \c"
                read ANSWER
                [ "${ANSWER}" = "" ] && ANSWER=$DEFAULT
                export ORACLE_SID=`grep "^${ANSWER}:" /etc/oratab|cut -d: -f1 -s`
                export ORACLE_HOME=`grep "^${ANSWER}:" /etc/oratab|cut -d: -f2 -s`
                export AGENT_HOME="u01/app/oracle/product/emagent/agent_inst/"
                export OH=$ORACLE_HOME
                export DIAG="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}"/"${ORACLE_SID^^}"/trace"
                export DIAGS="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}s"/"${ORACLE_SID^^}"/trace"
                export TNS_ADMIN="/u01/app/oracle/product/network/admin"
                NEW_VER=`cat /etc/oratab | grep ^\`echo ${ORACLE_SID}:\` | cut -d'/' -f 5`
                export PATH=`echo $PATH | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g"`
                export LD_LIBRARY_PATH=`echo ${LD_LIBRARY_PATH} | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g"`
                if [ "${ORACLE_SID}" = "" ]
                then
                        echo -e "\n\n              ${ANSWER} : Invalid Oracle SID \c"
                        sleep 2
                fi
        done
else                                    # Set to first entry in oratab
        export ORACLE_SID=`cat /etc/oratab|grep -v "^#"|cut -d: -f1 -s|head -1`
        export ORACLE_HOME=`cat /etc/oratab|grep -v "^#"|cut -d: -f2 -s|head -1`
        export ORACLE_SID=`grep "^${ANSWER}:" /etc/oratab|cut -d: -f1 -s`
        export ORACLE_HOME=`grep "^${ANSWER}:" /etc/oratab|cut -d: -f2 -s`
        NEW_VER=`cat /etc/oratab | grep ^\`echo ${ORACLE_SID}:\` | cut -d'/' -f 5`
        export PATH=`echo ${PATH} | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g"`
        export LD_LIBRARY_PATH=`echo ${LD_LIBRARY_PATH} | sed "s@/u01/app/oracle/[a-z]*/[0-9]*/[a-z0-9_]*@$ORACLE_HOME@g"`
        export OH=$ORACLE_HOME
        export AGENT_HOME="/u01/app/oracle/product/emagent/agent_inst/"
        export DIAG="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}"/"${ORACLE_SID^^}"/trace"
        export DIAGS="/u01/app/oracle/product/diag/rdbms/"${ORACLE_SID,,}s"/"${ORACLE_SID^^}"/trace"
        export TNS_ADMIN="/u01/app/oracle/product/network/admin"

fi
echo
echo Oracle SID is now `tput rev`$ORACLE_SID`tput rmso`, Oracle Home is `tput rev`$ORACLE_HOME`tput rmso`
echo

EdStevens · Accepted Answer · 2020-12-18 15:58:40Z

1

Looks to me like you are making this waaay more complex than it needs to be and re-inventing the wheel (oraenv)

How bout this?

#!/bin/sh
cat /etc/oratab |grep -v ^# | grep -v ^$
echo
echo Pick one of the above
source oraenv

answered Dec 18, 2020 at 15:58

EdStevens

1,9841 gold badge9 silver badges8 bronze badges

It does not show you the PDBs. /etc/oratab will only show you the CDBs.
– r0tt
Dec 18, 2020 at 16:17
@r0tt - that's funny, my oratab shows pdbs.
– EdStevens
Dec 18, 2020 at 16:28
Thank @EdStevens - I wasn't sure if oratab contained PDBs also. But, I suspect that its existence depends on how you create the PDB. odca or sqlplus
– Michael Kutz
Dec 18, 2020 at 20:33
@MichaelKutz - I don't have a lot of experience yet with multitenant - just one test database on my personal laptop - oracle 12.1 on OL6. But rolling it over in my mind a bit, I may have added that oratab entry myself, not yet knowing what I was doing. Futher , it occurs to me that one never connects directly to a PDB with a BEQ connection. There is no pmon process for the pdb. Therefore, there is no need to set the shell environment specifically for a PDB. So again, the OP's script is simply doing things that do not need to be done.
– EdStevens
Dec 18, 2020 at 21:32
@r0tt - see my response to Michael Kutz. On further reflection, I don't see any need to set the shell environment specifically for a PDB. If one wants to connect directly to a pdb, it would be with a tns connection, so that the environment of the client is not specific to the database. In fact, once you start dealing with a tns connection - it is by definition a network connection. So the fact that the client and server may happen to be on the same machine is totally irrelevant.
– EdStevens
Dec 18, 2020 at 21:34

| Show 6 more comments

Al B. · Accepted Answer · 2021-03-18 19:41:22Z

I believe the list of available PDBs should be stored in a file and not derived, similar to how list of available databases is stored in oratab file. Stored list is not only faster, but is also more reliable in situations when PDB did not register with listener (ex. was closed). It is similar to how getting SID list from oratab is more reliable than building SID list dynamically from "ps -ef | grep pmon" (what if db is shut down ?).

Since Oracle does not have PDB list file, in my environment I maintain my own file with two fields:

cat cdb_pdb_list
cdb1 pdb11
cdb1 pdb12
...

Then to set PDB environment I use database picker script which lets me select from combined list of SIDs from oratab and PIDs from pdb list file. Once selected, the script sets ORACLE_SID and ORACLE_PDB_SID variables and then calls standard "oraenv" for other variables. Any subsequent local connections with "sqlplus / as sysdba" etc go to selected PDB.

Usage example: Notice how PDBs show up as "cdb:pdb" in the list


hostname:+ASM::/home/oracle:> . sid

Select your database:

 1) -MGMTDB                       10) db2                     19) db11
 2) +ASM                          11) db3                     20) db12
 3) cdb1                          12) db4                     21) db13
 4) cdb1:pdb11                    13) db5                     22) db14
 5) cdb1:pdb12                    14) db6                     23) db15
 6) cdb1:pdb13                    15) db7                     24) db16
 7) cdb2                          16) db8                     25) db17
 8) cdb2:pdb21                    17) db9                     26) db18
 9) cdb2:pdb_dropme               18) db10                    27) db19
Your choice (enter number)? 5
Selected database: cdb1:pdb12
ORACLE_SID: cdb11
ORACLE_PDB_SID: pdb12
The Oracle base remains unchanged with value /u01/app/oracle

hostname:cdb11:pdb12:/home/oracle:>
hostname:cdb11:pdb12:/home/oracle:> sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 18 12:27:10 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> show con_name
CON_NAME
------------------------------
PDB12

SQL>

Here is the database picker script. Call it with "dot" or "source" to persist variables in parent shell.

#!/usr/bin/ksh
# July 2019 Albert Balbekov
#
# Interactive database picker
# Displays menu of databases defined in oratab then asks which one to use then uses oraenv to set env variables
#
# This script is normally sourced in user shell to be able to change parent shell variables
# alias sid='source $HOME/admin/script/sid'
#
# History:
#       July 2019       Initial creation
#       Mar 2021        Adding PDBs
#

CDB_PDB_LIST=/home/oracle/admin/scripts/cdb_pdb_list

# check if we are on grid
GRID_HOME=$( grep '^\+ASM' /var/opt/oracle/oratab | cut -f 2 -d: )

# get list of instances from oratab
if [ ! -z "${GRID_HOME}" ]; then
    # remove trailing digit since we are on RAC
    # then sort and uniq to remove duplicates
    # this gives list of databases (as opposite to list of instances)
    DBS="$( grep -v '^#' /var/opt/oracle/oratab | cut -f 1 -d: | sed 's/[0-9]$//' | sort | uniq )"
else
    DBS="$( grep -v '^#' /var/opt/oracle/oratab | cut -f 1 -d: | sort )"
fi

# get list of PDBs from cdb_pdb_list
PDBS="$( cat $CDB_PDB_LIST | awk '{print $1 ":" $2}' )"

# combine and sort
DBS="$( printf '%s\n' $DBS $PDBS | sort )"

echo "\n"
echo 'Select your database:'
echo "\n"

PS3="Your choice (enter number)? "

# if user is in ksh then select menu tends to use all vertical space before wrapping menu to another column
# we will limit terminal rows with stty to force select to wrap menu earlier
#saverows=$( stty | grep rows | cut -f1 -d";" | cut -f2 -d"=" )
stty rows 7

select db in $DBS; do
    if [[ -n $db ]]; then
       echo "Selected database: "$db
       # can place db-specific actions here ...
       break
    else
       echo "Invalid."
    fi
done

# reset stty size back to what it was
#stty rows $saverows
resize > /dev/null

pid="$( echo $db: | cut -d: -f2 )"
db="$( echo $db: | cut -d: -f1 )"

# construct ORACLE_SID by adding RAC node number to db name
if [[ -n "${GRID_HOME}" ]]; then
    # determine RAC node number
    RAC_NODE_NUM=$( $GRID_HOME/bin/olsnodes -n | grep `hostname` | cut -f2 )

    # add RAC node number to database name to get SID
    export ORACLE_SID=${db}${RAC_NODE_NUM}
else
   # not a RAC - SID is db
   export ORACLE_SID=$db
fi
export ORACLE_UNQNAME=${db}
export ORACLE_PDB_SID=$pid

echo ORACLE_SID: $ORACLE_SID
echo ORACLE_PDB_SID: $ORACLE_PDB_SID

SAVE_ORAENV_ASK=$ORAENV_ASK
ORAENV_ASK=NO
source oraenv
ORAENV_ASK=$SAVE_ORAENV_ASK

Stack Exchange Network

Scripts for setting the environment for Oracle Database Multitenant

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
oracle
environment-variables
or ask your own question.

Hot Network Questions

Scripts for setting the environment for Oracle Database Multitenant

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged oracleenvironment-variables or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
oracle
environment-variables
or ask your own question.