Friday, July 17, 2020

ORACLE_SID_PDB - Connect as SYSDBA without password into a PDB directly

Can you connect as SYSDBA without password into a PDB directly? Of course with the usual way of “sqlplus / as sysdba”. Yes you can, even without no bequeath connection available.

There is a 19c EBS notes.
In one of the most important EBS MOS Notes: 2552181.1 – Interoperability Notes: Oracle E-Business Suite

MOS Notes: 2552181.1 – Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c under section 2.5 you can read:

  • For the PDB database, source the $ORACLE_HOME/<PDB SID>_<HOST>.env/cmd file.
    If you are on UNIX/Linux, set the ORACLE_PDB_SID environment variable to <PDB SID>. Then, run sqlplus <user>/<password>@<PDB SID> or connect as SYSDBA.

Testing it:

Using the variable setting on OS :
export ORACLE_PDB_SID=joords_pdb

sqlplus / as sysdba

SQL*Plus: Release - Production on Thu May 7 22:30:05 2020

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

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

SQL> show con_name


You need to unset the variable setting if you want to connect to the CDB again as "sqlplus / as sysdba"  =>  unset ORACLE_PDB_SID

This solution is aviable since ORACLE version 18.8.0

How this feature is working

Oracle has created an AFTER EVENT LOGON trigger. The trigger fires when you logon as SYS or SYSTEM. Logon Trigger settings:
SQL> select trigger_body from dba_triggers where trigger_name = 'DBMS_SET_PDB'

   pdb_name varchar2(64);
     DBMS_SYSTEM.get_env ('ORACLE_PDB_SID', pdb_name);
     if(pdb_name is not null)
        EXECUTE IMMEDIATE 'alter session set container = ' || '"' || pdb_name || '"';
      end if;
     when others then
   end dbms_set_pdb; 

A Pitfals

When the settings of ORACLE_PDB_SID doesn't exists within the environment of the CDB. Then you connect to a CDB level, see the PL/SQL code

No comments:

Post a Comment