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 ways 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 has a service associated with it having the same name we have given while creating a 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.
Using OS authetication
We can connect with OS authentication.
$ export ORACLE_SID=cdb122 $ 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>
Connect with service
We can also connect using the tns service name as shown below:
$ 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>
Switching to CDB from PDB
When we are connected to other pdb we can switch to cdb using alter session set container command.
SQL> show con_name CON_NAME ------------------------------ PDB122
SQL> alter session set container=CDB$ROOT; Session altered.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT
How to Connect to PDBs with Same Name in Different CDBs
You have duplicated 2 auxiliary database instances from a single target CDB container database. Both instances reside on the same server and are registered against the same listener. How can I connect to a specific PDB with the same name but configured in different CDBs?
You’ll find that a single PDB service is registered under 2 instances. This is how LSNRCTL services might look:
Services Summary... The command completed successfully Service "PDB_1" has 2 instance(s). <==Pluggable database created in each container database Instance "cdb_1", status READY, has 1 handler(s) for this service... <== Container database1 Handler(s): "DEDICATED" established:63112 refused:0 state:ready Instance "cdb_2", status READY, has 1 handler(s) for this service... <== Container database2 Handler(s): "DEDICATED" established:349293 refused:0 state:ready
If you want to connect to a PDB service in a specific CDB instance, you can specify the INSTANCE_NAME in the TNS connect string. Set INSTANCE_NAME to point to the specific container database name.
Note that in our example, the SERVICE_NAME matches the PDB name. The INSTANCE_NAME in each TNS connect string is set to each CDB instance name.
PDB_1=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yourhosthere)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB_1) (INSTANCE_NAME = cdb_1)
)
)
PDB_2=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yourhosthere)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB_1) (INSTANCE_NAME = cdb_2)
)
)