Hello Friends,
In this post we will talk about how we can Drop redologs from filesystem and recreate redo logs on ASM Path
So the scenario is :
Database has been migrated from NON ASM to asm and now in this post we will drop and recreate the redologs on ASM
check for the current path of redologs from view v$logfile and v$log
SQL> col MEMBER for a55
SQL> set lines 200
SQL> /
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------- --- ----------
1 ONLINE /oracle/DMZ/origlogA/log_g11m1.dbf NO 0
1 ONLINE /oracle/DMZ/mirrlogA/log_g11m2.dbf NO 0
2 ONLINE /oracle/DMZ/origlogB/log_g12m1.dbf NO 0
2 ONLINE /oracle/DMZ/mirrlogB/log_g12m2.dbf NO 0
3 ONLINE /oracle/DMZ/origlogA/log_g13m1.dbf NO 0
3 ONLINE /oracle/DMZ/mirrlogA/log_g13m2.dbf NO 0
4 ONLINE /oracle/DMZ/origlogB/log_g14m1.dbf NO 0
4 ONLINE /oracle/DMZ/mirrlogB/log_g14m2.dbf NO 0
8 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 1 209715200 512 2 YES INACTIVE 1.0697E+13 30-JUN-23 1.0697E+13 30-JUN-23 0
2 1 2 209715200 512 2 YES INACTIVE 1.0697E+13 30-JUN-23 1.0697E+13 30-JUN-23 0
3 1 3 209715200 512 2 NO CURRENT 1.0697E+13 30-JUN-23 9.2954E+18 0
4 1 0 209715200 512 2 YES UNUSED 0 0 0
Drop the inactive member of redo logs and add the same using the ASM path as below
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 ('+DATA','+RECO') size 200M;
Database altered.
SQL> alter database add logfile group 2 ('+DATA','+RECO') size 200M;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database add logfile group 4 ('+DATA','+RECO') size 200M;
Database altered.
As the redolog member 3 is been used as current , Make a log switch and checkpoint to change the status .
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> alter system checkpoint;
System altered.
Check the status asthe log 3 is now inactive can be deleted . Add standby logs only if standby is being used or you will create a stanby
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 4 209715200 512 2 YES INACTIVE 1.0697E+13 30-JUN-23 1.0697E+13 30-JUN-23 0
2 1 5 209715200 512 2 NO CURRENT 1.0697E+13 30-JUN-23 9.2954E+18 0
3 1 3 209715200 512 2 YES INACTIVE 1.0697E+13 30-JUN-23 1.0697E+13 30-JUN-23 0
4 1 0 209715200 512 2 YES UNUSED 0 0 0
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 ('+DATA','+RECO') size 200M;
Database altered.
Check the status of redo logs now it should be on asm as shown below .
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------- --- ----------
1 ONLINE +DATA/DMZ/ONLINELOG/group_1.824.1140859727 NO 0
1 ONLINE +RECO/DMZ/ONLINELOG/group_1.2542.1140859727 NO 0
2 ONLINE +DATA/DMZ/ONLINELOG/group_2.826.1140859735 NO 0
2 ONLINE +RECO/DMZ/ONLINELOG/group_2.4274.1140859735 NO 0
3 ONLINE +DATA/DMZ/ONLINELOG/group_3.839.1140859809 NO 0
3 ONLINE +RECO/DMZ/ONLINELOG/group_3.1995.1140859809 NO 0
4 ONLINE +DATA/DMZ/ONLINELOG/group_4.823.1140859753 NO 0
4 ONLINE +RECO/DMZ/ONLINELOG/group_4.5305.1140859753 NO 0
8 rows selected.
ConversionConversion EmoticonEmoticon