Multitenant (Pluggable
Management)
Introduction
Find information on CDB
and PDB
Login to Specific
PDB, Mount Options
Management of Users
in CDB's and PDB's
Change Initialization
Parameters
Manually Creation
of Pluggable Database (PDB)
Get DataFile
information from PCD or CDB
Rename, Clone and Plug a
Database
Create a PDB from a non-CBD
Other Actions with
PDB's (Open, Close, Alter system, Check History)
RMAN on PDB's and CD's
Triggers
Upgrade
a PDB Using Unplug/Plugin
More Information (12.2)
Introduction
What is
Multitenant Architecture?
It is quite simple, Multiple tenants
share same resources on a mutual benefit for different
purposes at a very broad level. The same applies to Oracle
Database where Multiple Databases share a single instance of
resources aiming for different purposes on the same Server.
This Oracle Database which is built on Multitenant foundation
is called Container Database(CDB), and each container(tenant)
residing inside is called Pluggable Database(PDB,
Container).
Benefits
- It is
completely transparent to Application, there is
no specific configuration required to connect to a
Database in Multitenant Architecture over a network. It
works the same way as it works with Non CDBs. Every
Container or PDB has its own Service name to allow
connections from Clients like JDBC/ODBC/XA/SQPLUS etc...
- A Multitenant
Container Database is created using either DBCA or "CREATE
DATABASE" Command
- A Multitenant
Container Database or shortly a CDB, can be created with
an empty container, no Pluggable Databases or shortly
PDB(s) associated with it, [or] with one or more
containers(More than one PDB)
- Memory
configurable is sum of memory required for all PDBs, is
distributed on demand across all PDBs
- A CDB can
contain 253 PDBs including the SEED Container
- Starting up and
Shutting down a CDB is no different than a Non-CDB except
we need to manually mount and dismount associated PDBs
- The Architecture basically
consists following containers
- Root
container - It is the core of the CDB, contains all
Oracle supplied metadata like packages,
core dictionary objects, and common users. It
is denoted as CDB$ROOT
- SEED
Container (SEED PDB) - This is a template database
distributed for new PDBs, whenever we create a new PDB,
Oracle copies mandatory datafiles (common datafiles) for
creation of PDB from this SEED Database. This is by
default in READ-ONLY mode, can't be altered or modified.
This is denoted as PDB$SEED
- User defined
PDB Container (PDB) - This is the Pluggable Database
created for Application purpose, contains only Consumer
Data or USER Data
- CDB has common
user environment. A User exists in the ROOT Container can
login to any PDB with the privileges given. A CDB also has
local users exclusive to PDBs. A Common user and role name
must start with C## or c## and consists only of ASCII
characters.
- If a common
user is required to login to a container, "c##" must be
supplied as suffix. For example, If a common user
"PDBADMIN" has privilege to acces a container PDB, We need
to use it as "C##PDBADMIN" in order to
login successfully to container PDB
- "CONTAINER=ALL"
clause can be used on root container
while performing different tasks such as "ALTER
SYSTEM", "ALTER DATABASE", "ALTER USER" etc.. This clause
sets the specific action or configuration to all available
PDBs, as well as future PDBs
- Control files,
REDO Logs, and UNDO files are owned by ROOT Container as
these Database files are Instance specific. Every PDB has
its own set of SYSTEM/SYSAUX/TEMP tablespaces, and also
includes the Sample User data such as SCOTT, HR etc..
- ENABLE_PLUGGABE_DATABASE
initialization parameter specifies If a particular
Database is CDB or Non-CDB
- By default
Oracle instance mounts the Pluggable Database, so it has
to be manually opened into READ/WRITE Mode. In case of
Cluster, it has to be opened in each instance
Service Name.
When you create a PDB, Oracle automatically adds it as a service
in the listener. You can confirm it by looking at the listener
status
Find
information on CDB and PDB
To find If a Database is CDB or Non-CDB
SELECT NAME, CDB, CON_ID FROM V$DATABASE;
NAME CDB
CON_ID
--------- --- ----------
DIEGO
YES 0
or
SHOW PARAMETER enable_pluggable_database
NAME
TYPE VALUE
-------------------------- --------
-------------
enable_pluggable_database boolean
TRUE
To find the information about Pluggable Databases,
COL PDB_NAME FOR A30
SELECT PDB_ID,PDB_NAME,STATUS,CON_ID FROM CDB_PDBS;
PDB_ID PDB_NAME
STATUS CON_ID
---------- ----------- ------ ----------
3
PDB
NORMAL 1
2
PDB$SEED NORMAL 1
4
SALESPDB NORMAL 1
If you query the CDB_PDBS view you can receive
useful information about all PDBs contained: id, name, unique
identifiers, SCN at the time of creation and the status.
The STATUS column has several values:
- NEW, the pdb has been created but never
opened
- NORMAL, pdb available to be used
- UNPLUGGED, the pdb has been unplugged
- NEEDS UPGRADE, the pdb must be upgraded
- CONVERTING, a non-CDB is going to be converted into
a pdb
- UNUSABLE, the pdb cannot be used (creation in
progress)
or
select name, open_mode from v$pdbs;
NAME
OPEN_MODE
------------- ----------
PDB$SEED READ ONLY
TEST
MOUNTED
You can check the containers (or PDBs) created in a database in a
view named V$PDBS, which is brand new in Oracle Database 12c.
select con_id, dbid, name from v$pdbs;
CON_ID
DBID NAME
---------- ---------- ------------------------------
2
4050437773 PDB$SEED
3
3315520345 PDB1
4 3874438771
PDB2
5 3924689769
PDB3
Note how the DBIDs are also different for each PDB. There are two
striking oddities in this output:
- There is no CON_ID of 1. The answer is simple - there is a
special container called the "root" container, known as CDB$Root
that is created to hold the metadata. This container has the
CON_ID of 1.
- There is a PDB called PDB$SEED, which is something we didn't
create.
Login to Specific PDB, Mount Options
To login to a Container or a specific PDB
There are two ways we can login into a Container or PDB
1. SQLPLUS/CONNECT - This requires a TNS entry to login to a
specific PDB locally or remotely
2. ALTER SESSION
ALTER SESSION SET CONTAINER=SALESPDB;
show con_name
Now all commands in this session will be executed in the context of
the PDB called PDB1.
To switch between Containers using ALTER SESSION,
1. To switch to SEED Container,
ALTER SESSION SET CONTAINER=PDB$SEED;
2. To switch to ROOT Container,
ALTER SESSION SET CONTAINER=CDB$ROOT;
Startup PDB
Startup pluggable database pdb1 open;(read/write)
Startup pluggable database pdb1 open read only;
Startup pluggable database pdb1 force;
(closes/opens)
To mount or dismount and find the status of a PDB,
ALTER PLUGGABLE DATABASE PDB OPEN;
ALTER PLUGGABLE DATABASE PDB CLOSE IMMEDIATE;
[OR]
SHUT IMMEDIATE
For multiple PDBs,
ALTER PLUGGABLE DATABASE PDB,PDB1,SALESPDB OPEN;
ALTER PLUGGABLE DATABASE PDB,PDB1,SALESPDB CLOSE;
SELECT NAME,OPEN_MODE,TOTAL_SIZE/1024/1024/1024 FROM
V$PDBS;
NAME
OPEN_MODE TOTAL_SIZE/1024/1024/1024
----------- ---------- -------------------------
PDB$SEED READ
ONLY
.263671875
PDB READ
WRITE
.424804688
SALESPDB MOUNTED
0
Now you can open/close all pluggable database :
Alter pluggable database all Open;
Alter pluggable database all close ;
To modify any PDB Characteristics,
"ALTER PLUGGABLE DATABASE" is the command that replaces the "ALTER
DATABASE" command in a CDB Environment. Remember that you must be
connected to the the PDB before creating the tablespace.
CREATE TABLESPACE TS_PDB DATAFILE '/dbs/CONDB/PDB/ts_pdb_01.dbf'
SIZE 10M;
ALTER PLUGGABLE DATABASE PDB DEFAULT TABLESPACE TS_PDB;
Management
of Users in CDB's and PDB's
When connected to a multitenant database the management of users and
privileges is a little different to traditional Oracle environments.
In multitenant environments there are two types of user.
- Common User : The user is present in all containers
(root and all PDBs).
- Local User : The user is only present in a specific
PDB. The same username can be present in multiple PDBs, but they
are unrelated.
Likewise, there are two types of roles.
- Common Role : The role is present in all containers
(root and all PDBs).
- Local Role : The role is only present in a specific
PDB. The same role name can be used in multiple PDBs, but they
are unrelated.
Some DDL statements have a CONTAINER clause added to allow them to
be directed to the current container or all containers.
Create a Common user in the ROOT Container and
accessing ALL PDBs,
If you want to create a user that is visible to all the PDBs in that
CDB, you should create a *common* user. Logging into the container
database (CDB) as a DBA user, you can create a common user as shown
below:
A Common user and role name must start with C## or c## and consists
only of ASCII characters.
show con_name
CDB$ROOT
CREATE USER C##PDB IDENTIFIED BY PDB;
GRANT CREATE SESSION TO C##PDB;
GRANT CREATE SESSION TO C##PDB CONTAINER=ALL; (ALL
Specifies all Containers or PDBs)
CONNECT C##PDB@PDB
Enter password:
Connected.
Create a Local user
The following example shows how to create local users with and
without the CONTAINER clause from the root container.
CONN / AS SYSDBA
-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdb1;
-- Create the local user using the CONTAINER clause.
CREATE USER test IDENTIFIED BY test1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test CONTAINER=CURRENT;
-- Connect to a privileged user in the PDB.
CONN system/password@pdb1
-- Create the local user using the default CONTAINER
setting.
CREATE USER test_user4 IDENTIFIED BY password1;
GRANT CREATE SESSION TO test_user4;
If a local user is to be used as a DBA user, it requires the PDB_DBA
role granted locally to it.
A similar logic applied to roles: Common Roles must start with c##.
Change
Initialization Parameters
Configure Instance Parameters in a CDB (ALTER SYSTEM)
When connected as a privileged user and pointing to the root
container, any ALTER SYSTEM command will by default be directed at
just the root container. This means the following two commands are
functionally equivalent in this context.
ALTER SYSTEM SET parameter_name=value;
ALTER SYSTEM SET parameter_name=value CONTAINER=CURRENT;
In addition to the default action, an initialization parameter
change from the root container can target all containers using the
following syntax.
ALTER SYSTEM SET parameter_name=value CONTAINER=ALL;
By using CONTAINER=ALL you are instructing the PDBs that they should
inherit the specific parameter value from the root container. Unless
overridden by a local setting for the same parameter, any subsequent
local changes to the root container for this specific parameter will
also be inherited by the PDBs.
The PDBs are able to override some parameter settings by issuing a
local ALTER SYSTEM call from the container.
Configure Instance Parameters in a PDB (ALTER SYSTEM)
Only a subset of the initialization parameters can be modified
locally in the PDB. These can be displayed using the following
query.
COLUMN name FORMAT A35
COLUMN value FORMAT A35
SELECT name, value
FROM v$system_parameter
WHERE ispdb_modifiable = 'TRUE'
ORDER BY name;
To make a local PDB change, make sure you are either connected
directly to a privileged use in the PDB, or to a privileged common
user, who has their container pointing to the PDB in question. As
mentioned previously, if the CONTAINER clause is not mentioned, the
current container is assumed, so the following ALTER SYSTEM commands
are functionally equivalent.
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;
ALTER SYSTEM SET parameter_name=value;
ALTER SYSTEM SET parameter_name=value CONTAINER=CURRENT;
Instance-level parameter changes in the root container are stored in
the SPFILE in the normal way. When you change PDB-specific
initialization parameters in the PDB they are not stored in the
SPFILE. Instead they are saved in the PDB_SPFILE$ table. See
documentation here.
Some extra (and good ) examples:
Remember, to target the
PDB you must either connect directly to a privileged user using a
service pointing to the PDB, or connect to the root container and
switch to the PDB container. Some of the possible PDB
modifications are shown below.
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;
-- Default edition for PDB.
ALTER PLUGGABLE DATABASE DEFAULT EDITION = ora$base;
-- Default tablespace type for PDB.
ALTER PLUGGABLE DATABASE SET DEFAULT BIGFILE TABLESPACE;
ALTER PLUGGABLE DATABASE SET DEFAULT SMALLFILE TABLESPACE;
-- Default tablespaces for PDB.
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE users;
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp;
-- Change the global name. This will change the container name and the
-- name of the default service registered with the listener.
ALTER PLUGGABLE DATABASE OPEN RESTRICTED FORCE;
ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO pdb1a.localdomain;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN;
-- Time zone for PDB.
ALTER PLUGGABLE DATABASE SET TIME_ZONE='GMT';
-- Make datafiles in the PDB offline/online and make storage changes.
ALTER PLUGGABLE DATABASE DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf' OFFLINE;
ALTER PLUGGABLE DATABASE DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf' ONLINE;
ALTER PLUGGABLE DATABASE DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf'
RESIZE 1G AUTOEXTEND ON NEXT 1M;
-- Supplemental logging for PDB.
ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER PLUGGABLE DATABASE DROP SUPPLEMENTAL LOG DATA;
In addition there is a
mechanism to control the maximum size of the PDB and the amount of
the shared temp space it can use.
-- Limit the total storage of the the PDB (datafile and local temp files).
ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 5G);
-- Limit the amount of temp space used in the shared temp files.
ALTER PLUGGABLE DATABASE STORAGE (MAX_SHARED_TEMP_SIZE 2G);
-- Combine the two.
ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 5G MAX_SHARED_TEMP_SIZE 2G);
-- Remove the limits.
ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;
Manually
Creation of Pluggable Database (PDB)
There are many ways to create a PDB:
- Create a PDB by copying the seed PDB
- Create a PDB by cloning another PDB
- Create a PDB by using the XML metadata files and other files
and plugging them into a CDB
- Create a PDB using a non-CDB (multiple ways)
- Use DBMS_PDB to create an unplugged PDB
- Create an empty PDB and use data pump to move data
- Using GoldenGate replication to create
Create a PDB from Scratch
If we are using Oracle Managed Files (OMF) we don't need to worry
about the file placement. Oracle will handle it for us.
ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';
From 12.1.0.2 onward there is an inline variation of this using the
CREATE_FILE_DEST clause. The path set in this clause will be used as
the OMF location for the new PDB.
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY
Password1 CREATE_FILE_DEST='/u01/app/oracle/oradata';
The second method uses the FILE_NAME_CONVERT clause in the CREATE
PLUGGABLE DATABASE statement.
create pluggable database TEST admin user admin identified
by password1 file_name_convert=
('/u01/app/oracle/oradata/db12c/pdbseed/','/u01/app/oracle/oradata/db12c/test/');
Alternatively, we can specify the PDB_FILE_NAME_CONVERT
initialization parameter before calling the command without using
the FILE_NAME_CONVERT clause.
CONN / AS SYSDBA
ALTER SESSION SET
PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb3/';
CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm
IDENTIFIED BY Password1;
CREATE PLUGGABLE DATABASE dwpdb ADMIN USER dwadm IDENTIFIED BY
password ROLES=(SELECT_CATALOG_ROLE,
GATHER_SYSTEM_STATISTICS);
(PDB_DBA role is also granted in addition to the above specifically
granted roles.)
CREATE PLUGGABLE DATABASE dwpdb ADMIN USER dwadm IDENTIFIED BY
password STORAGE (MAXSIZE 10G MAX_SHARED_TEMP_SIZE 100M) DEFAULT
TABLESPACE dw DATAFILE '/disk1/oracle/dbs/dwpdb/dw1.dbf' SIZE 2G
AUTOEXTEND ON PATH_PREFIX = '/disk1/oracle/dbs/dwpdb/'
FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/',
'/disk1/oracle/dbs/dwpdb/');
The PDBs are created with the status of 'NEW'. They must be opened
in READ WRITE mode at least once for the integration of the PDB into
the CDB to be complete.
ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;
select pdb_name, status from cdb_pdbs;
PDB_NAME
Status
----------------------- ------------
PDB$SEED
NORMAL
TEST
NEW
select name, open_mode from v$pdbs;
NAME
OPEN_MODE
----------- ----------
PDB$SEED READ ONLY
TEST
MOUNTED
You can also use the SHOW PDBS command from SQL*Plus.
SQL> SHOW PDBS
NAME
OPEN_MODE
----------- ----------
PDB$SEED READ ONLY
TEST
MOUNTED
select name, con_id from v$active_services order by 1;
NAME
CON_ID
---------------- ------
SYS$BACKGROUND 1
SYS$USERS
1
db12c
1
db12cXDB
1
TEST
3
The new Pluggable DB has a Con_id=3. Most of Oracle Data Dictionary
contains new_column called con_id , to check datafile related
to new pluaggable database :
select name from v$datafile where con_id=3 ;
NAME
-----------------------------------------------
/u01/app/oracle/oradata/db12c/gls/system01.dbf
/u01/app/oracle/oradata/db12c/gls/sysaux01.dbf
Get
data file Information for a PDB or CDB:
List all the Files (CDB+PDB)
SELECT file#, name FROM v$datafile ORDER BY file#;
FILE# NAME
----------
----------------------------------------------------------------------
1
/u01/app/oracle/oradata/cdb1/system01.dbf
3
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
4
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
5
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
6
/u01/app/oracle/oradata/cdb1/users01.dbf
7
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
8
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
9
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
10
/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
29
/u01/app/oracle/oradata/pdb2/system01.dbf
30
/u01/app/oracle/oradata/pdb2/sysaux01.dbf
31
/u01/app/oracle/oradata/pdb2/pdb2_users01.dbf
List Files for a Specific PDB
select file_name, con_id from cdb_data_files where con_id=3 ;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db12c/gls/system01.dbf
/u01/app/oracle/oradata/db12c/gls/sysaux01.dbf
select file_name, con_id from cdb_temp_files where
con_id=3;
FILE_NAME
CON_ID
----------------------------------------------------
------
/u01/app/oracle/oradata/db12c/gls/pdbseed_temp01.dbf
3
We Control which list for for tablespace & datafiles by using con_id.
List tablespace in root container
select tablespace_name, con_id from cdb_tablespaces where
con_id=1;
TABLESPACE_NAME CON_ID
----------------- ----------
SYSTEM
1
SYSAUX
1
UNDOTBS1
1
TEMP
1
USERS
1
CDATA
1
List Database Files In root Container
select file_name, con_id from cdb_data_files where con_id=1;
FILE_NAME
CON_ID
---------------------------------------------------
----------
/u01/app/oracle/oradata/db12c/users01.dbf
1
/u01/app/oracle/oradata/db12c/undotbs01.dbf
1
/u01/app/oracle/oradata/db12c/sysaux01.dbf
1
/u01/app/oracle/oradata/db12c/system01.dbf
1
/u01/app/oracle/oradata/db12c/gls/test.dbf
1
Temp Tablespace in root container
select file_name, con_id from cdb_temp_files where con_id=1;
FILE_NAME
CON_ID
-------------------------------------------- ----------
/u01/app/oracle/oradata/db12c/temp01.dbf 1
Where is Everything?
SELECT d.con_ID, p.PDB_NAME, d.FILE_ID,
d.TABLESPACE_NAME,
d.FILE_NAME
FROM CDB_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID(+) = d.CON_ID
order by d.con_id;
CON_ID
PDB FILE_ID
TABLESPACE_NAME FILE_NAME
--------- --------- --------- ----------------
-----------------------------------------------
1
6
USERS
/u01/app/oracle/oradata/cdb1/users01.dbf
1
4
UNDOTBS1
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
1
3
SYSAUX
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
1
1
SYSTEM
/u01/app/oracle/oradata/cdb1/system01.dbf
2
PDB$SEED
2
SYSTEM
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
2
PDB$SEED
5
SYSAUX
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
3
PDB1
7
SYSTEM
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
3
PDB1
8
SYSAUX
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
4
PDB_SS
9
SYSTEM
/u01/app/oracle/oradata/cdb1/pdb_ss/system01.dbf
4
PDB_SS
10
SYSAUX
/u01/app/oracle/oradata/cdb1/pdb_ss/sysaux01.dbf
4
PDB_SS
11
EXAMPLE
/u01/app/oracle/oradata/cdb1/pdb_ss/example.dbf
More Information HERE
Rename,
Clone and Plug a Database
Rename Pluggable database
alter pluggable database TEST close immediate ;
alter pluggable database TEST open restricted ;
select name, open_mode from v$pdbs;
alter pluggable database TEST rename global_name to NEW;
select name, con_id from v$active_services order by 1;
NAME CON_ID
------ ------
NEW 3
if you are not connected to pluggable database or set session
container then you will receive error message
ORA-65046: operation not allowed from outside a pluggable
alter pluggable database NEW close immediate
;
alter pluggable database NEW open ;
Cloning a PDB
alter pluggable database pdb1 close;
alter pluggable database pdb1 open read only;
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1; --
Cloning a New PDB From Another PDB
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
PATH_PREFIX = '/disk2/oracle/pdb2' FILE_NAME_CONVERT =
('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/');
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/',
'/disk2/oracle/pdb2/') STORAGE (MAXSIZE 2G
MAX_SHARED_TEMP_SIZE 100M);
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link;
Once the new DB is cloned, the new PDB must be opened in READ
WRITE mode:
ALTER PLUGGABLE DATABASE pdb1 close;
ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb2 OPEN;
You can also generate a cloned PDB without data (metadata only)
using the NO DATA parameter:
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb1/pdb2/')
NO DATA;
You can alse generate a cloned PDB with JUST some tablespaces from
another database using the USER_TABLESPACES:
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2/','/u01/app/oracle/oradata/cdb1/pdb1/')
USER_TABLESPACES=('ts1', 'ts2');
The following example creates a clone with none of the user-defined
tablespaces present.
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2/','/u01/app/oracle/oradata/cdb1/pdb1/')
USER_TABLESPACES=NONE;
Finally the ALL EXCEPT variant allows you to list those tablespaces
to be excluded.
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2/','/u01/app/oracle/oradata/cdb1/pdb1/')
USER_TABLESPACES=ALL EXCEPT('ts3');
Plug and Unplug a Database
Before performing this action, the PDB must be CLOSE or open in READ
ONLY mode:
alter pluggable database NEW close immediate
;
alter pluggable database NEW open
read only force ;
Unplug database included with xml file :
alter pluggable database NEW unplug
into '/u01/app/oracle/oradata/NEW.xml';
I can Drop the databases....
drop pluggable database NEW keep
datafiles ;
DROP PLUGGABLE DATABASE NEW INCLUDING
DATAFILES;
And Plug them back. Before Plug Database to any Container you need
to make sure from compatibility , there's PL/SQL code written by
Oracle to check compatibility.
DECLARE
compatible BOOLEAN := FALSE;
BEGIN
compatible :=
DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file =>
'/u01/app/oracle/oradata/NEW.xml', pdb_name=> 'NEW');
if compatible then
DBMS_OUTPUT.PUT_LINE('Is
pluggable NEW compatible? YES');
else DBMS_OUTPUT.PUT_LINE('Is pluggable NEW
compatible? NO');
end if;
END;
/
Is pluggable NEW compatible?
YES
If the PDB is not compatible, violations are listed in the
PDB_PLUG_IN_VIOLATIONS view.
Let's Plug the Database:
create pluggable database NEW2 using
'/u01/app/oracle/oradata/NEW.xml'
nocopy tempfile reuse ;
OR
create pluggable database NEW2 using
'/u01/app/oracle/oradata/NEW.xml'
source_file_name_convert=('/app/oracle/oradata/CDB001','/opt/app/oracle/oradata/CDBTEST')
nocopy tempfile reuse;
Another method to plug database with a new new name from an existing
one:
create pluggable database NEW_PLUG_COPY
using '/u01/app/oracle/oradata/NEW.xml'
copy FILE_NAME_CONVERT=('/u01/app/oracle/oradata/NEW','/u01/app/oracle/oradata/NEW_PLUG_COPY');
Therefore
Copy Clause : if you want the files listed in the XML
file to be copied to the new location and used for the new PDB.
Nocopy Clause: if you want the files for the PDB to remain in their
current locations.
But what if i want to move all datafiles and create new pluggable
database, Oracle provides you with new clause to do this which is
"move" check below :
create pluggable database NEW_PLUG_MOVE
as clone using '/u01/app/oracle/oradata/NEW_2.xml'
move FILE_NAME_CONVERT=('/u01/app/oracle/oradata/NEW','/u01/app/oracle/oradata/MOVE');
I can also create a new pluggable database from one that is already
in READ only:
alter pluggable database PDB001 open read only force;
select name, open_mode from v$pdbs;
NAME
OPEN_MODE
------------ ----------
PDB$SEED READ ONLY
PDB001 READ ONLY
PDB002 READ WRITE
create pluggable database PDB003 from PDB001
file_name_convert=('/app/oracle/oradata/CDB001/PDB001','/app/oracle/oradata/CDB001/PDB003');
After the command is sucessfully completed the status and the open
mode of the new pluggable database are NEW and MOUNTED.
select PDB_NAME, STATUS from CDB_PDBS;
PDB_NAME STATUS
---------- -------------
PDB$SEED NORMAL
PDB001 NORMAL
PDB002 NORMAL
PDB003 NEW
select name, open_mode from V$PDBS;
NAME
OPEN_MODE
----------- ----------
PDB$SEED READ ONLY
PDB001 READ ONLY
PDB002 READ WRITE
PDB003 MOUNTED
A new service is created too.
select name, pdb from V$SERVICES order by creation_date;
NAME
PDB
-------------- ------------------------------
CDB001XDB CDB$ROOT
SYS$BACKGROUND CDB$ROOT
CDB001
CDB$ROOT
SYS$USERS CDB$ROOT
pdb001
PDB001
pdb002
PDB002
pdb003
PDB003
alter pluggable database PDB001,PDB003 open READ WRITE FORCE;
select name, open_mode from V$PDBS;
NAME OPEN_MODE
---------- ----------
PDB$SEED READ ONLY
PDB001 READ WRITE
PDB002 READ WRITE
PDB003 READ WRITE
select PDB_NAME, STATUS from CDB_PDBS;
PDB_NAME STATUS
---------- -------------
PDB$SEED NORMAL
PDB001 NORMAL
PDB002 NORMAL
PDB003 NORMAL
Add the following entry on the tnsnames.ora file.
PDB003 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
10.0.2.15)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB003)
)
)
Clone a PDB with SQL Developer
The DBA section of SQL Developer includes tree node called
"Container Database".

