如何调整日志文件大小

如何调整日志文件大小:

很多时候我们需要调整日志文件的大小,可以通过如下步骤进行调整。首先查看一下当前日志文件的信息:

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 -