How to clone a pluggable database in Oracle

Question

I'm new to Oracle's pluggable databases (we still use Oracle 11.2 at work). For a test of partitions and subpartitions, I'll need to create a couple of dozen tablespaces. I thought, I'd quickly clone my current database, do the tests, and drop the database afterwards.

I was able to clone the database:

CREATE PLUGGABLE DATABASE ora193p2 FROM ora193p1
 FILE_NAME_CONVERT = (
   '/opt/oracle/oradata/ORA193C/ORA193P1/', 
   '/opt/oracle/oradata/ORA193C/ORA193P2/');

Pluggable database ORA193P2 created.

but got an error ORA-01109: database not open when trying to connect to it.

I've tried to open it, but get an error message, too (ora193c is the name of the cdb):

ALTER DATABASE ora193p2 OPEN;
ORA-01509: specified name 'ORA193P2' does not match actual 'ORA193C'

I used the database from vagrant-boxes.

Answer 1

For pluggable databases you need to add key word pluggable database followed by pdb name

SQL> create pluggable database pdbclone from orclpdb;

Pluggable database created.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
         4 PDBCLONE                       MOUNTED

SQL> alter pluggable database pdbclone open;

Pluggable database altered.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
         4 PDBCLONE                       READ WRITE NO
SQL> alter pluggable database pdbclone save state;

Pluggable database altered.

Answer 2

From 12.1.0.2 onward, you can preserve the PDB startup state through CDB restart. The 12.1.0.2 patchset introduced SAVE STATE and DISCARD STATE options:

ALTER PLUGGABLE DATABASE pdb_name OPEN;
ALTER PLUGGABLE DATABASE pdb_name SAVE STATE;

To discard the saved state:

ALTER PLUGGABLE DATABASE pdb_name DISCARD STATE;

For 12.1.0.1 and before, you could create an after startup trigger:

CREATE OR REPLACE TRIGGER open_pdbs 
  AFTER STARTUP ON DATABASE 
BEGIN 
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN'; 
END open_pdbs;
/

It creates a after startup system level trigger in CDB.

See Oracle 12c Post Installation Mandatory Steps