LLI Wiki — My Digital Knowledge Base

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

Notes

Mastodon LLIMastodon SocialFosstodon