I’m happy when I learn something new every day. And today, right before I wanted to turn my computer into “sleep”, I learned something which surprises me a lot. Have you ever asked the question: Can you connect as SYSDBA without password into a PDB directly? Of course with the usual way of “sqlplus / as sysdba”.

Photo by Jonas Verstuyft on Unsplash
Can you?
Our answer always was: No. You can’t. There is no bequeath connection available.
Today somebody asked a similar question internally. And the answer caught Roy, Daniel, Bill and myself by surprise. There is an environment variable available: ORACLE_PDB_SID
.
Of course, at first we checked the documentation. And this is not a real surprise: it is not documented. Which is not the fault of our doc writers because they can only documented what they’ve get told.
Next step: MyOracle Support. And there are several hits. Even though I couldn’t find any explanation note, I found a lot of 19c EBS notes – and a bug report, too. In one of the most important EBS 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, runsqlplus <user>/<password>@<PDB SID>
or connect as SYSDBA.
This looks strange. But of course, I’ve had to try it out.
Try it out!
Quick test in our hands-on lab environment.
At first, I set the environment variable:
-
export ORACLE_PDB_SID=PDBHUGO
And then I connect with / as sysdba
and see what happens.
-
$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 7 22:30:05 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 con_name CON_NAME ------------------------------ PDBHUGO
Wow! It works.
Why is it there? I can just assume that it has been created for the EBS upgrade to 19c as this requires to migrate EBS into a CDB/PDB. All the hits in MOS right now have to do either with EBusiness Suite – or with a bug which seem to be fixed in 19.6.0 and newer:
But I found other EBS notes such as MOS Note: 2554156.1 – Export/Import Process for Oracle E-Business Suite Release 12.2 Database Instances Using Oracle Database 19c which clearly describe to set and unset this parameter in case you’d like to connect directly into the PDB with SYSDBA and without specifying a password.
And you need to unset it …
Well, now as this hack gets introduced and works since Oracle 19.6.0 obviously (and at least since 18.8.0 as well – see below), you need to be a bit careful. When you are used to land always in CDB$ROOT when you connect with sqlplus / as sysdba
, then you log onto the PDB instead if ORACLE_PDB_SID is still set.
$ export | grep SID declare -x ORACLE_PDB_SID="PDBHUGO" declare -x ORACLE_SID="HUGO"
So be aware … just a bit 🙂
Quick annotation
Twitter is sometimes really cool. Just 45 minutes after I published the blog post, Orawill replied with the confirmation that this works actually NOT ONLY in Oracle 19c, but also at least since Oracle 18.8.0. Thanks – I didn’t try this one … but I was tempted and tried it with 12.2.0.1 with the newest April 2020 RU – but it does not work in 12.2.0.1. Another reason to quickly jump to 19c if you haven’t done already.
Additional annotations
Somebody commented quickly on the blog that it may be a problem if you have several PDBs with the same name but in different CDBs. Or several CDBs. I tested this – this is not an issue. It looks like as the ORACLE_SID variable is considered as well to make the distinction between various CDBs on the same host. I tested it with CDB1 and CDB2, both having an independent PDB3. And depending on ORACLE_SID, I connected always to the correct one.
Then somebody else brought up the very good hint what happens when you set an ORACLE_PDB_SID to a PDB which does not exist. Of course I needed to test this, too, right away:
$ export ORACLE_PDB_SID=HUGO
[CDB2] oracle@hol:/u01/app/oracle/product/19/rdbms/admin
$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 22:51:38 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 con_name CON_NAME ------------------------------ CDB$ROOT
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB3 READ WRITE NO
So be aware 🙂 You will be placed into CDB$ROOT in this case. But IMHO this makes sense – but is unusual as typically setting a SID which does not exist will lead to a connection error.
This is really tricky – be aware and take care 🙂
Story Continues …
[Update July 2020]
Actually after I wrote this blog post, Roy and I did more testing – and several readers of the blog gave feedback, e.g. “This does not work on Windows”. So I tested a lot more and filed two bugs. If you are interested in more details, please read on here:
Links
- MOS Notes: 2552181.1 – Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c
- MOS Note: 2554156.1 – Export/Import Process for Oracle E-Business Suite Release 12.2 Database Instances Using Oracle Database 19c
- Bug 29615824.8 – ORA-00600 [kkae_switch_svc: get cached default] error when ORACLE_PDB_SID is set
- Pitfalls – Connect to a PDB with ORACLE_PDB_SID directly
–Mike
Ops, this is something different i will try it for sure Thanks for sharing my friend
Hello Mike,
Thank you very much for sharing this information ! It works like a charm.
However maybe also good to mention that special care should be taken that, while using the parameter ORACLE_PDB_SID, you always make sure to double-check that the parameter ORACLE_PDB_SID was initialized correctly with a valid PDB from the corresponding CDB. Otherwise you nevertheless end up in CDB$ROOT while you might think that you are connected to a PDB (that you thought to have initialized with the parameter ORACLE_PDB_SID). You won’t get a warning that it could not connect to the non-existing PDB in the CDB but it will connect to CDB$ROOT instead (set with parameter ORACLE_SID).
Greetings,
Chris
Thanks for this hint, Chris!
I see more stuff coming in and will need to add a few things now.
Cheers,
Mike
Works for me when there is only one CDB on the Server.
On a Windows Server with two CDBs I always hit the root Container.
Unpredictable, not documentet … be warned.
Pretty good hint – I didn’t try this one … let me check if the ORACLE_SID plays a role, or not.
Cheers,
Mike
Nope – works with multiple CDBs as well. I tested with CDB1 and CDB2, both intentionally each having a PDB3 in them. Depending on ORACLE_SID, I connected always to the correct one. Try it please!
Cheers,
Mike
Hi Mike, it doesn’t work in 12.1.0.2.0 I have tested it just now.
Regards.
Hi David, I updated the blog post already on Friday – as it does not work with 12.2.0.1, it won’t work with 12.1.0.2 either 🙂
Minimum is something around 18.x 🙂
Cheers,
Mike
But i can confirm that works in 18.8.0.0 😉 Great!!!!
Thanks for confirming, David!
Cheers,
Mike
Hi Mike,
we have Oracle database of 11.2.0.4 version and we want to upgrade to 12c version.
As per the Oracle Database upgrade matrix, we can also upgrade directly to 18c (12.2.0.2) or 19c (12.2.0.3).
what do you suggest upgrading Oracle database (11.2.0.4) to 18c or 19c is more reliable or stabilize than 12.1.X ?
of course if we upgrade to 18c 19c , we will get more feature but i want to know this from STABILITY prospect.
which is more stabilize version.
Thanks
Brijesh
Hi Brijesh,
glad that you ask because I put this into a blog post already:
https://mikedietrichde.com/2020/02/26/to-which-release-should-you-upgrade-to-revisited/
Cheers,
Mike
Mike –
A couple months ago I stumbled on this while looking at logon triggers – Oracle created DBMS_SET_PDB in my 19c environment. This trigger will do an “alter session set container” to the value of $ORACLE_PDB_SID for SYS and SYSTEM only.
However, there is no reason a custom logon trigger couldn’t be created for other DB users, a different environment variable or even an older release. (As always, test well before implementing)
Hi Peter,
thanks for sharing – I will investigate 🙂
Cheers,
Mike
Pete,
let me know whenever we meet somewhere – beer or wine is on me then.
The trigger is the mechanism – when you disable it, you land in CDB$ROOT. When it is enabled, it gets you into the PDB.
Cheers,
Mike
Nice: it works on XE 18c as well!!
$ export ORACLE_PDB_SID=XEPDB1 && sqlplus -s / as sysdba <<< "show con_name"
CON_NAME
——————————
XEPDB1
🙂
Thanks Simon – hope you are doing well!
Cheers,
Mike
There is no magic to it. This works because Oracle has created a login trigger which checks the environment variable and issues a “alter session set container” – a when others clause silently ignores errors.
Didnt see Pete Dinin already posted the trigger mechanism. Good to check what has been posted before first I guess. 🙂
Thanks – and yes, you all are right.
The “unmagic” is that is hasn’t been documented (yet).
Cheers,
Mike
Doesn’t work on 19c any more
Sure it does. I use 19.7.0 – and it works.
Thanks,
Mike
I am on Windows and running one CDB 19.7 and it doesn’t seem to work Mike.
C:\Oracle\product\19.0.0\db_home1\bin>Set ORACLE_PDB_SID=FSUAT3
C:\Oracle\product\19.0.0\db_home1\bin>set ORACLE_SID=HOPSDBDEV1
C:\Oracle\product\19.0.0\db_home1\bin>sqlplus / as sysdba
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 – Production
Version 19.7.0.0.0
sys@hopsdbdev1> show con_name
CON_NAME
——————————
CDB$ROOT
sys@hopsdbdev1> alter sesion set container=FSUAT3;
sys@hopsdbdev1> show con_name
CON_NAME
——————————
FSUAT3
Correct – and I forgot to add the link to the follow up blog post:
https://mikedietrichde.com/2020/07/13/pitfalls-connect-to-a-pdb-directly-with-oracle_pdb_sid/
I filed a bug for it.
Sorry for the inconvenience – cheers,
Mike
Hi there, it’s been a while but wanted to share that it worked for me on Windows with 19.3, with variables set in Windows registry.
Microsoft Windows [Version 10.0.19043.1237]
(c) Microsoft Corporation. All rights reserved.
C:\Users\alexl>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Wed Sep 22 16:34:59 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> show con_name
CON_NAME
——————————
ORCLPDB
i think this should be done with the ORACLE_SID set to the container DB
This should be good to datapump scripts!