Friday, 3 July 2020

Oracle 19c / as sysdba to PDB

Up until now I, like most others, have when attempting to connect as sys to a PDB have used either:

export ORACLE_SID=TESTC
export ORAENV_ASK=NO
. oraenv -s
unset ORAENV_ASK

sqlplus / as sysdba

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 TEST                           READ WRITE NO
         6 TEST2                          READ WRITE NO

SQL> alter session set container=TEST;
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 TEST                           READ WRITE NO
or this, ensuring I have a TNSNames.ora alias set for TEST:

sqlplus sys/sys-pwd@test

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 TEST                           READ WRITE NO

Well I've just learnt another, easier, way to connect as sys directly to a PDB. This seems to be new to 19c, I've tried with 12c and it doesn't work.

export ORACLE_SID=TESTC
export ORAENV_ASK=NO
. oraenv -s
unset ORAENV_ASK

export ORACLE_PDB_SID=TEST

sqlplus / as sysdba

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 TEST                           READ WRITE NO

It's this new variable ORACLE_PDB_SID that does the magic.

You are now connected directly to the PDB from the command line and not having to enter the sys password.

I haven't tried this on Windows but I assume that it will work as well.

Oracle CPU downloader

Every quarter I have to go through and download numerous patches for the Oracle CPU (Critical Patch Update). You have to view the CPU docume...