如何設定redo log的OMF 及如何修改log檔案大小

tolywang發表於2010-02-17
知識點：
1。可以透過設定db_create_online_log_dest_n引數設定重作日誌的OMF
2。設定日誌檔案大小。
引用別人的一句話：
$log中的STATUS列,CURRENT表示當前使用的自然是不可以drop的,ACTIVE的表示非當前但是還是處於活動狀態,日誌中指向的快取中的髒資料塊還沒有完全被刷到磁碟上,所以也是不可以drop的,INACTIVE表示不活動的,相應資料塊都寫入磁碟,這種狀態如果需要歸檔並且已經歸檔,檔案就可以drop了.最後新增上去的日誌組,在一次還沒使用前處於的是一個UNUSED狀態ACTIVE和INACTIVE狀態都可能未歸檔,是否歸檔可以看ARCHIVED列

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 12月 9 00:24:48 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> conn /as sysdba
已連線。
SQL> show parameter log
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
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
......
SQL> alter system set db_create_online_log_dest_1='C:\oracle\oradata\heer';
系統已更改。
SQL> c /1/2
  1* alter system set db_create_online_log_dest_2='C:\oracle\oradata\heer'
SQL> run
  1* alter system set db_create_online_log_dest_2='C:\oracle\oradata\heer'
系統已更改。
SQL> show parameter log
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1          string      C:\oracle\oradata\heer
db_create_online_log_dest_2          string      C:\oracle\oradata\heer
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
。。。。。。
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          8  104857600          1 NO  CURRENT                3641501 2005-12-08 21:54:48
         2          1          7  104857600          1 YES INACTIVE               3583971 2005-12-07 22:29:25
         3          1          6  104857600          1 YES INACTIVE               3524708 2005-12-06 22:18:19
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
         3 STALE   ONLINE  C:\ORACLE\ORADATA\HEER\REDO03.LOG
         2 STALE   ONLINE  C:\ORACLE\ORADATA\HEER\REDO02.LOG
         1         ONLINE  C:\ORACLE\ORADATA\HEER\REDO01.LOG
SQL> alter database drop logfile group 3;
資料庫已更改。
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
         2 STALE   ONLINE  C:\ORACLE\ORADATA\HEER\REDO02.LOG
         1         ONLINE  C:\ORACLE\ORADATA\HEER\REDO01.LOG
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          8  104857600          1 NO  CURRENT                3641501 2005-12-08 21:54:48
         2          1          7  104857600          1 YES INACTIVE               3583971 2005-12-07 22:29:25
SQL> alter database add logfile group 3 size 10M;
資料庫已更改。
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          8  104857600          1 NO  CURRENT                3641501 2005-12-08 21:54:48
         2          1          7  104857600          1 YES INACTIVE               3583971 2005-12-07 22:29:25
         3          1          0   10485760          2 YES UNUSED                       0
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
         3         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY100_.LOG
         2 STALE   ONLINE  C:\ORACLE\ORADATA\HEER\REDO02.LOG
         1         ONLINE  C:\ORACLE\ORADATA\HEER\REDO01.LOG
         3         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY101_.LOG
SQL> alter database drop logfile group 2;
資料庫已更改。
SQL> alter database add logfile group 2 size 10m;
資料庫已更改。
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          8  104857600          1 NO  CURRENT                3641501 2005-12-08 21:54:48
         2          1          0   10485760          2 YES UNUSED                       0
         3          1          0   10485760          2 YES UNUSED                       0
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
         3         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY100_.LOG
         2         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0100_.LOG
         1         ONLINE  C:\ORACLE\ORADATA\HEER\REDO01.LOG
         3         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY101_.LOG
         2         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0101_.LOG
SQL> alter system switch logfile;
系統已更改。
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          8  104857600          1 YES ACTIVE                 3641501 2005-12-08 21:54:48
         2          1          9   10485760          2 NO  CURRENT                3676851 2005-12-09 00:32:29
         3          1          0   10485760          2 YES UNUSED                       0
SQL> /
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          8  104857600          1 YES ACTIVE                 3641501 2005-12-08 21:54:48
         2          1          9   10485760          2 NO  CURRENT                3676851 2005-12-09 00:32:29
         3          1          0   10485760          2 YES UNUSED                       0
......
這個地方查了幾遍都沒有完成checkpoint，索性顯示的再checkpoint一次，ok了。
SQL> alter system checkpoint;
系統已更改。
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          8  104857600          1 YES INACTIVE               3641501 2005-12-08 21:54:48
         2          1          9   10485760          2 NO  CURRENT                3676851 2005-12-09 00:32:29
         3          1          0   10485760          2 YES UNUSED                       0
SQL>
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          8  104857600          1 YES INACTIVE               3641501 2005-12-08 21:54:48
         2          1          9   10485760          2 NO  CURRENT                3676851 2005-12-09 00:32:29
         3          1          0   10485760          2 YES UNUSED                       0
SQL> alter database drop logfile group 1;
資料庫已更改。
SQL> alter database add logfile group 1 size 10m;
資料庫已更改。
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          0   10485760          2 YES UNUSED                       0
         2          1          9   10485760          2 NO  CURRENT                3676851 2005-12-09 00:32:29
         3          1          0   10485760          2 YES UNUSED                       0
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ----------------------------------------------------------------------------------------------------
         3         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY100_.LOG
         2         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0100_.LOG
         1         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_1_1SJS1S00_.LOG
         3         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_3_1SJQY101_.LOG
         2         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_2_1SJR0101_.LOG
         1         ONLINE  C:\ORACLE\ORADATA\HEER\O1_MF_1_1SJS1S01_.LOG
已選擇6行。
SQL> alter system switch logfile;
系統已更改。
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1         10   10485760          2 NO  CURRENT                3680899 2005-12-09 00:50:30
         2          1          9   10485760          2 YES ACTIVE                 3676851 2005-12-09 00:32:29
         3          1          0   10485760          2 YES UNUSED                       0
SQL> alter system switch logfile;
系統已更改。
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1         10   10485760          2 YES ACTIVE                 3680899 2005-12-09 00:50:30
         2          1          9   10485760          2 YES INACTIVE               3676851 2005-12-09 00:32:29
         3          1         11   10485760          2 NO  CURRENT                3681024 2005-12-09 00:50:59
參考文章：

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-627280/，如需轉載，請註明出處，否則將追究法律責任。

相關文章