Clone Database: Turn your backup (rman image copy) to a database without copy/restore?

Imagine how it could be if you can use your image copies of your databases as your UAT/dev databases, without much usign complex restore procedure, how it could be if you refresh/clone the databases.

The answer is is using Oracle Direct NFS Client and dbms_dnfs package.

Clonedb is a new Direct NFS (DNFS) feature introduced in the 11.2.0.2 database patchset. Rather than using the traditional RMAN database duplication, clonedb uses dNFS technology to instantly fire up a clone using an existing backup of a database as the data store. The clone uses copy-on-write technology, so only changed blocks need to be stored locally, while the unchanged data is referenced directly from the backup files. This drastically increases the speed of cloning a system and means that several separate clones can function against a single set of backup datafiles, thus saving considerable amounts of space & time.

Before proceeding, the required environment to use this cool feature.

• A traditional NFS Server and or the Oracle DNFS client
• Oracle NFS Client driver
• Oracle Database using 11.2.0.2

1) Setup NFS Server:-

On NFS Server

mkdir –p /home/oracle/nfsshares

/home/oracle/nfsshares    *(rw,sync,no_wdelay,insecure,insecure_locks,no_root_squash)

chkconfig nfs on

service nfs restart

 

Use the below in your target server

mkdir –p /home/oracle/TEST

nas1:/home/oracle/nfsshares/ /home/oracle/TEST  nfs  rw,bg,hard,nointr,tcp,vers=3,timeo=600,rsize=32768,wsize=32768,actimeo=0  0 0

2) Backup the source database , use image copy

rman target=/

configure controlfile autobackup off;
sql 'alter database begin backup';
run {
set nocfau;
backup as copy database format '/home/oracle/TEST/backups/%U' ;
}
sql 'alter database end backup';

3) Set up my target database

a) Pfile

test.__db_cache_size=243269632
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__pga_aggregate_target=104857600
test.__sga_target=373293056
test.__shared_io_pool_size=0
test.__shared_pool_size=113246208
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/test/oradata/test/control01.ctl','

/home/oracle/test/oradata/test/control02.ctl'
*.db_block_size=8192
*.db_domainCC"1[=''
*.db_name='test'
*.diagnostic_dest='/home/oracle/test/diag'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.log_archive_dest_1='LOCATION=/home/oracle/test/archive'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=373293056
*.undo_tablespace='UNDOTBS1'

b) Create directories

Source
mkdir -p /home/oracle/prod
cd /home/oracle/prod
mkdir -p diag oradata

Target

mkdir -p /home/oracle/Clone
cd /home/oracle/Clone
mkdir -p diag oradata

 

3) Set environment

export ORACLE_SID=prod
export MASTER_COPY_DIR=/home/oracle/PROD/backups
export CLONE_FILE_CREATE_DEST=/home/oracle/Clone/

export CLONEDB_NAME=TEST

Note:

MASTER_COPY_DIR is the directory where you store your master copy of the databases

CLONE_FILE_CRETE_DEST is the directory where your on copy write technology datafiles store.

 

4) Run the clone.pl script downloaded from Metalink Note 1210656.1 and run as below, this script required three arguments init.ora location, databasescript, renamescript

[oracle@rac1 tmp]$ perl clone.pl $ORACLE_HOME/initclone.ora createdb.sql rename.sql
…..

[oracle@rac1 tmp]$ ls -ltr

-rw-r--r-- 1 oracle oinstall  790 Sep 23 13:37 rename.sql
-rw-r--r-- 1 oracle oinstall  834 Sep 23 13:37 createdb.sql
-rwxr--r-- 1 oracle oinstall 6941 Sep 24  2011 clone.pl

5) View what this output contains

[oracle@rac1 tmp]$ cat rename.sql
declare
begin
dbms_dnfs.clonedb_renamefile('/home/oracle/TEST/backups/data_D-PROD_I-174444288_TS-SYSAUX_FNO-2_03mn954v' , '/home/oracle/clone/oradata/clone/ora_data_clone0.dbf');
dbms_dnfs.clonedb_renamefile('/home/oracle/TEST/backups/data_D-PROD_I-174444288_TS-SYSTEM_FNO-1_02mn94t1' , '/home/oracle/clone/oradata/clone/ora_data_clone1.dbf');
dbms_dnfs.clonedb_renamefile('/home/oracle/TEST/backups/data_D-PROD_I-174444288_TS-UNDOTBS1_FNO-3_04mn95bd' , '/home/oracle/clone/oradata/clone/ora_data_clone2.dbf');
dbms_dnfs.clonedb_renamefile('/home/oracle/TEST/backups/data_D-PROD_I-174444288_TS-USERS_FNO-4_05mn95f0' , '/home/oracle/clone/oradata/clone/ora_data_clone3.dbf');
end;
/
show errors;
alter database open resetlogs;
drop tablespace TEMP;
create temporary tablespace TEMP;

