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”.
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:
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
cmdfile. 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.
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:
And then I connect with
/ as sysdba and see what happens.
$ sqlplus / as sysdba SQL*Plus: Release 184.108.40.206.0 - Production on Thu May 7 22:30:05 2020 Version 220.127.116.11.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 18.104.22.168.0 - Production Version 22.214.171.124.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 🙂
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 126.96.36.199 with the newest April 2020 RU – but it does not work in 188.8.131.52. Another reason to quickly jump to 19c if you haven’t done already.
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
$ sqlplus / as sysdba SQL*Plus: Release 184.108.40.206.0 - Production on Fri May 8 22:51:38 2020 Version 220.127.116.11.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 18.104.22.168.0 - Production Version 22.214.171.124.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:
- 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