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

Following procedure need to be followed for migrating a non-ASM database to ASM.

Now shutdown the non-ASM database and change the parameters control_files,db_create_file_dest,db_create_online_log_dest_1{2/3/4…}
C:\>set oracle_sid=CATDB
C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 13:09:07 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Go to init file or spfile , then modify the parameters mentioned above.
C:\oracle\product\10.2.0\db_1\database>notepad initcatdb.ora
Eg: control_files='+DATA\control01.ctl','+DATA\control02.ctl','+DATA\control03.ctl'
Accordingly change the other paths to ASM DISK GROUP.
C:\oracle\product\10.2.0\db_1\database>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 18 13:24:33 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE='C:\oracle\product\10.2.0\db_1\database\initCATDB.oraORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 192940932 bytes
Database Buffers 411041792 bytes
Redo Buffers 7135232 bytes

Open a new session and connect to RMAN and then copy the controlfile from older location to ASM location using the following command:
C:\Documents and Settings\Quest>set oracle_sid=catdb
C:\Documents and Settings\Quest>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Feb 18 13:27:40 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: catdb (not mounted)
RMAN> restore controlfile from 'C:\oracle\product\10.2.0\oradata\catdb\control01.ctl';
Starting restore at 18-FEB-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/control01.ctl
output filename=+DATA/control02.ctl
output filename=+DATA/control03.ctl
Finished restore at 18-FEB-10

Now mount the database from existing RMAN session
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

Now take a backup copy into ASM disk group, this copied ASM Database backup will be used as the actual ASM database files .
RMAN> backup as copy database format '+DATA';
Starting backup at 18-FEB-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\SYSTEM01.DBF
output filename=+DATA/catdb/datafile/system.259.711293871 tag=TAG20100218T133750 recid=1 stamp=711293907
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\SYSAUX01.DBF
output filename=+DATA/catdb/datafile/sysaux.260.711293917 tag=TAG20100218T133750 recid=2 stamp=711293936
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\UNDOTBS01.DBF
output filename=+DATA/catdb/datafile/undotbs1.261.711293941 tag=TAG20100218T133750 recid=3 stamp=711293943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\USERS01.DBF
output filename=+DATA/catdb/datafile/users.262.711293945 tag=TAG20100218T133750 recid=4 stamp=711293946
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/catdb/controlfile/backup.263.711293949 tag=TAG20100218T133750 recid=5 stamp=711293950
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-FEB-10
Now we are gonna see the powerful feature of RMAN, we switch the actual non-ASM database to ASM using the below simple but powerful command.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/catdb/datafile/system.259.711293871"
datafile 2 switched to datafile copy "+DATA/catdb/datafile/undotbs1.261.711293941"
datafile 3 switched to datafile copy "+DATA/catdb/datafile/sysaux.260.711293917"
datafile 4 switched to datafile copy "+DATA/catdb/datafile/users.262.711293945"

Switch the tempfiles from non-ASM to ASM ,

RMAN>
run{
2> set newname for tempfile 1 to '+DATA/catdb/tempfile';
3> switch tempfile all;
4> }
executing command: SET NEWNAME
renamed tempfile 1 to +DATA/catdb/tempfile in control file

Now our database is switched from non-ASM to ASM
Now you can can open the database
RMAN> ALTER DATABASE OPEN (RESETLOGS);
database opened.
Now, and create new online logfiles in the ASM and drop the existing non-ASM ONLINE LOG FILES:
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO03.LOG
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO02.LOG
1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO01.LOG

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('+data');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('+data');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('+data');
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO03.LOG
2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO02.LOG
1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO01.LOG
4 +DATA/catdb/onlinelog/group_4.265.711296273
5 +DATA/catdb/onlinelog/group_5.266.711296283
6 +DATA/catdb/onlinelog/group_6.267.711296293

6 rows selected.
Make sure that the non-ASM online redo logfiles are not in current/active state.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ---------------------------------------------
4 +DATA/catdb/onlinelog/group_4.265.711296273
5 +DATA/catdb/onlinelog/group_5.266.711296283
6 +DATA/catdb/onlinelog/group_6.267.711296293

So, at last please comment on this post and critics are most welcome!!