A very common request for every DBA is to clone databases between environments. In the following post we will address the steps required to clone a pluggable database (PDB) very easily. This post would be very useful in diverse scenarios, such as creating new development or QA scenarios. Cloning environments quickly is one of the foundation ideas of any given DBaaS (database-as-a-service) project

In the following example we will use SQL*Plus to perform a PDB cloning operation within the same CDB.

First, let’s list all existing PDBs and CDBs by executing the following statement:

SQL> SET LINESIZE 200
SQL> SELECT name, pdb
 2     FROM v$services
 3    ORDER BY name;

NAME                      PDB
---------------           ----------
SYS$BACKGROUND            CDB$ROOT
SYS$USERS                 CDB$ROOT
db01.node.com             CDB$ROOT
db01XDB                   CDB$ROOT
pdb01.node.com            PDB01
pdb02.node.com            PDB02

6 rows selected.

Our goal will be to clone pdb02 existing pluggable database as pdb02_clone new pluggable database.

Query current status of existing PBDs as follows:

SQL> COLUMN pdb_name FORMAT a15
SQL> SELECT pdb_id, pdb_name, status FROM dba_pdbs ORDER BY pdb_id;

    PDB_ID PDB_NAME        STATUS
---------- --------------- -------------
         2 PDB$SEED        NORMAL
         3 PDB01           NORMAL
         4 PDB02           NORMAL

Prepare the source PDB to be cloned. Let’s switch pdb02 pluggable database to read only mode:

SQL> SELECT name, open_mode 
 2     FROM v$pdbs
 3    WHERE name = 'PDB02';

NAME                           OPEN_MODE
------------------------------ ----------
PDB02                          READ WRITE

SQL> ALTER PLUGGABLE DATABASE pdb02 CLOSE IMMEDIATE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pdb02 OPEN READ ONLY;

Pluggable database altered.

SQL> SELECT name, open_mode 
 2     FROM v$pdbs
 3    WHERE name = 'PDB02';

NAME                           OPEN_MODE
------------------------------ ----------
PDB02                          READ ONLY

Locate a stage directory to store the data files for pdb02, in this example we will use “/dbclone” as stage directory:

$ cd /dbclone

$ mkdir pdb02_clone

Now, update database configuration accordingly:

SQL> ALTER SYSTEM SET db_create_file_dest = '/dbclone/pdb02_clone' 
 2     SCOPE=memory;

System altered.

Cloning a PDB database is a straightforward process, execute the following statement to clone pdb02 pluggable database as pdb02_clone pluggable database:

SQL> CREATE PLUGGABLE DATABASE pdb02_clone FROM pdb02;

Pluggable database created.

Once created, open the new pluggable database:

SQL>  ALTER PLUGGABLE DATABASE pdb02_clone OPEN;

Pluggable database altered.

Query pdb02_clone current status:

SQL> SELECT name, open_mode 
 2     FROM v$pdbs
 3    WHERE name LIKE 'PDB02%';

NAME                           OPEN_MODE
------------------------------ ----------
PDB02                          READ ONLY
PDB02_CLONE                    READ WRITE

The cloning process is completed now.

You can now open the source pluggable database:

SQL> ALTER PLUGGABLE DATABASE pdb02 CLOSE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pdb02 OPEN;

Pluggable database altered.

Let’s verify the current status:

SQL> SELECT name, open_mode 
 2     FROM v$pdbs
 3    WHERE name LIKE 'PDB02%';

NAME                           OPEN_MODE
------------------------------ ----------
PDB02                          READ WRITE
PDB02_CLONE                    READ WRITE

And optionally, you can clean up the cloned environment as follows:

SQL> ALTER PLUGGABLE DATABASE pdb02_clone CLOSE IMMEDIATE;

Pluggable database altered.

SQL> DROP PLUGGABLE DATABASE pdb02_clone INCLUDING DATAFILES;

Pluggable database dropped.

A future post will address how to clone PDBs between different CDBs.

Link to script that contains the examples in this post.

Comment

2015-10 – Cloning pluggable databases

HEADQUARTERS