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’]
Leave a Reply