Pitfalls: Connect to a PDB directly with ORACLE_PDB_SID

A few weeks ago I learned about the ability to connect directly into a PDB by using the environment variable ORACLE_PDB_SID. I blogged about it in Can you connect as SYSDBA without password into a PDB directly?. And today, I’d like to give you a bit more insights and warn you about the Pitfalls: Connect to a PDB directly with ORACLE_PDB_SID.

Photo by Mauro Sbicego on Unsplash

How does it work?

When I wrote the blog post Can you connect as SYSDBA without password into a PDB directly? I haven’t dug into the technique behind the ability of doing a sqlplus / as sysdba directly into a PDB without the password. But gladly, some of my readers knew already. THANKS so much – as I always repeat, this blog is luckily not a one-way street as I keep learning from you guys out there as well.

The technique used here is no magic. It is done with a trigger called DBMS_SET_PDB.

This trigger most likely comes in with the April 2019 RUs for Oracle Database 19c and 18c. It does not exist in Oracle Database 12.2.0.1 or Oracle 12.1.0.2. And it works in XE as well – just for the records.

It is an AFTER EVENT LOGON trigger. The trigger fires when you logon as SYS or SYSTEM.

You can query the code by yourself:

SQL> select trigger_body from dba_triggers where trigger_name = 'DBMS_SET_PDB'

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

Important hint upfront

Please be aware that the parameter ORACLE_PDB_SID works only in conjunction with ORACLE_SID. The ORACLE_SID defines to which SID you connect to. ORACLE_PDB_SID does not work without the correct ORACLE_SID. As I received this question already (“How does it know to which PDB1 it should connect, in CDB1 or CDB2?”) I thought I clarify this here.

What are the pitfalls?

I’m not a PL/SQL expert, not at all. But even I can see from looking at the lines above what happens when you connect with any PDB name, regardless if the PDB exists or not. You can use any PDB name – and you’ll be connected. But only in the case that you use the name of an existing PDB, you’ll be connected to it. In all other cases, your connection won’t be rejected – but you’ll be placed into CDB$ROOT.

Let’s do a little quiz – in which PDB am I connected now?

$ export ORACLE_SID=CDB2
$ export ORACLE_PDB_SID=PDB1

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 13 13:23:31 2020
Version 19.7.0.0.0

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


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

SQL> 

You’d think I’m connected to PDB1 obviously. But there is no PDB1 in my CDB2:

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 13 13:23:31 2020
Version 19.7.0.0.0

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


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

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED			  READ ONLY  NO

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

So this is the first big danger.

There is no error, no warning, not even a message telling you that you are in fact not in PDB1 but instead in the CDB$ROOT. The trigger tried an alter session set container=PDB1;.

But instead of displaying the error, the trigger does not do any sort of error handling. I don’t even want to sense what Steven Feuerstein thinks about it.

This happens, when you execute the connection manually:

SQL> alter session set container=pdb1;
ERROR:
ORA-65011: Pluggable database PDB1 does not exist.

This makes sense and is expected.

Can it get worse?

Yes, of course. At least it happened to me. I had used the environment variable ORACLE_PDB_SID but wasn’t aware that it was still set in my terminal. I got connected to the PDB instead of the CDB$ROOT. And I ran a script which was not meant to be executed in the PDB.

Well, nothing broke as it is my toy environment only.

But what I’d like to explain: Be very careful when using the ORACLE_PDB_SID environment variable. The best – in case you decide to use it – is to add it to your profile, and set it in conjunction with a profile only.

As this switch is done via a trigger, you can’t expect any sort of output in SQL*Plus. Maybe SQLcli can do some magic? But a simple

BEGIN
 DBMS_OUTPUT.PUT_LINE('Connected to : '|| sys_context('userenv','con_name'));
END;
/

does not work.

And personally I’d still vote for a failed connection rather than a successful one into the wrong container by “accident”.

• Bug 31627219 – TRIGGER DBMS_SET_PDB DOES FALSE CONNECTIONS WITHOUT ANY ERROR HANDLING

Can it get even worse?

Yes, it can. As you can see in the comments section, several people were wondering why it does not work on Windows. And you see the test done by Martin Bruegger as well – the variable is empty, and hence the switch can’t happen. I will file a bug for it. Thanks a lot for testing this for me.

• Bug 31627193 – ORACLE_PDB_SID ENVIRONMENT VARIABLE DOES NOT WORK ON WIN – FAILS TO CONNECT TO PDB

Finally, there should be documentation available on it the sooner or later I guess.

Further Links and Information

• Can you connect as SYSDBA without password into a PDB directly?
• How NOT to handle exceptions – Steven Feuerstein’s Blog

–Mike