Overview

This guide provides a script to configure the Oracle environment for both Container Databases (CDB) and Non-Container Databases (Non-CDB) by setting environment variables and connecting to the appropriate database instance or Pluggable Database (PDB).

Script Flow

1. Unset the ORACLE_PDB_SID environment variable.
2. Retrieve running Oracle instances using ps -ef | grep pmon.
3. Validate the Oracle SID from the /etc/oratab file.
4. Set environment variables (ORACLE_SID, ORACLE_HOME, etc.).
5. Use sqlplus to determine if the database is a CDB or Non-CDB.
6. If Non-CDB, set ORACLE_SID and proceed.
7. If CDB, list available PDBs and set ORACLE_PDB_SID.
8. Use a logon trigger to connect directly to the PDB using sqlplus.

Script Implementation

The following script automates the process—finding running Oracle instances, validating them via /etc/oratab, setting environment variables, and handling CDB vs Non-CDB logic.

#Author: Sandeep Reddy
function select_oracle_pdb() {
  echo "Scanning for Oracle database instances..."
  DB_SIDS=$(ps -ef | grep pmon | grep -v 'ASM' | awk -F_ '{print $3}' | sort -u | grep '^[^+]' | grep '^[^-]')

  if [ -z "$DB_SIDS" ]; then
    echo "Error: No active Oracle SIDs found."
    return 1
  fi

  echo "Available active Oracle SIDs:"
  select chosen_db_sid in $DB_SIDS "Quit"; do
    if [ "$chosen_db_sid" == "Quit" ]; then
      echo "Exiting."
      return 1
    elif [ -n "$chosen_db_sid" ]; then
      export ORACLE_SID="$chosen_db_sid"
      unset ORACLE_HOME ORACLE_BASE TNS_ADMIN ORACLE_PDB_SID

      # Try using oraenv first
      if command -v oraenv > /dev/null; then
        echo "Using oraenv to set environment for ORACLE_SID: $ORACLE_SID"
        ORAENV_ASK=NO; export ORAENV_ASK
        ORAENV_OUTPUT=$( . oraenv <<EOF
$ORACLE_SID
EOF
)
        unset ORAENV_ASK
        echo "$ORAENV_OUTPUT"
      else
        echo "oraenv not found. Falling back to /etc/oratab."
        ORACLE_HOME=$(grep "^${ORACLE_SID}:" /etc/oratab | cut -d: -f2)
        export ORACLE_HOME
      fi

      export PATH="$ORACLE_HOME/bin:$PATH"
      export TNS_ADMIN="$ORACLE_HOME/network/admin"

      # Determine database type
      db_type=$(sqlplus -S / as sysdba <<EOF
SET PAGES 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT CASE WHEN CDB = 'YES' THEN 'CDB' ELSE 'NONCDB' END FROM V\\$DATABASE;
EXIT;
EOF
)
      db_type=$(echo "$db_type" | xargs)
      echo " - Database Type: $db_type"

      if [ "$db_type" = "NONCDB" ]; then
        echo " - Non-CDB detected. No PDB selection needed."
        export ORACLE_PDB_SID=""
        break
      else
        echo " - CDB detected. Listing available PDBs:"
        pdbs=$(sqlplus -S / as sysdba <<EOF
SET PAGES 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT NAME FROM V\\$PDBS WHERE NAME NOT LIKE 'PDB$SEED';
EXIT;
EOF
)
        echo "$pdbs"
        read -p "Enter PDB name to connect: " chosen_pdb
        export ORACLE_PDB_SID="$chosen_pdb"
        echo "ORACLE_PDB_SID set to: $ORACLE_PDB_SID"
        break
      fi
    else
      echo "Invalid selection; try again or select 'Quit'."
    fi
  done
}

select_oracle_pdb

Key Notes

1. Unsetting ORACLE_PDB_SID
2. Environment Reset: The script unsets existing variables (ORACLE_HOME, ORACLE_BASE, TNS_ADMIN, ORACLE_PDB_SID) to avoid conflicts.
   oraenv First: Prioritizes oraenv for environment setup, falling back to /etc/oratab otherwise.
   Error Checks: Checks whether sqlplus is available and warns if environment setup fails.
   CDB vs Non-CDB: Detects whether the database is a CDB; if yes, lists PDBs (excluding PDB$SEED) for user selection.
   Usage
   Save this script as oracle_profile.sh, source it, and then run:
   source ./oracle_profile.sh

Database Type Examples

Non-CDB Database Example

$ source ./oracle_profile.sh
$ select_oracle_pdb
Scanning for Oracle database instances...
Available active Oracle SIDs:
1) deeds1
2) Quit
#? 1
Using oraenv to set environment variables for ORACLE_SID: deeds1
...
  - Database Type: NONCDB
  - This is a Non-CDB. Environment set to deeds1.
    

CDB Database Example

$ source ./oracle_profile.sh
$ select_oracle_pdb
Scanning for Oracle database instances...
Available active Oracle SIDs:
1) DEEDCDB1
2) Quit
#? 1
Using oraenv to set environment variables for ORACLE_SID: DEEDCDB1
...
  - Database Type: CDB
  - This is a CDB. Listing available PDBs:
PDB1
PDB2
Enter the PDB name to connect: PDB1
ORACLE_PDB_SID set to: PDB1