oracle增加logfile大小

標籤：

有時候由於logfile太小，導致日誌切換頻繁，引起log file switch ...之類的等待事件，這個時候需要增加 logfile的大小。

增加logfile 大小的步驟:

一:查看初始化參數

SQL> show parameter db_create_

NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest                  string      C:/oracle/product/10.2.0/oradata db_create_online_log_dest_1          string db_create_online_log_dest_2          string db_create_online_log_dest_3          string db_create_online_log_dest_4          string db_create_online_log_dest_5          string

為什麼要這麼做呢？因為設定了OMF的這些參數，就可以在建立logfile 的時候讓ORACLE去命名logfile。

我啟用了db_create_file_dest這個參數，那麼oracle就會預設的在這個路徑下建立logfile 以及在 db_recory_file_dest中建立一個logfile.

二:查看當前日誌的情況

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------          4          1         60  104857600          2 YES      INACTIVE               1746560 12/21/2009          5          1         61  104857600          2 NO       CURRENT                1746623 12/21/2009

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE ---------- ------- ------- -------------------------------------------------------------------------------- ---------------------          4         ONLINE  C:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ROBINSON/ONLINELOG/LOG1A.LOG        NO          4         ONLINE  C:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ROBINSON/ONLINELOG/LOG1B.LOG        NO          5         ONLINE  C:/ORACLE/PRODUCT/10.2.0/ORADATA/ROBINSON/ONLINELOG/LOG2A.LOG                    NO          5         ONLINE  C:/ORACLE/PRODUCT/10.2.0/ORADATA/ROBINSON/ONLINELOG/LOG2B.LOG                    NO

三：建立新的記錄檔組

SQL> alter database add logfile group 6 size 150m;

Database altered

SQL> alter database add logfile group 7 size 150m;

Database altered

四：切換記錄檔到新建立的記錄檔組

SQL> alter system switch logfile ;

System altered

SQL> alter system switch logfile ;

System altered

五：查看當前記錄檔資訊，如果status為inactive,ARCHIVED為YES，那麼drop 老的記錄檔組

QL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------          4          1         60  104857600          2 YES      INACTIVE               1746560 12/21/2009          5          1         61  104857600          2 YES      ACTIVE                 1746623 12/21/2009          6          1         62  157286400          2 YES      ACTIVE                 1748762 12/21/2009          7          1         63  157286400          2 NO       CURRENT                1748764 12/21/2009 SQL> alter database drop logfile group 4;

Database altered

SQL> alter database drop logfile group 5;

Database altered

六：重新建立記錄檔組

SQL> alter database add logfile group 1 size 150m;

Database altered

SQL> alter database add logfile group 2 size 150m;

Database altered 七：再次切換記錄檔 SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

八：查看記錄檔資訊，如果status為inactive,archived 為yes，那麼drop 老的記錄檔組

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------          1          1         64  157286400          2 YES      ACTIVE                 1749065 12/21/2009          2          1         65  157286400          2 NO       CURRENT                1749067 12/21/2009          6          1         62  157286400          2 YES      INACTIVE               1748762 12/21/2009          7          1         63  157286400          2 YES      ACTIVE                 1748764 12/21/2009

SQL> alter system flush buffer_cache;  ---此處是為了儘快讓status變成inactive.

System altered

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------          1          1         64  157286400          2 YES      INACTIVE               1749065 12/21/2009          2          1         65  157286400          2 NO       CURRENT                1749067 12/21/2009          6          1         62  157286400          2 YES      INACTIVE               1748762 12/21/2009          7          1         63  157286400          2 YES      INACTIVE               1748764 12/21/2009

八:刪除中間記錄檔組

SQL> alter database drop logfile  group 6;

Database altered

SQL> alter database drop logfile  group 7;

Database altered

oracle增加logfile大小