狀態為inactive的日誌一定歸檔了嗎?

warehouse發表於2008-07-15

inactive表示該日誌在例項恢復時不再需要被用到,僅此而已,和是否歸檔沒有任何關係,前端時間看到pub上有人問起這個問題,我們通常看到狀態為inactive的日誌archived=YES也就是通常都歸檔了,不太容易觀察到archived=no,在9i下可以透過設定log_archive_start=false來控制不歸檔,但是10g是否不太容易控制,這裡透過縮小flash_recovery_area使其沒有空間從而不能實現歸檔來觀察一下狀態為inactive且archived=no的日誌!

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 122 INACTIVE NO
2 121 INACTIVE YES
3 123 CURRENT NO

[@more@]

SQL> archive log list;
資料庫日誌模式 存檔模式
自動存檔 啟用
存檔終點 USE_DB_RECOVERY_FILE_DEST
最早的聯機日誌序列 120
下一個存檔日誌序列 122
當前日誌序列 122
SQL> show parameter recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:oracleproduct10.2.0flash
_recovery_area
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
SQL> alter system set db_recovery_file_dest_size=1g;

系統已更改。

SQL> alter system set db_recovery_file_dest_size=500m;

系統已更改。

SQL> alter system set db_recovery_file_dest_size=300m;

系統已更改。

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 122 CURRENT NO
2 121 INACTIVE YES
3 120 INACTIVE YES

SQL> alter system switch logfile;

系統已更改。

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 122 ACTIVE NO
2 121 INACTIVE YES
3 123 CURRENT NO

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 122 ACTIVE NO
2 121 INACTIVE YES
3 123 CURRENT NO

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 122 ACTIVE NO
2 121 INACTIVE YES
3 123 CURRENT NO

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 122 ACTIVE NO
2 121 INACTIVE YES
3 123 CURRENT NO

SQL> alter system checkpoint;

系統已更改。

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 122 INACTIVE NO
2 121 INACTIVE YES
3 123 CURRENT NO

SQL>
SQL> desc v$flash_recovery_area_usage
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

FILE_TYPE VARCHAR2(12)
PERCENT_SPACE_USED NUMBER
PERCENT_SPACE_RECLAIMABLE NUMBER
NUMBER_OF_FILES NUMBER

SQL> select* from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 2.34 0 1
ONLINELOG 0 0 0
ARCHIVELOG 99.83 0 46
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 30.4 26.79 16

已選擇6行。

SQL>

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

相關文章