Oracle multitenant: PDB command cheat sheet
Connect to, create, auto-open and plug in a PDB, including recovery without the XML manifest.
Cheat sheet for working with pluggable databases (PDB) in an Oracle 12c+ multitenant container database (CDB): connecting, switching containers, creating, opening and saving the state of a PDB.
Connect to a PDB
Add a service entry for the PDB in tnsnames.ora:
MYPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sqlserver01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mypdb.example.com)
)
)
sqlplus system/<PASSWORD>@MYPDB
From a session on the CDB, list the PDBs and switch container:
show pdbs;
alter session set container=CDB$ROOT;
alter session set container=MYPDB;
Create a PDB from the seed
CREATE PLUGGABLE DATABASE "APPPDB" ADMIN USER "pdbadmin" IDENTIFIED BY '<PASSWORD>'
FILE_NAME_CONVERT=(
'/u02/oradata/CDB1/pdbseed/undotbs01.dbf', '/u02/oradata/CDB1/APPPDB/undotbs01.dbf',
'/u02/oradata/CDB1/pdbseed/system01.dbf', '/u02/oradata/CDB1/APPPDB/system01.dbf',
'/u02/oradata/CDB1/pdbseed/sysaux01.dbf', '/u02/oradata/CDB1/APPPDB/sysaux01.dbf'
)
STORAGE UNLIMITED TEMPFILE REUSE;
Open the PDB and keep it open after a restart
sqlplus / as sysdba
alter session set container=APPPDB;
alter pluggable database APPPDB open;
-- or, from inside the PDB:
ALTER DATABASE OPEN;
alter session set container=CDB$ROOT;
By default a PDB stays closed when the CDB restarts. Save its state so that it opens automatically:
show pdbs
show con_name
select con_name, state from dba_pdb_saved_states;
alter pluggable database APPPDB SAVE state;
Create a tablespace and a user inside the PDB
CREATE SMALLFILE TABLESPACE USERS
DATAFILE '/u02/oradata/CDB1/APPPDB/USERS_TBLS.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 10485760 MAXSIZE 34359721984
BLOCKSIZE 8192
DEFAULT NOCOMPRESS NO INMEMORY
ONLINE
SEGMENT SPACE MANAGEMENT AUTO
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE USER app_user IDENTIFIED BY '<PASSWORD>'
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO app_user;
Plug a PDB back in without its XML manifest
If the XML description file of an unplugged PDB is lost, regenerate it from the datafiles with DBMS_PDB.RECOVER:
BEGIN
DBMS_PDB.RECOVER(
PDB_DESCR_FILE => '/u02/oradata/CDB1/clone_pdb/NEW_PDB_TEST.xml',
PDB_NAME => 'NEW_PDB_TEST',
FILENAMES => '/u02/oradata/CDB1/NEW_PDB_test/system01.dbf,/u02/oradata/CDB1/NEW_PDB_test/sysaux01.dbf,/u02/oradata/CDB1/NEW_PDB_test/undotbs01.dbf');
END;
/
Then plug it in with the manifest:
CREATE PLUGGABLE DATABASE "NEW_PDB" USING '/u02/temp/test.xml'
SOURCE_FILE_NAME_CONVERT=NONE
NOCOPY
STORAGE UNLIMITED TEMPFILE REUSE;
If the paths recorded in the manifest do not match the real file locations, it fails like this:
ORA-19505: failed to identify file "/u02/oradata/CDB1/NEW_PDB_test/system01.dbf"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Fix the file paths in the XML (or use FILE_NAME_CONVERT/SOURCE_FILE_NAME_CONVERT accordingly) and retry.
References
- How to connect to a CDB and PDBs in Oracle 12c
- Oracle 12c pluggable database commands
- Open the PDBs automatically when the CDB restarts
- Multitenant: create and configure a pluggable database (Oracle-Base)
- How to rename a pluggable database
Notes
- All names, paths, passwords and domains are generic placeholders.
- The
DBMS_PDB.RECOVERand plug-in examples come from a test where the manifest paths did not match the files.