Oracle Multi-Tenancy PDB database cloning with ASM

Background

One of the most exciting announcements at last years Oracle Open World #OOW19 was around multi-tenancy and the changes to Oracle licensing allowing non multi-tenant users to have up to 3 user-created PDB’s in a container database at any time for free.

With the above, and the announcement of de-support of non-CDB architecture from 20c I produced a blog on how to use the SNAPSHOT COPY option to create space efficient database clones with dNFS.

You can read-up on how to use Snapshot Copy, Oracle dNFS and a FlashBlade to clone pluggable database here.

In this Blog I am going to show how we can also create a space efficient Pluggable database (PDB) clone using a modern all-flash storage array on Oracle ASM using the ‘CREATE PLUGGABLE DATABASE’ command.

Architecture

The image below shows my lab layout of a source database server running an Oracle database with a single pluggable database ‘PDB1’, and a target database server to host our cloned database and PDBs.

In the post I will clone my source database to my target database server using a Protection Group storage snapshot, and then then create pluggable database ‘PDB2’ from ‘PDB1’ in the target database using the Oracle ‘CREATE PLUGGABLE DATABASE’ command.

Source Database Environment

Before we clone our non-production Pluggable database, let’s check the size of my source Swingbench database from within Oracle.

Non-Production Clone

Let’s start by cloning our source Oracle ASM database to our test server, and repeat the same size check.

If you’re interested and what to have a look at the above scripts I have shared the example cloning scripts here.

Pluggable Database Clone

Connect to container database

$ sqlplus / as sysdba

Show connection Name

SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT

Show status of pluggable databases

SQL> SHOW PDBS
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
         2 PDB$SEED   READ ONLY  NO
         4 PDB1       READ WRITE NO

Create Pluggable Database (pdb2)

We can use the Oracle CREATE PLUGGABLE DATABASE command to manually clone a pluggable database. From 18c your source PDB no longer has to have an open mode of ‘READ ONLY’

Below, you can see my empty target volume ‘z-oracle2-dg_oradata2‘, the space reported used is due to ASM headers.

Disk Group LUN Path
---------- --------------------------
DATA1      /dev/pureasm/dg_oradata1
DATA2      /dev/pureasm/dg_oradata2

SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 FILE_NAME_CONVERT = ('+DATA1', '+DATA2');
Pluggable database created.

This can take quite a few minutes depending upon the size of the database, as Oracle will be creating a full size clone. However, if required and resources available you may be able to speed up this operation by using the PARALLEL option set to the number of datafiles e.g

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 FILE_NAME_CONVERT = ('+DATA1', '+DATA2') PARALLEL 8;

If you have access to your storage array you should be able to monitor the reads from the source ASM diskgroup and writes to the target ASM diskgroup volume(s).

show status of pluggable database

SQL> SHOW PDBS 
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
         2 PDB$SEED   READ ONLY  NO
         3 PDB2       MOUNTED
         4 PDB1       READ WRITE NO

Open Pluggable Database

Now we need to open our pluggable database.

SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;

SQL> SHOW PDBS 
    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- ----------
         2 PDB$SEED   READ ONLY  NO
         3 PDB1       READ WRITE NO
         4 PDB2       READ WRITE NO

Ok, and check the sizes again.

What about the physical space used ?

Above, you can see my newly created pluggable database ‘pdb2‘ (z-oracle2-dg_oradata2) has consumed very little space due to Pure Storage FlashArray always-on data reduction services.

Summary

In this Blog I have shown how you can use a modern all-flash storage array to deliver a full size, space efficient, performant pluggable database clone from within Oracle using the ‘CREATE PLUGGABLE DATABASE’ command.

As I am using volumes on a Pure Storage FlashArray I avoid the performance overhead of using sparse file systems and write overhead with COW (Copy on Write) or more accurately Copy on First Write solutions.

The FlashArrays data reduction has delivered a space efficient clone without the need to keep a ‘READ-ONLY’ source pluggable database.

[twitter-follow screen_name=’RonEkins’ show_count=’yes’]