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.

@Kumar: Yes, that works. Thank you!. Do you want to add this as an answer, so that I can accept it? — wolφi, May 08 '20 at 01:33

score 2 · Accepted Answer · 2020-05-08T01:57:25.263

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.

While googling, I found that the following statement will apparently open the pdb on the next startup: `ALTER PLUGGABLE DATABASE ora193p2 SAVE STATE;` — wolφi, May 08 '20 at 01:52

score 1 · Answer 2 · answered May 08 '20 at 08:15

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

Thanks! It took a while, but I found the data dictionary view [DBA_PDB_SAVED_STATES](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_PDB_SAVED_STATES.html#GUID-274837D6-59B3-4C9D-80C5-481BF8298DBA) where one can check this saved state. — wolφi, May 08 '20 at 09:09

How to clone a pluggable database in Oracle

2 Answers2