The multitenant option introduced in Oracle Database 12c allows a single
container database (CDB) to host multiple separate pluggable databases (PDB).
Connecting to a Container Database (CDB)
Unlock
the HR user on PDB
We can use the Oracle Multitenant
option to configure and manage a multitenant environment. The multitenant
architecture enables an Oracle database to function as a multitenant container
database (CDB) that includes zero, one, or many customer-created pluggable
databases (PDBs).
A PDB is a portable collection of
schemas, schema objects, and nonschema objects that appears to an Oracle Net
client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.
The multitenant option represents
one of the biggest architectural changes in the history of the Oracle database.
The option introduced the concepts of the Container Database (CDB) and
Pluggable Database (PDB).
- Container
Database (CDB): On the surface this seems very similar to
a conventional Oracle database, as it contains most of the working parts
you will be already familiar with (control files, data files, undo, temp files,
redo logs etc.). It also houses the data dictionary for those objects that
are owned by the root container and those that are visible to all PDBs.
- Pluggable
Database (PDB): Since the CDB contains most of the
working parts for the database, the PDB only needs to contain information
specific to itself. It does not need to worry about control files, redo
logs and undo etc. Instead it is just made up of data files and temp files
to handle it's own objects. This includes it's own data dictionary,
containing information about only those objects that are specific to the
PDB.
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 we can use OS Authentication.
$ export
ORACLE_SID=orcl
$ 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
You can connect to other common
users in similar way.
SQL> CONN
system/password
Connected.
The V$SERVICES views can be used to display
available services from the database.
COLUMN name
FORMAT A30
SELECT name,
pdb FROM v$services ORDER BY name;
NAME PDB
------------------------------
------------------------------
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
orcl CDB$ROOT
orclXDB CDB$ROOT
pdborcl PDBORCL
5 rows
selected.
SQL>
The lsnrctl utility allows you to display
the available services from the command line.
$ lsnrctl
service
LSNRCTL for
Linux: Version 12.1.0.1.0 - Production on 10-JUL-2016 07:01:34
Copyright (c)
1991, 2013, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services
Summary...
Service
"orcl" has 1 instance(s).
Instance "orcl", status READY, has
1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0
refused:0 state:ready
LOCAL SERVER
Service
"orclXDB" has 1 instance(s).
Instance "orcl", 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=localhost)(PORT=21196))
Service
"pdborcl" has 1 instance(s).
Instance "orcl", 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/orcl
Connected.
SQL> --
tnsnames.ora
SQL> CONN
system/password@orcl
Connected.
The connection using a TNS alias
requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora"
file, such as the one shown below.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
Displaying
the Current Container
The SHOW CON_NAME and SHOW CON_ID commands in SQL*Plus display
the current container name and ID respectively.
SQL> SHOW
CON_NAME
CON_NAME
------------------------------
CDB$ROOT
SQL> SHOW
CON_ID
CON_ID
------------------------------
1
They can also be retrieved using
the SYS_CONTEXT function.
SELECT
SYS_CONTEXT('USERENV', 'CON_NAME')
FROM dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
SELECT
SYS_CONTEXT('USERENV', 'CON_ID')
FROM dual;
SYS_CONTEXT('USERENV','CON_ID')
--------------------------------------------------------------------------------
1
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 = pdborcl;
Session
altered.
SQL> SHOW
CON_NAME
CON_NAME
------------------------------
PDBORCL
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/password@//localhost:1521/pdborcl
Connected.
SQL> --
tnsnames.ora
SQL> CONN
system/password@pdborcl
Connected.
The connection using a TNS alias
requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora"
file, such as the one shown below.
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
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 CREATE SESSION privilege to enable connections.
Note: In a manual
'startup' Oracle will only start the CDB and not the PDBs unless we have
created a trigger to start them automatically. If we created the trigger then
Oracle will also start the PDBs one at a time and, depending on how many we
have, that can take a while.
In case of 12c when CDB (Container Database) is
started/Open, All the Pluggable databases are not started automatically,
instead all PDB's will be mount Status except SEED Database which will be Read
only mode.
When connecting
to PDB (Pluggable Database), we will receive the following error if PDB is not
open
ERROR:
ORA-01033: ORACLE
initialization or shutdown in progress
Process ID: 0
Session ID: 0
Serial number: 0
Let's check CDB
and PDB database status
select
instance_name,status from v$instance;
INSTANCE_NAME STATUS
----------------
-----------------------------
orcl OPEN
select
name,con_id,open_mode from v$pdbs;
NAME CON_ID
OPEN_MODE
——————————
———-—————————— ———-
PDB$SEED 2 READ
ONLY
PDBORCL 3 MOUNTED
So the PDBORCL is
only mounted not open although the instance is up and the container CDB is
open.
To find the name
of the service we will use v$active_services view. In most of
the cases oracle software creates database and its service with the same name.
SELECT name FROM
v$active_services WHERE con_id = 3;
So we need to open the PDB:
As DBA we need to
open the Pluggable database PDBORCL to allow access to users
1) sqlplus / as sysdba
2) ALTER SESSION SET CONTAINER =
pdborcl;
3) SHOW con_name;
4) ALTER PLUGGABLE DATABASE open;
OR
ALTER PLUGGABLE DATABASE PDBORCL OPEN;
OR
ALTER PLUGGABLE DATABASE ALL OPEN;
5) Let's check PDB database status
select name,con_id,open_mode from v$pdbs;
NAME CON_ID OPEN_MODE
—————————— ———-—————————— ———-
PDB$SEED 2
READ ONLY
PDBORCL 3 READ
WRITE
6) CONN system/system@pdborcl
OR
CONN system/system@//localhost:1521/pdborcl
Connected.
Unlock
the HR user on PDB
ALTER USER hr IDENTIFIED BY hr ACCOUNT unlock;
Login to PDB database using HR user
conn hr/hr@pdborcl
To avoid starting
PDB every time when Container database is started, we can write a trigger to
open all the pluggable databases.
Create or
replace trigger OPEN_PDBS
AFTER STARTUP
ON DATABASE
Begin
Execute immediate
‘ALTER PLUGGABLE DATABASE ALL OPEN';
End;
/
We get different
results when we query dba_data_files
depending on whether connected to the CDB or PDB:
1) sqlplus / as
sysdba
2) select file_name from
dba_data_files;
CDB
FILE_NAME
------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
3)
CONN system/system@pdborcl
Connected.
4)
select file_name from
dba_data_files;
PDB
FILE_NAME
--------------------------------------------------------------
/u01/app/oracle/oradata/orcl/pdborcl/system01.dbf
/u01/app/oracle/oradata/orcl/pdborcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/orcl/pdborcl/example01.dbf
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.
# Syntax
jdbc:oracle:thin:@[HOST][:PORT]:SID
jdbc:oracle:thin:@[HOST][:PORT]/SERVICE
# Example
jdbc:oracle:thin:@localhost:1521:pdborcl
jdbc:oracle:thin:@localhost:1521/pdborcl
When attempting to connect to a PDB
using the SID format, we will receive the following error.
ORA-12505,
TNS:listener does not currently know of SID given in connect descriptor
Ideally, we 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 our listener.
USE_SID_AS_SERVICE_listener=on
Reload or restart the listener.
$ lsnrctl
reload
Now both of the following
connection attempts will be successful as any SIDs will be treated as services.
jdbc:oracle:thin:@localhost:1521:pdborcl
jdbc:oracle:thin:@localhost:1521/pdborcl
