Monday, April 16, 2012

Resize Redo log

If the redo logs need to be resized, the following steps can be taken. 
1.  Create new log group members with the following command:

ALTER DATABASE ADD LOGFILE
GROUP 4 (    '/ccbprod/oradata/ccbprod/REDO04A.LOG',
        '/ccbprod/oradata/ccbprod/REDO04B.LOG') SIZE 1280M reuse,
GROUP 5 (    '/ccbprod/oradata/ccbprod/REDO05A.LOG',
        '/ccbprod/oradata/ccbprod/REDO05B.LOG') SIZE 1280M reuse,
GROUP 6 (    '/ccbprod/oradata/ccbprod/REDO06A.LOG',
        '/ccbprod/oradata/ccbprod/REDO06B.LOG') SIZE 1280M reuse;

2.     Switch the logfiles  by issuing: ALTER SYSTEM SWITCH LOGFILE until one of the new redo logs from step 1 is the current redo log.  (Can check this by @datafiles).
        (select group#, status from v$log;)

As a DBA, you should already be aware that if you are going to drop a logfile group, it cannot be the current logfile group. I have run into instances; however, where attempting to drop the logfile group resulted in the following error as a result of the logfile group having an active status:
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)
ORA-00312: online log 1 thread 1: '<file_name>'
Easy problem to resolve. Simply perform a checkpoint on the database:
SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

3.     Drop the original redo logs with:

    ALTER DATABASE DROP LOGFILE GROUP 1, GROUP 2, GROUP 3;

4.     Delete the operating system files from the old redo logs.

5.  (Optional).  Repeat steps 1-4 to create redo groups 1-3 and drop groups 4-6 to restore the redo group numbers starting from 1.

ALTER DATABASE ADD LOGFILE
GROUP 1 (    '/redoa/ccbprod/REDO01A.LOG',
        '/redob/ccbprod/REDO01B.LOG') SIZE 1280M reuse,
GROUP 2 (    '/redoa/ccbprod/REDO02A.LOG',
        '/redob/ccbprod/REDO02B.LOG') SIZE 1280M reuse,
GROUP 3 (    '/redoa/ccbprod/REDO03A.LOG',
        '/redob/ccbprod/REDO03B.LOG') SIZE 1280M reuse;

6. ALTER DATABASE DROP LOGFILE GROUP 4, GROUP 5, GROUP 6;

7. double check redo members:
set lines 1000
select GROUP#,MEMBER from v$logfile;

No comments:

Post a Comment