About problems with Oracle 12c Multitenant Database
Back in the days everything was easier, wasn’t it? Thus far you connect to your database and in doubt you send a query to v$instance or v$database to check you’re actually connected to the right place. But since 12c the world is no more that clear. Meanwhile we’ve got the Multitenant Option and by connecting to a Pluggable Database, maybe you won’t get the desired result.
But for what reason it is such a problem?
In a multitenant environment you can connect directly to your Pluggable Database, but also jump between Pluggable Databases and the Root Container back and forth. When you work with several sessions and also several windows, you can lose track sometimes. Also for scripting it can be helpful to make changes to the right PDB and not to the Root Container by mistake.
For the following example we work with a simple 12c Multitenant Database with CDB (the ROOT Container) and an attached Pluggable Database (PDB).
CDB = SJW12C01 and PDB = SJWPDB01
Our v$instance is not very helpful anymore regarding PDB, even the result is completeley correct:
CDB
sqlplus system@SJW12C01 SQL> select instance_name, con_id, version from v$instance; INSTANCE_NAME CON_ID VERSION ---------------- ---------- ----------------- sjw12c01 0 12.1.0.2.0
PDB
sqlplus system@SJWPDB01 SQL> select instance_name, con_id, version from v$instance; INSTANCE_NAME CON_ID VERSION ---------------- ---------- ----------------- sjw12c01 0 12.1.0.2.0
Unfortunately the same thing with v$database:
CDB
sqlplus system@SJW12C01 SQL> select name, con_id, db_unique_name from v$database; NAME CON_ID DB_UNIQUE_NAME --------- ---------- ------------------------------ SJW12C01 0 SJW12C01
PDB
sqlplus system@SJWPDB01 SQL> select name, con_id, db_unique_name from v$database; NAME CON_ID DB_UNIQUE_NAME --------- ---------- ------------------------------ SJW12C01 0 SJW12C01
With v$pdbs we do have a first option to get helpful information. You will get similar results with select * from cdb_pdbs. In the example we jump per alter session set container from CDB to PDB and back:
CDB > PDB > CDB
sqlplus system@SJW12C01 SQL> select dbid, con_id, name from v$pdbs; DBID CON_ID NAME ---------- ---------- ------------------------------ 4043975488 2 PDB$SEED 4185832567 3 SJWPDB01 SQL> alter session set container = SJWPDB01; Session altered. SQL> select dbid, con_id, name from v$pdbs; DBID CON_ID NAME ---------- ---------- ------------------------------ 4185832567 3 SJWPDB01 SQL> alter session set container = CDB$ROOT; Session altered. SQL> select dbid, con_id, name from v$pdbs; DBID CON_ID NAME ---------- ---------- ------------------------------ 4043975488 2 PDB$SEED 4185832567 3 SJWPDB01
As you can see, you get informations about a PDB$SEED in the Container Database. The SEED Database is used as a draft in the multitenant environment for creating new PDB’s. In a PDB you will always get only one result in every case, your current PDB.
But there is a better way using USERENV from SYS_CONTEXT:
CDB
sqlplus system@SJW12C01 SQL> select sys_context('USERENV','CON_NAME') CON_NAME, sys_context('USERENV','CON_ID') CON_ID, sys_context('USERENV','DB_NAME') DB_NAME from DUAL; CON_NAME CON_ID DB_NAME ---------- ---------- ---------- CDB$ROOT 1 SJW12C01
PDB
sqlplus system@SJWPDB01 SQL> select sys_context('USERENV','CON_NAME') CON_NAME, sys_context('USERENV','CON_ID') CON_ID, sys_context('USERENV','DB_NAME') DB_NAME from DUAL; CON_NAME CON_ID DB_NAME ---------- ---------- ---------- SJWPDB01 3 SJW12C01
Pay attention to the DB_NAME. It will always return the name of the (Root) Container Database. You may considered the CON_ID. Don’t be confused – CON_ID 0 means the whole Multitenant Database and 1 is reserved for the Root Container. CON_ID 2 is always PDB$SEED. Everything else from CON_ID 3 up is a Pluggable Databases.
Having that in mind and with a bit of DECODE you get the current database name and if you are connected to a CDB or PDB:
CDB
sqlplus system@SJW12C01 SQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME, decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE from DUAL; DB_NAME TYPE ---------- ---- SJW12C01 CDB
PDB
sqlplus system@SJWPDB01 SQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME, decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE from DUAL; DB_NAME TYPE ---------- ---- SJWPDB01 PDB
Easier and a bit faster is using SHOW CON_ID, SHOW CON_NAME via SQL Plus. But it only works here and SHOW PDBS only works as SYSDBA. Pay attention to the connect / as sysdba – freshly connecting I change from PDB to CDB. Don’t be confused by the parameter db_name. Concentrate on con_id and con_name:
CDB > PDB > CDB > PDB
sqlplus system@SJW12C01 SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string SJW12C01 SQL> show con_id CON_ID ------------------------------ 1 SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter session set container = SJWPDB01; Session altered. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string SJW12C01 SQL> show con_id CON_ID ------------------------------ 3 SQL> show con_name CON_NAME ------------------------------ SJWPDB01 SQL> conn / as sysdba Connected. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 SJWPDB01 READ WRITE NO SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show con_id CON_ID ------------------------------ 1 SQL> alter session set container = SJWPDB01; Session altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 SJWPDB01 READ WRITE NO
Remark:
Starting Oracle 12.2 sys_context(‘USERENV’,’DB_NAME’) will show the name of the Database in CDB$ROOT and the name of the PDB inside the PDB.
Thanks, this is awesome info
Very useful info
Really good for new commer to container database.
Very useful…. thanks !
Fortunately we are getting the same name with sys_context(‘USERENV’, ‘DB_NAME’) and sys_context(‘USERENV’, ‘CON_NAME’) when connect to a PDB. Has this behavior changed after 12.1 ?
If you are in a CDB DB_NAME is the database name and CON_NAME is CDB$ROOT. If you are connected to a PDB both will be the PDB name. That’s valid even for 19c. If you need the Database name from within the PDB you can use DB_UNIQUE_NAME.
Hope that helps
Johannes
Hi Johannes,
Is there anyway to find without connecting to the database, In our case, we have consolidated and in server will have 10+ database where has every CDB may have atleast 3-4 PDB’s plugged into it. Since we created service for every PDB’s we are checking with listener status as its showing corresponding services and its CDB. Wondering is there any other method to find PDB and its CDB.
Thanks.
Hi Bala,
actually I don’t know any command which shows all PDBs for all CDBs. CDB fleet management might be a solution but that’s limited to EXA and cloud. So I wrote a shell script which connects to all CDB and select from v$pdbs. listener shows the services for the CDB but that does not help either because it doesn’t show the PDB information.
Regards
Johannes
Thank you very much for sharing this very helpful information.
Hi
I would suggest changing this from this thread also, since this is not correct.
“Pay attention to the DB_NAME. It will always return the name of the (Root) Container Database.”
This has been changed with Version 12.2. But thanks for the hint I will add it as a remark.
Useful information.
very useful ! thank you
Pingback: My Site