Creating database clones with ZFS really FAST

While working on my most recent project i tested an interesting ZFS feature: writeable snapshots aka “clones”.

While testing i came to the conclusion that with the help of clones oracle databases (and, of course – other databases as well) can be clones extremely fast with almost no disk space required. So i tested it.

Read on for the results…

Setup everything

Prior testing snapshots i had to install an oracle database and had to create a database. This wont be covered here.

The ZFS file system structure looks like this:

root@oracle:~# df -h
Filesystem             size   used  avail capacity  Mounted on
/                        19T    20G    19T     1%    /
/dev                      0K     0K     0K     0%    /dev
proc                      0K     0K     0K     0%    /proc
ctfs                      0K     0K     0K     0%    /system/contract
mnttab                    0K     0K     0K     0%    /etc/mnttab
objfs                     0K     0K     0K     0%    /system/object
swap                     49G   176K    49G     1%    /etc/svc/volatile
/usr/lib/libc/libc_hwcap1.so.119T    20G    19T     1%   
/lib/libc.so.1fd          0K     0K     0K     0%    /dev/fd
swap                     49G     4K    49G     1%    /tmp
swap                     49G    12K    49G     1%    /var/run
pool1/zones/oracle/db01  27T    52K    19T     1%    /u01/oradata/ORA10P

From the output above you will most probably recognize the path for storing our SOURCE database: /u01/oradata/ORA10P.

In this article i will clone the database with the SID “ORA10P” located in “/u01/oradata/ORA10P” to a database with SID “ORA11P” located in “/u01/oradata/ORA11P”.

Cloning the database

Cloning the database uses the ability of ZFS to create snapshots and to mount them as a writable file system. In addition to that we clone an oracle database by re-creating the control files and changing the database SID.

In the following i will outline the required steps:

Step 1 – Create the Snapshot

For the first tests i created the snapshot while the database was closed:

root@oracle:~# zfs snapshot pool1/zones/oracle/db01@db_clone

Step 2 – Create and Mount the Clone

Create a writeable snapshot called “clone”:

zfs clone pool1/zones/oracle/db01@db_clone pool1/zones/oracle/db02

Set Mountpoint for that clone:

zfs set mountpoint=/u01/oradata/ORA11P pool1/zones/oracle/db02

Thats it. With these steps you created a writable snapshop of your database and mounted it at /u01/oradata/ORA11P.

Lets take a look at the used space:

root@oracle:~# zfs list
NAME                          USED  AVAIL  REFER  MOUNTPOINT
pool1                        7.92T  18.7T  59.6K  /pool1
pool1/zones/oracle/db01      1011M  18.7T   912M  /u01/oradata/ORA10P
pool1/zones/oracle/db02          0  18.7T   912M  /u01/oradata/ORA11P

According to ZFS data stored in /u02/oradata/ORA11P does not take up any space at all… so it is empty?

No it´s not:

root@oracle:~# ls -la  /u01/oradata/ORA11P
total 1867170
drwxrwxr-x   2 ora10p   dba           13 Feb 17 15:00 .
drwxrwxr-x   4 ora10p   dba            4 Feb 17 15:09 ..
-rw-r-----   1 ora10p   dba      7061504 Feb 17 15:02 control01.ctl
-rw-r-----   1 ora10p   dba      7061504 Feb 17 15:02 control02.ctl
-rw-r-----   1 ora10p   dba      7061504 Feb 17 15:02 control03.ctl
-rw-r-----   1 ora10p   dba      52429312 Feb 17 15:01 redo01.log
-rw-r-----   1 ora10p   dba      52429312 Feb 17 15:01 redo02.log
-rw-r-----   1 ora10p   dba      52429312 Feb 17 15:02 redo03.log
-rw-r-----   1 ora10p   dba      251666432 Feb 17 15:02 sysaux01.dbf
-rw-r-----   1 ora10p   dba      492838912 Feb 17 15:02 system01.dbf
-rw-r-----   1 ora10p   dba      20979712 Feb 17 15:00 temp01.dbf
-rw-r-----   1 ora10p   dba      26222592 Feb 17 15:02 undotbs01.dbf
-rw-r-----   1 ora10p   dba      5251072 Feb 17 15:02 users01.dbf

The space shown by “zfs list” shows the occupied amount of space. Referenced blocks are not counted. Immediate after creating the clone all blocks in the clone refer to the original blocks thus taking up no space at all.

