Step by Step to change the Redo Log File size online in Oracle Database

Step by Step to change the Redo Log File size online in Oracle Database

Step1:

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

Step2: Make the last redo log CURRENT

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

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

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

Step3: Drop first redo log

After making the last online redo log file the CURRENT one, drop the first online redo log:


SQL> alter database drop logfile group 1;

Database altered.  


Step4: Precaution

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: ''
Easy problem to resolve. Simply perform a checkpoint on the database:

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

Step5: Re-create dropped online redo log group

Re-create the dropped redo log group with different size (if desired):


SQL> alter database add logfile group 1 (
  2  '/data01/oradata/orcl/redo_g01a.log',  
  3  '/data02/oradata/orcl/redo_g01b.log',
  4  '/data03/oradata/orcl/redo_g01c.log') size 250m reuse;

Database altered.

Step6: Force another log switch

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

Ref: http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_34.shtml