Cloning and Deleting a Pluggable Database

The following example uses Windows 7 64bit, so the filenames will differ to those of a Linux environment.

And don't forget to amend the tnsnames.ora and register any new PDBs with the listener.

 

To clone a local/remote pluggable database

 

1.       Create a directory to hold the data files.

md C:\app\oracle\oradata\jhcdb\jhpdb3

2.       Make sure you’re in the root cdb. I’m using a common user with DBA granted to all containers.

alter session set container = cdb$root;

3.       Close the source PDB and re-open it read only.

alter pluggable database jhpdb1 close;
alter pluggable database jhpdb1 open read only;

4.       Create the clone with some filename remapping and sizing info.

create pluggable database jhpdb3 from jhpdb1
file_name_convert = ('C:\app\oracle\oradata\jhcdb\jhpdb1', 'C:\app\oracle\oradata\jhcdb\jhpdb3')
storage (maxsize 2G max_shared_temp_size 100M);

If you want to clone a remote pdb, you can use a database link. Same as above except firstly create a database link from the target DB, then open the pdb on the source read only, then create pluggable database on the target needs a @dblink next to the source name (eg jhpdb1@rem_db).

 

Don’t forget that a PDB must be opened at least once before you can do anything with it.

 

To remove a PDB

 

                drop pluggable database jhpdb3 including datafiles;     
-- this leaves the directory but none of the files.

 

If you miss off the including datafiles, it obviously leaves them and they can be reintroduced to the database later (see below).

 

To unplug a pluggable database

 

                Alter pluggable database jhpdb3 close;

                Alter pluggable database jhpdb3 unplug into 'C:\app\oracle\unplug\jhpdb3.xml';

 

Because the unplugged PDB is still part of the database, the files remain in the same location and they still form part of the rman backups.

 

To plug in an unplugged pluggable database

 

To replug an unplugged PDB into the same name, you must first drop the existing PDB but keep the datafiles

 

                drop pluggable database jhpdb3 keep datafiles;

                create pluggable database jhpdb3 using 'C:\app\oracle\unplug\jhpdb3.xml' nocopy tempfile reuse;

Alter pluggable database jhpdb3 open;

 

To replug an unplugged PDB into a new name (perhaps on another database) with new file location on another database

 

create pluggable database jhpdb5 using 'C:\app\oracle\unplug\jhpdb4.xml'

file_name_convert = ('C:\app\oracle\oradata\jhcdb\jhpdb4', 'C:\app\oracle\oradata\jhcdb\jhpdb5')

move;

 

Note you will get an ORa-65122 if you try to plug in a clone on the same CDB as its source. You must clone. The only way to do this is to plug in a dropped unplugged PDB as above.

 

 

Using DBMS_PDB to check plug compatibility

 

Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the unplugged PDB is compatible with the CDB.

 

SET SERVEROUTPUT ON

DECLARE

compatible CONSTANT VARCHAR2(3) :=

  CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

      pdb_descr_file => '/home/oracle/devpdb3.xml')

  WHEN TRUE THEN 'YES'

  ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

 

.

 

Happyjohn