Oracle CDB/PDB Features and Management (12.2, 18c, 19c)
This document outlines key features and management operations for Container Databases (CDBs) and Pluggable Databases (PDBs) in Oracle Database versions 12.2, 18c, and 19c.
1. Creating a Container Database (CDB)
A CDB is the root container that hosts multiple PDBs.
CREATE DATABASE ... ENABLE PLUGGABLE DATABASE;
This command will create CDB$ROOT (the root container) and PDB$SEED (a template for creating new PDBs).
To verify the CDB status:
SELECT NAME, CDB, CON_ID FROM V$DATABASE;
2. Creating an Application Root (12.2, 18c, 19c)
An Application Root acts as a parent container for application PDBs, allowing for centralized management of common application data and metadata.
Connect to the CDB root before creating:
CONNECT TO the_cdb_root_user_as_sysdba; -- Example: CONNECT SYS/password@cdb_name AS SYSDBA;
CREATE PLUGGABLE DATABASE your_app_root_name AS APPLICATION CONTAINER;
3. Creating an Application Seed (12.2, 18c, 19c)
An Application Seed is a template PDB within an Application Root, used for creating new application PDBs.
Ensure the current container is the application root:
ALTER SESSION SET CONTAINER = your_app_root_name;
CREATE PLUGGABLE DATABASE AS SEED;
4. Creating a PDB from Seeds (12.2, 18c, 19c)
You can create new PDBs from PDB$SEED (in CDB$ROOT) or from an Application Seed (in an Application Root).
-- Connect to the CDB root
ALTER SESSION SET CONTAINER=CDB$ROOT;
-- Create a new PDB
CREATE PLUGGABLE DATABASE mypdbnew
ADMIN USER MY_DBA IDENTIFIED BY "####"
STORAGE (MAXSIZE UNLIMITED) -- Example storage clause
DEFAULT TABLESPACE mypdbnew_tbs
DATAFILE '/path/to/datafiles/mypdbnew01.dbf' SIZE 100M
FILE_NAME_CONVERT =('/path/to/seed_datafiles/','/path/to/new_pdb_datafiles/'); -- Adjust paths
-- Example: FILE_NAME_CONVERT =('/u01/app/oracle/oradata/CDB1/pdbseed/','/u01/app/oracle/oradata/CDB1/mypdbnew/');
-- Open the new PDB
ALTER PLUGGABLE DATABASE mypdbnew OPEN;
-- Verify user information in the CDB
SELECT con_id, username, default_tablespace, common FROM cdb_users WHERE con_id = (SELECT con_id FROM v$pdbs WHERE name = 'MYPDBNEW');
5. Moving PDBs / Plugging In a PDB (12.2, 18c, 19c)
This process involves unplugging a PDB from one CDB and plugging it into another, or into the same CDB.
-- Close the PDB to be unplugged
ALTER PLUGGABLE DATABASE mypdb CLOSE IMMEDIATE;
-- Unplug the PDB, creating an XML manifest file
ALTER PLUGGABLE DATABASE mypdb UNPLUG INTO '/backup_location/mypdb.xml';
This command creates mypdb.xml (the manifest file) and leaves the PDB's datafiles in their current location.
Move Files: Manually move the PDB's datafiles (e.g.,
system.dbf,sysaux.dbf,mypdb.dbf) and themypdb.xmlfile to the new desired location.Check Compatibility (in the target CDB):
-- Connect to the target CDB root ALTER SESSION SET CONTAINER=CDB$ROOT; -- Run the check (no output means compatible, errors indicate issues) SELECT DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/path/to/new_location/mypdb.xml') FROM DUAL;Create PDB by Plugging In:
CREATE PLUGGABLE DATABASE mypdb USING '/path/to/new_location/mypdb.xml' SOURCE_FILE_NAME_CONVERT =('/old/path/','/new/path/') -- Adjust paths as needed NOCOPY; -- Use NOCOPY if datafiles are already moved to the target locationOpen the Plugged-In PDB:
ALTER PLUGGABLE DATABASE mypdb OPEN;If in a RAC environment, ensure it's opened on all relevant nodes.
6. Cold Clone from Another PDB (12.2, 18c, 19c)
A cold clone creates a new PDB from an existing PDB while the source PDB is closed or in read-only mode.
-- Open source PDB in read-only mode (required for cold clone)
ALTER PLUGGABLE DATABASE mypdb OPEN READ ONLY FORCE;
-- Create the clone PDB
CREATE PLUGGABLE DATABASE mypdb_test
FROM mypdb
FILE_NAME_CONVERT =('/path/to/source_datafiles/','/path/to/clone_datafiles/'); -- Adjust paths
-- Open the cloned PDB
ALTER PLUGGABLE DATABASE mypdb_test OPEN;
(Note: For a Hot Clone, the source PDB does not need to be in read-only mode.)
7. Cold Clone from Non-CDB (12.2, 18c, 19c)
You can convert a non-CDB into a PDB and plug it into a CDB.
Generate XML Manifest from Non-CDB:
Connect to the non-CDB as
SYSDBA.
EXEC DBMS_PDB.DESCRIBE(pdb_name => 'NONCDB_NAME', xml_file => '/path/to/noncdb.xml');Create PDB in CDB using XML:
Move the non-CDB's datafiles and the generated
noncdb.xmlfile to the target CDB's desired location.Connect to the target CDB root.
CREATE PLUGGABLE DATABASE noncdb_pdb USING '/path/to/noncdb.xml' SOURCE_FILE_NAME_CONVERT =('/old/noncdb/datafiles/','/new/pdb/datafiles/') NOCOPY;Run
noncdb_to_pdb.sql: After plugging in, this script must be run inside the new PDB to complete the conversion.ALTER PLUGGABLE DATABASE noncdb_pdb OPEN; ALTER SESSION SET CONTAINER = noncdb_pdb; @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
8. Dropping a Pluggable Database (12.2, 18c, 19c)
-- Close the PDB first
ALTER PLUGGABLE DATABASE mypdb CLOSE;
-- Drop the PDB and its associated datafiles
DROP PLUGGABLE DATABASE mypdb INCLUDING DATAFILES;
9. Hot Clone a Remote PDB or Non-CDB (12.2+)
This feature allows cloning a PDB or non-CDB from a remote database over a database link. The source must use local undo mode.
-- Create a database link to the remote source database
CREATE DATABASE LINK db_link CONNECT TO remote_user IDENTIFIED BY remote_user_password USING 'remotedb_service_name';
-- Create the new PDB as a clone from the remote source
CREATE PLUGGABLE DATABASE newpdb FROM remotedb@db_link;
-- Open the new PDB
ALTER PLUGGABLE DATABASE newpdb OPEN;
-- If cloning from a non-CDB, run the conversion script
-- ALTER SESSION SET CONTAINER = newpdb;
-- @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
10. Relocate PDB (12.2+)
Relocating a PDB moves it from one CDB to another while keeping the PDB online during most of the operation.
-- Create a database link to the source CDB
CREATE DATABASE LINK db_link CONNECT TO remote_user IDENTIFIED BY remote_user_password USING 'source_cdb_service_name';
-- Create the PDB in the target CDB and initiate relocation
CREATE PLUGGABLE DATABASE remote_pdb_name FROM remote_pdb_name@db_link RELOCATE;
-- Open the relocated PDB in the target CDB
ALTER PLUGGABLE DATABASE remote_pdb_name OPEN;
11. PDB Archive Files (12.2+)
PDB archive files (.pdb extension) are self-contained archives that include both the PDB's XML manifest and its datafiles. This simplifies PDB transport.
-- Unplug the PDB into an archive file
ALTER PLUGGABLE DATABASE mypdb UNPLUG INTO '/backup_location/mypdb.pdb';
```mypdb.pdb` is an archive containing both the `.xml` file and the datafiles.
```sql
-- Create a PDB from an archive file
CREATE PLUGGABLE DATABASE mypdb_test USING '/backup_location/mypdb.pdb';
12. PDB Refresh (12.2+)
Refreshable PDB clones allow a PDB to be periodically updated from a remote source PDB.
-- Create a database link to the source PDB
CREATE DATABASE LINK db_link CONNECT TO remote_user IDENTIFIED BY remote_user_password USING 'remote_pdb_service_name';
-- Create a refreshable PDB clone in manual refresh mode
CREATE PLUGGABLE DATABASE newpdb FROM remote_pdb_name@db_link REFRESH MODE MANUAL;
-- Open the refreshable PDB (initially read-only)
ALTER PLUGGABLE DATABASE newpdb READ ONLY;
-- To perform a manual refresh:
ALTER PLUGGABLE DATABASE newpdb CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE newpdb REFRESH;
ALTER PLUGGABLE DATABASE newpdb READ ONLY; -- PDB will be read-only after refresh
Auto-refresh:
ALTER PLUGGABLE DATABASE newpdb REFRESH MODE EVERY 120 MINUTES;This auto-refresh only occurs if the PDB is closed.
13. Proxy PDB (12.2+)
A Proxy PDB acts as a pointer to a PDB in a remote CDB, allowing local access to a remote PDB without actually moving its datafiles.
Benefits:
Existing client connections unchanged: Clients can connect to the proxy PDB as if it were local.
Single entry point for cloud DB: Simplifies access to remote databases, especially in cloud environments.
Share an application root container: Enables sharing of an application root's content across multiple containers.
Example (CDB 1 instance):
-- Create a database link to the remote CDB CREATE DATABASE LINK db_clone_link CONNECT TO c##remote_clone_user IDENTIFIED BY remote_clone_user_password USING 'CDB2_SERVICE_NAME'; -- Create the proxy PDB CREATE PLUGGABLE DATABASE PDB2_PROXY AS PROXY FROM PDB2@db_clone_link; -- Verify the proxy PDB SELECT pdb_name, is_proxy_pdb, status FROM dba_pdbs WHERE pdb_name = 'PDB2_PROXY';No longer DB link & link user required for subsequent access after creation.
Accessing the Proxy PDB:
ALTER SESSION SET CONTAINER=PDB2_PROXY; -- Now you are effectively connected to the remote PDB2 via the proxy. -- DDL and DML operations performed here will execute on the remote CDB2 instance.
14. Snapshot Carousel (PDB Archives) (18c+)
Snapshot Carousel provides a repository for periodic point-in-time copies (snapshots) of a PDB, enabling easy recovery or cloning to a specific point in time.
-- Create a PDB with snapshot mode enabled
CREATE PLUGGABLE DATABASE pdb_snap
ADMIN USER MY_DBA IDENTIFIED BY "####"
SNAPSHOT MODE EVERY 24 HOURS; -- Automatically creates a snapshot every 24 hours
-- Open the PDB
ALTER PLUGGABLE DATABASE pdb_snap OPEN;
-- MAX_PDB_SNAPSHOT can be changed between 0 to 8 (0 will delete existing snapshots)
ALTER PLUGGABLE DATABASE pdb_snap SET MAX_PDB_SNAPSHOTS = 5;
-- Manual snapshot creation
ALTER PLUGGABLE DATABASE pdb_snap SNAPSHOT xyz_snap;
-- Create a new PDB from one of the archives (snapshots)
CREATE PLUGGABLE DATABASE pdb_from_snap
FROM pdb_snap
USING SNAPSHOT xyz_snap;
15. Transportable Backups (18c+)
This feature supports using backups performed on a PDB before it is unplugged and plugged into a new container. This significantly streamlines PDB relocation for purposes like load balancing or migration between on-premises and cloud, as it avoids the need for new backups immediately before and after each PDB move.
16. Switchover Refreshable Clone PDB between CDBs (Migration) (18c+)
This allows for a planned or unplanned switchover of a refreshable clone PDB between different CDBs, facilitating PDB migration with minimal downtime.
Planned Switchover:
ALTER PLUGGABLE DATABASE your_pdb_name REFRESH MODE EVERY 2 MINUTES FROM remote_pdb_name@dblink SWITCHOVER;This command prepares the PDB for a switchover, automatically refreshing it.
Unplanned Switchover (after a planned switchover setup):
ALTER PLUGGABLE DATABASE your_pdb_name REFRESH; -- Perform a final refresh ALTER PLUGGABLE DATABASE your_pdb_name REFRESH MODE NONE; -- Disable refresh mode ALTER PLUGGABLE DATABASE your_pdb_name OPEN READ WRITE; -- Open the PDB in read-write mode
17. Transient No-Standby PDBs (Clone) (18c+)
This feature allows creating a hot clone of a PDB without it being replicated to a standby database, useful for temporary testing or development environments.
Hot clone to transient PDB:
CREATE PLUGGABLE DATABASE transient_pdb FROM source_pdb CLONE STANDBY=NONE;Cold clone of this transient PDB with standby: You can then create a cold clone of this transient PDB that does include standby replication.
Drop transient PDB:
DROP PLUGGABLE DATABASE transient_pdb INCLUDING DATAFILES;
18. AWR for Pluggable Database (12.2 onwards)
Starting from Oracle 12.2, AWR (Automatic Workload Repository) data can be collected at the PDB level, providing granular performance insights for individual PDBs.
Enable AWR Auto Flush for PDBs:
ALTER SYSTEM SET awr_pdb_autoflush_enabled=TRUE;View PDB Snapshots:
SELECT * FROM awr_pdb_snapshot;
