Administration

Replace and mirror online redo logs

User Rating:

/ 0

Details: Parent Category: Articles; Created on Monday, 21 March 2011 14:44; Last Updated on Wednesday, 04 December 2013 14:03; Published on Monday, 21 March 2011 14:44; Written by Guy Lambregts; Hits: 6792

Replace and mirror online redo logs

Redo log files were created non mirrored, at the wrong place and with the wrong size

09:48:47 PLATINUM_SYSTEM_19/02/2011 10:46>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS ---------- ---------- ---------- ---------- ---------- --- ---------------- FIRST_CHANGE# FIRST_TIME ------------- ---------------- 1 1 1151 52428800 2 YES INACTIVE 238227662 08/03/2011 09:36 2 1 1152 52428800 2 YES INACTIVE 238227665 08/03/2011 09:36 3 1 1153 52428800 2 YES INACTIVE 238227668 08/03/2011 09:36 4 1 1154 52428800 2 YES CURRENT 238227672 08/03/2011 09:37

This is how we add new redo log groups with the correct size

09:49:09 PLATINUM_SYSTEM_19/02/2011 10:46>alter database add logfile group 5 ('D:\ORACLE\PLATINUM\REDO05.LOG') size 200M; Database altered. 09:49:33 PLATINUM_SYSTEM_19/02/2011 10:46>alter database add logfile group 6 ('D:\ORACLE\PLATINUM\REDO06.LOG') size 200M; Database altered. 09:50:09 PLATINUM_SYSTEM_19/02/2011 10:46>alter database add logfile group 7 ('D:\ORACLE\PLATINUM\REDO07.LOG') size 200M; Database altered. 09:50:37 PLATINUM_SYSTEM_19/02/2011 10:46>alter database add logfile group 8 ('D:\ORACLE\PLATINUM\REDO08.LOG') size 200M; Database altered.

This is how we add logfile members to the existing redo log groups

09:51:16 PLATINUM_SYSTEM_19/02/2011 10:46>alter database add logfile member 'E:\ORACLE\PLATINUM\REDO205.LOG' to group 5; Database altered. 09:52:16 PLATINUM_SYSTEM_19/02/2011 10:46>alter database add logfile member 'E:\ORACLE\PLATINUM\REDO206.LOG' to group 6; Database altered. 09:53:03 PLATINUM_SYSTEM_19/02/2011 10:46>alter database add logfile member 'E:\ORACLE\PLATINUM\REDO207.LOG' to group 7; Database altered. 09:53:34 PLATINUM_SYSTEM_19/02/2011 10:46>alter database add logfile member 'E:\ORACLE\PLATINUM\REDO208.LOG' to group 8; Database altered.

This is how we remove the old incorrectly sized and incorrectly placed redo logs

09:54:31 PLATINUM_SYSTEM_19/02/2011 10:46>alter system checkpoint; System altered. 09:55:27 PLATINUM_SYSTEM_19/02/2011 10:46>alter system archive log current; System altered. 09:55:38 PLATINUM_SYSTEM_19/02/2011 10:46>alter system archive log current; System altered. 09:55:43 PLATINUM_SYSTEM_19/02/2011 10:46>alter system archive log current; System altered. 09:55:45 PLATINUM_SYSTEM_19/02/2011 10:46>alter system archive log current; System altered. 09:55:47 PLATINUM_SYSTEM_19/02/2011 10:46>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS ---------- ---------- ---------- ---------- ---------- --- ---------------- FIRST_CHANGE# FIRST_TIME ------------- ---------------- 1 1 1151 52428800 2 YES INACTIVE 238227662 08/03/2011 09:36 2 1 1152 52428800 2 YES INACTIVE 238227665 08/03/2011 09:36 3 1 1153 52428800 2 YES INACTIVE 238227668 08/03/2011 09:36 4 1 1154 52428800 2 YES ACTIVE 238227672 08/03/2011 09:37 5 1 1155 209715200 2 YES ACTIVE 238228380 08/03/2011 09:55 6 1 1156 209715200 2 YES ACTIVE 238228383 08/03/2011 09:55 7 1 1157 209715200 2 YES ACTIVE 238228386 08/03/2011 09:55 8 1 1158 209715200 2 NO CURRENT 238228389 08/03/2011 09:55 8 rows selected. 09:55:56 PLATINUM_SYSTEM_19/02/2011 10:46>alter database drop logfile group 1; Database altered. 09:56:13 PLATINUM_SYSTEM_19/02/2011 10:46>alter database drop logfile group 2; Database altered. 09:56:19 PLATINUM_SYSTEM_19/02/2011 10:46>alter database drop logfile group 3; Database altered.

