Tuesday 1 December 2015

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!!!




















Posted by



at





32 comments:
  


















Label

















        

      









Subscribe to:
Posts (Atom)