oracle-databaseoracle19cpluggable-database

How to clone a pluggable database in Oracle


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.


Solution

  • 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.