[Oracle]アーカイブログの出力量を確認[日別・時間別](RAC対応)

アーカイブログ(ARCHIVE LOG)出力量を日別、時間別毎に集計するSQLです。

あくまで出力量の履歴の確認なので、使用率が確認したい場合おまけのほうをご参照ください。

11gR2, 19cで確認しました。

アーカイブログモードの確認

SQL:

SELECT LOG_MODE FROM V$DATABASE;

実行例:

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

SQL>

ARCHIVELOGと表示されていればアーカイブログモードです。

NOARCHIVELOGと表示される場合はノーアーカイブログモードです、この場合アーカイブログは出力されません。

SQL*Plusの「ARCHIVE LOG LIST」コマンドでも確認できます。

アーカイブログ出力先の確認

SQL: 初期化パラメーター[ log_archive_dest_n ] を確認するSQLです。

log_archive_dest_1db_recovery_file_dest、値が設定されているlog_archive_dest_nを表示してます。

log_archive_dest_nパラメーターは10g以降 Standard Editionでも使用できます。

SET PAGESIZE 100
COL NAME FORMAT A30
COL VALUE FORMAT A30
SELECT INST_ID,NAME,VALUE
FROM GV$SYSTEM_PARAMETER
WHERE
NAME = LOWER('LOG_ARCHIVE_DEST_1')
OR ( REGEXP_LIKE (NAME,'LOG_ARCHIVE_DEST_[1-3]','i') AND VALUE IS NOT NULL )
OR NAME = LOWER('DB_RECOVERY_FILE_DEST')
ORDER BY NAME,INST_ID
;

2022/02/26 フィルタの誤りでlog_archive_dest_1~3までしか検索にヒットしないようになっていた部分修正しました。

実行例①:通常パターン

SQL> SET PAGESIZE 100
SQL> COL NAME FORMAT A30
SQL> COL VALUE FORMAT A30
SQL> SELECT INST_ID,NAME,VALUE
  2  FROM GV$SYSTEM_PARAMETER
  3  WHERE
  4  NAME = LOWER('LOG_ARCHIVE_DEST_1')
  5  OR ( REGEXP_LIKE (NAME,'LOG_ARCHIVE_DEST_[1-3]','i') AND VALUE IS NOT NULL )
  6  OR NAME = LOWER('DB_RECOVERY_FILE_DEST')
  7  ORDER BY NAME,INST_ID
  8  ;

   INST_ID NAME                           VALUE
---------- ------------------------------ ------------------------------
         1 db_recovery_file_dest
         2 db_recovery_file_dest
         1 log_archive_dest_1             LOCATION=+ARCH
         2 log_archive_dest_1             LOCATION=+ARCH
         1 log_archive_dest_2             service=UFOS async valid_for=(
                                          online_logfile,primary_role) r
                                          eopen=60 db_unique_name=UFOS

         2 log_archive_dest_2             service=UFOS async valid_for=(
                                          online_logfile,primary_role) r
                                          eopen=60 db_unique_name=UFOS


6行が選択されました。

SQL>

基本的に「log_archive_dest_1」に設定されているLOCATION=に続く値がアーカイブログの出力先です。

RACの場合、インスタンス毎に出力先を変えているって設計をまれに見かけるので念ため各インスタンスの値を確認しておくほうがよいです。

環境によってはlog_archive_dest_2~以降にも設定されてる場合があります。

その場合アーカイブログを冗長化をしているってことになります。

ただし上の実行例の「log_archive_dest_2」のservice~ の設定はアーカイブログ出力先の冗長化とはちょっと異なり、DataGuardの構成に必要な設定になります。

実行例②:高速リカバリ領域を使用している場合の例

SQL> SET PAGESIZE 100
SQL> COL NAME FORMAT A30
SQL> COL VALUE FORMAT A30
SQL> SELECT INST_ID,NAME,VALUE
  2  FROM GV$SYSTEM_PARAMETER
  3  WHERE
  4  NAME = LOWER('LOG_ARCHIVE_DEST_1')
  5  OR ( REGEXP_LIKE (NAME,'LOG_ARCHIVE_DEST_[1-3]','i') AND VALUE IS NOT NULL )
  6  OR NAME = LOWER('DB_RECOVERY_FILE_DEST')
  7  ORDER BY NAME,INST_ID
  8  ;

   INST_ID NAME                           VALUE
