Converting ASM database to non-ASM environment

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

Create directories (locations) where you want to put your datafiles,controlfile,and redologfiles.
Now shutdown the 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.
SQL>

Go to init file or spfile , then modify the parameters mentioned above.
C:\oracle\product\10.2.0\db_1>
C:\oracle\product\10.2.0\db_1\database>notepad initcatdb.ora
Eg: control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL’,’C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL'

Accordingly change the other paths ASM DISK GROUP to normal file system.
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.ora
ORACLE 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 non-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 '+data\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=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\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 a file system location, this copied file system Database backup will be used as the actual non-ASM database files .
RMAN> backup as copy database format 'c:\oracle\product\10.2.0\oradata\catdb\%U.dbf';
Starting backup at 18-FEB-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DATA/catdb/datafile/system.259.711293871
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSTEM_FNO-1_08L6C22K.DBF tag=TAG20100218
T233755 recid=11 stamp=711329913
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DATA/catdb/datafile/sysaux.260.711293917
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSAUX_FNO-3_09L6C242.DBF tag=TAG20100218
T233755 recid=12 stamp=711329942
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DATA/catdb/datafile/undotbs1.261.711293941
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-UNDOTBS1_FNO-2_0AL6C24R.DBF tag=TAG201002
18T233755 recid=13 stamp=711329949
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DATA/catdb/datafile/users.262.711293945
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-USERS_FNO-4_0BL6C24U.DBF tag=TAG20100218T
233755 recid=14 stamp=711329952
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=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\CF_D-CATDB_ID-2256105880_0CL6C252.DBF tag=TAG20100218T233755 recid=15
stamp=711329955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-FEB-10

We switch the actual ASM database to non-ASM using the below simple but powerful command.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSTEM_FNO-1_08L6C2
2K.DBF"
datafile 2 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-UNDOTBS1_FNO-2_0AL6
C24R.DBF"
datafile 3 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-SYSAUX_FNO-3_09L6C2
42.DBF"
datafile 4 switched to datafile copy "C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATA_D-CATDB_I-2256105880_TS-USERS_FNO-4_0BL6C24
U.DBF"

Switch the tempfiles from non-ASM to ASM ,
RMAN> run{
2> set newname for tempfile 1 to 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB/tempfile';
3> switch tempfile all;
4> }
executing command: SET NEWNAME
renamed tempfile 1 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB/tempfile in control file


Now our database is switched from ASM to non- ASM
Now you can open the database
RMAN> ALTER DATABASE OPEN (RESETLOGS);
database opened

Now, and create new in the non-ASM and drop the existing ASM ONLINE LOG FILES:
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 +DATA/catdb/onlinelog/group_3.265.711296273
2 +DATA/catdb/onlinelog/group_2.266.711296283
1 +DATA/catdb/onlinelog/group_1.265.711296273

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO04.LOG');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO05.LOG');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO06.LOG');
Database altered.

SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
6 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO06.LOG
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO05.LOG
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO04.LOG
1 +DATA/catdb/onlinelog/group_1.265.711296273
2 +DATA/catdb/onlinelog/group_2.266.711296283
3 +DATA/catdb/onlinelog/group_3.267.711296293
6 rows selected.

Make sure that the 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
---------- ---------------------------------------------
6 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO06.LOG
5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO05.LOG
4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\REDO04.LOG




********************************************************************************