RESIZING ONLINE REDOLOG FILES

The following demonstration shows how to resize the online redo log files in an oracle database.

The query below gives the size of all the online redo log files which is 250MB.

SQL> SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES

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

         1 /fs02/oradata/demo/demo_redo1a.log            262144000

         1 /fs03/oradata/demo/demo_redo1b.log            262144000

         1 /fs04/oradata/demo/demo_redo1c.log            262144000

         2 /fs02/oradata/demo/demo_redo2a.log            262144000

         2 /fs03/oradata/demo/demo_redo2b.log            262144000

         2 /fs04/oradata/demo/demo_redo2c.log            262144000

         3 /fs02/oradata/demo/demo_redo3a.log            262144000

         3 /fs03/oradata/demo/demo_redo3b.log            262144000

         3 /fs04/oradata/demo/demo_redo3c.log            262144000

9 rows selected.

Check the status of the log group.

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

    GROUP# STATUS

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

         1 CURRENT

         2 INACTIVE

         3 INACTIVE

Now force a log switch until the last redo log is marked "CURRENT" by issuing the following command:

SQL> alter system switch logfile;

SQL> alter system switch logfile;

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

    GROUP# STATUS

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

         1 INACTIVE

         2 INACTIVE

         3 CURRENT

Drop the first logfile group and recreate the same with the desired size, in this case 512MB

SQL> alter database drop logfile group 1;

Database altered.

or

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>'

Nothing to panic here and this is an easy problem to resolve. Simply perform a checkpoint on the database and try to drop again.

SQL> alter system checkpoint global;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

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

Re-create the dropped redo log group with different size

alter database add logfile group 1 (

   '/fs02/oradata/demo/demo_redo1a.log', 

   '/fs03/oradata/demo/demo_redo1b.log',

   '/fs04/oradata/demo/demo_redo1c.log') size 500m reuse;

After re-creating the online redo log group, force a log switch. The online redo log group just created should become the "CURRENT" one.

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

    GROUP# STATUS

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

         1 UNUSED

         2 INACTIVE

         3 CURRENT

SQL> alter system switch logfile;

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

    GROUP# STATUS

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

         1 CURRENT

         2 INACTIVE

         3 ACTIVE

Do the same for all the logfiles.

alter database add logfile group 2 (

   '/fs02/oradata/demo/demo_redo2a.log', 

   '/fs03/oradata/demo/demo_redo2b.log',

   '/fs04/oradata/demo/demo_redo2c.log') size 500m reuse;

alter database add logfile group 3 (

   '/fs02/oradata/demo/demo_redo3a.log', 

   '/fs03/oradata/demo/demo_redo3b.log',

   '/fs04/oradata/demo/demo_redo3c.log') size 500m reuse;

Now, if you check the sizes of the logfiles you can see the size increased.

SQL> SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES

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

         1 /fs02/oradata/demo/demo_redo1a.log            524288000

         1 /fs03/oradata/demo/demo_redo1b.log            524288000

         1 /fs04/oradata/demo/demo_redo1c.log            524288000

         2 /fs02/oradata/demo/demo_redo2a.log            524288000

         2 /fs03/oradata/demo/demo_redo2b.log            524288000

         2 /fs04/oradata/demo/demo_redo2c.log            524288000

         3 /fs02/oradata/demo/demo_redo3a.log            524288000

         3 /fs03/oradata/demo/demo_redo3b.log            524288000

         3 /fs04/oradata/demo/demo_redo3c.log            524288000

9 rows selected.

Mission accomplished. :-)