Oracle 12c: Connecting, Switching to Container Databases (CDB) and Pluggable Databases (PDB)

You can connect to other common users in similar way.

Container Database= C_ORCL and SID=CORCL

Pluggable Database=P_ORCL and SID=PORCL

c:\app\grid\vinay_singh\product\12.1.0\grid\BIN>set ORACLE_SID=CORCL

c:\app\grid\vinay_singh\product\12.1.0\grid\BIN>sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Wed Oct 2 17:00:37 2013

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

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

C_ORCL    READ WRITE

SQL> SELECT name, open_mode  FROM   v$pdbs ORDER BY name;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

P_ORCL                         READ WRITE

SQL> COLUMN name FORMAT A30

SQL>

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

SQL> SELECT name, pdb

  2  FROM   v$services

  3  ORDER BY name;

NAME                           PDB

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

CORCLXDB                       CDB$ROOT

C_ORCL                         CDB$ROOT

SYS$BACKGROUND                 CDB$ROOT

SYS$USERS                      CDB$ROOT

p_orcl                         P_ORCL

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

lsnrctl service

LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 02-OCT-2013 17:04:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

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

    Handler(s):

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

         LOCAL SERVER

Service "CORCLXDB" has 1 instance(s).

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

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: LP-101F74F2BAA3, pid: 4824>

         (ADDRESS=(PROTOCOL=tcp)(HOST=LP-101F74F2BAA3.HCLC.CORP.HCL.IN)(PORT=554
14))

Service "C_ORCL" has 1 instance(s).

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

    Handler(s):

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

         LOCAL SERVER

Service "p_orcl" has 1 instance(s).

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

    Handler(s):

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

         LOCAL SERVER

The command completed successfully

Connections using services are unchanged from previous versions.

SQL> -- EZCONNECT

SQL> CONN system/password@//localhost:1521/cdb1.localdomain

Connected.

SQL>

SQL> -- tnsnames.ora

SQL> CONN system/ora@c_orcl

Connected.

SQL>

Displaying the Current Container

The SHOW CON_NAME command in SQL*Plus displays the current container name.

SQL> SHOW CON_NAME

CON_NAME

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

CDB$ROOT

It can also be retrieved using the SYS_CONTEXT function.

SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME')

  2  FROM   dual;

SYS_CONTEXT('USERENV','CON_NAME')

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

CDB$ROOT

SQL> ALTER SESSION SET container = P_ORCL;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

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

P_ORCL

Switching Between Containers

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.

SQL> ALTER SESSION SET container = cdb$root;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

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

CDB$ROOT

Connecting to a Pluggable Database (PDB)

Direct connections to pluggable databases must be made using a service. Each pluggable database automatically registers a service with the listener. This is how any application will connect to a pluggable database, as well as administrative connections.

SQL> -- EZCONNECT

SQL> CONN system/ora@localhost:1521/p_orcl

Connected.

SQL> -- tnsnames.ora

SQL> CONN system/ora@p_orcl

Connected.

SQL>

PDB users with the SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a closed PDB. All other PDB users can only connect when the PDB is open. As with regular databases, the PDB users require the CONNECT SESSION privilege to enable connections.