---------- ------------------------------ ------------------------------
         1 db_recovery_file_dest          +FLASH
         2 db_recovery_file_dest          +FLASH
         1 log_archive_dest_1
         2 log_archive_dest_1

SQL>

高速リカバリ領域を使用している場合の例です。「db_recovery_file_dest」の値が高速リカバリ領域の出力先になります。

log_archive_dest_n に ‘LOCATION=USE_DB_RECOVERY_FILE_DEST’ を設定することでアーカイブログの出力先が高速リカバリ領域になります。この設定を省略すると、log_archive_dest_1の出力先が高速リカバリ領域になります。上の例です。

データベース管理者ガイド

日別出力量の確認

SQL: DEST_IDは上記確認したlog_archive_dest_nの値を設定して下さい。通常は1です。

SET PAGESIZE 100
COL "GB" FOR 990.99
SELECT
    TO_CHAR(COMPLETION_TIME,'YYYY/MM/DD') AS "日付",  -- 出力日付
--    TO_CHAR(COMPLETION_TIME,'YYYY/MM/DD HH24') AS "時間",  -- 出力時間
    SUM(BLOCKS*BLOCK_SIZE)/1024/1024/1024 AS "GB"   -- 出力量
FROM V$ARCHIVED_LOG
WHERE DEST_ID=1
    GROUP BY TO_CHAR(COMPLETION_TIME,'YYYY/MM/DD')
--    GROUP BY TO_CHAR(COMPLETION_TIME,'YYYY/MM/DD HH24')
ORDER BY 1;

実行例:

SQL> /

YYYY/MM/DD      GB
---------- -------
2020/06/09    0.34
2020/06/10    0.36
2020/06/11    0.53
2020/06/12    0.51
2020/06/13    0.34
2020/06/14    0.68
2020/06/15    0.17
2020/06/16    2.13
2020/06/17    0.51
2020/06/18    0.34
2020/06/19    0.35
2020/06/20    0.53
2020/06/21    0.51
2020/06/22    0.17

14行が選択されました。

SQL>

V$ARCHIVED_LOGで全てのスレッドが含まれているのでRACにおいてもGV$ビューでの検索は不要です。逆にGV$ビューを検索してしまうと2重でカウントされてしまうのでNGです。

時間別出力量の確認

SQL: DEST_IDは上記確認したlog_archive_dest_nの値を設定して下さい。通常は1です。

SET PAGESIZE 100
COL "GB" FOR 990.99
SELECT
--    TO_CHAR(COMPLETION_TIME,'YYYY/MM/DD') AS "YYYY/MM/DD",  -- 出力日付
    TO_CHAR(COMPLETION_TIME,'YYYY/MM/DD HH24') AS "YYYY/MM/DD HH24",  -- 出力時間
    SUM(BLOCKS*BLOCK_SIZE)/1024/1024/1024 AS "GB"   -- 出力量
FROM V$ARCHIVED_LOG
WHERE DEST_ID=1
--    GROUP BY TO_CHAR(COMPLETION_TIME,'YYYY/MM/DD')
    GROUP BY TO_CHAR(COMPLETION_TIME,'YYYY/MM/DD HH24')
ORDER BY 1;

実行例:

SQL> /

YYYY/MM/DD HH      GB
------------- -------
2020/06/09 14    0.17
2020/06/09 22    0.17
2020/06/10 09    0.19
2020/06/10 14    0.17
2020/06/11 06    0.17
2020/06/11 09    0.19
2020/06/11 23    0.17
2020/06/12 04    0.17
2020/06/12 20    0.17
2020/06/12 22    0.17
2020/06/13 12    0.34
2020/06/14 01    0.17
2020/06/14 07    0.17
2020/06/14 10    0.17
2020/06/14 20    0.17
2020/06/15 03    0.17
2020/06/16 06    0.48
2020/06/16 07    0.02
2020/06/16 08    1.26
2020/06/16 09    0.20
2020/06/16 22    0.17
2020/06/17 11    0.34
2020/06/17 22    0.17
2020/06/18 07    0.17
2020/06/18 22    0.17
2020/06/19 09    0.18
2020/06/19 14    0.17
2020/06/20 02    0.17
2020/06/20 09    0.19
2020/06/20 14    0.17
2020/06/21 06    0.34
2020/06/21 14    0.17
2020/06/22 01    0.17

33行が選択されました。

SQL>

