19 | 08 | 2017
Latest Articles
Popular Articles

Administration

Replace and mirror online redo logs

User Rating:  / 0
PoorBest 

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.

 

 

See also Redo logs switch history per hour, per day