Oracle database cloning magic with dNFS and CloneDB.

In this Blog Post I will show Oracle’s CloneDB, dNFS and Pure Storage FlashBlade snapshots can be used to create space efficient Oracle database clone(s) on a sparse file system.

Oracle RMAN backup

Source database dNFS mount

I will start be taking an RMAN image backup of my Oracle ASM ‘labs’ database to my dNFS mount point, as you can see from the below, my ‘labs’ database is ~144GB.

Source database RMAN backup
Oracle RMAN backup

From the FlashBlade I can see my ~144GB RMAN backup is only using 44.45GB, this is due to the data reduction capabilities of the FlashBlade.

RMAN backup on FlashBlade

FlashBlade Snapshot

Now I have an Oracle RMAN backup on my FlashBlade NFS server I am nearly ready to use Oracle CloneDB, however before running the CloneDB perl script I will create a ‘read-only’ snapshot of my RMAN image backup, this will allow me to create clones from different points-in-time.

I have previously blogged on how to use my Python script to take a FlashBlade snapshot and if you would like to re-read it you can check-it out here you can also find the code on GitHub if you want to have a closer look.

In the example below I defined an environmental variable DAY as a capitalised short name e.g. MON to provide me a per day snapshot.

#FlashBlade details
export API_TOKEN=T-74f533df-5f0e-447b-bced-XXXXXXXXXXXX
export FLASHBLADE=fbstaines01
export FILESYSTEM=z-fbhosts_oralabs
export DAY=$(date +"%^a")
python3 pureTakeFBsnapshot.py -s ${FLASHBLADE} -t ${API_TOKEN} -f ${FILESYSTEM} -S ${DAY}

Oracle CloneDB

We can now run the CloneDB perl script (clonedb.pl), this located within the $ORACLE_HOME/rdbms/install directory. Here is the link to the Oracle docs if you want to find out more about its use.

The clonedb.pl script use 3 environmental variables

  • MASTER_COPY_DIR
    • The .snapshot directory that contains the our RMAN image backup
  • CLONE_FILE_CREATE_DEST
    • The directory where our database files will be created, including data files, log files, control files.
  • CLONEDB_NAME
    • The name of the CloneDB database.

CloneDB supports three command line parameters:

  • The full path of the production database’s PFILE.
  • The name of the 1st SQL script generated by clonedb.pl, default is crtdb.sql
  • The name of the 2nd SQL script generated by clonedb.pl. default is dbren.sql
export DAY=$(date +"%^a")
export SOURCE_FS=z-fbhosts_oralabs.${DAY}
export MASTER_COPY_DIR=/mnt/oralabs/.snapshot/${SOURCE_FS}
export FILE=${MASTER_COPY_DIR}/init${SOURCEDB_NAME}.ora
export CLONEDB_NAME=CLONE${DAY}
export CLONEDB_BASE=/u05/oracle
export CLONE_FILE_CREATE_DEST=$CLONEDB_BASE/oradata/${CLONEDB_NAME}
perl $ORACLE_HOME/rdbms/install/clonedb.pl ${FILE} crtdb.sql rename.sql

In this example the clonedb.pl perl script has created ‘crtdb.sql’ and ‘rename.sql’ using a source .snapshot directory of ‘/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON’ and target sparse dNFS directory of ‘/u05/oracle/oradata/CLONEMON’. My new database is going to be called CLONEMON.

Before can use the ‘creatdb.sql’ and ‘rename.sql’ we need to do a bit of housekeeping, remove references to the source init in both files and remember to remove the trailing comma on the last line of the list of datafiles. Finally update the new init.ora, replacing the original database name with our new cloned database name.

creatdb.sql

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
STARTUP NOMOUNT PFILE=/u05/oracle/oradata/CLONEMON/initCLONEMON.ora
CREATE CONTROLFILE REUSE SET DATABASE CLONEMON RESETLOGS
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXINSTANCES 1
    MAXLOGHISTORY 908
LOGFILE
  GROUP 1 '/u05/oracle/oradata/CLONEMON/CLONEMON_log1.log' SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u05/oracle/oradata/CLONEMON/CLONEMON_log2.log' SIZE 100M BLOCKSIZE 512
DATAFILE
'/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-2_4kujno35',
'/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-5_4lujno35',
'/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-8_4mujno35',
'/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-9_4nujno35',
'/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SYSAUX_FNO-3_4pujno35',
'/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SYSTEM_FNO-1_4qujno36',
'/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_4oujno35',
'/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-USERS_FNO-7_4rujno36'
CHARACTER SET WE8DEC;

