Convert ASM to Non-ASM File system

Steps:-
1>
 /u01/oracle>export ORACLE_SID=PROD
/u01/oracle>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 06 13:09:07 2011
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.

2> 

Go to init file or spfile , then modify the parameters mentioned above.
/u01/oracle/product/10.2.0/db_1>
/u01/oracle/product/10.2.0/db_1/database>vi initprod.ora
Eg: control_files='/U01/ORACLE/PRODUCT/10.2.0/ORADATA/PROD/CONTROL01.CTL','/U01/ORACLE/PRODUCT/10.2.0/ORADATA/PROD/CONTROL02.CTL’,’/U01/ORACLE/PRODUCT/10.2.0/ORADATA/PROD/CONTROL03.CTL'

3>

Accordingly change the other paths ASM DISK GROUP to normal file system.

u01/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='/u01/oracle/product/10.2.0/db_1/database/initPROD.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

4>

Open a new session and connect to RMAN and then copy the controlfile from older location to non-ASM location using the following command:

/u01/oracle/rman>export ORACLE_SID=PROD

/u01/oracle/rman>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 06 13:27:40 2011

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

connected to target database: prod (not mounted)

RMAN> restore controlfile from '+data/control01.ctl';

Starting restore at 06-MAY-11

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=/u01/oracle/product/10.2.0/oradata/PROD/CONTROL01.CTL

output filename=/u01/oracle/product/10.2.0/oradata/PROD/CONTROL02.CTL

output filename=/u01/oracle/product/10.2.0/oradata/PROD/CONTROL03.CTL

Finished restore at 06-MAY-11

5>

Now mount the database from existing RMAN session

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

6>

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 '/u01/oracle/product/10.2.0/oradata/prod/%U.dbf';

Starting backup at 06-MAY-11

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/prod/datafile/system.259.711293871

output filename=/u01/oracle/product/10.2.0/oradata/PROD/DATA_D-PROD_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/prod/datafile/sysaux.260.711293917

output filename=/u01/oracle/product/10.2.0/oradata/PROD/DATA_D-PROD_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/prod/datafile/undotbs1.261.711293941

output filename=/u01/oracle/product/10.2.0/oradata/PROD/DATA_D-PROD_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/prod/datafile/users.262.711293945

output filename=/u01/oracle/product/10.2.0/oradata/PROD/DATA_D-PROD_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=/u01/oracle/product/10.2.0/oradata/PROD/CF_D-PROD_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 06-MAY-11

7>

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/u01/oracle/product/10.2.0/oradata/PROD/DATA_D-PROD_I-2256105880_TS-SYSTEM_FNO-1_08L6C2

2K.DBF"

datafile 2 switched to datafile copy "/u01/oracle/product/10.2.0/oradata/PROD/DATA_D-PROD_I-2256105880_TS-UNDOTBS1_FNO-2_0AL6

C24R.DBF"

datafile 3 switched to datafile copy "/u01/oracle/product/10.2.0/oradata/PROD/DATA_D-PROD_I-2256105880_TS-SYSAUX_FNO-3_09L6C2

42.DBF"

datafile 4 switched to datafile copy "/u01/oracle/product/10.2.0/oradata/PROD/DATA_D-PROD_I-2256105880_TS-USERS_FNO-4_0BL6C24

U.DBF"

8>

Switch the tempfiles from ASM to non-ASM ,

RMAN> run{

2> set newname for tempfile 1 to '/u01/oracle/product/10.2.0/oradata/PROD/tempfile';

3> switch tempfile all;

4> }

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oracle/product/10.2.0/oradata/PROD/tempfile in control file

9>

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 redo log files in the non-ASM file system and drop the existing ASM ONLINE LOG FILES:

SQL> select group#,member from v$logfile;

GROUP# MEMBER

---------- --------------------------------------------------

3 +DATA/prod/onlinelog/group_3.265.711296273

2 +DATA/prod/onlinelog/group_2.266.711296283

1 +DATA/prod/onlinelog/group_1.265.711296273

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/oracle/product/10.2.0/oradata/PROD/REDO04.LOG');

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/oracle/product/10.2.0/oradata/PROD/REDO05.LOG');

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/oracle/product/10.2.0/oradata/PROD/REDO06.LOG');

Database altered.

SQL> select group#,member from v$logfile;

GROUP# MEMBER

---------- --------------------------------------------------

6 /u01/oracle/product/10.2.0/oradata/PROD/REDO06.LOG

5 /u01/oracle/product/10.2.0/oradata/PROD/REDO05.LOG

4 /u01/oracle/product/10.2.0/oradata/PROD/REDO04.LOG

1 +DATA/prod/onlinelog/group_1.265.711296273

2 +DATA/prod/onlinelog/group_2.266.711296283

3 +DATA/prod/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 /u01/oracle/product/10.2.0/oradata/PROD/REDO06.LOG

5 /u01/oracle/product/10.2.0/oradata/PROD/REDO05.LOG

4 /u01/oracle/product/10.2.0/oradata/PROD/REDO04.LOG