Oracle Database Resizing Redolog files

Re size Redo log file

At this Article I'd like to present Resizing Redo log file, So 2 days ago i got a question "how do you re size redo log file" at my intern view. The simple question was good and i decided to post about this issue.

First of all lets check our redo log group.

 SQL>  select x.group#,x.member,y.bytes/1024/1024 "size"

  2  from v$logfile x, v$log y

  3  where x.group# = y.group#;

    GROUP# MEMBER                                         size

---------- ---------------------------------------- ----------

         3 /u01/app/oracle/oradata/orcl/redo03.log          50

         2 /u01/app/oracle/oradata/orcl/redo02.log          50

         1 /u01/app/oracle/oradata/orcl/redo01.log          50

I've 3 group and 50mb size of them.

Lets check the status 

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

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 CURRENT

         3 INACTIVE

 Now switch the log file to the last group.

SQL> alter system switch logfile;

System altered.

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

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 ACTIVE

         3 CURRENT

 If we want to make active group to inactive we would use the checkpoint command.

SQL> alter system checkpoint global;

System altered.

 So I'm gonna re size first log file from 50mb to 60mb.

first make sure group 1 is inactive then drop it.

SQL> alter database drop logfile group 1;

Database altered.

 i have got 3 group that's why i should have at less 2 group at my database. if you try to drop another one you would got a error.

Now I'm going to add my new group 1 sized 60mb

SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/orcl/redo01.log') size 60m reuse;

Database altered.

check the status.

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

    GROUP# STATUS

---------- ----------------

         1 UNUSED

         2 INACTIVE

         3 CURRENT

 okay its done.

check the size of group

SQL> select x.group#,x.member,y.bytes/1024/1024 "size"

  2  from v$logfile x, v$log y

  3  where x.group# = y.group#;

    GROUP# MEMBER                                         size

---------- ---------------------------------------- ----------

         3 /u01/app/oracle/oradata/orcl/redo03.log          50

         2 /u01/app/oracle/oradata/orcl/redo02.log          50

         1 /u01/app/oracle/oradata/orcl/redo01.log          60

So that's it,