如何调整日志文件大小:
很多时候我们需要调整日志文件的大小,可以通过如下步骤进行调整。首先查看一下当前日志文件的信息:
sys@TQGZS> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 119 52428800 1 YES INACTIVE 6250128 13-DEC-09
3 1 118 52428800 1 YES INACTIVE 6250126 13-DEC-09
2 1 120 52428800 1 NO CURRENT 6250130 13-DEC-09
sys@TQGZS> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
3 ONLINE /u01/oracle/oradata/tqgzs/redo03.log NO
2 ONLINE /u01/oracle/oradata/tqgzs/redo02.log NO
1 ONLINE /u01/oracle/oradata/tqgzs/redo01.log NO
可以使用如下命令增加新的日志组,在创建新的日志组时,可以定义期望的日志大小:
sys@TQGZS> alter database add logfile group 4 '/u01/oracle/oradata/tqgzs/redo04.log' size 50M;
Database altered.
sys@TQGZS> alter database add logfile group 5 '/u01/oracle/oradata/tqgzs/redo05.log' size 50M;
Database altered.
查看此时的日志组信息:
sys@TQGZS> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 119 52428800 1 YES INACTIVE 6250128 13-DEC-09
2 1 120 52428800 1 NO CURRENT 6250130 13-DEC-09
5 1 0 52428800 1 YES UNUSED 0
4 1 0 52428800 1 YES UNUSED 0
3 1 118 52428800 1 YES INACTIVE 6250126 13-DEC-09
可以强制切换日志,使数据库使用新创建的日志组:
sys@TQGZS> alter system switch logfile;
System altered.
sys@TQGZS> alter system switch logfile;
System altered.
sys@TQGZS> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 119 52428800 1 YES INACTIVE 6250128 13-DEC-09
2 1 120 52428800 1 YES ACTIVE 6250130 13-DEC-09
5 1 122 52428800 1 NO CURRENT 6258555 19-DEC-09
4 1 121 52428800 1 YES ACTIVE 6258553 19-DEC-09
3 1 118 52428800 1 YES INACTIVE 6250126 13-DEC-09
然后可以将当前STATUS为INACTIVE的日志组删除,保留新创建的日志组:
sys@TQGZS> alter database drop logfile group 1;
Database altered.
sys@TQGZS> alter database drop logfile group 3;
Database altered.
注意,如果在归档模式下,INACTIVE的日志组尚未完成归档,那么日志组不能被删除,可以等待系统归档完成,如果系统出现问题,可以通过手工归档:
alter system archive log sequence xxx;
最后一步的清理,有时候需要手工删除操作系统上的日志文件,以释放存储空间:
sys@TQGZS> ! rm /u01/oracle/oradata/tqgzs/redo01.log
当然,如果需要使用原有的日志组号,日志文件可以被重新初始化使用:
sys@TQGZS> alter database add logfile group 3 '/u01/oracle/oradata/tqgzs/redo03.log' size 50M reuse;
Database altered.sys@TQGZS> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
2 1 123 52428800 1 YES INACTIVE 6258684 19-DEC-09
5 1 125 52428800 1 YES ACTIVE 6258707 19-DEC-09
4 1 124 52428800 1 YES INACTIVE 6258688 19-DEC-09
3 1 126 52428800 1 NO CURRENT 6258955 19-DEC-09
- The End -