Before to begin this work binaries for ASM should be installed and necessary diskgroups should be created to accommodate the space on new storage.
Note : Make sure that your diskgroup size (for Datafiles) should be grater than the total database size. Below are the steps needs to be follow for this migration.
Connect to database and check the datafile location,controlfile name, logfile members and current log sequence#
[oracle@asm ~]$ sqldba SQL*Plus: Release 11.2.0.3.0 Production on Tue May 29 08:04:25 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$datafile; NAME ----------------------------------------------------------------- /u01/app/oracle/oradata/abcd/system01.dbf /u01/app/oracle/oradata/abcd/sysaux01.dbf /u01/app/oracle/oradata/abcd/undotbs01.dbf /u01/app/oracle/oradata/abcd/users01.dbf /u01/app/oracle/oradata/abcd/example01.dbf SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/oradata/arch_dest Oldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3 SQL> select * from v$Log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS ---------- ---------- ---------- ---------- ---------- ---------- 1 1 1 52428800 512 1 YES INACTIVE 2 1 2 52428800 512 1 YES INACTIVE 3 1 3 52428800 512 1 NO CURRENT SQL> select name from v$controlfile; NAME ----------------------------------------------------------------- /u01/app/oracle/oradata/abcd/control01.ctl /u01/app/oracle/oradata/abcd/control02.ctl SQL> show parameter db_create_file_des NAME TYPE VALUE ------------------------------------ ----------- ---------------- db_create_file_dest stringChange the CONTROL_FILES and DB_CREATE_FILE_DEST parameters to point ASM diskgroups.
SQL> ALTER SYSTEM SET control_files='+DATAFILE','+ARCHLOG' scope=spfile; System altered. SQL> ALTER SYSTEM SET db_create_file_dest='+DATAFILE' SCOPE=spfile; System altered. SQL> create pfile='/tmp/pfile_abcd.ora' from spfile File created.Need to make sure that we have sufficient space on those diskgroups to hold existing database.
Before to create ASM Diskgroups we should have done Capacity Planing. According to that panning diskgroups should be created otherwise during the conversion time it might get fail saying diskgroup space exhausted!
Now take a copy of the whole database into new diskgroup format.
RMAN> backup as copy database format '+DATAFILE'; Starting backup at 29-MAY-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=135 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/abcd/system01.dbf output file name=+DATAFILE/abcd/datafile/system.270.784541581 tag=TAG20120529T081300 RECID=2 STAMP=784541600 As you can see here, All files from filesystem are getting pushed into ASM storage. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/abcd/sysaux01.dbf output file name=+DATAFILE/abcd/datafile/sysaux.268.784541607 tag=TAG20120529T081300 RECID=3 STAMP=784541622 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/abcd/example01.dbf output file name=+DATAFILE/abcd/datafile/example.265.784541631 tag=TAG20120529T081300 RECID=4 STAMP=784541637 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/abcd/undotbs01.dbf output file name=+DATAFILE/abcd/datafile/undotbs1.264.784541639 tag=TAG20120529T081300 RECID=5 STAMP=784541639 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/abcd/users01.dbf output file name=+DATAFILE/abcd/datafile/users.269.784541641 tag=TAG20120529T081300 RECID=6 STAMP=784541641 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 29-MAY-12
As you can see the above output, while taking copy of the database, each datafile copies are getting restored into ASM diskgroup.
Note : This can be done before to begin the outage, so that we can reduce the outage number of outage hours to minutes
After the migration if you want to open the database without RESETLOGS then no need to take backup of controlfile.
Need to bounce the database now.
While starting up the db place the instance in NOMOUNT state
RMAN> shutdown immediate database closed database dismounted Oracle instance shut down RMAN> startup nomount connected to target database (not started) Oracle instance started Total System Global Area 521936896 bytes Fixed Size 2229944 bytes Variable Size 159385928 bytes Database Buffers 352321536 bytes Redo Buffers 7999488 bytes
Now restore the controlfiles from any one of the multiplexed location
RMAN>restore controlfile from '/u01/app/oracle/oradata/abcd/control01.ctl'; Starting restore at 29-MAY-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=135 device type=DISK channel ORA_DISK_1: copied control file copy output file name=+DATAFILE/abcd/controlfile/current.256.784541557 output file name=+ARCHLOG/abcd/controlfile/current.256.784541557 Finished restore at 29-MAY-12 Here you could see Controlfiles are restored into ASM instance RMAN> sql 'alter database mount'; sql statement: alter database mount released channel: ORA_DISK_1As you notice here, during the restoration process control files are restoring into ASM diskgroups based on the location specified in parameter file.
At this time a copy of each datafile got created on ASM diskgroup so we need to update the controlfile with the new location of each datafile. To achieve this we can switch the entire database to copy (image copy) and recover the database.
RMAN> switch database to copy; datafile 1 switched to datafile copy "+DATAFILE/abcd/datafile/system.270.784541581" datafile 2 switched to datafile copy "+DATAFILE/abcd/datafile/sysaux.268.784541607" datafile 3 switched to datafile copy "+DATAFILE/abcd/datafile/undotbs1.264.784541639" datafile 4 switched to datafile copy "+DATAFILE/abcd/datafile/users.269.784541641" datafile 5 switched to datafile copy "+DATAFILE/abcd/datafile/example.265.784541631" RMAN> recover database; Starting recover at 29-MAY-12 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/abcd/redo03.log archived log file name=/u01/app/oracle/oradata/abcd/redo03.log thread=1 sequence=3 media recovery complete, elapsed time: 00:00:00 Finished recover at 29-MAY-12Now we can open the database WITHOUT RESETLOGS.
RMAN> sql 'alter database open'; sql statement: alter database openNow connect to the database and verify the datafile locations.
[oracle@asm ~]$ sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.3.0 Production on Tue May 29 08:15:13 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- ABCD READ WRITE SQL> select name from v$datafile; NAME ----------------------------------------------------------------- +DATAFILE/abcd/datafile/system.270.784541581 +DATAFILE/abcd/datafile/sysaux.268.784541607 +DATAFILE/abcd/datafile/undotbs1.264.784541639 +DATAFILE/abcd/datafile/users.269.784541641 +DATAFILE/abcd/datafile/example.265.784541631Now all datafiles has been migrated into ASM storage! Till now we migrated only Datafiles. but we have to migrate tempfiles and online Redo logs too. for temporary files we can add it manually.
SQL> select member from v$logfile; MEMBER -------------------------------------------------- /u01/app/oracle/oradata/abcd/redo03.log /u01/app/oracle/oradata/abcd/redo02.log /u01/app/oracle/oradata/abcd/redo01.logMigrating redo logs into ASM is simple way, just add more redolog groups and then drop existing redolog groups which are in Filesystem. Before to add redolog groups make sure you set DB_CREATE_ONLINE_LOG_DEST_n parameter and it should point to ASM diskgroups.
SQL> show parameter db_create_online_log_dest NAME TYPE VALUE ------------------------------------ ---------- ------ db_create_online_log_dest_1 string +LOG1 db_create_online_log_dest_2 string +LOG2 SQL> alter database add logfile group 4; Database altered. SQL> alter database add logfile group 5; Database altered. SQL> alter database add logfile group 6; Database altered. SQL> alter database drop logfile group 2; Database altered. SQL> alter database drop logfile group 3; Database altered. SQL> alter system switch logfile; System altered. SQL> alter system checkpoint; System altered. Manually switching logfiles because Current Group can not be dropped. SQL> select * from v$LOg; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 1 52428800 512 1 YES INACTIVE 1046676 29-MAY-12 1047525 29-MAY-12 4 1 2 104857600 512 1 NO CURRENT 1047525 29-MAY-12 2.8147E+14 5 1 0 104857600 512 1 YES UNUSED 0 0 6 1 0 104857600 512 1 YES UNUSED 0 0 SQL> alter database drop logfile group 1; Database altered. SQL> select * from v$Log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 4 1 2 104857600 512 1 NO CURRENT 1047525 29-MAY-12 2.8147E+14 5 1 0 104857600 512 1 YES UNUSED 0 0 6 1 0 104857600 512 1 YES UNUSED 0 0 SQL> select member from v$Logfile; MEMBER --------------------------------------------------- +DATAFILE/abcd/onlinelog/group_4.266.784542027 +DATAFILE/abcd/onlinelog/group_5.267.784542035 +DATAFILE/abcd/onlinelog/group_6.271.784542041
Now the entire database got migrated to ASM storage and opened the database WITHOUT RESETLOGS!!
I Hope this article helped to you. I am expecting your suggestions/feedback.
