Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 15 September 2014

How to Modifying the Size of Redo Logs

To change the size of your redo logs, you must drop them and then re-create them. You can do this online without shutting down the database.
Steps:
1. Make sure the logfile you wish to change is not the current or Active logfile.
2. Drop the logfile group you wish to change.
3. Re-create the logfile group, resizing it as required.

SQL> select group#, status from v$log;
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
3 CURRENT
Suppose as per the above case, we want to replace log group 3, which is CURRENT so we need to make it INACTIVE first. To do this we need to force log switch wait for the archive generated.

SQL> Alter system switch 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;
Checkpoint the system. This will remove the log file we want to drop from a possible active status.

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.
Note that we use the REUSE keyword also to indicate that we are reusing the files that already exist there and Oracle will resize them accordingly.

SQL> Alter database drop logfile group 3;
Now, add the logfile group with the new sizing information.

SQL> alter database add logfile group 3
  ('D:\ORACLE9I\ORADATA\ORAC\redo03a.log',
  'D:\ORACLE9I\ORADATA\ORAC\redo03b.log') size 100m REUSE;

2 comments:

  1. if it possible how to delete inactive or unused redolog members or groups plz give me steps

    ReplyDelete
    Replies
    1. Dear Prasad,

      You can check this article already clear steps are given for above mentioned task.

      Delete