V$ARCHIVED_LOGで全てのスレッドが含まれているのでRACにおいてもGV$ビューでの検索は不要です。逆にGV$ビューを検索してしまうと2重でカウントされてしまうのでNGです。

おまけ:アーカイブログ領域の使用率/使用サイズを見る方法

高速リカバリ領域を使用しているか否かで確認方法が変わります。

高速リカバリ領域を使用していない場合:

OSやASMのファイルシステムの容量が格納できるサイズになるので、
OSやASMのコマンドで容量や使用しているサイズを確認するのが一番簡単で確実です。

例:df 、duコマンド、ASMを使用している場合は asmcmd lsdg等

[grid@rac-01 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304     30716     5280                0            5280              0             N  DGARCH/
MOUNTED  EXTERN  N         512   4096  4194304    102396    90276                0           90276              0             N  DGDATA/
MOUNTED  EXTERN  N         512   4096  4194304     10236     9804                0            9804              0             Y  DGOCR/
MOUNTED  EXTERN  N         512   4096  4194304     30716    18220                0           18220              0             N  DGREDO01/
MOUNTED  EXTERN  N         512   4096  4194304     30716    18220                0           18220              0             N  DGREDO02/
[grid@rac-01 ~]$ e

※V$ARCHIVED_LOGのアーカイブログサイズを集計することで使用サイズを確認することも出来きます。が使用率監視を行う場合にはおすすめ出来ません。なぜならその領域にアーカイブログ以外のファイルなどが存在していて領域を圧迫していないとも限らないからです(一時的に手動で作成したファイルのコピーなど)。

格納先がASMの場合はSQLでアーカイブログの使用量を確認することも可能です。下記記事を参考にしていただければと思います。

参考:[Oracle]ASMディレクトリ毎の使用量/使用率/ファイル数を確認するSQL

高速リカバリ領域を使用している場合:

高速リカバリ領域はDB内に使用率を持っているため専用のVIEWで確認する必要があります。V$RECOVERY_AREA_USAGEではファイルタイプごとの使用率が確認できます。

下記例の場合、ARCHIVED LOGが高速リカバリエリア領域のうち11.18%を使用していることがわかります。

SQL> set pagesize 30
SQL> set linesize 100
SQL> SELECT * FROM V$RECOVERY_AREA_USAGE;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                         11.18                         0               8          0
BACKUP PIECE                           .21                         0               1          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                            0                         0               0          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8行が選択されました。

SQL>

仮にOSやASM上の領域にまだ空きがあるとしも高速リカバリ領域の使用率が100%になるとアラートログに以下のようなエラーを履き、高速リカバリ領域にファイル出力できなくなります。高速リカバリ領域の容量は初期化パラメーターDB_RECOVERY_FILE_DEST_SIZEで設定します。

Errors in file /u01/app/oracle/diag/rdbms/hanpen/hanpen1/trace/hanpen1_arc3_4102.trc:
ORA-19815: 警告: db_recovery_file_dest_size(314572800バイト)は100.00%バイトが使用され、残り0バイトが使用可能です。
2022-02-26T21:44:45.070414+09:00
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
2022-02-26T21:44:45.086327+09:00
Errors in file /u01/app/oracle/diag/rdbms/hanpen/hanpen1/trace/hanpen1_arc3_4102.trc:
ORA-19809: リカバリ・ファイルの制限を超えています
ORA-19804: 18874368バイトのディスク領域を314572800バイト制限から再利用できません
ARC3 (PID:4102): Error 19809 Creating archive log file to '+FLASH'
ARC3 (PID:4102): Stuck archiver: inactive mandatory LAD:1
ARC3 (PID:4102): Stuck archiver condition declared
2022-02-26T21:44:45.148627+09:00
Errors in file /u01/app/oracle/diag/rdbms/hanpen/hanpen1/trace/hanpen1_arc3_4102.trc:
ORA-16038: ログ2、順序番号1249をアーカイブできません。
ORA-19809: リカバリ・ファイルの制限を超えています
ORA-00312: オンライン・ログ2 スレッド1: '+DATA/HANPEN/ONLINELOG/group_2.261.1057722367'
ARC3 (PID:4102): Archival error occurred on a closed thread, archiver continuing
2022-02-26T21:44:45.148834+09:00
ORACLE Instance hanpen1, archival error, archiver continuing

2022/02/26 高速リカバリ領域があふれた際のエラー内容が誤っていたため修正しました。

おすすめ

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です