Automated PDB Enumeration and Dynamic Login Selector for Oracle Multitenant Databases

Detailed Description:

This Bash script automates the enumeration and dynamic selection of Pluggable Databases (PDBs) in an Oracle Multitenant environment. It connects to the Oracle Container Database (CDB) as a SYSDBA, queries the V$PDBS view to retrieve a list of active PDBs (excluding the default seed database), and stores these names for further processing.

Key Functionalities:

  1. Environment Setup:
  • Sources the Oracle environment configuration from oracle.env, ensuring all necessary environment variables (like ORACLE_HOME and PATH) are correctly set for Oracle tools.
  1. Dynamic PDB Retrieval:
  • Executes a silent SQL*Plus command to query v$pdbs and retrieves names of all PDBs except the PDB$SEED (which is a template and not meant for use).
  • The list of PDBs is saved into /tmp/log/pdbs.log.
  1. Menu Creation:
  • Reads each non-empty line from the log file into a Bash array pdb_list.
  • Adds a none option to allow the user to exit the script without connecting.
  1. Interactive Selection:
  • Uses Bash’s select statement to present a user-friendly, numbered menu of available PDBs.
  • Allows the user to select a PDB from the list or choose “none” to exit.
  1. Database Connection:
  • If a valid PDB is selected, the script sets the ORACLE_PDB_SID environment variable to the chosen PDB name.
  • It then launches SQL*Plus connected as SYSDBA to the selected PDB.
  1. Input Validation:
  • The script checks the user’s input for validity and only proceeds with connection if a correct PDB is selected.

 

Use Case:

This script is ideal for DBAs managing Oracle 12c+ Multitenant architectures, who frequently need to connect to different PDBs. It simplifies the task of identifying and connecting to PDBs without hardcoding names or manually querying the database.

 

Script:

. /home/oracle/oracle.env

${ORACLE_HOME}/bin/sqlplus -s << EOF > /tmp/log/pdbs.log

connect / as sysdba

set heading off feedback off pagesize 0 verify off trimspool on

SELECT name FROM v\$pdbs WHERE name NOT LIKE ‘PDB%SEED’;

exit;

EOF

pdb_list=()

while read -r pdbname; do

[[ -n “$pdbname” ]] && pdb_list+=(“$pdbname”)

done < /tmp/log/pdbs.log

pdb_list+=(“none”)

PS3=”Select a PDB to connect: ”

select department in “${pdb_list[@]}”; do

if [[ “$department” == “none” ]]; then

echo “Exiting.”

break

elif [[ ” ${pdb_list[@]} ” =~ ” ${department} ” ]]; then

export ORACLE_PDB_SID=”$department”

echo “Connecting to PDB: $ORACLE_PDB_SID”

sqlplus / as sysdba

break

else

echo “Invalid selection”

fi

done

Recent Posts