0

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
r0tt
  • 850
  • 6
  • 22
  • 41

2 Answers2

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
EdStevens
  • 1,931
  • 1
  • 8
  • 8
  • It does not show you the PDBs. /etc/oratab will only show you the CDBs. – r0tt Dec 18 '20 at 16:17
  • @r0tt - that's funny, _my_ oratab shows pdbs. – EdStevens Dec 18 '20 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 '20 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 '20 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 '20 at 21:34
  • @EdStevens can you post your oratab - just curious how pds can be displayed? – r0tt Dec 19 '20 at 22:17
  • @r0tt - there's nothing special about it. The entry for a pdb in my oratab looks just like any other: 'pdb01:/u01/app/oracle/product/12.1.0/dbhome_1:N'. But please read my previous comment. I don't beleive there is any use of a pdb in oratab, _AND_ I don't see any use of setting a shell environment specifically for a pdb. – EdStevens Dec 19 '20 at 23:32
  • I read your comments. I get your point. Still - as I described in my question. The environment can get complex. Imagen 100s of PDbs in different CDBs on the same system. The tnsnames.ora needs to be maintained locally. I believe a DBA can have the requirement to view all pdbs without checking each CDB manually. Your oratab entry is not valid I believe unless your CDB is called PDB1. Autostart can be configured with oratab and a PDB cannot be started like this. This entry does not make sense. – r0tt Dec 20 '20 at 11:09
  • @r0tt - "This entry does not make sense". Or course it doesn't. Read my previous " I may have added that oratab entry myself, not yet knowing what I was doing". I though you were trying to _set the environment_ for a given PDB, which itself makes no sense, as I also explained. (your script is doing a lot of environment setting). If your goal is to _discover_ and _report_ the available PDBs, then you will have to connect to each CDB and get it to report the PDBs, which is what appears you are doing. So we then come back to 'performance'. So, what do you consider 'unacceptable'? – EdStevens Dec 20 '20 at 14:49
  • Ok - understand. No, it’s not setting the environment for the pdbs, just displaying the pdbs within a cdb. Maybe it could make sense to add a function that creates a connection string which allows you to connect to the pdb directly. Mike Dietrch describes here: https://mikedietrichde.com/2020/05/07/can-you-connect-as-sysdba-without-password-into-a-pdb-directly/. If the tnsnames.ora is not maintained we need to connect like sqlplus sys/password@//localhost:1521/pdb1 and the "/ as sysdba" does not work. If your tnsnames.ora is maintained we could connect like this: sqlplus sys@pdb1 as sysdba – r0tt Dec 22 '20 at 08:29
  • the '/ as sysdba' does not work because that is a local BEQ connection, which is not applicable to PDBs. – EdStevens Dec 22 '20 at 13:29
1

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
Al B.
  • 11
  • 2