Zahid Karim
Recent Posts
1.Draw plus and cross sign into PDF via PLSQL using PL_FPDF
2.Draw Triangle directly into PDF via PLSQL using PL_FPDF
3.Write fast and efficient PLSQL - DBMS_PROFILER
4.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
5.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
Find me on
Create Virtual Machine using VMWare Migrate Oracle Flash Recovery Area to ASM storage via RMAN

How to migrate online redo logs to Oracle ASM storage

Posted by Zahid on August 28, 2009.

This tutorial is about migrating your online redo log files to ASM storage. It assumes that you already have a database up and running and have installed ASM instance to use with it. Your database was created on file system and you want to migrate it to ASM you have just installed.

NOTE: This tutorial was performed on Oracle 10g hosted on RHEL 5.

$ sqlplus / as sysdba /* In this method we drop and re-create the redo logs on the ASM storage. */ SQL> alter system set db_create_online_log_dest_1='+DATA' scope=spfile; System altered. /* This parameter defines the default location number 1 for online logs i.e. if you don't specify a location for the log file while creating it it will be created on the location defined in this parameter. */ SQL>select l.group# , l.bytes , l.status , lf.member 2 from v$logfile lf , v$log l 3 where lf.group# = l.group#; GROUP# BYTES STATUS MEMBER ------ -------- ------- ------------------------------------------ 2 52428800 UNUSED /u01/apps/oracle/oradata/ora10g/redo02.log 3 52428800 UNUSED /u01/apps/oracle/oradata/ora10g/redo03.log 1 52428800 CURRENT /u01/apps/oracle/oradata/ora10g/redo01.log /* Here in this case log group 2 and 3 are yet UNUSED, so its safe to drop them and re-create them on ASM. This database is running in NOARCHIVELOG mode so its okay to drop any group which is check pointed or UNUSED. IF you are on an ARCHIVELOG enabled database then make sure any group you delete is archived i.e. SELECT group# , archived from v$log; and archived should be YES. */ SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 size 52428800; Database altered. /* This magic works because of db_create_online_log_dest_1 parameter. Log group 3 created on ASM. No need of mentioning the file name. */ SQL> alter database drop logfile group 2; Database altered. SQL> alter database add logfile group 2 size 52428800; Database altered. SQL> select l.group# , l.bytes , l.status , l.archived , lf.member 2 from v$logfile lf , v$log l 3 where lf.group# = l.group#; GROUP# BYTES STATUS MEMBER ------ -------- ------- -------------------------------------------- 2 52428800 UNUSED +DATA/ora10g/onlinelog/group_2.266.716316205 3 52428800 UNUSED +DATA/ora10g/onlinelog/group_3.264.716316181 1 52428800 CURRENT /u01/apps/oracle/oradata/ora10g/redo01.log /* Both log groups 2 and 3 are shifted to ASM diskgroup DATA. Log group 1 is current so cannot be dropped. */ SQL> alter system switch logfile; System altered. SQL> select l.group# , l.bytes , l.status , l.archived , lf.member 2 from v$logfile lf , v$log l 3 where lf.group# = l.group#; GROUP# BYTES STATUS MEMBER ------ -------- ------- -------------------------------------------- 2 52428800 CURRENT +DATA/ora10g/onlinelog/group_2.266.716316205 3 52428800 UNUSED +DATA/ora10g/onlinelog/group_3.264.716316181 1 52428800 ACTIVE /u01/apps/oracle/oradata/ora10g/redo01.log /* Log switched and group 1 is no more CURRENT but it is still active. ACTIVE means the group is still required for crash recovery and hasn't yet check pointed. An active log group can't be deleted either. */ SQL> alter system checkpoint; System altered. SQL> select l.group# , l.bytes , l.status , l.archived , lf.member 2 from v$logfile lf , v$log l 3 where lf.group# = l.group#; GROUP# BYTES STATUS MEMBER ------ -------- -------- -------------------------------------------- 2 52428800 CURRENT +DATA/ora10g/onlinelog/group_2.266.716316205 3 52428800 UNUSED +DATA/ora10g/onlinelog/group_3.264.716316181 1 52428800 INACTIVE /u01/apps/oracle/oradata/ora10g/redo01.log /* We issued a manual check point and now log group 1 is INACTIVE i.e. no more required for crash recovery */ SQL> alter database drop logfile group 1; Database altered. SQL> alter database add logfile group 1 size 52428800; Database altered. SQL> select l.group# , l.bytes , l.status , lf.member 2 from v$logfile lf , v$log l 3 where lf.group# = l.group#; GROUP# BYTES STATUS MEMBER ------ -------- ------- -------------------------------------------- 2 52428800 CURRENT +DATA/ora10g/onlinelog/group_2.266.716316205 3 52428800 UNUSED +DATA/ora10g/onlinelog/group_3.264.716316181 1 52428800 UNUSED +DATA/ora10g/onlinelog/group_1.265.716316273 SQL>

We have all our online redo logs on ASM diskgroup +DATA.

See also:
How to install ASM instance on a non-asm Oracle database
How to configure Raw Disks in RHEL 5
How to migrate Oracle datafiles to ASM storage via RMAN
How to Configure ASM disks using ASMLib or Raw Disks
Migrate Oracle Flash Recovery Area to ASM storage via RMAN
Add ASM Instance into Enterprise Manager