Wednesday, July 13, 2011

Migrate ASM to NON-ASM in oracle 10g

Migrating a database back from ASM storage to non-ASM storage is similar to the original migration from NON-ASM to ASM database. We can  migrate from ASM to non-ASM storage similar to that of the NON-ASM to ASM . Here are the steps to migrate from ASM to NON-ASM.

Step 1 :  Take the RMAN Full Backup of Database (not mandatory).

Step 2 : Start  database with ASM  

Step 3 :  Create  pfile  from  spfile 

C:\>sqlplus sys/xxxx@noida as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 7 16:09:45 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> select name,open_mode from v$database;
NAME           OPEN_MODE
---------      ------------------
NOIDA        READ WRITE

SQL> sho parameter spfile;
NAME                          TYPE                      VALUE
------------------          -----------         ------------------------------
spfile                         string              +RATCAT/noida/spfilenoida.ora

SQL> create pfile='c:\qq.ora' from spfile;
File created.

Step 4 :  Edit pfile Parameter 
Edit the pfile to reflect controlfile name in file system location and the specify the location of udump,adump,cdump in file system location . In my case the changes are as follows : 

control_files='D:\oracle\product\10.2.0\oradata\control01.ctl','D:\oracle\product\10.2.0\oradata\control02.ctl'
core_dump_dest='D:\oracle\product\10.2.0/admin/noida/cdump'
background_dump_dest='D:\oracle\product\10.2.0/admin/noida/bdump'
user_dump_dest='D:\oracle\product\10.2.0/admin/noida/udump'

Step 5 :  Startup database at nomount 

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 7 16:15:41 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup pfile='C:\qq.ora' nomount;
ORACLE instance started.
Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              88081088 bytes
Database Buffers          192937984 bytes
Redo Buffers                7139328 bytes

Step 6 : Use RMAN to copy the control file from asm to non-asm 

SQL> host rman target sys/xxxx@noida
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 7 16:23:53 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: noida (not mounted)

RMAN> restore controlfile from  '+RATCAT/noida/controlfile/current.260.755871509' ;

Starting restore at 07-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL02.CTL
Finished restore at 07-JUL-11

Step 7  :  Mount the Database 

RMAN> alter database mount ; 
database mounted
released channel: ORA_DISK_1

Step 8 :  Use RMAN to copy the database from ASM to NON-ASM.

RMAN> backup  as  copy  database  format  'D:\oracle\product\10.2.0\oradata\%U' ; 

Starting backup at 07-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+RATCAT/noida/datafile/system.256.755871363
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSTEM_FNO-1_05MGRQAR 

tag=TAG20110707T162707 recid=2 stamp=755886461
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+RATCAT/noida/datafile/sysaux.257.755871363
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSAUX_FNO-3_06MGRQBU 

tag=TAG20110707T162707 recid=3 stamp=755886480
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+RATCAT/noida/datafile/example.265.755871581
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-EXAMPLE_FNO-5_07MGRQCN 

tag=TAG20110707T162707 recid=4 stamp=755886495
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+RATCAT/noida/datafile/undotbs1.258.755871365
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-UNDOTBS1_FNO-2_08MGRQD7 

tag=TAG20110707T162707 recid=5 stamp=755886506
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+RATCAT/noida/datafile/users.259.755871365
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-USERS_FNO-4_09MGRQDA 

tag=TAG20110707T162707 recid=6 stamp=755886507
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
outputfilename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\CF_D-NOIDA_ID-1509813972_0AMGRQDB tag=TAG20110707T162707 

recid=7 stamp=755886508
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-JUL-11

Step 9 :  Update the controlfile 
Switch Database to specify that a datafile copy is now the current datafile, that is, the datafile pointed to by the control file.

RMAN> switch database to copy ; 

datafile 1 switched to datafile copy 
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSTEM_FNO-1_05MGRQAR"
datafile 2 switched to datafile copy 

"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-UNDOTBS1_FNO-2_08MGRQD7"
datafile 3 switched to datafile copy 

"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSAUX_FNO-3_06MGRQBU"
datafile 4 switched to datafile copy 

"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-USERS_FNO-4_09MGRQDA"
datafile 5 switched to datafile copy 

"D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-EXAMPLE_FNO-5_07MGRQCN"

RMAN>exit 

SQL> select name,open_mode from v$database;
NAME         OPEN_MODE
---------      -----------------
NOIDA        MOUNTED

