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