Establish Connection in CDB and PDB

When it comes to Multi-tenant architecture,  one question that comes to our mind is how to connect to container database and pluggable database.

Today we will see all possible way to connect to the container database.

Obviously, we connect database using service name for the non-local connection. When we create CDB, a service is created with the same name as the database concatenated with the domain name.

And each pdb is had service associated with it having the same name we have given while creating pluggable database concatenated with the domain name.

We can check the service name for each pdb using cdb_services.

For example :

SQL> SELECT PDB,NAME FROM CDB_SERVICES;

PDB		     NAME
-------------------- --------------------
CDB$ROOT	     SYS$BACKGROUND
CDB$ROOT	     SYS$USERS
CDB$ROOT	     cdb122XDB
CDB$ROOT	     cdb122
PDB122		     pdb122

We can see here services. PDB122 database has pdb122 service associated. CDB$ROOT has cdb122 service associated as my cdb database name is cdb122.

Now various ways to connect to CDB.

1. We can connect with OS authentication.

[oracle@localhost ~]$ export ORACLE_SID=cdb122
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 14 05:51:12 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

2. Connect with service

[oracle@localhost admin]$ sqlplus system/oracle@cdb122

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 14 05:53:28 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

3. When we are connected to other pdb we can switch to cdb using alter session set container command.

Note: User needs SET CONTAINER privilege.

SQL> show con_name

CON_NAME
------------------------------
PDB122
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

To check how to connect with PDBs.

Stay tuned for More articles on Oracle Multitenant

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp