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…
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
-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:
- Locate the section starting with”– Set #2. RESETLOGS case”
- Skip forward until you find:
CREATE CONTROLFILE REUSE DATABASE “ORA10P” RESETLOGS NOARCHIVELOG
Note that the database name might be different!
- DELETE EVERYTHING ABOVE THE LINE STARTING WITH “CREATE DATABASE”
- Edit that line as follows:
CREATE CONTROLFILE SET DATABASE “ORA11P” RESETLOGS NOARCHIVELOG
- Skip to the file paths:
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
CHARACTER SET WE8ISO8859P1
- Edit them to reflect the new paths:
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
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!
- 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
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!