Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c

Connecting to the root of a container database is the same as that of any previous database instance. On the database server you can use OS Authentication.

 

[oracle@vmdb12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 30 11:38:44 2015

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  

The V$SERVICES views can be used to display available services from the database.

SQL> select name,pdb from v$services order by 2;

NAME PDB
——————– ———-
zwcXDB CDB$ROOT
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
ptest12c PTEST12C
ptest12c_1 PTEST12C_1
ptest12c_2 PTEST12C_2

6 rows selected.  

The lsnrctl utility allows you to display the available services from the command line.

[oracle@vmdb12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2015 12:17:38

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER_TEST12C

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 16-NOV-2015 18:02:53

Uptime 14 days 18 hr. 14 min. 45 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /data/amit/app/oracle/product/12.1.0/db_1/network/admin/listener.ora

Listener Log File /data/amit/app/oracle/diag/tnslsnr/bigdata2/listener_test12c/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost.localdomain)(PORT=5500))(Security=(my_wallet_directory=/data/amit/app/oracle/admin/test12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "PTEST12C" has 2 instance(s).

Instance "PTEST12C", status UNKNOWN, has 1 handler(s) for this service...

Instance "test12c", status READY, has 1 handler(s) for this service...

Service "ptest12c_1" has 1 instance(s).

Instance "test12c", status READY, has 1 handler(s) for this service...

Service "ptest12c_2" has 1 instance(s).

Instance "test12c", status READY, has 1 handler(s) for this service...

Service "ptest12c_3" has 1 instance(s).

Instance "test12c", status READY, has 1 handler(s) for this service...

Service "test12c" has 2 instance(s).

Instance "test12c", status UNKNOWN, has 1 handler(s) for this service...

Instance "test12c", status READY, has 1 handler(s) for this service...

Service "test12cXDB" has 1 instance(s).

Instance "test12c", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@vmdb12c ~]$ lsnrctl service 

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2015 12:19:05

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Service "PTEST12C" has 2 instance(s).

Instance "PTEST12C", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:71 refused:0

LOCAL SERVER

Instance "test12c", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

Service "ptest12c_1" has 1 instance(s).

Instance "test12c", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

Service "ptest12c_2" has 1 instance(s).

Instance "test12c", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

Service "ptest12c_3" has 1 instance(s).

Instance "test12c", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

Service "test12c" has 2 instance(s).

Instance "test12c", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:10 refused:0

LOCAL SERVER

Instance "test12c", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

Service "test12cXDB" has 1 instance(s).

Instance "test12c", status READY, has 1 handler(s) for this service...

Handler(s):

.......

The command completed successfully

Connections using services are unchanged from previous versions.
easy connect

[oracle@vmdb12c ~]$ sqlplus system/oracle123@202.137.235.171:1521/PTEST12C_2

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:24:39 2015

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

Last Successful login time: Tue Dec 01 2015 13:00:19 +05:30

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

------------------------------

PTEST12C_2

[oracle@vmdb12c ~]$ sqlplus system/oracle123@202.137.235.171:1521/PTEST12C_3

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:24:39 2015

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

Last Successful login time: Tue Dec 01 2015 13:00:19 +05:30

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

------------------------------

PTEST12C_3

tnsnames.ora


[oracle@vmdb12c ~]$sqlplus system/oracle@PTEST12C_3

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:47:25 2015

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

Last Successful login time: Tue Dec 01 2015 15:24:39 +05:30

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

------------------------------

PTEST12C_3

[oracle@vmdb12c ~]$sqlplus system/oracle@PTEST12C_1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:47:25 2015

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

Last Successful login time: Tue Dec 01 2015 15:24:39 +05:30

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

------------------------------

PTEST12C_1

tnsnames.ora

TEST12C =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test12c)

)

)

PTEST12C =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PTEST12C)

)

)

PTEST12C_1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PTEST12C_1)

)

)

PTEST12C_3 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PTEST12C_3)

)

)

LISTENER_TEST12C =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

When logged in to the CDB as an appropriately privileged user, the ALTER SESSION command can be used to switch between containers within the container database.

[oracle@vmdb12c ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:53:10 2015

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> alter session set container=PTEST12C_1;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

4 PTEST12C_1 READ WRITE NO

SQL> alter session set container=PTEST12C_2;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

5 PTEST12C_2 READ WRITE NO

SQL> alter session set container=cdb$root;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO 

4 PTEST12C_1 READ WRITE NO

5 PTEST12C_2 READ WRITE NO

6 PTEST12C_3 READ WRITE NO

SQL> select sys_context('USERENV','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————–
CDB$ROOT

SQL> alter session set container=PTEST12C_1; 

Session altered.

SQL> select sys_context('USERENV','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————– 
PTEST12C_1

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!