Oracle歸檔日誌異常增長問題的排查過程 轉載 ： https://blog.csdn.net/3moods/article/details/132031152

Libra_bai發表於2024-08-02
OracleHTTPAI

Oracle歸檔日誌是Oracle資料庫的重要功能，用於將資料庫的重做日誌檔案（Redo Log）儲存到歸檔日誌檔案（Archive Log）中。歸檔日誌的作用是提供資料庫的備份和恢復功能，以及支援資料庫的持續性和資料完整性。
當資料庫處於歸檔模式時，資料庫引擎會將已經寫滿的重做日誌檔案儲存到歸檔日誌檔案中，而不是覆蓋已有的重做日誌。這樣可以確保資料庫的完整性，並且可以使用歸檔日誌檔案進行資料庫的恢復操作。
歸檔日誌對於資料庫的備份和恢復非常重要。透過定期備份歸檔日誌檔案，可以保證資料庫在發生故障時能夠進行恢復。同時，歸檔日誌還允許將資料庫恢復到特定的時間點，以滿足特定業務需求。

基礎操作

在Oracle資料庫中，可以透過以下步驟來設定和檢視歸檔日誌空間：

  1. 首先，確認資料庫是否處於歸檔模式。可以透過以下SQL語句查詢：
SQL> SELECT log_mode FROM v$database;
LOG_MODE
  ARCHIVELOG

如果log_mode的值為ARCHIVELOG，則資料庫處於歸檔模式；如果值為NOARCHIVELOG，則資料庫未啟用歸檔模式。

  1. 如果資料庫未啟用歸檔模式，可以透過以下SQL語句將其切換到歸檔模式：
    修改歸檔模式的操作只能在 mount 狀態下進行，不能處於 open 狀態
SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup mount
ORACLE 例程已經啟動。
Total System Global Area 3290345472 bytes
Fixed Size                  2180224 bytes
Variable Size            2382367616 bytes
Database Buffers          889192448 bytes
Redo Buffers               16605184 bytes
資料庫裝載完畢。
SQL> alter database archivelog;
資料庫已更改。
SQL> alter database open;
資料庫已更改。
  1. 確認資料庫已切換到歸檔模式後，可以設定歸檔日誌空間的大小。可以透過以下SQL語句設定歸檔日誌空間的大小為50MB（根據需求進行調整）：
    52428800 = 50 * 1024 * 1024
SQL> alter system set db_recovery_file_dest_size=  52428800;
系統已更改。
  1. 使用以下SQL語句查詢當前歸檔日誌空間的使用情況：
select name,
       space_limit / 1024 / 1024 / 1024 || 'GB' as 空間限制,
       space_used / 1024 / 1024 / 1024 || 'GB' 已使用
  from v$recovery_file_dest

這將顯示歸檔日誌目標的名稱、空間限制和已使用的空間。

問題發生

下面進入對一次因歸檔日誌空間佔滿，導致系統停止服務的故障在某個陽光明媚的週末發生後的處理過程。

  1. 系統停止響應，資料庫登入有以下提示：
ORA-00257:archiver error. Connect internal only,until freed
  1. 很明顯，歸檔日誌滿了，立即刪除歸檔日誌，保留最近3天。
rman
RMAN> connect target 使用者名稱/密碼;
連線到目標資料庫: ORCL (DBID=1616110362)
RMAN> delete archivelog until time 'sysdate-3';
  1. 問題未解決，檢視歸檔空間佔用情況。
select name,
       space_limit / 1024 / 1024 / 1024 || 'GB' as 空間限制,
       space_used / 1024 / 1024 / 1024 || 'GB' 已使用
  from v$recovery_file_dest
  1. 發現佔用空間未釋放，接著刪除所有歸檔：
RMAN> delete archivelog all;
  1. 系統恢復。過了幾個小時，問題再次發生。
  2. 再次刪除所有歸檔日誌，系統恢復，開始排查問題原因。

排查過程

  1. 按天統計
select to_char(COMPLETION_TIME, 'yyyymmdd'), count(*)
  from v$archived_log t
 where COMPLETION_TIME > sysdate - 7
 group by to_char(COMPLETION_TIME, 'yyyymmdd')
 order by to_char(COMPLETION_TIME, 'yyyymmdd');

這是一個查詢語句，用於查詢過去7天內完成的歸檔日誌數量，並按照日期進行分組和排序。
發現前6天正常，當天歸檔日誌異常增長。
2. 按小時統計

select to_char(FIRST_TIME, 'yyyymmddhh24'), count(*)
  from sys.v_$archived_log t
 where t.FIRST_TIME > trunc(sysdate)
 group by to_char(FIRST_TIME, 'yyyymmddhh24')
 order by to_char(FIRST_TIME, 'yyyymmddhh24')

該SQL用於查詢當天開始的歸檔日誌數量，並按照小時進行分組和排序。
3. 按天和小時綜合統計

SELECT    TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'999') "00",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'999') "01",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'999') "02",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'999') "03",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'999') "04",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'999') "05",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'999') "06",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'999') "07",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'999') "08",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'999') "09",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'999') "10",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'999') "11",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'999') "12",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'999') "13",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'999') "14",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'999') "15",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'999') "16",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'999') "17",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'999') "18",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'999') "19",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'999') "20",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'999') "21",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'999') "22",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'999') "23"
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD') 
ORDER BY 1 DESC;

此SQL語句，用於統計每天每個小時的日誌數量，並按照日期倒序排序
3. 根據按小時統計分析，發現歸檔日誌集中在當天2個時間段，其他時間段基本正常。懷疑是在相關時間自動執行的後臺任務造成，經深入排查予以否認。
4. AWR報告生成

sqlplus /nolog
conn / as sysdba
@?/rdbms/admin/awrrpt.sql

報告生成失敗，原因是沒有快照（Snap）
5. 分析沒有快照（Snap）原因，網上說一般是SYSAUX表空間不足造成的，查詢表空間佔用情況，果然滿了
6. 清理表空間

select distinct 'truncate table ' || segment_name || ';',
                s.bytes / 1024 / 1024 MB
  from dba_segments s
 where s.segment_name like 'WRH$%'
   and segment_type in ('TABLE PARTITION', 'TABLE')
   and s.bytes / 1024 / 1024 > 100
 order by s.bytes / 1024 / 1024 desc;

此SQL可生成清理以 ‘WRH$’ 開頭的、大於100MB的表的SQL。生成後執行，完成表空間清理。

  1. 問題解決，真是陰差陽錯。

猜測的原因：
因SYSAUX表空間滿，造成連鎖反應，表現為歸檔日誌異常增長。

一般情況分析

歸檔日誌增長一般是DML操作大量資料造成的，而由SYSAUX表空間滿的原因所造成的則比較少見，故記之。

排查歸檔日誌暴增的方法，一般包括以下三個手段：

  1. SQL語句
  2. AWR
  3. 挖掘歸檔日誌

相關文章