Can you connect as SYSDBA without password into a PDB directly?

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, 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:

• 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:

• Bug 29615824.8 – ORA-00600 [kkae_switch_svc: get cached default] error when ORACLE_PDB_SID is set

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:

• Pitfalls – Connect to a PDB with ORACLE_PDB_SID directly

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