Resizing Logfiles

The best way to resize logfiles is by creating new logfile groups in the new size, then dropping the old logfile groups.


Example:

SYS> select group#,member from v$logfile;

GROUP# MEMBER
---------- ----------------------------------------

1 D:\DATABASE\TRN3\LOGTRN3_1A.LGF
1 F:\DATABASE\TRN3\LOGTRN3_1A.LGF
2 D:\DATABASE\TRN3\LOGTRN3_2A.LGF
2 F:\DATABASE\TRN3\LOGTRN3_2A.LGF
3 D:\DATABASE\TRN3\LOGTRN3_3A.LGF
3 F:\DATABASE\TRN3\LOGTRN3_3A.LGF

6 rows selected.

SYS> alter database add logfile group 4
2 ('d:\database\trn3\logtrn3_4a.lgf','f:\database\trn3\logtrn3_4b.lgf')
3* size 10240K
SYS> /

Database altered.

SYS> alter database add logfile group 5
2 ('d:\database\trn3\logtrn3_5a.lgf','f:\database\trn3\logtrn3_5b.lgf')
3* size 10240K
SYS> /

Database altered.

SYS> alter database add logfile group 6
2 ('d:\database\trn3\logtrn3_6a.lgf','f:\database\trn3\logtrn3_6b.lgf')
3* size 10240K
SYS> /

Database altered.

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

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED

6 rows selected.

SYS> alter database drop logfile group 2;

Database altered.

SYS> alter database drop logfile group 3;

Database altered.

SYS> alter system switch logfile;

System altered.

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

GROUP# STATUS
---------- ----------------
1 ACTIVE
4 CURRENT
5 UNUSED
6 UNUSED

SYS> alter system switch logfile;

System altered.

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

GROUP# STATUS
---------- ----------------
1 INACTIVE
4 ACTIVE
5 CURRENT
6 UNUSED

SYS> alter database drop logfile group 1;

Database altered.

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

GROUP# STATUS
---------- ----------------
4 INACTIVE
5 CURRENT
6 UNUSED