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.
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 22.214.171.124 or Oracle 126.96.36.199. 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 188.8.131.52.0 - Production on Mon Jul 13 13:23:31 2020 Version 184.108.40.206.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 220.127.116.11.0 - Production Version 18.104.22.168.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 22.214.171.124.0 - Production on Mon Jul 13 13:23:31 2020 Version 126.96.36.199.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 188.8.131.52.0 - Production Version 184.108.40.206.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
If we put manually in the trigger “whenever sqlerror exit failure” it should kick us from sqlplus, if there is no such PDB. I haven’t tested it yet.
I guess it would be fairly simple (and your solution may work).
Maybe I will file a bug to get a more proper error handling,
Thanks for sharing – cheers,
Hi again Mike,
I tried playing with the triggers, but could not get it to work (but I am also not a dev:) ). I also noticed a weird bug – when switching to a PDB with “alter session set container” – the functionality of “set serveroutput on” is lost. Therefore triggers cannot print out anything on switch. This may also be a problem when executing scripts that are supposed to print out stuff. I already created an SR for this, so we’ll see.
Great Topic ! Thanks for the “heads up””. And yes; any failing connection to a non existant PDB should be refused (in my opinion)!
I think we both are on the same page here …!
Anyone got this to work on a Windows CDB? In my tests it doesn´t work.
I set the 2 (ORACLE_SID, ORACLE_PDB_SID) variables but every sqlplus / as sysdba leads me to the CDB$ROOT not into the PDB. Maybe just a Linux “feature”? 🙂
check if the trigger is there on your database. If it is, I would expect it to work on Windows as well.
the trigger exists in the CDB. I set ORACLE_HOME, ORACLE_SID and ORACLE_PDB_SID in a command shell but it has no effect. Maybe i need some more variables?! sqlnet.ora setting? Hm, however, for me that is not the preferred option to connect to the pdb but it would be interesting if it works 🙂 Like you i´m pretty sure that it has to work on Windows. Maybe just something is missing. I do some more tests maybe i can figure out what.
Hi Mike and Sven,
just tested it on Windows. The trigger uses DBMS_SYSTEM.get_env to read ORACLE_PDB_SID, but does not get a result.
Here is my test:
[SYS@T2CDB2] SQL> set serveroutput on
[SYS@T2CDB2] SQL> declare
2 V1 varchar2(64);
3 V2 varchar2(64);
5 DBMS_SYSTEM.get_env (‘ORACLE_SID’,V1);
6 DBMS_SYSTEM.get_env (‘ORACLE_PDB_SID’,V2);
7 DBMS_OUTPUT.put_line(‘get_env result – ORACLE_SID:’||V1||’ ORACLE_PDB_SID:’||V2);
get_env result – ORACLE_SID:t2cdb2 ORACLE_PDB_SID:
PL/SQL procedure successfully completed.
[SYS@T2CDB2] SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 220.127.116.11.0 – Production
thanks a lot – this is good to know.
I will file a bug for it.
I know people are always looking for a way to minimise the effort of an upgrade, and believe me I live this every day. The problem I have with solutions like this is they feel like scratching around for a way of remaining in denial. The world has changed. We should change too.
OS authentication has always brought problems. There are alternative solutions to many of those. For the vast majority of situations I would suggest a Secure External Password Store is a much better solution, and one that will work quite happily with multitenant.
I’m not against presenting the alternatives, but I feel like providing a positive direction would maybe be a more useful approach. Just my opinion though.
you know that I’m almost 100% in agreement with you here – but I need to explain what happens, and what the downsides are. I believe that only with this approach people can decide by themselves. I’ve got asked about it – and I was not aware. Personally I don’t use it. But everybody needs to decide and understand the risk.
Except those who are on Windows as you see from the update and the comments section … 🙁
PS: And I can’t agree more regarding the Secure External Password Store
The following will not solve the pitfall from occurance, but at least you will be able to see it.
We defined to change the SQL prompt in the login.sql (using it from SQLPATH):
set termout off
col CONTXT new_value CONTXT noprint
col DBTXT new_value DBTXT noprint
select ‘::’||sys_context(‘userenv’, ‘con_name’) as CONTXT from dual where (select ‘TRUE’ from v$version where banner like ‘Oracle Database 12c%’)=’TRUE’;
select upper(sys_context(‘userenv’, ‘cdb_name’)) as DBTXT from dual;
set sqlprompt “_USER’@’&&DBTXT&&CONTXT _PRIVILEGE> ”
set termout on
If the database version is capable of having PDB’s (>12c), it will add the actual connected “::CONTAINERNAME” in the prompt
thanks for sharing!!!
Hmm Mike, please correct the “12c” in my previous comment, as this should also work on 18 and 19 database versions ofcause….
When playing around with ORACLE_PDB_SID and rman I discovered something interesting. I have both ORACLE_SID=xglcdb and ORACLE_PDB_SID=xgldb set in my bash_profile. When I connect to rman , it was connected to both the container and pluggable:
Recovery Manager: Release 18.104.22.168.0 – Production on Tue Oct 6 16:17:53 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: XGLCDB:XGLDB (DBID=2901449985)
That is not what I want. I wanted to connect to the container (xglcdb). My rman script was failing
When i unset the pdb variable, it worked .
$ unset ORACLE_PDB_SID
Recovery Manager: Release 22.214.171.124.0 – Production on Tue Oct 6 17:15:48 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target
connected to target database: XGLCDB (DBID=1679089024)
thanks a lot – this is quite interesting.
As you can backup a PDB only as well, I would blindly guess that it is intended here.
If you want to connect to the CDB$ROOT – and have an existing PDB and set the ORACLE_PDB_SID at the same time, it will always connect to the PDB instead.
After working with Oracle for more than 10 years, I would’ve expected this product, the database, to elevate itself from the “stodgy” status.
It did not.
So if you’re telling me that after 35 years of existence, the sqlplus product is still doesn’t have/is:
– a history
– a connection indicator to show a hapless person is she or he connected to
– still unable to programmatically inject queries in it, while maintaining the connection after the queries have completed
… then I believe the world is a better place if the Oracle database goes to die a quiet death. Maybe the company itself too.
The lack of connection indicator to a PDB or to a root container itself is just plan intellectual laziness.
Wait a bit – this is not a platform to rant 🙂
But SQL Plus has a history since Oracle 126.96.36.199:
It has a connection indicator – but this doesn’t work with PDBs as they run “inside”.
And please … 🙂
Cheers and take care!
Thanks Mike for sharing this article. Adding my experience(and that’s when I actually landed up here). 🙂
I created 2 environment variables –
erpcdb.env — source environment for cdb
stage_pdb.env — source environment for pdb stage
just focusing on below 2 env variables, I first set them as follows –
In above scenario, if I source stage_pdb.env first and then if would want to source erpcdb.env and login to erpcdb again, I was left with ORACLE_PDB_SID=STAGE still assigned with my earlier stage_pdb.env setting and it again got connected to by pdb.
Conclusion, for setting environment variables in same windows with different environment files, we must make sure ORACLE_PDB_SID is unset when source cdb environment file.
thanks a lot – and there will be proper error handling and protection soon.
setting ORACLE_PDB_SID worked fine in our environment until someone decided to use a standby db ;~)
DBMS_SYSTEM.get_env won’t work in a mounted database.
So I was a little bit confused why I’m connected to the CDB and not to the PDB as expected. Then I’d remembered your post, thanks for the pitfall note
Thanks for the feedback, Holger!
We just tested this at a customer site in August. And once it happened that we accidentally landed in the CDB$ROOT instead of the PDB. Since the trigger does zero error handling, the risk is high. When you always open a new session in SQL Plus, you can enable another “alert” by using a glogin.sql which displays the SID. But as soon as somebody does an “alter session” that won’t help anymore.
Yes, we allways open a new session in SQL-Plus and login.sql is uesd to show the db-name (container name if used), status, startup time db-role and so on.
ORACLE_PDB_SID is set/reset by global environment scripts. So allmost all dba business worked perfect.
Even PDB in a standby db opend read only worked fine.
Our only pitfall is a PDB in a mounted standby db, but login.sql will show the root container so I’d think we could handel this.
Thanks for your answer