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)

File System On ASM Volume

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

The purpose of the post is to shows how easily you can create and administer filesystem with diskgroup.

Create and Mount Diskgroup

First, we have to create diskgroup. Existing diskgroup can also be used.

SQL> create diskgroup temdg external redundancy disk '/dev/sdk1' name disk12g;

SQL> select inst_id,name,state,compatibility from gv$asm_diskgroup where name ='TEMDG'

Mount the newly created diskgroup on other instances. The newly created diskgroup will only be mounted on the local ASM instance.

$ssh grid@node2   --connect to the other ASM node

SQL> alter diskgroup TEMDG mount;

Create ACFS File System

ASMCMD> volcreate -G temdg -s 1g testacfs

ORA-15032: not all alterations performed

ORA-15221: ASM operation requires compatible.asm of 11.2.0.0.0 or higher (DBD ERROR: OCIStmtExecute)

The above was intentional…this show that you need to advance the compatible.asm to 11.2.

ASMCMD> setattr  -G temdg compatible.asm 11.2

OR

SQL>  ALTER DISKGROUP TEMDG SET ATTRIBUTE 'COMPATIBLE.ASM'='11.2';

We can now successfully run volcreate

ASMCMD>  volcreate -G temdg -s 1g testacfs

OR

SQL> ALTER DISKGROUP TEMDG ADD VOLUME TESTACFS2 SIZE 1G;

Check the volume device name by executing:

ASMCMD> volinfo -G temdg –a                               --(or query V$ASM_VOLUME)

With the device path we can now create filesystem [as root user].

This should be done only on one node!

[root@red1 ~]# mkfs -t acfs /dev/asm/testacfs-225

Let mount the newly created filesystem.

Note: this is cluster filesystem, so you can mount it on other nodes.

[root@red2 ~]# mkdir /testmnt

[root@red2 ~]#  mount -t acfs /dev/asm/testacfs-225 /testmnt

We can also decide to register file system with ACFS mount registry, this will automate mount of the file system.

[root@red2 ~]# acfsutil registry -a /dev/asm/testacfs-225 /testmnt

acfsutil registry: mount point /testmnt successfully added to Oracle Registry

Note: Do not register an Oracle ACFS file system that has had an individual file system Cluster Ready Services (CRS) resource added. For example, do not register a file system with acfsutil registry if the Oracle ACFS file system has been registered using srvctl add filesystem.

Resizing filesystem

Filesystem can also be resized without much work but you must have free space in your DiskGroup.

For example, resizing /testmnt to 6G

#acfsutil size 6G /testmnt

Conclusion:

If ASM infrastructure is configured for RAC DB, you can easily take advantage of the ASM volume to shared files among cluster nodes.

Testing Scalability of RAC: Using SwingBench OLTP

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

I used swingbench and a particular service in swingbench that does this “Models the classic order entry stress test. It has a similar profile to the TPC-Cbenchmark. This version models a online order entry system with users being required to log-on before purchasing goods.”

Section A: Creating oracle database service

I will create a service called swing to easily monitor and evaluate using one instance of RAC cluster (singleton). Later, I will modify the swing service to use two instances in the RAC cluster.

1.    Create service with preferred instance. (it only use one instance at a particular time, if node 1 is down, swing.tobi.com service will failover to orcl2)

$ srvctl add service -d orcl -s swing.tobi.com -r orcl1 -a orcl2

2      $srvctl start service -d orcl -s swing.tobi.com

Section B: Setting up SwingBench & Loading data

Download SwingBench from : http://dominicgiles.com/downloads.html

1.      Make sure you have the right JRE version that works well with the swingbench software you downloaded.

$ java -version

Cd to swingbench/bin directory

$cd /tmp/swingbench/bin

$./oewizard

Create tablespace for soe database user. SOE tablespace will contain Order Entry data.

SQL> CREATE TABLESPACE SOE DATAFILE SIZE 4096M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Section C: Generate OLTP Activities

1.      Edit configs/oeconfig.xml (your swingbench location )

<ConnectString>//red-scan.tobi.com/swing.tobi.com</ConnectString>

<Password>soe</Password>

<UserName>soe</UserName>

//red-scan.tobi.com/swing.tobi.com connection string to the particular service I created above.

2.      $ bin/charbench -c /tmp/swingbench/configs/oeconfig.xml -uc 20  -a -v users,trans,tps   

N.B: to know more about charbench command options $charbench -h

3.      Check service time and through using gv$servicemetric_history

SELECT

service_name

, TO_CHAR(begin_time, 'HH:MI:SS') begin_time , TO_CHAR(end_time, 'HH:MI:SS') end_time

, instance_name

, elapsedpercall service_time

, callspersec throughput

FROM

gv$instance i , gv$active_services s , gv$servicemetric_history m WHERE s.inst_id = m.inst_id AND s.name_hash = m.service_name_hash

AND i.inst_id = m.inst_id

AND m.group_id = 10 and service_name='swing.tobi.com'

ORDER BY

service_name

, i.inst_id

, begin_time ;

On the sheet above, swing.tobi.com only runs on instance (node) orcl1 and has average value of 11000 and 1500 for service time and throughput respectively.

You can only use some Linux utility such as sar and vmstat to monitor the resources consumption on all the node on the cluster.

Section D: Increase The Preferred Instance of swing.tobi.com Service to 2

The following procedure is like turning on Active/Active mode for swing.tobi.com service. Earlier it was created to be available at a point in time on only one instance (node) i.e. Active/Passive.

[oracle@red1 ~]$ srvctl modify service -d orcl -s swing.tobi.com -i orcl2 -r

[oracle@red1 ~]$ srvctl config service -d orcl

Service name: swing.tobi.com

Service is enabled

Server pool: orcl_swing.tobi.com

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: SERVICE_TIME

TAF policy specification: NONE

Edition:

Preferred instances: orcl1,orcl2

Available instances:

[oracle@red1 ~]$  srvctl start service -d orcl -s swing.tobi.com -n red2

[oracle@red1 ~]$ srvctl status  service -d orcl -s swing.tobi.com

Service swing.tobi.com is running on instance(s) orcl1,orcl2

1.      Check service time and through using gv$servicemetric_history

SELECT

service_name

, TO_CHAR(begin_time, 'HH:MI:SS') begin_time , TO_CHAR(end_time, 'HH:MI:SS') end_time

, instance_name

, elapsedpercall service_time

, callspersec throughput

FROM

gv$instance i , gv$active_services s , gv$servicemetric_history m WHERE s.inst_id = m.inst_id AND s.name_hash = m.service_name_hash

AND i.inst_id = m.inst_id

AND m.group_id = 10 and service_name='swing.tobi.com'

ORDER BY

service_name

, i.inst_id

, begin_time ;

Conclusion:

The result shows that throughput is reduced to half, when compared with first query above when one instance (server) is hosting the swing.tobi.com service. This is a show of RAC scalability in OLTP environment; you can accept more users without contention by add more nodes/instances. But NOTE “service time” did not reduce, because in an OLTP environment, users database activities are very light (i.e. short transaction that cannot be further broken down).

OLTP service time can be improve by other means e.g. using fast disk for critical datafiles.

At OS Level: sar –q shows that system/nodes load averages is halved when the two instance is enable to receive requested through the swing services.

Tobi.com is strictly an internal private domain name for me  J

Ref & More reading:

1.       Oracle® Real Application Clusters Administration and Deployment Guide
11g Release 2 (11.2)

2.       http://dominicgiles.com