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. :-)
