Case study: delete the file system log group and retain the ASM log Group

Case study: delete the file system log group and retain the ASM log Group

Note:
To migrate an oracle File System to ASM, delete the log Group of the file system and retain only the ASM log group. If both are retained, the read/write log performance is unbalanced. Reading and Writing logs to ASM is much faster than reading and writing logs to the file system.

Procedure:
SQL> select * from v $ log;
-- Check that the current log group is 2, 1st, and 3. The log group is INACTIVE and the dirty blocks have been written. You can delete these two log groups.
GROUP # THREAD # SEQUENCE # BYTES MEMBERS ARC STATUS
---------------------------------------------------------------------
FIRST_CHANGE # FIRST_TIM
----------------------
1 1 2 52428800 2 YES INACTIVE
864542 29-MAY-14


2 1 7 52428800 2 NO CURRENT
864576 29-MAY-14


3 1 3 52428800 2 YES INACTIVE
864552 29-MAY-14




GROUP # THREAD # SEQUENCE # BYTES MEMBERS ARC STATUS
---------------------------------------------------------------------
FIRST_CHANGE # FIRST_TIM
----------------------
4 1 4 52428800 1 YES INACTIVE
864565 29-MAY-14


5 1 5 52428800 1 YES INACTIVE
864568 29-MAY-14


6 1 6 52428800 1 YES INACTIVE
864571 29-MAY-14




6 rows selected.


SQL> alter database drop logfile group 1;


Database altered.




SQL> select * from v $ log;


GROUP # THREAD # SEQUENCE # BYTES MEMBERS ARC STATUS
---------------------------------------------------------------------
FIRST_CHANGE # FIRST_TIM
----------------------
2 1 7 52428800 2 NO CURRENT
864576 29-MAY-14


3 1 3 52428800 2 YES INACTIVE
864552 29-MAY-14


4 1 4 52428800 1 YES INACTIVE
864565 29-MAY-14




GROUP # THREAD # SEQUENCE # BYTES MEMBERS ARC STATUS
---------------------------------------------------------------------
FIRST_CHANGE # FIRST_TIM
----------------------
5 1 5 52428800 1 YES INACTIVE
864568 29-MAY-14


6 1 6 52428800 1 YES INACTIVE
864571 29-MAY-14






SQL> col name for a40
SQL> set pagesize 200
SQL> r
1 * select l. group #, l. sequence #, f. member as name, l. status, l. archived from v $ log l, v $ logfile f where f. group # = l. group #


GROUP # SEQUENCE # NAME STATUS
----------------------------------------------------------------------------
ARC
---
3 3/u01/app/oracle/oradata/metro/redo03.log INACTIVE
YES


2 7/u01/app/oracle/oradata/metro/redo02.log CURRENT
NO


4 4 + DATA/metro/onlinelog/group_4.274.848866 INACTIVE
125
YES


2 7/disk1/metro/redofile/redo02a. log CURRENT
NO


3 3/disk1/metro/redofile/redo03a. log INACTIVE
YES


5 5 + DATA/metro/onlinelog/group_5.275.848866 INACTIVE
171
YES


6 6 + DATA/metro/onlinelog/group_6.276.848866 INACTIVE
215
YES




7 rows selected.


SQL> set linesize 200;
SQL> r
1 * select l. group #, l. sequence #, f. member as name, l. status, l. archived from v $ log l, v $ logfile f where f. group # = l. group #


GROUP # SEQUENCE # NAME STATUS ARC
-------------------------------------------------------------------------------
3 3/u01/app/oracle/oradata/metro/redo03.log INACTIVE YES
2 7/u01/app/oracle/oradata/metro/redo02.log CURRENT NO
4 4 + DATA/metro/onlinelog/group_4.274.848866 INACTIVE YES
125


2 7/disk1/metro/redofile/redo02a. log CURRENT NO
3 3/disk1/metro/redofile/redo03a. log INACTIVE YES
5 5 + DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171


6 6 + DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215




7 rows selected.


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


SQL> select l. group #, l. sequence #, f. member as name, l. status, l. archived from v $ log l, v $ logfile f where f. group # = l. group #;


GROUP # SEQUENCE # NAME STATUS ARC
-------------------------------------------------------------------------------
2 7/u01/app/oracle/oradata/metro/redo02.log CURRENT NO
4 4 + DATA/metro/onlinelog/group_4.274.848866 INACTIVE YES
125


2 7/disk1/metro/redofile/redo02a. log CURRENT NO
5 5 + DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171


6 6 + DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215




SQL> alter system switch logfile;
-- Archive and change the current log Group


System altered.


SQL> select l. group #, l. sequence #, f. member as name, l. status, l. archived from v $ log l, v $ logfile f where f. group # = l. group #;
-- The log group is active and dirty blocks are not written. You cannot delete the log Group 2.


GROUP # SEQUENCE # NAME STATUS ARC
-------------------------------------------------------------------------------
2 7/u01/app/oracle/oradata/metro/redo02.log ACTIVE YES
4 8 + DATA/metro/onlinelog/group_4.274.848866 CURRENT NO
125


2 7/disk1/metro/redofile/redo02a. log ACTIVE YES
5 5 + DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171


6 6 + DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215
SQL> r
1 * select l. group #, l. sequence #, f. member as name, l. status, l. archived from v $ log l, v $ logfile f where f. group # = l. group #
-- After a few minutes, check the log status again. Log group 2 has written the dirty blocks, and you can delete the log group.


GROUP # SEQUENCE # NAME STATUS ARC
-------------------------------------------------------------------------------
2 7/u01/app/oracle/oradata/metro/redo02.log INACTIVE YES
4 8 + DATA/metro/onlinelog/group_4.274.848866 CURRENT NO
125


2 7/disk1/metro/redofile/redo02a. log INACTIVE YES
5 5 + DATA/metro/onlinelog/group_5.275.848866 INACTIVE YES
171


6 6 + DATA/metro/onlinelog/group_6.276.848866 INACTIVE YES
215


SQL> alter database drop logfile group 2;
-- Delete log Group 2
Database altered.