Step 3 – Backup the source database control file to trace

As SYSDBA connected to your SOURCE database (in our case “ORA10P” is our source database) execute:

SQL>
SQL> alter database backup controlfile to trace;

Database altered.

After that locate the trace file just created. Look for the trace file in the directory you specified as database parameter “user_dump_dest”.

At my site the parameter is set to “/u01/app/oracle/admin/ORA10P/udump”. So i have to look for the trace file there.

-bash-4.0$ ls -lat  /u01/app/oracle/admin/ORA10P/udump
total 90
-rw-r—–   1 ora10p   dba         6044 Feb 17 15:16 ora10p_ora_21829.trc
drwxr-x—   2 ora10p   dba           13 Feb 17 15:16 .
-rw-r—–   1 ora10p   dba          625 Feb 17 15:16 ora10p_ora_21824.trc
-rw-r—–   1 ora10p   dba          569 Feb 17 15:16 ora10p_ora_21795.trc
-rw-r—–   1 ora10p   dba          755 Feb 17 15:02 ora10p_ora_20396.trc

Most probably the file i am looking for is “ora10p_ora_21829.trc” because i looked for the file immediate after backing up the control file to trace.

Step 4 – Edit the control file

I have successfully located the control file trace dump and copied the file:

-bash-4.0$ cp  ora10p_ora_21829.trc create.sql

The file header looks like this:

/u01/app/oracle/admin/ORA10P/udump/ora10p_ora_21829.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/ora10p
System name:    SunOS
Node name:      oracle
Release:        5.11
Version:        snv_132
Machine:        i86pc
Instance name: ORA10P
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 21829, image: oracle@oracle (TNS V1-V3)

*** SERVICE NAME:(SYS$USERS) 2010-02-17 15:16:57.065
*** SESSION ID:(159.1) 2010-02-17 15:16:57.065
*** 2010-02-17 15:16:57.065
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''

[....]

[....]

--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.

[....]

For editing execute the following steps:

1. Locate the section starting with”–     Set #2. RESETLOGS case”
2. Skip forward until you find:
   CREATE CONTROLFILE REUSE DATABASE “ORA10P” RESETLOGS  NOARCHIVELOG

   Note that the database name might be different!

3. DELETE EVERYTHING ABOVE THE LINE STARTING WITH “CREATE DATABASE”
4. Edit that line as follows:
   CREATE CONTROLFILE SET DATABASE “ORA11P” RESETLOGS  NOARCHIVELOG
5. Skip to the file paths:
   […]
   LOGFILE
   GROUP 1 ‘/u01/oradata/ORA10P/redo01.log’  SIZE 50M,
   GROUP 2 ‘/u01/oradata/ORA10P/redo02.log’  SIZE 50M,
   GROUP 3 ‘/u01/oradata/ORA10P/redo03.log’  SIZE 50M
   — STANDBY LOGFILE
   DATAFILE
   ‘/u01/oradata/ORA10P/system01.dbf’,
   ‘/u01/oradata/ORA10P/undotbs01.dbf’,
   ‘/u01/oradata/ORA10P/sysaux01.dbf’,
   ‘/u01/oradata/ORA10P/users01.dbf’
   CHARACTER SET WE8ISO8859P1
   ;
6. Edit them to reflect the new paths:
   […]
   LOGFILE
   GROUP 1 ‘/u01/oradata/ORA11P/redo01.log’  SIZE 50M,
   GROUP 2 ‘/u01/oradata/ORA11P/redo02.log’  SIZE 50M,
   GROUP 3 ‘/u01/oradata/ORA11P/redo03.log’  SIZE 50M
   — STANDBY LOGFILE
   DATAFILE
   ‘/u01/oradata/ORA11P/system01.dbf’,
   ‘/u01/oradata/ORA11P/undotbs01.dbf’,
   ‘/u01/oradata/ORA11P/sysaux01.dbf’,
   ‘/u01/oradata/ORA11P/users01.dbf’
   CHARACTER SET WE8ISO8859P1
   ;
   Note: This is the most important part here. You have to make sure you do not reference any file used by the source database here!
7. Delete EVERYTHING BELOW

Step 5 – Clone the database by re-creating the control file

After editing the script it should look like the following sample for re-naming the source database with SID “ORA10P” to “ORA11P”:

