Skip to content
Home » Oracle » Connect to PDB as SYSDBA by ORACLE_PDB_SID

Connect to PDB as SYSDBA by ORACLE_PDB_SID

Connect as SYSDBA

We usually connect to the database by / as SYSDBA without providing any password for maintenance works, such as startup, shutdown and backup. This is because we are in OSDBA or OSOPER group, so we are naturally authenticated at OS-level and don't have to provide password to login.

Connect to a PDB

In multitenant environments, the thing becomes a little different, we can still connect to Container Database (CDB) without password by the traditional way, but not Pluggable Database (PDB).

If we want to connect to a PDB, we usually provide a connection string which includes credentials and a tnsname to login.

[oracle@test ~]$ sqlplus sys/password@orclpdb as sysdba
...
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

A problem comes, how do we connect to the pluggable database (PDB) without any password? Let's see the second case.

[oracle@test ~]$ sqlplus / as sysdba
...
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=ORCLPDB;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

As you can see, we set container in session in order to get into PDB. Honestly, both cases are somewhat inconvenient, we want to connect to the PDB directly by OS authentication without password.

Use ORACLE_PDB_SID

Since 18c, we have another scheme to connect to a PDB directly without password. The secret ingredient is set an additional environment variable ORACLE_PDB_SID to let Oracle know what PDB we want to directly connect to.

[oracle@test ~]$ echo $ORACLE_SID
ORCLCDB
[oracle@test ~]$ export ORACLE_PDB_SID=ORCLPDB
[oracle@test ~]$ sqlplus / as sysdba
...
SQL> show con_name

CON_NAME
------------------------------
ORCLPDB
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

It's extremely useful when we want to directly export data from a PDB without providing any password. Moreover, utilities that need authentication are all applicable to this case. For example, using RMAN to backup tablespaces belong to a PDB.

Please note that, ORACLE_PDB_SID is a dependency of ORACLE_SID. That is, if there's no ORACLE_SID, Oracle still have no idea where to go.

If you found that you still can't do this in 18c or 19c database, maybe you should consider to apply Database Release Update (DBRU) on Oracle database 19c.

For releases before 18c, you may create a logon trigger to do it.

Leave a Reply

Your email address will not be published. Required fields are marked *