Non-ASM to ASM Database Migration

Last week, I was searching of some ASM contents on google and came across this great ASM post on "Shafiulla Oracle DBA Blog".

Although, Conceptual Details on ASM were hardly touched, but the post was pretty straight-forward and a direct technical approach. Any DBA can use content to get their hands on 10g ASM. And, so I thought of working on the contents in my test 10g environment. And within a matter of 2 hours, I was able to migrate my Non-ASM Database to ASM Database on Windows.

Here's a brief outline on the whole content:

1. Creation of ASM Disks using "asmtool", spaced enough to have your Non-ASM Database fit in.
   asmtool -create disk01 2048
2. Creation of OCR Repository and CSS Service, using "localconfig"
   localconfig add
3. Creation of ASM Instance using "DBCA", as first step.
   Here you will have to set the following paramters like, ASM_DISKGROUP, ASM_DISKSTRING, LOCK_NAME_SPACE, etc.
4. Creation of ASM Disk Groups and Addition od ASM Disks (as Candidates), using "DBCA", assecond step.
   Ensuring _ASM_ALLOW_ONLY_RAW_DISKS is set to FALSE, to successfully add ASM Disks.
   alter system "_ASM_ALLOW_ONLY_RAW_DISKS"=false scope=spfile;
5. Ensure the Availability of the Instance:
   net start oracleasmservice+asm
6. Checking the Assignments of the ASM Disks
   show parameter asm
   At this point the ASM Instance is up with mounted ASM Disk Groups.
7. Change the following parameters (control_files, db_create_file_dest, db_create_online_log_dest_n) and startup the Non-ASM Database in NOMOUNT state.
   control_files='+DATA\control01.ctl','+DATA\control02.ctl','+DATA\control03.ctl'
8. In another session, connect as target to Non-ASM Database, using "rman"
9. Restore the control file from original location to ASM Disk, and mount the database, under "rman" session:
   restore controlfile from 'C:\oracle\asmdb\control01.ctl';
10. Take a Copy Backup of the Non-ASM Database in ASM Disk space, under "rman" session.
    backup as copy database format '+DATA';
11. Switch the Database and Tempfiles to ASM Disk having the copy backup.
    switch database to copy;
    run{set newname for tempfile 1 to '+DATA/asmdb/tempfile'; switch tempfile all;}
12. Now, open the database.
13. Add redo groups to ASM Disk, and drop the redo groups from non-ASM location
    alter database add logfile group 4 ('+data');

That's it!!! Your Non-ASM Database has just now become an ASM Database. For detailed steps on how to achieve this, please have a walkthough on the following posts. :

• Migration(Convertion) of Database from non-ASM to ASM (Part - 01)
• Migration of Database from non-ASM to ASM (Part - 02)

I am sure you will enjoy it!!!

Successful Migration on New Server

On the 20th Feb 2010, we successfully migrated our entire system onto the new server, and the migrated the 9iR2 Production Database to 10gR2 Database (10.2.0.4), and the system were made available for the end users.

Just a little bit about the server configuration:

IBM x3650 Server, Xeon Quad Core E5430 2.66 GHz, with 16 GB RAM, having around 500 GB storage after RAID configurations.

The Testing took more time than planned, but it paid off really well. We tested the entire ERP Application and Database, in terms of Data Validation and Performance. And the test results were impressive. And from 21st Feb, users were online.

In the past 15 days, we carried out the SGA optimization , encountered an Oracle Bug (will discuss its workaround in separate post), Enterprise Manager was setup, RMAN Backups and other jobs were configured (same as it was before the migration), Clone Server were setup for periodic cloning, and the DEV and TEST Environments were also refreshed.

As of now, I am enjoying the 10g Database on a powerful server. We have also recieved good feedback from a couple of users, although formal feedback has not been collected yet. With a couple of weeks of more support, I am eyeing at 11g testing and upgrade preparations...

RMAN Compressed Backups

RMAN Backups in Oracle Database 10g Standard Edition enables us to take compressed backups. "AS COMPRESSED BACKUPSET" is the addition in the default backup command:

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Well, that should be good, so I put the backup command at test for my newly migrated 10g Database. Here we go...

My Normal RMAN Backup takes an average of 30 minutes to complete, generating around 20.5 GB of Backupset for a 40GB Database.
So, I am expecting anything close to 40-50% compression (backupset size from 10-12 GB) after using the new command, and I am expecting the job to take more than 30 minutes to finish (say between 1-1:30 hrs).

And yet again, Oracle makes my day, today!!! I not only finish the job in less than 30 minutes, but guess what!, The compression is magnificent. The job finished at an average of 14 mins (superb) and the compression is almost 85-90%, i.e. backupset size was 2.5 GB (Mind Blowing). Talk about the Performance Gains and Storage Save.

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
Starting backup at
06-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
.
.
.
input datafile fno=00001 name=E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=E:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00002 name=E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00004 name=E:\ORACLE\ORADATA\ORCL\USERS01.DBF
input datafile fno=00005 name=E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
channel ORA_DISK_1: starting piece 1 at 06-MAR-10
channel ORA_DISK_1: finished piece 1 at 06-MAR-10
piece handle=E:\ORACLE\BACKUP\RMAN\ORCL\ORCL_KTL7T94E_1_1_20100306
tag=TAG20100306T153854 comment=NONE
channel ORA_DISK_1: starting piece 2 at 06-MAR-10
channel ORA_DISK_1: finished piece 2 at 06-MAR-10
piece handle=E:\ORACLE\BACKUP\RMAN\ORCL\ORCL_KTL7T94E_2_1_20100306
tag=TAG20100306T153854 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:14:01
Finished backup at 06-MAR-10
Starting Control File and SPFILE Autobackup at 06-MAR-10
piece handle=\\BLADE5\MIS\BACKUP\RMAN\ORCL\C-1235707396-20100306-16 comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAR-10

RMAN> BACKUP AS COMPRESSED BACKUPSET archivelog all;
Starting backup at 06-MAR-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1120 recid=1107 stamp=712943759
channel ORA_DISK_1: starting piece 1 at 06-MAR-10
channel ORA_DISK_1: finished piece 1 at 06-MAR-10
piece handle=E:\ORACLE\BACKUP\RMAN\ORCL\ORCL_KVL7TA4H_1_1_20100306
tag=TAG20100306T155601 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 06-MAR-10
Starting Control File and SPFILE Autobackup at 06-MAR-10
piece handle=\\BLADE5\MIS\BACKUP\RMAN\ORCL\C-1235707396-20100306-17 comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAR-10