[oracle@rac1 tmp]$ cat createdb.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=/home/oracle/clone/oradata/clone/initclone.ora
CREATE CONTROLFILE REUSE SET DATABASE clone RESETLOGS
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXINSTANCES 1
MAXLOGHISTORY 908
LOGFILE
GROUP 1 '/home/oracle/clone/oradata/clone/clone_log1.log' SIZE 100M BLOCKSIZE 512,
GROUP 2 '/home/oracle/clone/oradata/clone/clone_log2.log' SIZE 100M BLOCKSIZE 512
DATAFILE
'/home/oracle/TEST/backups/data_D-PROD_I-174444288_TS-SYSAUX_FNO-2_03mn954v',
'/home/oracle/TEST/backups/data_D-PROD_I-174444288_TS-SYSTEM_FNO-1_02mn94t1',
'/home/oracle/TEST/backups/data_D-PROD_I-174444288_TS-UNDOTBS1_FNO-3_04mn95bd',
'/home/oracle/TEST/backups/data_D-PROD_I-174444288_TS-USERS_FNO-4_05mn95f0'
CHARACTER SET WE8DEC;

[oracle@rac1 tmp]$

6) Run the scripts generated in target database “Test” and it will create your database, and open the database ‘TEST” in reset logs if you encounter the media recovery.

$ sqlplus / as sysdba

SQ> @createdb.sql

SQL> @dbrename.sql

SQL> alter database open resetlogs;

7) Look at your database and data file locations

COLUMN df_name FORMAT A50
COLUMN ts_name FORMAT A10

SELECT t.name AS ts_name,
d.name AS df_name,
status
FROM   v$datafile d
JOIN v$tablespace t ON t.ts# = d.ts#
ORDER BY t.name;

TS_NAME    DF_NAME                                            STATUS
---------- -------------------------------------------------- -------
EXAMPLE    /home/oracle/Clone/TEST/example.dbf    ONLINE
SYSAUX     /home/oracle/Clone/TEST/sysaux.dbf    ONLINE
SYSTEM     /home/oracle/Clone/TEST/system.dbf    SYSTEM
UNDOTBS1   /home/oracle/Clone/TEST/undotbs1.dbf    ONLINE
USERS      /home/oracle/Clone/TEST/users.dbf    ONLINE

SQL>

8) Look at the filesystem of your

$ du -k /home/oracle/prod/*
354280    /home/oracle/prod/data_D-DB11G_I-199362479_TS-EXAMPLE_FNO-5_1mmjkqp5
717512    /home/oracle/prod/data_D-DB11G_I-199362479_TS-SYSAUX_FNO-2_1kmjkqmb
738012    /home/oracle/prod/data_D-DB11G_I-199362479_TS-SYSTEM_FNO-1_1jmjkqku
563764    /home/oracle/prod/data_D-DB11G_I-199362479_TS-UNDOTBS1_FNO-3_1lmjkqno
30764    /home/oracle/prod/data_D-DB11G_I-199362479_TS-USERS_FNO-4_1nmjkqpv
$

$ du -k /home/oracle/Clone/TEST/*.dbf
16    /home/oracle/Clone/TEST/example.dbf
52    /home/oracle/Clone/TEST/sysaux.dbf
76    /home/oracle/Clone/TEST/system.dbf
176    /home/oracle/Clone/TEST/undotbs1.dbf
16    /home/oracle/Clone/TEST/users.dbf
$

See the above image copies in location /home/oracle/prod are as is and the datafile location, observe especially the sizes of the files, these files only stores the blocks that of changed from the backup

Another important aspect with this feature is you can create multiple copies of the database with one backup copy as the source of the datafiles will be image backup copies and the changes will go to original datafiles as like above.

Hope you have enjoyed reading

References:-

http://www.oracle-base.com/articles/11g/clonedb-11gr2.php

-Thanks

Geek DBA