Saturday, August 8, 2009

How we faster the process of converting a non-ASM single-instance database to RAC database with ASM using RCONFIG tool?

I have been given with a challenging task to convert one of our critical production databases, which is of 1 TB (Terabyte) in size, to Oracle 10g RAC with ASM storage option. Even though, there are many methods and tools available to perform this activity, I have preferred to use the RCONFIG tool.

We prepared an input XML file required for RCONFIG tool, and run the RCONFIG utility as follows:

$ cd /oracle/ora102/db_1/assistants/rconfig/sampleXMLs
$ rconfig ConverToRAC.xml
When we start the RCONFIG tool to convert the database to RAC, the RCONFIG tool initially moves all the non-ASM database files to ASM disk files, for this RCONFIG tool internally invokes RMAN utility to backup the target database to the ASM disk groups, eventually the database is converted to RAC using RCONFIG.

The conversion took almost 9 hours to complete the process, because during the conversion RMAN used only one channel per data file to backup to ASM disks. There was no chance of improving the RMAN copy process by allocating more channels in the input XML file, and also Oracle doesn’t recommend doing other changes in the input XML file.

One thing was observed during the RMAN copy that RMAN is using target database control file instead of recovery catalog, and also using the RMAN default preconfigured settings for that database.

To know the RMAN default preconfigured settings for the database:

$ export ORACLE_SID=MYPROD
$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 5 10:21:05 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: MYPROD (DBID=1131234567)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora102/db_1/dbs/snapcf_T24MIG1.f'; # default
Here we see that the PARALLELISM is 1 (default), that’s why the RMAN using only one channel during backing up the non-ASM datafiles to ASM Disk Groups, and were taking 9 hours to complete the backup.

We have changed the PRALLELISM count to 6 (it depends upon number of CPUs you have in the server).

Solution:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 6;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora102/db_1/dbs/snapcf_T24MIG1.f'; # default
After changing the PARALLELISM count to 6, the RMAN has allocated 6 channels and the conversion process has improved greatly and reduced the downtime drastically to 4 Hours 30 minutes.

Following is the extract of rconfig.log file, this file is located under:

$ORACLE_HOME/db_1/cfgtoolslogs/rconfig
............................................................................
............................................................................
............................................................................
[17:17:16:43] Log RMAN Output=RMAN> backup as copy database to destination '+DATA_DG';
[17:17:16:53] Log RMAN Output=Starting backup at 04-AUG-09
[17:17:16:258] Log RMAN Output=using target database control file instead of recovery catalog
[17:17:16:694] Log RMAN Output=allocated channel: ORA_DISK_1
[17:17:16:698] Log RMAN Output=channel ORA_DISK_1: sid=866 devtype=DISK
[17:17:17:9] Log RMAN Output=allocated channel: ORA_DISK_2
[17:17:17:13] Log RMAN Output=channel ORA_DISK_2: sid=865 devtype=DISK
[17:17:17:324] Log RMAN Output=allocated channel: ORA_DISK_3
[17:17:17:327] Log RMAN Output=channel ORA_DISK_3: sid=864 devtype=DISK
[17:17:17:637] Log RMAN Output=allocated channel: ORA_DISK_4
[17:17:17:641] Log RMAN Output=channel ORA_DISK_4: sid=863 devtype=DISK
[17:17:17:967] Log RMAN Output=allocated channel: ORA_DISK_5
[17:17:17:971] Log RMAN Output=channel ORA_DISK_5: sid=862 devtype=DISK
[17:17:18:288] Log RMAN Output=allocated channel: ORA_DISK_6
[17:17:18:293] Log RMAN Output=channel ORA_DISK_6: sid=861 devtype=DISK
[17:17:20:416] Log RMAN Output=channel ORA_DISK_1: starting datafile copy
[17:17:20:427] Log RMAN Output=input datafile fno=00053 name=/oradata/MYPROD/users_01.dbf
[17:17:20:532] Log RMAN Output=channel ORA_DISK_2: starting datafile copy
[17:17:20:544] Log RMAN Output=input datafile fno=00021 name=/oradata/MYPROD/ users_02.dbf
[17:17:20:680] Log RMAN Output=channel ORA_DISK_3: starting datafile copy
[17:17:20:694] Log RMAN Output=input datafile fno=00022 name=/oradata/MYPROD/ users_03.dbf
[17:17:20:786] Log RMAN Output=channel ORA_DISK_4: starting datafile copy
[17:17:20:800] Log RMAN Output=input datafile fno=00023 name=/oradata/MYPROD/ users_04.dbf
[17:17:20:855] Log RMAN Output=channel ORA_DISK_5: starting datafile copy
[17:17:20:868] Log RMAN Output=input datafile fno=00024 name=/oradata/MYPROD/ users_05.dbf
[17:17:20:920] Log RMAN Output=channel ORA_DISK_6: starting datafile copy
[17:17:20:930] Log RMAN Output=input datafile fno=00011 name=/oradata/MYPROD/ users_06.dbf
............................................................................
............................................................................
............................................................................
[21:29:5:518] Log RMAN Output=Finished backup at 04-AUG-09
............................................................................
............................................................................
............................................................................

[21:39:10:723] [NetConfig.startListenerResources:5] started Listeners associated with database MYPROD
[21:39:10:723] [Step.execute:255] STEP Result=Operation Succeeded
[21:39:10:724] [Step.execute:284] Returning result:Operation Succeeded
[21:39:10:724] [RConfigEngine.execute:68] bAsyncJob=false
[21:39:10:725] [RConfigEngine.execute:77] Result= < version="1.1">


<ConvertToRAC>
<Convert>
<Response>
<Result code="0" >
Operation Succeeded
</Result>
</Response>
<ReturnValue type="object">
<Oracle_Home>
/oracle/ora102/db_1
</Oracle_Home>
<SIDList>
<SID>MYPROD1<\SID>
<SID>MYPROD2<\SID>
<\SIDList> </ReturnValue>
</Convert>
</ConvertToRAC></RConfig>

Note: For the sake of look and feel format, the above output has been trimmed neatly. You can also observer that 6 channels were being allocated, timings of backup start and end, and the success code end of the rconfig.log file.

References:

To know more about RCONFIG tool and other Metalink references on it, please take a look at the below blog post written by Mr. Syed Jaffar Hussain.

http://jaffardba.blogspot.com/2008/09/my-experience-of-converting-cross.html

Oracle 10g R2 Documentation information on RCONFIG:

http://download.oracle.com/docs/cd/B19306_01/install.102/b14205/cvrt2rac.htm#BABBAAEH

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/