rename.sql

declare
begin
dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-2_4kujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON0.dbf');
dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-5_4lujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON1.dbf');
dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-8_4mujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON2.dbf');
dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-9_4nujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON3.dbf');
dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SYSAUX_FNO-3_4pujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON4.dbf');
dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SYSTEM_FNO-1_4qujno36' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON5.dbf');
dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_4oujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON6.dbf');
dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-USERS_FNO-7_4rujno36' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON7.dbf');
end;
/
show errors;
alter database open resetlogs;
alter tablespace TEMP add tempfile;

SQLPlus Output

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 23 12:24:03 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> 
SQL> STARTUP NOMOUNT PFILE=/u05/oracle/oradata/CLONEMON/initCLONEMON.ora
ORACLE instance started.
Total System Global Area 1.6267E+11 bytes
Fixed Size                 30140696 bytes
Variable Size            1.9864E+10 bytes
Database Buffers         1.4227E+11 bytes
Redo Buffers              506728448 bytes
SQL> CREATE CONTROLFILE REUSE SET DATABASE CLONEMON RESETLOGS
  2      MAXLOGFILES 32
  3      MAXLOGMEMBERS 2
  4      MAXINSTANCES 1
  5      MAXLOGHISTORY 908
  6  LOGFILE
  7    GROUP 1 '/u05/oracle/oradata/CLONEMON/CLONEMON_log1.log' SIZE 100M BLOCKSIZE 512,
  8    GROUP 2 '/u05/oracle/oradata/CLONEMON/CLONEMON_log2.log' SIZE 100M BLOCKSIZE 512
  9  DATAFILE
 10  '/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-2_4kujno35',
 11  '/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-5_4lujno35',
 12  '/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-8_4mujno35',
 13  '/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-9_4nujno35',
 14  '/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SYSAUX_FNO-3_4pujno35',
 15  '/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SYSTEM_FNO-1_4qujno36',
 16  '/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_4oujno35',
 17  '/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-USERS_FNO-7_4rujno36'
 18  CHARACTER SET WE8DEC;
Control file created.
SQL> SQL> declare
  2  begin
  3  dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-2_4kujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON0.dbf');
  4  dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-5_4lujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON1.dbf');
  5  dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-8_4mujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON2.dbf');
  6  dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SOE_FNO-9_4nujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON3.dbf');
  7  dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SYSAUX_FNO-3_4pujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON4.dbf');
  8  dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-SYSTEM_FNO-1_4qujno36' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON5.dbf');
  9  dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-UNDOTBS1_FNO-4_4oujno35' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON6.dbf');
 10  dbms_dnfs.clonedb_renamefile('/mnt/oralabs/.snapshot/z-fbhosts_oralabs.MON/data_D-LABS_I-3539279042_TS-USERS_FNO-7_4rujno36' , '/u05/oracle/oradata/CLONEMON//ora_data_CLONEMON7.dbf');
 11  end;
 12  /
PL/SQL procedure successfully completed.
SQL> show errors;
No errors.
SQL> alter database open resetlogs;
Database altered.
SQL> alter tablespace TEMP add tempfile;
Tablespace altered.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

New Cloned Database

v$clonefile view

We can use the v$clonefile view to map database file name to our read-only snapshot file, please note as our newly created CloneDB database is using the .snapshot directory this must be available all the time the test/dev database is required.

set feed off
set pages 100
set linesize 250
column SNAPSHOTFILENAME heading 'Snapshot File Name' format a100
column CLONEFILENAME heading 'Clone File Name' format a55
column BLOCKS_ALLOCATED heading 'Blocks Allocated'
select 
  SNAPSHOTFILENAME, CLONEFILENAME 
from
  v$clonedfile
/
v$clonedfile view
Full size database on dNFS sparse file system

Operating system view

If we check our Linux database server we can see that our 145GB is showing the expected file sizes.

Sparse filesystem showing full size files.

And all our other clones are also reported as fully sized.

[oracle@z-rac1 oradata]$ du -hs `ls`
 145G CLONEMON
 145G CLONETHU
 145G CLONEWED

However, when we check the actual space used, we can see the sparse file system has consumed very little space on our FlashBlade dNFS share, and physically even less after data reduction.

[oracle@z-rac1 u05]$ df -h /u05
Filesystem                     Size  Used Avail Use% Mounted on
192.168.4.100:/z-rac1_clonedb  1.0T  1.2G 1023G   1% /u05
FlashBlade CloneDB mount point

If you want to see this action, you may want to check-out this video.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s