SQL> alter database open;
Database altered.

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL01.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL02.CTL

Step 10 :  Migrate redo-log file to file system location. 

SQL> select member from v$logfile;
MEMBER
------------------------------------------------------
+RATCAT/noida/onlinelog/group_3.263.755871523
+SATMAT/noida/onlinelog/group_3.259.755871525
+RATCAT/noida/onlinelog/group_2.262.755871519
+SATMAT/noida/onlinelog/group_2.258.755871521
+RATCAT/noida/onlinelog/group_1.261.755871513
+SATMAT/noida/onlinelog/group_1.257.755871515

6 rows selected.

SQL> select group#,sequence#,members,archived,status from v$log;
    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          2          2 YES INACTIVE
         2          3          2 YES INACTIVE
         3          4          2 NO  CURRENT

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database add  logfile group 1 'D:\oracle\product\10.2.0\oradata\redo01.redo' size  50M;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database add logfile group 2  'D:\oracle\product\10.2.0\oradata\redo02.redo'  size  50M;
Database altered.

SQL> select member from v$logfile;
MEMBER
--------------------
+SATMAT/noida/onlinelog/group_3.259.755871525
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO02.REDO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO01.REDO

SQL> select group#,sequence#,members,archived,status from v$log;
    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          5          1 NO  CURRENT
         2          0          1 YES UNUSED
         3          4          2 YES ACTIVE

SQL> alter system switch logfile ;
System altered.

SQL> alter database drop logfile group 3; 
ALTER DATABASE DROP LOGFILE GROUP 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance noida (thread 1)
ORA-00312: online log 3 thread 1: '+RATCAT/noida/onlinelog/group_3.263.755871523'
ORA-00312: online log 3 thread 1: '+SATMAT/noida/onlinelog/group_3.259.755871525'

SQL> alter database clear unarchived logfile group 3;
Database altered.

SQL>alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile group 3  'D:\oracle\product\10.2.0\oradata\redo03.redo'  size  50M;
Database altered.

SQL> select member from v$logfile;
MEMBER
--------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO03.REDO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO02.REDO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO01.REDO

Step 11 :  Recreate the tempfile

SQL> select file_name,tablespace_name from dba_temp_files;
FILE_NAME                                                            TABLESPACE_NAME
-------------------------------------                               -------------------
+RATCAT/noida/tempfile/temp.264.755871559          TEMP

SQL> alter tablespace temp add tempfile 'D:\oracle\product\10.2.0\oradata\temp01.dbf' size 200m;
Tablespace altered.

SQL> alter tablespace temp drop tempfile  '+RATCAT/noida/tempfile/temp.264.755871559';
Tablespace altered.

SQL> select file_name,tablespace_name from dba_temp_files;
FILE_NAME                                                                        TABLESPACE_NAME
--------------------                                                               ------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEMP01.DBF              TEMP

Step 12 :  Recreate spfile 

SQL> sho parameter spfile
NAME                         TYPE                      VALUE
--------------             -----------     ------------------------------
spfile                          string

SQL> create spfile from pfile='C:\qq.ora';
File created.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              88081088 bytes
Database Buffers          192937984 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.

Step 13 :  Check the database files 

SQL> sho parameter spfile
NAME          TYPE                                                       VALUE
---------      ---------     --------------------------------------------------------------------------------
spfile          string           D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILENOIDA.ORA

SQL> select file_name from dba_data_files;

FILE_NAME
----------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-USERS_FNO-4_09MGRQDA
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSAUX_FNO-3_06MGRQBU
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-UNDOTBS1_FNO-2_08MGRQD7
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-SYSTEM_FNO-1_05MGRQAR
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_D-NOIDA_I-1509813972_TS-EXAMPLE_FNO-5_07MGRQCN

SQL> select name from v$controlfile;
NAME
-------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL01.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CONTROL02.CTL

SQL> select member from v$logfile;
MEMBER
-----------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO03.REDO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO02.REDO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\REDO01.REDO

Enjoy   :)  :)

4 comments:

sankar natarajan said...

Perfect works like a charm there are so many blogs which bluffs but not this one tested works 100% Great one thank you very much.

sankar natarajan said...

Perfect works like a charm there are so many blogs which bluffs but not this one tested works 100% Great one thank you very much.

sankar natarajan said...

Perfect works like a charm there are so many blogs which bluffs but not this one tested works 100% Great one thank you very much.

Anonymous said...

Nice blog and showcase.