DBA Hub

📋Steps in this guide1/6

Multitenant : Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)

This article explains how to connect to container databases (CDB) and pluggable databases (PDB) on Oracle 12c Release 1 (12.1).

oracle 12cconfigurationintermediate
by OracleDba
16 views
1

Connecting to a Container Database (CDB)

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. You can connect to other common users in similar way. The views can be used to display available services from the database. The utility allows you to display the available services from the command line. Connections using services are unchanged from previous versions. The connection using a TNS alias requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file, such as the one shown below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
$ export ORACLE_SID=cdb1
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 26 15:29:49 2013

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


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

SQL>

SQL> CONN system/password
Connected.
SQL>

COLUMN name FORMAT A30

SELECT name, pdb
FROM   v$services
ORDER BY name;

NAME			       PDB
------------------------------ ------------------------------
SYS$BACKGROUND                 CDB$ROOT
SYS$USERS                      CDB$ROOT
cdb1                           CDB$ROOT
cdb1XDB                        CDB$ROOT
pdb1                           PDB1
pdb2                           PDB2

6 rows selected.

SQL>

$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 20-MAY-2014 09:01:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "cdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "cdb1XDB" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ol6-121.localdomain)(PORT=21196))
Service "pdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "pdb2" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
$

SQL> -- EZCONNECT
SQL> CONN system/password@//localhost:1521/cdb1
Connected.
SQL>

SQL> -- tnsnames.ora
SQL> CONN system/password@cdb1
Connected.
SQL>

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-121.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1)
    )
  )
2

Displaying the Current Container

The and commands in SQL*Plus display the current container name and ID respectively. They can also be retrieved using the function.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SQL> SHOW CON_NAME

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

SQL> SHOW CON_ID

CON_ID
------------------------------
1
SQL>

SELECT SYS_CONTEXT('USERENV', 'CON_NAME')
FROM   dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SQL>


SELECT SYS_CONTEXT('USERENV', 'CON_ID')
FROM   dual;

SYS_CONTEXT('USERENV','CON_ID')
--------------------------------------------------------------------------------
1

SQL>
3

Switching Between Containers

When logged in to the CDB as an appropriately privileged user, the command can be used to switch between containers within the container database. From Oracle 12.2 onward the command can also specify the service within the container. We can demonstrate this by creating a new service in a PDB, then switching to that service.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SQL> ALTER SESSION SET CONTAINER=pdb1;

Session altered.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB1
SQL> ALTER SESSION SET CONTAINER=cdb$root;

Session altered.

SQL> SHOW CON_NAME

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

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1'

BEGIN
  DBMS_SERVICE.create_service('my_new_service','my_new_service');
  DBMS_SERVICE.start_service('my_new_service');
END;
/

ALTER SESSION SET CONTAINER=pdb1 SERVICE=my_new_service;

COLUMN con_id FORMAT A10
COLUMN service_name FORMAT A30

SELECT SYS_CONTEXT('USERENV', 'CON_ID') AS con_id,
       SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service_name
FROM   dual;

CON_ID     SERVICE_NAME
---------- ------------------------------
6          my_new_service

SQL>
4

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. The connection using a TNS alias requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file, such as the one shown below. 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 privilege to enable connections.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> -- EZCONNECT
SQL> CONN system/password@//localhost:1521/pdb1
Connected.
SQL>

SQL> -- tnsnames.ora
SQL> CONN system/password@pdb1
Connected.
SQL>

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-121.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )
5

JDBC Connections to PDBs

It has already been mentioned that you must connect to a PDB using a service. This means that by default many JDBC connect strings will be broken. Valid JDBC connect strings for Oracle use the following format. When attempting to connect to a PDB using the SID format, you will receive the following error. Ideally, you would correct the connect string to use services instead of SIDs, but if that is a problem the USE_SID_AS_SERVICE_listener_name listener parameter can be used. Edit the "$ORACLE_HOME/network/admin/listener.ora" file, adding the following entry, with the "listener" name matching that used by your listener. Reload or restart the listener. Now both of the following connection attempts will be successful as any SIDs will be treated as services.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# Syntax
jdbc:oracle:thin:@[HOST][:PORT]:SID
jdbc:oracle:thin:@[HOST][:PORT]/SERVICE

# Example
jdbc:oracle:thin:@ol6-121:1521:pdb1
jdbc:oracle:thin:@ol6-121:1521/pdb1

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

USE_SID_AS_SERVICE_listener=on

$ lsnrctl reload

jdbc:oracle:thin:@ol6-121:1521:pdb1
jdbc:oracle:thin:@ol6-121:1521/pdb1
6

Data Pump Connections (expdp, impdp)

Connections to the and utilities are unchanged, provided you specify a service. Connections as SYSDBA must be to a common user. For example. For more information see: - Introduction to the Multitenant Architecture - Overview of the Multitenant Architecture - Managing a Multitenant Environment - Connections to Containers in a CDB - Multitenant : All Articles - Multitenant : Connecting to CDBs and PDBs Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
expdp username/password@service ...
expdp \"username/password@service as sysdba\" ...

impdp username/password@service ...
impdp \"username/password@service as sysdba\" ...

expdp scott/tiger@pdb1 tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
expdp \"sys/SysPassword1@pdb1 as sysdba\" tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
expdp \"c##myuser/MyPassword1@pdb1 as sysdba\" tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

Comments (0)

Please to add comments

No comments yet. Be the first to comment!