How to Modifying the Size of Redo Logs ?

We must drop and re-create the redo to re-size the redo logs and that can be done online without interrupting database service.

Step - 1 Make sure the logfile you want to change is not the current or active log file.
Step - 2 Drop the logfile group you want to change.
Step - 3 Re-create the logfile group, re-sizing it as required.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

Here we have to change log group 3 but it seems current so we have to make it inactive by switching logfile.

A CURRENT status indicates that the resulting dirty block that are associated with the redo in the logfile have not yet been check pointed which is required before we drop the logfile.

SQL> Alter system switch logfile;

SQL> Alter system switch logfile;

Checkpoint the system. This will remove the log file we want to drop from a possible active status.

SQL> Alter system checkpoint;

Now check the status of logfile again the status of group 3 is 'INACTIVE' now

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

Once the checkpoint is complete, we drop the online redo log group, and re-create it defining a new size for the redo log files.

SQL> Alter database drop logfile group 3;

Now add the logfile group with the new sizing information.

SQL> alter database add logfile group 3
  ('\ORADATA\REDO\redo03a.log',
   '\ORADATA\REDO\redo03b.log') size 500m REUSE;

No comments:

Post a Comment