This is the error we get when 1 member is still needed for crash recovery.
Oracle will never allow to remove a redo log when its contents are still necessary for instance recovery.

09:56:23 PLATINUM_SYSTEM_19/02/2011 10:46>alter database drop logfile group 4; alter database drop logfile group 4 * ERROR at line 1: ORA-01624: log 4 needed for crash recovery of instance PLATINUM (thread 1) ORA-00312: online log 4 thread 1: 'D:\ORACLE\PLATINUM\REDO04.LOG' ORA-00312: online log 4 thread 1: 'D:\ORACLE\PLATINUM\REDO204.LOG' 09:56:27 PLATINUM_SYSTEM_19/02/2011 10:46>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS ---------- ---------- ---------- ---------- ---------- --- ---------------- FIRST_CHANGE# FIRST_TIME ------------- ---------------- 4 1 1154 52428800 2 YES ACTIVE 238227672 08/03/2011 09:37 5 1 1155 209715200 2 YES ACTIVE 238228380 08/03/2011 09:55 6 1 1156 209715200 2 YES ACTIVE 238228383 08/03/2011 09:55 7 1 1157 209715200 2 YES ACTIVE 238228386 08/03/2011 09:55 8 1 1158 209715200 2 NO CURRENT 238228389 08/03/2011 09:55

We just force another checkpoint

09:56:46 PLATINUM_SYSTEM_19/02/2011 10:46>alter system checkpoint; System altered. 09:56:57 PLATINUM_SYSTEM_19/02/2011 10:46>alter database drop logfile group 4; Database altered.

Finally we get the correctly placed and sized redo log members. All is online.

09:57:03 PLATINUM_SYSTEM_19/02/2011 10:46>select member from v$logfile; MEMBER -------------------------------------------------------------------------------- D:\ORACLE\PLATINUM\REDO05.LOG D:\ORACLE\PLATINUM\REDO06.LOG D:\ORACLE\PLATINUM\REDO07.LOG D:\ORACLE\PLATINUM\REDO08.LOG E:\ORACLE\PLATINUM\REDO205.LOG E:\ORACLE\PLATINUM\REDO206.LOG E:\ORACLE\PLATINUM\REDO207.LOG E:\ORACLE\PLATINUM\REDO208.LOG 8 rows selected. 09:58:44 PLATINUM_SYSTEM_19/02/2011 10:46>select group#,bytes/(1024*1024) from v$log; GROUP# BYTES/(1024*1024) ---------- ----------------- 5 200 6 200 7 200 8 200

Added on dec 4 2013 : If you have more than 1 member in the redo log groups, you can easily remove one of the members

12:10:25 SILVER 04/12/2013 11:49 > alter database drop logfile member '/u01/oradata/SILVER/redo02.log'; alter database drop logfile member '/u01/oradata/SILVER/redo02.log' * ERROR at line 1: ORA-01609: log 2 is the current log for thread 1 - cannot drop members ORA-00312: online log 2 thread 1: '/u01/oradata/SILVER/redo02.log' ORA-00312: online log 2 thread 1: '/u01/oradata/SILVER_COPY/redo002.log'

12:10:36 SILVER 04/12/2013 11:49 > alter database drop logfile member '/u01/oradata/SILVER/redo01.log';

Database altered.

12:10:53 SILVER 04/12/2013 11:49 > alter database drop logfile member '/u01/oradata/SILVER/redo03.log';

Database altered.

12:11:05 SILVER 04/12/2013 11:49 > alter system archive log current;

System altered.

12:11:13 SILVER 04/12/2013 11:49 > alter database drop logfile member '/u01/oradata/SILVER/redo02.log';

Database altered.