CLONE A MULTI-TERABYTE ORACLE DATABASE IN 5 MINUTES
In an environment with multiple Oracle Databases running large amounts of data (multi-terabyte), the snapshot storage feature is very useful when you want to preserve the state of the Oracle database, make a clone, create a standby database or just backup the environment. This feature simply saves the current state of your Oracle storage environment while the database is online (in open mode) or offline (the Oracle instance is shutdown during the snapshot phase). When the Oracle database is in no archive log mode, only snapshot offline mode is possible to save a consistent state of your Oracle storage environment. However, in an archive log mode, both online and offline modes are possible because it would be possible to apply archive log modes for the period between the SCN of the Snapshots and the target SCN (Database point-in-time recovery (DBPITR)). The Snapshot feature is another method to back up the Oracle database. Compare with hard disk imaging, it require less storage space for each snapshot than a system partition image file, and the restoration speed is significantly increased. Backing up the Oracle databases using Oracle Recovery manager and Snapshot feature guarantees much safer and protected Oracle database environments. In this article, we will show how the use HP 3PAR Thin Provisioning Software, Oracle ASM utilities and the Oracle database point-in-time recovery procedure to clone a multi-terabyte Oracle production database within 5 minutes (in this case 7 Terabyte of size). We will use HP 3PAR InForm OS Command Line Interface Software, command line user interface for managing, and configuring HP 3PAR Storage Systems and the Oracle Automatic Storage Management (ASM) commands for managing the Oracle Storage. (Note: Thin Provisioning is a mechanism to improve the utilization efficiency of storage capacity deployment and virtualization technology.)
The following table describes the source and target Oracle databases. We name the source database (livedb in this case) the database to be cloned and the target database (clonedb) which is the database result of the clone procedure.
Source database name
Source database Size
Source database version
Source DB host name
Source OS version
Oracle Linux 6
Source Oracle Storage manager
Source ASM disk group
Source Storage Solution
Target database name
Target database Size
Target database version
Target DB host name
Target OS version
Oracle Linux 6
Target Oracle Storage manager
Target ASM disk group
Target Storage Solution
Let’s check the database size of the Source livedb database. As you can see the livedb has 7 terabytes.
jupiter(livedb):/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 126.96.36.199.0 Production on Sat Feb 28 05:50:44 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
total_size in GB
The next step is to check the ASM disks used by the livedb database.
SYS:livedb SQL> show parameter db_create
NAME TYPE VALUE
We can see the livedb uses LIVEDB_DG diskgroup. We use asmcmd command to list the ASM disks;
JUPITER(+ASM):/home/oracle>asmcmd lsdsk -G LIVEDB_DG
The overall size allocated for the 4 ASM disks is 8 terabytes.
SQL> select name,total_mb from v$asm_disk where header_status='MEMBER' and group_number=35;
Now, we use the HP 3PAR InForm OS Command Line Interface to list the set of the Virtual Volume used by the Oracle database. We run the command showvvset to show all autonomic virtual volume groups or sets.
3PAR700 cli% showvvset
14 JUPITER_3PAR_LIVE JUPITER_3PAR_LIVE.0
From the above outputs we see we have a virtual volume set name JUPITER_3PAR_LIVE along with four virtual volumes (JUPITER_3PAR_LIVE.0, JUPITER_3PAR_LIVE.1 , JUPITER_3PAR_LIVE.2 and JUPITER_3PAR_LIVE.3) all used by the oracle livedb database.
The Oracle database livedb is in no archive log mode so we clean shutdown the database to make the snapshot.
SYS:livedb SQL> select log_mode from v$database;
SYS:livedb SQL> shutdown immediate;
After clean shutdown, we create the snapshots using the following command:
3PAR700 cli% createsv -exp 1825d TPAR_CLONEDB.@count@ set:JUPITER_3PAR_LIVE
This command will create a snapshot backup of the JUPITER_3PAR_LIVE volume set. We set the virtual volume expiration times with the -exp option in our case we set it to 1825 days. We created a virtual copy, or snapshot, of the virtual volume set using the command createsv. That’s it. With a downtime of only 5 minutes we now have a snapshot copy of the livedb.
We can startup the livedb database using the startup command.
First we create a virtual volume set for easy management of the new TPAR_CLONEDB* copies using createvvset 3par command.
3PAR700 cli% createvvset TPAR_CLONEDB TPAR_CLONEDB*
We show the newly set created;
3PAR700 cli% showvvset |grep TPAR_CLONEDB
75 TPAR_CLONEDB TPAR_CLONEDB.0
Manipulate the Snapshot for the cloned database
First, export the virtual volumes to the JUPITER host using the command createvlun.
createvlun TPAR_CLONEDB.0 auto JUPITER
Next, perform a LUN scanning on all HBAs in the Jupiter host and reload the multipath.
We can get the list of LUNS using the HP3PARInfo –i command; this will show the LUNS used by CLONEDB snapshots;
350002ac000000000020004d40000632e 2T mpathxe dm-154 TPAR_CLONEDB.0 JUPITER_3PAR_LIVE_0
At this stage, we need to rename the ASM disks from JUPITER_3PAR_LIVE* to TPAR_CLONEDB* using oracleasm renamedisk from ASMLIB command line.
/usr/sbin/oracleasm renamedisk -f /dev/dm-154 TPAR_CLONEDB_0
Another important step is to modify the disk headers of the ASM disks to reflect the new ASM diskgroup that cloned is going to use. From the outputs (see below), we see that the actual ASM disks are still referring to the LIVEDB_DG diskgroup. We need to modify the name from LIVEDB_DG to CLONEDB_DG.
[root@jupiter LIVEDB]# strings -a /dev/dm-154 |head -4
To modify the ASM disk headers, we can use kfed command; kfed can be used to read and write ASM metadata in disk headers and ASM metadata files. Please note that kfed in write mode is a powerful tool and can corrupt the disk metadata if it is not well used. You can refer to the My Oracle Support Document: “ASM tools used by Support: KFOD, KFED, AMDU (Doc ID 1485597.1) “for more details about kfed usage.
First we change the owner and group of CLONEDB_DG* disks from root to oracle;
Chown –R oracle:dba /dev/oracleasm/disks/ TPAR_CLONEDB *
Then, I created a script to do the kfed work; basically we search in ASM disks TPAR_CLONEDB* and we replace the occurrences of LIVEDB_DG to CLONEDB_DG.
[root@jupiter LIVEDB]# cat /usr/local/oracle/bin/LIVEDB/LIVEDB_kfed.sh
kfed op=read dev=$file | sed -e '24,24s/ '$search' / '$replace' /' -e '24,24s/length=.*/length='$newlength'/' > /tmp/$shortname.kfed
After running the kfed command as oracle user. We check again the ASM disk headers. You can see we now have CLONEDB_DG showing up.
[root@jupiter LIVEDB]# strings -a /dev/dm-154 |head -4
The next step is to mount the new diskgroup CLONEDB_DG;
ALTER DISKGROUP CLONEDB_DG mount;
Now that we have the new diskgroup mounted, we need to create a new pfile and conrolfile for the new cloned database.
We login into the livedb database and we issue the following commands:
alter database backup controlfile to trace as '/tmp/cr_ctl_clonedb_wissem.sql';
The following steps include replacing all the occurrences of livedb and LIVEDB_DG in /tmp/cr_ctl_clonedb_wissem.sql' and ’/tmp/pfile_clonedb_wissem.ora’ files.
The clonedb controlfile will looks like this; please note we replaced the “REUSE DATABASE” from the original control file to “SET DATABASE”.
CREATE CONTROLFILE SET DATABASE "CLONEDB" RESETLOGS NOARCHIVELOG
Finally we startup the database in nomount state, we run the controlfile script, open the database with resetlogs and add temporary files to the temporary tablespaces. (Note: you may also invoke the DBNEWID utility to modify the DBID of the newly cloned database).
Startup nomount pfile=’ /tmp/pfile_clonedb_wissem.ora’;
We have seen how combine snapshots feature and Oracle ASM utilities (ASMLIB, kfed, asmcmd) speed up and simply the cloning process of multi – terabyte Oracle databases.
About the Author
Wissem is a Senior DBA with over 12 years of experience specialized in Oracle HA solutions / Big Data. He works for “Schneider Electric / APC Global operations”. Wissem has also worked for several international enterprise leaders in many industries including Banking, Telecommunications, Internet and Energy. Wissem is the first Oracle ACE in Spain and he has earned many certifications including OCP DBA.