Right-clicking on the
"Container Database" node produces a popup menu showing you what
operations are available.

Right-clicking on a
specific PDB node produces a popup menu showing only those
operations that are relevant to that PDB.

If you understand the
DBCA and SQL*Plus approach to managing PDBs, these SQL Developer
screens are very straight forward.
Create and Manage with CC(Use 12c Cloud Control – OEM)
Go to the CDB target & manage storage & objects.
Under Provisioning & Patching – Provision (create or clone) or
Unplug Pluggable databases
You can create multiple PDBs at once
View Job Details under Procedure Activity
Go to CDB target (as a common user) and then look at PDB level … you
can switch containers and refresh to look at specific PDB
information
Cloud Control 12cR3
onward supports pluggable database functionality. Once you click
on the container database, the "Oracle Database > Control >
Open/Close Pluggable Database" menu option allows you to control
the state of the PDBs owned by the CDB.

The "Oracle Database
> Provision > Provision Pluggable Database" menu option
allows you to perform other operations PDBs owned by the CDB,
including cloning, unplugging amongst other things.

Create a PDB from
a NON-CDB
There are several ways to migrate a non CDB to a PDB:
Here we are going to discuss the last one (using the DBMS_PDB
Package).
I have this information already in my BLOG here.
I want to consolidate this database
plugging it into a container database.
In this scenario I'm going to use the DBMS_PDB package to create the
XML file with the metadata of ORCL database.
Before using the DBMS_PDB.DESCRIBE procedure, the ORCL
database needs to be in READ ONLY mode.
$ export ORACLE_SID=ORCL
$ sqlplus / as sysdba
SQL@ORCL> select name, open_mode from V$DATABASE;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
SQL@ORCL> shutdown immediate;
SQL@ORCL> startup mount;
SQL@ORCL> alter database open read only;
SQL@ORCL> begin
2 dbms_pdb.describe( pdb_descr_file => '/opt/app/oracle/oradata/orcl.xml');
3 end;
4 /
SQL@ORCL> host ls /opt/app/oracle/oradata/orcl*
/opt/app/oracle/oradata/orcl.xml
SQL@ORCL> shutdown immediate;
SQL@ORCL> exit
Now connect to the container database.
In my case I want to plug ORCL database into the CDBTEST
container. It currently contains 4 pluggable databases.
$ export ORACLE_SID=CDBTEST
$ sqlplus / as sysdba
SQL@CDBTEST> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL@CDBTEST> select name, open_mode from V$PDBS;
NAME OPEN_MODE
------------ ----------
PDB$SEED READ ONLY
PDBTEST1 READ WRITE
PDBTEST2 READ WRITE
PDBTEST3 READ WRITE
PDB003 READ ONLY
Here is the situation of the directories
for the CDBTEST container database:
[oracle@vsi08devpom ~]$ cd /opt/app/oracle/oradata/CDBTEST/
[oracle@vsi08devpom CDBTEST]$ ll
-rw-r----- 1 oracle oinstall 17973248 Aug 7 11:13 control01.ctl
drwxr-x--- 2 oracle oinstall 4096 Aug 1 14:54 PDB003
drwxr-x--- 2 oracle oinstall 4096 Jul 17 11:25 pdbseed
drwxr-x--- 2 oracle oinstall 4096 Jul 17 13:16 PDBTEST1
drwxr-x--- 2 oracle oinstall 4096 Jul 17 13:17 PDBTEST2
drwxr-x--- 2 oracle oinstall 4096 Jul 17 13:18 PDBTEST3
-rw-r----- 1 oracle oinstall 52429312 Aug 7 11:12 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Aug 7 00:16 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Aug 7 06:00 redo03.log
-rw-r----- 1 oracle oinstall 1310728192 Aug 7 11:12 sysaux01.dbf
-rw-r----- 1 oracle oinstall 828383232 Aug 7 11:12 system01.dbf
-rw-r----- 1 oracle oinstall 63971328 Aug 7 11:01 temp01.dbf
-rw-r----- 1 oracle oinstall 1205870592 Aug 7 11:12 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Aug 7 06:05 users01.dbf
Now you can plug the ORCL database into
the CDBTEST container database using the usual syntax
SQL@CDBTEST> create pluggable database ORCL using '/opt/app/oracle/oradata/orcl.xml' copy file_name_convert=('/opt/app/oracle/oradata/ORCL','/opt/app/oracle/oradata/CDBTEST/ORCL');
Pluggable database created.
The Oracle software copies under the
directory /opt/app/oracle/oradata/CDBTEST/ORCL all the datafiles
coming from /opt/app/oracle/oradata/ORCL location.
[oracle@vsi08devpom CDBTEST]$ ll
total 3459788
-rw-r----- 1 oracle oinstall 17973248 Aug 7 12:10 control01.ctl
drwxr-x--- 2 oracle oinstall 4096 Aug 7 11:49 ORCL
drwxr-x--- 2 oracle oinstall 4096 Aug 1 14:54 PDB003
drwxr-x--- 2 oracle oinstall 4096 Jul 17 11:25 pdbseed
drwxr-x--- 2 oracle oinstall 4096 Jul 17 13:16 PDBTEST1
drwxr-x--- 2 oracle oinstall 4096 Jul 17 13:17 PDBTEST2
drwxr-x--- 2 oracle oinstall 4096 Jul 17 13:18 PDBTEST3
-rw-r----- 1 oracle oinstall 52429312 Aug 7 12:10 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Aug 7 00:16 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Aug 7 06:00 redo03.log
-rw-r----- 1 oracle oinstall 1310728192 Aug 7 12:10 sysaux01.dbf
-rw-r----- 1 oracle oinstall 828383232 Aug 7 12:07 system01.dbf
-rw-r----- 1 oracle oinstall 63971328 Aug 7 12:03 temp01.dbf
-rw-r----- 1 oracle oinstall 1205870592 Aug 7 12:10 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Aug 7 06:05 users01.dbf
A new pluggable database is created with
a NEW status
SQL@CDBTEST> select pdb_id, pdb_name, dbid, status, creation_scn, con_id from CDB_PDBS;
PDB_ID PDB_NAME DBID STATUS CREATION_SCN CON_ID
---------- ---------- ---------- ------------- ------------ ----------
2 PDB$SEED 4063610283 NORMAL 217 1
3 PDBTEST1 3064465721 NORMAL 1547881 1
4 PDBTEST2 2395404598 NORMAL 1548944 1
5 PDBTEST3 2434165039 NORMAL 1550036 1
6 PDB003 1448206714 NORMAL 2744910 1
7 ORCL 1350603571 NEW 3226095 1
It's still not the time to open the new
PDB.
You need first to execute, while
connected to the new pluggable database, the script $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql:
[oracle@vsi08devpom CDBTEST]$ ll /opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql
-rw-r--r-- 1 oracle oinstall 19191 Apr 15 22:27 /opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql
SQL@CDBTEST> alter session set container=ORCL;
Session altered.
SQL@CDBTEST> @/opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql
SQL@CDBTEST> SET SERVEROUTPUT ON
SQL@CDBTEST> SET FEEDBACK 1
SQL@CDBTEST> SET NUMWIDTH 10
...
...
...
SQL@CDBTEST> -- leave the PDB in the same state it was when we started
SQL@CDBTEST> BEGIN
2 execute immediate '&open_sql &restricted_state';
3 EXCEPTION
4 WHEN OTHERS THEN
5 BEGIN
6 IF (sqlcode <> -900) THEN
7 RAISE;
8 END IF;
9 END;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL@CDBTEST>
SQL@CDBTEST> WHENEVER SQLERROR CONTINUE;
SQL@CDBTEST>
Now you can open the new ORCL pluggable
database.
SQL@CDBTEST> show con_name;
CON_NAME
------------------------------
ORCL
SQL@CDBTEST> alter database open;
Database altered.
The database is so available and ready
to be used.
SQL@CDBTEST> select pdb_id, pdb_name, dbid, status, creation_scn, con_id from CDB_PDBS;
PDB_ID PDB_NAME DBID STATUS CREATION_SCN CON_ID
---------- ---------- ---------- ------------- ------------ ----------
2 PDB$SEED 4063610283 NORMAL 217 1
3 PDBTEST1 3064465721 NORMAL 1547881 1
4 PDBTEST2 2395404598 NORMAL 1548944 1
5 PDBTEST3 2434165039 NORMAL 1550036 1
6 PDB003 1448206714 NORMAL 2744910 1
7 ORCL 1350603571 NORMAL 3226095 1
That's all.
Other Actions with PDB's (Open,
Close, Alter system, Check History)
Moving between CDB/PDBs Switch Containers…
ALTER SESSION SET CONTAINER=PDB1;
SQL> alter session set container=CDB1;
ERROR:
ORA-65011: Pluggable database does not exist
ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER SESSION SET CONTAINER=PDB$SEED;
ALTER SESSION SET CONTAINER=pdb_ss; (not case sensitive)
Open/Close PDBs
ALTER PLUGGABLE DATABASE PDB_SS, PDB1 CLOSE; (not in CDB)
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable
database
alter session set container=CDB$ROOT;
alter pluggable database ALL open read only;
(from CDB)
ALTER PLUGGABLE DATABASE PDB_SS, PDB1 CLOSE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN READ WRITE;
ALTER PLUGGABLE DATABASE CLOSE; (shutdown)
Alter pluggable database open upgrade; (to migrate)
alter pluggable database all except pdb1 close immediate;
select name, open_mode, open_time from
v$pdbs;
NAME OPEN_MODE
OPEN_TIME
---------- ---------- -------------------------
PDB$SEED READ ONLY 11-MAR-13
09.29.18.284 PM
PDB1 READ WRITE
27-MAR-13 01.26.32.905 AM
PDB_SS MOUNTED
27-MAR-13 01.29.47.225 AM
alter pluggable database pdb$seed close immediate;
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or
altered
You can preserve the state of a PDB by doing SAVE STATE :
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;
ALTER SYSTEM while in PDB
Effect of flushing shared_pool or buffer cache at different levels
ALTER SYSTEM FLUSH SHARED_POOL
ALTER SYSTEM FLUSH BUFFER_CACHE
ALTER SYSTEM SET USE_STORED_OUTLINES
ALTER SYSTEM SUSPEND/RESUME
ALTER SYSTEM CHECKPOINT
ALTER SYSTEM KILL SESSION
ALTER SYSTEM DISCONNECT SESSION
ALTER SYSTEM SET initialization_parameter
(Great commands to run at the PDB level)
Able to modify initialization parameter for a given PDB…
SELECT NAME FROM V$PARAMETER
WHERE ISPDB_MODIFIABLE = 'TRUE'
AND NAME LIKE 'optim%‘; (without
condition – can set 147 parameters out of 357)
(There were 341 parameters in 11gR2)
NAME
----------------------------------------
optimizer_adaptive_reporting_only
optimizer_capture_sql_plan_baselines
optimizer_dynamic_sampling
optimizer_features_enable
optimizer_index_caching
optimizer_index_cost_adj
optimizer_mode
optimizer_use_invisible_indexes
optimizer_use_pending_statistics
optimizer_use_sql_plan_baselines
Key ones modifiable: cursor_sharing, open_cursors,
result_cache_mode, sort_area_size
Key ones NOT modifiable: shared_pool_size, db_cache_size,
memory_target, pga…
Set PDB Resource Plans …
Keep runaway PDBs from affecting other PDBs
Allocate appropriate resource plans (between/within PDBs)
Set min/max CPU / I/O / Parallelism / (Future: Memory / Network /
I/O on non-Exadata)
alter system set RESOURCE_LIMIT = TRUE_CONTAINER = ALL
(dynamically enable resource limits for all containers)
alter system set RESOURCE_LIMIT = TRUE_CONTAINER = CURRENT
(dynamically enable resource limits for the root)
If 4 PDBs have 3 shares each, there are 12 shares total and each has
3/12 or 1/4th of the CPU resources.
If 2 PDBs have 3 shares & 2 PDBs have 1 share, then the ones
with 3 shares have 3/8ths of the CPU resources and are 3x more
likely to queue parallel queries than the ones that have 1 share.
CPU utilization_limit and parallel_server_limit percents also can be
set.
BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'newcdb_plan',
pluggable_database => ‘pdb1',
shares => 3,
utilization_limit => 70,
parallel_server_limit => 70);
END;
/
Resource Plan Queries…
SELECT PLAN, STATUS, COMMENTS
FROM DBA_CDB_RSRC_PLANS
ORDER BY PLAN;
PLAN
STATUS
COMMENTS
------------------------- --------------------
--------------------
DEFAULT_CDB_PLAN
ACTIVE
Default CDB plan
ORA$INTERNAL_CDB_PLAN
ACTIVE
Internal CDB plan
SELECT PLAN, PLUGGABLE_DATABASE, SHARES,
UTILIZATION_LIMIT,
PARALLEL_SERVER_LIMIT
FROM DBA_CDB_RSRC_PLAN_DIRECTIVES
ORDER BY PLAN;
Pluggable
Utilization Parallel
Plan
Database
Shares Limit
Limit
-------------------------- -------------------------
------ ----------- --------
DEFAULT_CDB_PLAN
ORA$DEFAULT_PDB_DIRECTIVE
1
100 100
DEFAULT_CDB_PLAN
ORA$AUTOTASK
90 100
Check PDB History
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION,
OP_TIMESTAMP,
CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
Sample output:
DB_NAME CON_ID
PDB_NAME
OPERATION OP_TIMEST
CLONED_FROM_PDB
---------- ------ --------------- ----------------
--------- ---------------
NEWCDB 3
PDB1
CREATE
01-APR-13 PDB$SEED
NEWCDB 4
PDB_SS
CREATE
01-APR-13 PDB$SEED
NEWCDB 5
PDB2
CLONE
02-APR-13 PDB1
Views
Finally there are several views that assist us to find DB Objects:
CDB : All objects in all containers * (root and all PDBs).
DBA : All objects in the current container (root or PDB).
ALL : Objects accessible by the current user in the current
container (root or PDB), including those owned by the current user.
USER : Objects owned by the current user in the current container
(root or PDB).
RMAN on PDB's and CDB's
Backup of a Container Database (CDB) is essentially the same as a
non-Container Database. The main thing to remember is, by doing a
full backup of the CDB you are also doing a full backup of
all PDBs.
A backup of the root container is a backup of the CDB, excluding
any of the PDBs. Connect to RMAN using OS authentication and
backup the root container using the following command. This means
you are connecting to the root container with "AS SYSDBA" privilege.
$ rman target=/
RMAN> BACKUP DATABASE ROOT;
Pluggable Database (PDB) Backup
There are two ways to back up pluggable databases. When connected to
RMAN as the root container, you can backup one or more PDBs using
the following command.
$ rman target=/
RMAN> BACKUP PLUGGABLE DATABASE pdb1, pdb2;
Alternatively, connect to a specific PDB and issue the following
command.
$ rman target=sys@pdb1
RMAN> BACKUP DATABASE;
You can also use specific statements for the PDB's
RMAN> alter pluggable database all open;
RMAN> alter pluggable database pdb1 close;
RMAN> restore pluggable database pdb1;
RMAN> recover pluggable database pdb1 until SCN 777070;
RMAN> alter pluggable database pdb1 open resetlogs;
srvctl add service … –pdb <pdb_name>
Tablespace and Datafile
Backups
Multiple PDBs in the
same CDB can have a tablespace with the same name, for example
SYSTEM, SYSAUX and USERS. One way to remove that ambiguity is
connect to the appropriate PDB. Once RMAN is connected to the PDB,
the tablespace backup commands is unchanged compared to previous
versions.
$ rman target=sys@pdb1
RMAN> BACKUP TABLESPACE system, sysaux, users;
Alternatively, you can
remove the ambiguity by qualifying the PDB name with the
tablespace name when connected to the root container.
$ rman target=sys@cdb1
RMAN> BACKUP TABLESPACE pdb1:system, pdb1:sysaux, pdb1:users, pdb2:system;
Datafiles have unique
file numbers and fully qualified names, so they can be backed up
from the root container or the individual PDB.
$ rman target=/
# Or
$ rman target=sys@pdb1
RMAN> BACKUP DATAFILE 8, 9, 10;
Complete Recovery
Container Database (CDB)
Complete Recovery
Restoring a CDB is
similar to restoring a non-CDB database, but remember restoring a
whole CDB will restore not only the root container, but all the
PDBs also. Likewise a Point In Time Recovery (PITR) of the whole
CDB will bring all PDBs back to the same point in time.
Connect to RMAN using OS
authentication and restore the whole CDB using the following
restore script. This means you are connecting to the root
container with "AS SYSDBA" privilege.
$ rman target=/
RUN {
SHUTDOWN IMMEDIATE; # use abort if this fails
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}
Root Container Complete
Recovery
Rather than recovering
the whole CDB, including all PDBs, the root container can be
recovered in isolation.
Connect to RMAN using OS
authentication and restore the root container using the following
restore script. This means you are connecting to the root
container with "AS SYSDBA" privilege.
$ rman target=/
RUN {
SHUTDOWN IMMEDIATE; # use abort if this fails
STARTUP MOUNT;
RESTORE DATABASE ROOT;
RECOVER DATABASE ROOT;
# Consider recovering PDBs before opening.
ALTER DATABASE OPEN;
}
It is probably a very
bad idea to restore and recover just the root container without
doing the same for the PDBs. Any difference in metadata between
the two could prove problematic.
Pluggable Database (PDB)
Complete Recovery
There are two ways to
restore and recover PDBs. From to root containers, you can restore
and recover one or more PDBs using the following script.
$ rman target=/
RUN {
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE;
RESTORE PLUGGABLE DATABASE pdb1, pdb2;
RECOVER PLUGGABLE DATABASE pdb1, pdb2;
ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN;
}
When connected directly
to a PDB, you can restore and recover the current PDB using a
local user with the SYSDBA privilege, as shown in the following
script.
$ sqlplus sys@pdb1 as sysdba
CREATE USER admin_user IDENTIFIED BY admin_user;
GRANT CREATE SESSION, PDB_DBA, SYSDBA TO admin_user;
EXIT;
$ rman target=admin_user@pdb1
SHUTDOWN IMMEDIATE;
RESTORE DATABASE;
RECOVER DATABASE;
STARTUP;
In
the current release, the RMAN commands will not work in a "run"
script without producing errors.
Tablespace and Datafile
Complete Recovery
Due to potential name
clashes, restoring a tablespace must be done while connected to
the PDB.
$ rman target=sys@pdb1
RUN {
ALTER TABLESPACE users OFFLINE;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;
}
Datafile recoveries can
be done while connected to the container or directly to the PDB.
$ rman target=/
# Or
$ rman target=sys@pdb1
RUN {
ALTER DATABASE DATAFILE 10 OFFLINE;
RESTORE DATAFILE 10;
RECOVER DATAFILE 10;
ALTER DATABASE DATAFILE 10 ONLINE;
}
Point In Time Recovery
(PITR)
Container Database (CDB)
Point In Time Recovery (PITR)
Point In Time Recovery
(PITR) of a CDB is the same as that of non-CDB instances. Just
remember, you are performing a PITR on the CDB and all the PDBs at
once.
$ rman target=/
RUN {
SHUTDOWN IMMEDIATE; # use abort if this fails
STARTUP MOUNT;
SET UNTIL TIME "TO_DATE('23-DEC-2013 12:00:00','DD-MON-YYYY HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
# Should probably open read-only and check it out first.
ALTER DATABASE OPEN RESETLOGS;
}
Pluggable Database (PDB)
Point In Time Recovery (PITR)
Point In Time Recovery
(PITR) of a PDB follows a similar pattern to that of a regular
database. The PDB is closed, restored and recovered to the
required point in time, then opened with the RESETLOGS
option.
In this case, the RESETLOGS
option
does nothing with the logfiles themselves, but creates a new PDB
incarnation.
$ rman target=/
RUN {
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
SET UNTIL TIME "TO_DATE('23-DEC-2013 12:00:00','DD-MON-YYYY HH24:MI:SS')";
RESTORE PLUGGABLE DATABASE pdb1;
RECOVER PLUGGABLE DATABASE pdb1;
ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
}
The simplicity of PITR
of PDBs hides a certain amount of complexity. For a start, a PDB
shares the root container with other PDBs, so a PITR of the root
container must be performed. This is done in the fast recovery
area (FRA) provided it is configured. If the FRA is not
configured, an AUXILIARY DESTINATION
must
be specified.
Aside from the FRA space
requirement, one other important restriction is relevant. If a
point in time recovery of a PDB has been done, it is not possible
to directly flashback the database to a time before the PDB point
in time recovery. The workaround for this is discussed in this article.
Table Point In Time
Recovery (PITR) in PDBs
Oracle 12c includes a
new RMAN feature which performs point in time recovery of tables
using a single command. You can read about this feature and see
examples of it's use in the following article.
The same mechanism is
available for recovering tables in PDBs, with a few minor changes.
For the feature to work with a PDB, you must log in as a root user
with SYSDBA or SYSBACKUP privilege.
$ rman target=/
Issue the RECOVER TABLE
command
in a similar way to that shown for a non-CDB database, but include
the OF
PLUGGABLE DATABASE
clause, as well as
giving a suitable AUXILIARY DESTINATION
location
for the auxiliary database. The following command also uses the REMAP TABLE
clause
to give the recovered table a new name.
RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
UNTIL SCN 5695703
AUXILIARY DESTINATION '/u01/aux'
REMAP TABLE 'TEST'.'T1':'T1_PREV';
Alternatively, you can
just stop at the point where the recovered table is in a data pump
dump file, which you can import manually at a later time. The
following example uses the DATAPUMP DESTINATION
, DUMP FILE
and NOTABLEIMPORT
clauses
to achieve this.
RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
UNTIL SCN 5695703
AUXILIARY DESTINATION '/u01/aux'
DATAPUMP DESTINATION '/u01/export'
DUMP FILE 'test_t1_prev.dmp'
NOTABLEIMPORT;
Triggers
The following database
events are available at both the CDB and PDB level.
AFTER STARTUP
:
Trigger fires after the CDB or PDB opens.
BEFORE SHUTDOWN
:
Trigger fires before the CDB shuts down or before the PDB
closes.
AFTER SERVERERROR
:
Trigger fires when a server error message is logged and it is
safe to fire error triggers. Available at [PLUGGABLE]
DATABASE
or SCHEMA
level.
AFTER LOGON
:
Trigger fires when a client logs into the CDB or PDB. Available
at [PLUGGABLE] DATABASE
or SCHEMA
level.
BEFORE LOGOFF
:
Trigger fires when a client logs out of the CDB or PDB.
Available at [PLUGGABLE] DATABASE
or SCHEMA
level.
AFTER SUSPEND
:
Trigger fires when a server error causes a transaction to be
suspended. Available at [PLUGGABLE]
DATABASE
or SCHEMA
level.
BEFORE SET CONTAINER
:
Trigger fires before the SET
CONTAINER
command executes. Available
at [PLUGGABLE] DATABASE
or SCHEMA
level.
AFTER SET CONTAINER
:
Trigger fires after the SET
CONTAINER
command executes. Available
at [PLUGGABLE] DATABASE
or SCHEMA
level.
The following database
event is only available at the CDB level.
AFTER DB_ROLE_CHANGE
:
Fires when the database role switches from primary to standby or
from standby to primary in a Data Guard configuration.
The following database
events are only available at the PDB level and require the ON PLUGGABLE DATABASE
clause
explicitly. Using the ON DATABASE
clause
results in an error.
AFTER CLONE
:
After a clone operation, the trigger fires in the new PDB and
then the trigger is deleted. If the trigger fails, the clone
operation fails.
BEFORE UNPLUG
:
Before an unplug operation, the trigger fires in the PDB and
then the trigger is deleted. If the trigger fails, the unplug
operation fails.
More Information
(12.2)
Multitenant : Create and Configure a Pluggable Database (PDB) in
Oracle Database 12c Release 1
Multitenant
: Default Tablespace Clause During PDB Creation in Oracle
Database 12c Release 2 (12.2)
Multitenant
: Parallel PDB Creation Clause in Oracle Database 12c Release 2
(12.2)
Multitenant
: PDB Archive Files for Unplug and Plugin in Oracle Database 12c
Release 2 (12.2)
Multitenant
: PDB CONTAINERS Clause in Oracle Database 12c (12.1.0.2 and
12.2)
Multitenant
: PDBs With Different Character Sets to the CDB in Oracle
Database 12c Release 2 (12.2)
Multitenant
: PDB Refresh in Oracle Database 12c Release 2 (12.2)
Multitenant
: Pluggable Database (PDB) Operating System (OS) Credentials in
Oracle Database 12c Release 2 (12.2)