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 |
|
|