In this post we will see how we can Drop redologs from Non-ASM file system and recreate them on ASM file system.Database has been migrated from NON ASM to ASM and now in this post we will drop and recreate the redologs on ASM
First we will check the current path of redologs from view v$logfile and v$log
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER CON_ID ---------- ------- ------- ---------------------------------------------------------- 1 ONLINE /oracle/DMZ/origlogA/log_g11m1.dbf 0 1 ONLINE /oracle/DMZ/mirrlogA/log_g11m2.dbf 0 2 ONLINE /oracle/DMZ/origlogB/log_g12m1.dbf 0 2 ONLINE /oracle/DMZ/mirrlogB/log_g12m2.dbf 0 3 ONLINE /oracle/DMZ/origlogA/log_g13m1.dbf 0 3 ONLINE /oracle/DMZ/mirrlogA/log_g13m2.dbf 0 4 ONLINE /oracle/DMZ/origlogB/log_g14m1.dbf 0 4 ONLINE /oracle/DMZ/mirrlogB/log_g14m2.dbf 0 8 rows selected. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- 1 1 1 209715200 512 2 YES INACTIVE 2 1 2 209715200 512 2 YES INACTIVE 3 1 3 209715200 512 2 NO CURRENT 4 1 0 209715200 512 2 YES UNUSED
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 as the log 3 is now inactive can be deleted. Add standby logs only if standby is being used or you will create a standby
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.
