Cloning ASM Diskgroup to Create Database Clone

(VERSION INFORMATION: Oracle GI & DB11.2.0.3; Red Hat 5u7)

Purpose is to simulate a VLDB environment where you have to clone your database using tools like Snapshot , BCV, SnapMirror. This scenario cloned RAC database using ASM to a single database.

Description of test environment:  There are two node RAC. The cloned database was created on the first node.

Cloning ASM Diskgroups

1)      Setup your storage device to have enough space for diskgroup clone.

ASMCMD> lsdsk --candidate –p

/dev/sfe1

 Note: all the necessary configuration on the raw devices as be performed e.g. ASMLib or UDEV. This lab use UDEV.

2)      ASMCMD> lsdsk -k -G data

 /dev/sde1

3)      As oracle OS user shutdown the database. Or you can use ALTER SYSTEM SUSPEND/RESUME database feature, you can suspend I/O to the database, split the mirror, and make a backup of the split mirror.

srvctl stop database -d orcl

4)      Create a clone of diskgroup disks. Here, we are using dd utility; in production environment you may come across some more sophisticated tools like BCV. In fact, some mirroring tools do not require Oracle DB to suspend I/O neither shutdown down before you split your mirror (Note: ASM instance does caching and striping, so be sure of the capability of the tool). Oracle DB suspend I/O features reduce downtime compare to DB shutdown.

dd if=/dev/sde1 of=/dev/sdf1 bs=1024k

5)      Use the KFED ASM utility to read and compare the disks header.

Change directory as root to grid ORACLE_HOME/bin

[root@red1 bin]# cd /u01/app/11.2.0/grid/bin

[root@red1 bin]# ./kfed read /dev/sde1 > kfed_sde1.log

[root@red1 bin]# ./kfed read /dev/sdf1 > kfed_sdf1.log

[root@red1 bin]# diff kfed_sde1.log kfed_sdf1.log

[root@red1 bin]# head kfed_sde1.log

Note that /dev/sdf1 will not be listed among candidate disks, as in step 1.

ASMCMD> lsdsk --candidate –p

6)      Rename disk group by renamedg command. I used a two-step method here. You can simply use one step method ($ renamedg dgname=data newdgname=clone_data verbose=true)

[grid@red1 ~]$ renamedg phase=one config=cloneasmdisk.conf verbose=true dgname=data newdgname=clone_data asm_diskstring='/dev/sdf1'

[grid@red1 ~]$ renamedg phase=two dgname=data newdgname=clone_data config=cloneasmdisk.conf verbose=true

7)      Using the diff(Linux utility) and kfed utility, verify the success of renamedg operation

[root@red1 bin]# ./kfed read /dev/sdf1 > kfed_sdf1.log

[root@red1 bin]# diff kfed_sde1.log kfed_sdf1.log

OR

Query v$asm_diskgroup

SQL> select name from v$asm_diskgroup;

8)      Mount the clone diskgroup.

SQL>alter diskgroup clone_data mount;

OR

ASMCMD> mount clone_data

ASMCMD> lsdg

Cloning Database

1.       Create and edit  pfile from the source database

SQL> create pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/clonedb.ora'  from spfile;

File created.

Using an editor, replace +DATA(sorce_diskgroup) with +CLONE_DATA(clone_diskgroup).

Edit parameters audit_file_dest, dispatchers

Also, substitute as necessary orcl (i.e. my source instance name) with clodb (name given to new instance).

After editing, pfile look like

pga_aggregate_target=134217728

sga_target=398458880

*.audit_file_dest='/u01/app/oracle/admin/clodb/adump'

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.0.0'

*.control_files='+CLONE_DATA'

#FOR control_files DO NOT USE THE FULL PATH. Also remove old control file

*.db_block_size=8192

*.db_create_file_dest='+clone_data'

*.db_domain='tobi.com'

*.db_name='clodb'

#changing the database name

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=clodbXDB)'

*.open_cursors=300

*.pga_aggregate_target=132120576

*.processes=200

*.remote_login_passwordfile='exclusive'

*.sessions=225

*.sga_target=398458880

*.undo_tablespace='UNDOTBS1'

*.db_unique_name='clodb'

*.db_create_online_log_dest_1='+CLONE_DATA'

*.cluster_database='false'

Create directory audit_file_dest

mkdir -p /u01/app/oracle/admin/clodb/adump

2.       Create control file trace from source DB:

SQL> alter database backup controlfile to trace as '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/controltrace.ora';

Edit according. After editing, see below listings:

CREATE CONTROLFILE SET DATABASE "CLODB" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 192

    MAXLOGMEMBERS 3

    MAXDATAFILES 1024

    MAXINSTANCES 32

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '+CLONE_DATA/orcl/onlinelog/group_1.261.839965849'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 2 (

    '+CLONE_DATA/orcl/onlinelog/group_2.262.839965855'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 3 (

    '+CLONE_DATA/orcl/onlinelog/group_3.265.839966205'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 4 (

    '+CLONE_DATA/orcl/onlinelog/group_4.266.839966209'

  ) SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '+CLONE_DATA/orcl/datafile/system.256.839965691',

  '+CLONE_DATA/orcl/datafile/sysaux.257.839965693',

  '+CLONE_DATA/orcl/datafile/undotbs1.258.839965695',

  '+CLONE_DATA/orcl/datafile/users.259.839965695',

  '+CLONE_DATA/orcl/datafile/undotbs2.264.839966043'

CHARACTER SET AL32UTF8;

3.       Startup new instance for clone database.

[oracle@red1 ~]$ ORACLE_SID=clodb

[oracle@red1 ~]$ export ORACLE_SID

[oracle@red1 ~]$ sqlplus / as sysdba

SQL>  startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/clodb3.ora';

ORACLE instance started.

4.       Create Control file and Rename database

SQL>  @/u01/app/oracle/product/11.2.0/dbhome_1/dbs/cloctl.sql

SQL> select name from v$datafile;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘+CLONE_DATA/orcl/tempfile/temp.263.839965869'   SIZE 35651584 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Note that the “ALTER TABLESPACE TEMP ADD TEMPFILE …” was copied from control trace created earlier.

5.       Do a little test: creating tablesapce testclone.

SQL> create tablespace testclone datafile size 10m;

  

Create spfile to ease your dba task J

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/clodb3.ora';

Conclusion:

For Very Large Database, backup time can be reduced significantly by cloning ASM diskgroups.

Ref & More reading:

1. Oracle® Automatic Storage Management Administrator's Guide 11g Release 2 (11.2)

2. Oracle® Database Backup and Recovery User's Guide 11g Release 2 (11.2)