CREATE CONTROLFILE SET DATABASE "ORA11P" RESETLOGS  NOARCHIVELOG
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 8
 MAXLOGHISTORY 292
LOGFILE
 GROUP 1 '/u01/oradata/ORA11P/redo01.log'  SIZE 50M,
 GROUP 2 '/u01/oradata/ORA11P/redo02.log'  SIZE 50M,
 GROUP 3 '/u01/oradata/ORA11P/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
 '/u01/oradata/ORA11P/system01.dbf',
 '/u01/oradata/ORA11P/undotbs01.dbf',
 '/u01/oradata/ORA11P/sysaux01.dbf',
 '/u01/oradata/ORA11P/users01.dbf'
CHARACTER SET WE8ISO8859P1
;

If neccessary create a new pfile for the new instance and the directories specified in “audit_file_dest”, “background_dump_dest” and “user_dump_dest”. Just make sure you change:

• control file location
• db_name

A simple pfile for our new database looks like this:

audit_file_dest='/u01/app/oracle/admin/ORA11P/adump'
background_dump_dest='/u01/app/oracle/admin/ORA11P/bdump'
compatible='10.2.0.1.0'
control_files='/u01/oradata/ORA11P/control01.ctl','/u01/oradata/ORA11P/control02.ctl','/u01/oradata/ORA11P/control03.ctl'
core_dump_dest='/u01/app/oracle/admin/ORA11P/cdump'
db_block_size=8192
db_file_multiblock_read_count=16
db_name='ORA11P'
open_cursors=300
pga_aggregate_target=134217728
processes=150
remote_login_passwordfile='EXCLUSIVE'
sga_target=536870912
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/u01/app/oracle/admin/ORA11P/udump'

After setting everything everything up DELETE the control files of the source database located in the clone file system “/u01/oradata/ORA11P“. DO NOT DELETE THE CONTROL FILES OF YOUR SOURCE DATABASE IN “/u01/oradata/ORA10P”!!

Afterwards tart the instance:

-bash-4.0$ export ORACLE_SID=ORA11P
-bash-4.0$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 17 15:50:34 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area  536870912 bytes
Fixed Size                  2120416 bytes
Variable Size             152937760 bytes
Database Buffers          377487360 bytes
Redo Buffers                4325376 bytes
SQL>

If the instance starts successfully with “startup nomount” execute our just created script:

SQL> @create
Control file created.

Note: If you would have not deleted the control files the script will raise an error it cannot re-create the control files because they are already there:

SQL> @create
CREATE CONTROLFILE SET DATABASE "ORA11P" RESETLOGS  NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/oradata/ORA11P/control01.ctl'
ORA-27038: created file already exists
Additional information: 1

The reason for the control files being already there is that we cloned everything including redo logs, temporary tablespace and also control files.

After re-creating the contron file issue:

SQL> alter database open resetlogs;
Database altered.
SQL> update global_name set global_name='ORA11P';
1 row updated.
SQL> commit;
Commit complete.
SQL> alter tablespace temp add tempfile '/u01/oradata/ORA11P/temp01.dbf' reuse;
Tablespace altered.

After that shut down the instance and startup again to check if everything is OK:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  536870912 bytes
Fixed Size                  2120416 bytes
Variable Size             152937760 bytes
Database Buffers          377487360 bytes
Redo Buffers                4325376 bytes
Database mounted.
Database opened.

After finishing these steps you have a bit wise identical copy of your source database. In addition to that i strongly recommend to check the alert.log for any errors.

Checking Space again

After cloning our database we observe an increase in storage needs for our cloned database:

pool1/zones/oracle/db01      1.02G  18.7T   912M  /u01/oradata/ORA10P
pool1/zones/oracle/db02      81.2M  18.7T   913M  /u01/oradata/ORA11P

After performing the steps above the just re-created control files and redo logs take 81.2 MB space because these blocks are not shared anymore.

The great picture

The example above is just a proof-of-concept. It shows there can be significant storage savings by using ZFs snapshot/cloning mechanism for instance to create database or development or testing purposes.

In addition to that cloing the database is extremely fast and does not depend on the size of the database being cloned. The whole procedure outlined here takes when done manually approx. 5 minutes regardless of the database size.

Most probably (i have not tested this yet) the database can be cloned online by putting the database in hot backup mode (“alter database backup begin”) before taking the snapshot.

And – most important – it is COMPLETELY SCRIPTABLE!