Autonomous Databaseのアラートログの差分を一定間隔でオブジェクト・ストレージに出力してみた

# はじめに
DBMS_CLOUD_PIPELINEパッケージを使用して、Autonomous Databaseのアラートログを一定間隔でオブジェクト・ストレージに書き出してみました。
Autonomous Databaseのアラートログは、v$diag_alert_extビューで確認できます。

※今回の検証ではリソース・プリンシパルを使用するため、あらかじめリソース・プリンシパルを使用するため設定をしてあります。

# 1. リソース・プリンシパルを使用してオブジェクト・ストレージにアクセスできるかを確認
DBMS_CLOUD.LIST_OBJECTSファンクションを使用して、リソース・プリンシパルを用いてオブジェクト・ストレージにアクセスできるかを確認してみます。
```
SQL> SELECT object_name, bytes, created 
  2  FROM   DBMS_CLOUD.LIST_OBJECTS(
  3            credential_name => 'OCI$RESOURCE_PRINCIPAL',
  4            location_uri    => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/ALERTLOG/o/'
  5*        );

OBJECT_NAME       BYTES CREATED                                
______________ ________ ______________________________________ 
test.txt             38 30-NOV-23 08.37.42.880000000 AM GMT    

SQL>
```
バケット内のファイルがリストできましたので、リソース・プリンシパルを用いてオブジェクト・ストレージにアクセスできることが確認できました。

# 2. パイプラインの作成とパラメータの設定

アラートログを一定間隔でオブジェクトストレージにエクスポートするためのパイプラインALERTLOG_EXP_PIPEを、DBMS_CLOUD_PIPELINE.CREATE_PIPELINEプロシージャを使用して作成します。
```
SQL> BEGIN
  2      DBMS_CLOUD_PIPELINE.CREATE_PIPELINE(
  3          pipeline_name=>'ALERTLOG_EXP_PIPE', -- 作成するパイプラインの名前
  4          pipeline_type=>'EXPORT', -- パイプラインのタイプ
  5          description=>'Export Alert Log to Object Store' -- パイプラインの説明
  6      );
  7  END;
  8* /

PL/SQLプロシージャが正常に完了しました。

SQL>
```
DBMS_CLOUD_PIPELINE.SET_ATTRIBUTEプロシージャを使用して、パイプラインALERTLOG_EXP_PIPEのパラメータを設定します。

|パラメータ|説明|
|---|---|
|credential_name|オブジェクト・ストレージへのアクセスに使用するクレデンシャル|
|location|エクスポート先のオブジェクト・ストレージ・バケット+ファイル名のプリフィックス|
|query|エクスポート対象のデータを抽出するSQL|
|key_column|SQLに含まれるタイムスタンプ列|
|format|エクスポートファイルのフォーマット|
|priority|パイプライン・ジョブの優先度（HIGH/MEDIUM/LOW）|
|interval|パイプライン・ジョブの実行間隔|

ここでは、アラートログを格納するディクショナリ・ビューv$diag_alert_extの検索結果をParquet形式で10分おきにエクスポートする設定を行います。
```
SQL> BEGIN
  2      DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE(
  3          pipeline_name => 'ALERTLOG_EXP_PIPE', 
  4          attributes    => JSON_OBJECT(
  5                               'credential_name' VALUE 'OCI$RESOURCE_PRINCIPAL',　-- ストレージアクセスに使用するクレデンシャル
  6                               'location' VALUE 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/ALERTLOG/o/alertlog', -- エクスポート先のバケット及びファイル名のプリフィックス
  7                               'query' VALUE 'SELECT * FROM v$diag_alert_ext', -- エクスポートデータのSQL
  8                               'key_column' VALUE 'originating_timestamp', -- エクスポートデータ内のタイムスタンプ列
  9                               'format' VALUE '{"type": "parquet"}', -- ファイル形式
 10                               'priority' VALUE 'MEDIUM', -- エクスポート・ジョブの優先度
 11                               'interval' VALUE '10') -- エクスポート間隔
 12      );
 13  END;
 14* /

PL/SQLプロシージャが正常に完了しました。

SQL> 
```

# 3. パイプラインの開始
現在時刻を確認します。
```
SQL> select systimestamp from dual;

SYSTIMESTAMP                           
______________________________________ 
01-DEC-23 02.58.00.520420000 AM GMT    

SQL>
```
DBMS_CLOUD_PIPELINE.START_PIPELINEプロシージャを使用して、パイプラインALERTLOG_EXP_PIPEを開始します。
start_dateを指定することで、初回実行時刻を設定できます。
```
SQL> BEGIN
  2      DBMS_CLOUD_PIPELINE.START_PIPELINE(
  3          pipeline_name => 'ALERTLOG_EXP_PIPE', -- パイプライン名
  4          start_date    => TO_TIMESTAMP_TZ('2023-12-01 03:00:00 +0:00','YYYY-MM-DD HH24:MI:SS TZH:TZM') -- パイプラインの初回実行日時（タイプスタンプ）
  5      );
  6  END;
  7* /

PL/SQLプロシージャが正常に完了しました。

SQL>
```
パイプラインALERTLOG_EXP_PIPEを開始しましたが、まだ初回実行時刻になっていないので、user_cloud_pipeline_historyビューにはデータがありません。
```
SQL> SELECT pipeline_name, start_date, end_date, status, error_message
  2  FROM user_cloud_pipeline_history      
  3  WHERE pipeline_name = 'ALERTLOG_EXP_PIPE'
  4* ORDER BY start_date;

行が選択されていません
SQL>
```
オブジェクト・ストレージ・バケットにもまだファイルは出力されていません。
```
SQL> SELECT object_name, bytes, created 
  2  FROM   DBMS_CLOUD.LIST_OBJECTS(
  3            credential_name => 'OCI$RESOURCE_PRINCIPAL',
  4            location_uri    => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/ALERTLOG/o/'
  5*        );

OBJECT_NAME       BYTES CREATED                                
______________ ________ ______________________________________ 
test.txt             38 30-NOV-23 08.37.42.880000000 AM GMT    

SQL> 
```

# 4. パイプラインの実行状況の確認

パイプラインの初回実行時刻が過ぎるまで待ちます。

```
SQL> SELECT systimestamp FROM dual;

SYSTIMESTAMP                           
______________________________________ 
01-DEC-23 03.00.45.327771000 AM GMT    

SQL> 
```

user_cloud_pipeline_historyビューで、パイプラインALERTLOG_EXP_PIPEの実行状況を確認します。
```
SQL> SELECT pipeline_name, start_date, end_date, status, error_message
  2  FROM user_cloud_pipeline_history      
  3  WHERE pipeline_name = 'ALERTLOG_EXP_PIPE'
  4* ORDER BY start_date;

PIPELINE_NAME        START_DATE                             END_DATE                               STATUS       ERROR_MESSAGE    
____________________ ______________________________________ ______________________________________ ____________ ________________ 
ALERTLOG_EXP_PIPE    01-DEC-23 03.00.00.033173000 AM GMT    01-DEC-23 03.00.10.033173000 AM GMT    SUCCEEDED                     

SQL> 
```
設定した時刻にパイプラインALERTLOG_EXP_PIPEが実行され、StatusがSUCCEEDEDになっていることが確認できました。

オブジェクト・ストレージ・バケットの内容を確認してみます。
```
SQL> SELECT object_name, bytes, created 
  2  FROM   DBMS_CLOUD.LIST_OBJECTS(
  3            credential_name => 'OCI$RESOURCE_PRINCIPAL',
  4            location_uri    => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/ALERTLOG/o/'
  5*        );

OBJECT_NAME                                     BYTES CREATED                                
____________________________________________ ________ ______________________________________ 
alertlog_1_20231201T030001574664Z.parquet       28375 01-DEC-23 03.00.09.630000000 AM GMT    
test.txt                                           38 30-NOV-23 08.37.42.880000000 AM GMT    
    

SQL>
```
プリフィックスがalertlogとなっているparquetファイルが出力されていることが確認できました。

parquetファイルの内容を確認するために、外部表alertlog_ext_tblを作成します。

```
SQL> BEGIN
  2      DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
  3          table_name =>'alertlog_ext_tbl',
  4          credential_name =>'OCI$RESOURCE_PRINCIPAL',
  5          file_uri_list =>'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/ALERTLOG/o/alertlog*.parquet',
  6          format =>  '{"type":"parquet", "schema": "first"}'
  7      );
  8  END;
  9* /

PL/SQLプロシージャが正常に完了しました。

SQL>
```

作成した外部表alertlog_ext_tblとv$diag_alert_extのレコード数と内容を比較してみます。
```
SQL> SELECT COUNT(*) FROM alertlog_ext_tbl;

   COUNT(*) 
___________ 
        378 

SQL>
```


```
SQL> SELECT COUNT(*) FROM v$diag_alert_ext;


   COUNT(*) 
___________ 
        378 

SQL> 
```

```
SQL> SELECT * FROM alertlog_ext_tbl
  2  ORDER BY originating_timestamp desc
  3* FETCH FIRST 1 ROW ONLY;

ORIGINATING_TIMESTAMP              NORMALIZED_TIMESTAMP    ORGANIZATION_ID    COMPONENT_ID    HOST_ID    HOST_ADDRESS       MESSAGE_TYPE    MESSAGE_LEVEL MESSAGE_ID                MESSAGE_GROUP    CLIENT_ID    MODULE_ID         PROCESS_ID    THREAD_ID    USER_ID    INSTANCE_ID    DETAILED_LOCATION    UPSTREAM_COMP_ID    DOWNSTREAM_COMP_ID    EXECUTION_CONTEXT_ID       EXECUTION_CONTEXT_SEQUENCE    ERROR_INSTANCE_ID    ERROR_INSTANCE_SEQUENCE MESSAGE_TEXT                                                                                                                                                        MESSAGE_ARGUMENTS    SUPPLEMENTAL_ATTRIBUTES    SUPPLEMENTAL_DETAILS       PARTITION    RECORD_ID FILENAME                                                     LOG_NAME    PROBLEM_KEY       VERSION       CON_UID    CONTAINER_ID CONTAINER_NAME                  CON_ID 
__________________________________ _______________________ __________________ _______________ __________ _______________ _______________ ________________ _________________________ ________________ ____________ _________________ _____________ ____________ __________ ______________ ____________________ ___________________ _____________________ _______________________ _____________________________ ____________________ __________________________ ___________________________________________________________________________________________________________________________________________________________________ ____________________ __________________________ _______________________ ____________ ____________ ____________________________________________________________ ___________ ______________ __________ _____________ _______________ ____________________________ _________ 
01-DEC-23 01.49.33.223000000 AM                            oracle             rdbms                                                    5               16 opiexe:4125:2802784106    admin_ddl                     DBMS_SCHEDULER    20348                                                                                                                                                                   0                    0                          0 Completed: alter pluggable database application all except APP$CDB$PDBONLY$JDCS,APP$CDB$PDBONLY$OLTP,APP$CDB$PDBONLY$DSCS,APP$CDB$APEXPATCH sync from cdb$root                                                                                      700       141059 /u02/app/oracle/diag/rdbms/euk1pod/euk1pod2/alert/log.xml                                        0    2066206261             101 SYA6VPHK3PZLKHQ_EVENTTEST          101 

SQL> 
```

```
SQL> SELECT * FROM v$diag_alert_ext
  2  ORDER BY originating_timestamp desc
  3* FETCH FIRST 1 ROW ONLY;

ORIGINATING_TIMESTAMP                  NORMALIZED_TIMESTAMP    ORGANIZATION_ID    COMPONENT_ID    HOST_ID    HOST_ADDRESS       MESSAGE_TYPE    MESSAGE_LEVEL MESSAGE_ID                MESSAGE_GROUP    CLIENT_ID    MODULE_ID         PROCESS_ID    THREAD_ID    USER_ID    INSTANCE_ID    DETAILED_LOCATION    UPSTREAM_COMP_ID    DOWNSTREAM_COMP_ID    EXECUTION_CONTEXT_ID       EXECUTION_CONTEXT_SEQUENCE    ERROR_INSTANCE_ID    ERROR_INSTANCE_SEQUENCE MESSAGE_TEXT                                                                                                                                                        MESSAGE_ARGUMENTS    SUPPLEMENTAL_ATTRIBUTES    SUPPLEMENTAL_DETAILS       PARTITION    RECORD_ID FILENAME                                                     LOG_NAME    PROBLEM_KEY       VERSION       CON_UID    CONTAINER_ID CONTAINER_NAME                  CON_ID 
______________________________________ _______________________ __________________ _______________ __________ _______________ _______________ ________________ _________________________ ________________ ____________ _________________ _____________ ____________ __________ ______________ ____________________ ___________________ _____________________ _______________________ _____________________________ ____________________ __________________________ ___________________________________________________________________________________________________________________________________________________________________ ____________________ __________________________ _______________________ ____________ ____________ ____________________________________________________________ ___________ ______________ __________ _____________ _______________ ____________________________ _________ 
01-DEC-23 01.49.33.223000000 AM GMT                            oracle             rdbms                                                    5               16 opiexe:4125:2802784106    admin_ddl                     DBMS_SCHEDULER    20348                                                                                                                                                                   0                    0                          0 Completed: alter pluggable database application all except APP$CDB$PDBONLY$JDCS,APP$CDB$PDBONLY$OLTP,APP$CDB$PDBONLY$DSCS,APP$CDB$APEXPATCH sync from cdb$root                                                                                      700       141059 /u02/app/oracle/diag/rdbms/euk1pod/euk1pod2/alert/log.xml                                        0    2066206261             101 SYA6VPHK3PZLKHQ_EVENTTEST          101 

SQL> 
```

外部表alertlog_ext_tblとv$diag_alert_extのレコード数、内容が一致していることが確認できました。

検証のために、Autonomous Databaseを一度停止し、再度起動して、アラートログのエントリを増やします。

v$diag_alert_extのレコード数と内容を確認します。
```
SQL> SELECT COUNT(*) FROM v$diag_alert_ext;

   COUNT(*) 
___________ 
        449 

SQL>
```

```
SQL> SELECT * FROM v$diag_alert_ext
  2  ORDER BY originating_timestamp desc
  3* FETCH FIRST 1 ROW ONLY;

ORIGINATING_TIMESTAMP                  NORMALIZED_TIMESTAMP    ORGANIZATION_ID    COMPONENT_ID    HOST_ID    HOST_ADDRESS       MESSAGE_TYPE    MESSAGE_LEVEL MESSAGE_ID                MESSAGE_GROUP    CLIENT_ID    MODULE_ID         PROCESS_ID    THREAD_ID    USER_ID    INSTANCE_ID    DETAILED_LOCATION    UPSTREAM_COMP_ID    DOWNSTREAM_COMP_ID    EXECUTION_CONTEXT_ID       EXECUTION_CONTEXT_SEQUENCE    ERROR_INSTANCE_ID    ERROR_INSTANCE_SEQUENCE MESSAGE_TEXT                                                                                                                                                        MESSAGE_ARGUMENTS    SUPPLEMENTAL_ATTRIBUTES    SUPPLEMENTAL_DETAILS       PARTITION    RECORD_ID FILENAME                                                     LOG_NAME    PROBLEM_KEY       VERSION       CON_UID    CONTAINER_ID CONTAINER_NAME                  CON_ID 
______________________________________ _______________________ __________________ _______________ __________ _______________ _______________ ________________ _________________________ ________________ ____________ _________________ _____________ ____________ __________ ______________ ____________________ ___________________ _____________________ _______________________ _____________________________ ____________________ __________________________ ___________________________________________________________________________________________________________________________________________________________________ ____________________ __________________________ _______________________ ____________ ____________ ____________________________________________________________ ___________ ______________ __________ _____________ _______________ ____________________________ _________ 
01-DEC-23 03.07.16.000000000 AM GMT                            oracle             rdbms                                                    5               16 opiexe:4125:2802784106    admin_ddl                     DBMS_SCHEDULER    395274                                                                                                                                                                  0                    0                          0 Completed: alter pluggable database application all except APP$CDB$PDBONLY$JDCS,APP$CDB$PDBONLY$OLTP,APP$CDB$PDBONLY$DSCS,APP$CDB$APEXPATCH sync from cdb$root                                                                                      700       141820 /u02/app/oracle/diag/rdbms/euk1pod/euk1pod2/alert/log.xml                                        0    2066206261             101 SYA6VPHK3PZLKHQ_EVENTTEST          101 

SQL>
```
パイプラインALERTLOG_EXP_PIPEの2回目の実行時刻（初回実行時刻の10分後）が過ぎるまで待ちます。
```
SQL> SELECT systimestamp FROM dual;

SYSTIMESTAMP                           
______________________________________ 
01-DEC-23 03.11.10.346518000 AM GMT    

SQL>
```
user_cloud_pipeline_historyビューで、ALERTLOG_EXP_PIPEの実行状況を確認します。
```
SQL> SELECT pipeline_name, start_date, end_date, status, error_message
  2  FROM user_cloud_pipeline_history      
  3  WHERE pipeline_name = 'ALERTLOG_EXP_PIPE'
  4* ORDER BY start_date;

PIPELINE_NAME        START_DATE                             END_DATE                               STATUS       ERROR_MESSAGE    
____________________ ______________________________________ ______________________________________ ____________ ________________ 
ALERTLOG_EXP_PIPE    01-DEC-23 03.00.00.033173000 AM GMT    01-DEC-23 03.00.10.033173000 AM GMT    SUCCEEDED                     
ALERTLOG_EXP_PIPE    01-DEC-23 03.10.00.066344000 AM GMT    01-DEC-23 03.10.10.066344000 AM GMT    SUCCEEDED                     

SQL> 
```
設定した通り、初回実行時刻の10分後にパイプライン・ジョブが実行されていることがわかります。

オブジェクト・ストレージ・バケットの内容を確認してみます。
```
SQL> SELECT object_name, bytes, created 
  2  FROM   DBMS_CLOUD.LIST_OBJECTS(
  3            credential_name => 'OCI$RESOURCE_PRINCIPAL',
  4            location_uri    => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxx/b/ALERTLOG/o/'
  5*        );

OBJECT_NAME                                     BYTES CREATED                                
____________________________________________ ________ ______________________________________ 
alertlog_1_20231201T030001574664Z.parquet       28375 01-DEC-23 03.00.09.630000000 AM GMT    
alertlog_1_20231201T031002012990Z.parquet       15968 01-DEC-23 03.10.10.227000000 AM GMT    
test.txt                                           38 30-NOV-23 08.37.42.880000000 AM GMT    

SQL> 
```
新たにプリフィックスがalertlogとなっているparquetファイルが出力されていることが確認できました。

外部表alertlog_ext_tblのレコード数と内容を比較してみます。

```
SQL> SELECT COUNT(*) FROM alertlog_ext_tbl;

   COUNT(*) 
___________ 
        449 

SQL>
```

```
SQL> SELECT * FROM alertlog_ext_tbl
  2  ORDER BY originating_timestamp desc
  3* FETCH FIRST 1 ROW ONLY;

ORIGINATING_TIMESTAMP              NORMALIZED_TIMESTAMP    ORGANIZATION_ID    COMPONENT_ID    HOST_ID    HOST_ADDRESS       MESSAGE_TYPE    MESSAGE_LEVEL MESSAGE_ID                MESSAGE_GROUP    CLIENT_ID    MODULE_ID         PROCESS_ID    THREAD_ID    USER_ID    INSTANCE_ID    DETAILED_LOCATION    UPSTREAM_COMP_ID    DOWNSTREAM_COMP_ID    EXECUTION_CONTEXT_ID       EXECUTION_CONTEXT_SEQUENCE    ERROR_INSTANCE_ID    ERROR_INSTANCE_SEQUENCE MESSAGE_TEXT                                                                                                                                                        MESSAGE_ARGUMENTS    SUPPLEMENTAL_ATTRIBUTES    SUPPLEMENTAL_DETAILS       PARTITION    RECORD_ID FILENAME                                                     LOG_NAME    PROBLEM_KEY       VERSION       CON_UID    CONTAINER_ID CONTAINER_NAME                  CON_ID 
__________________________________ _______________________ __________________ _______________ __________ _______________ _______________ ________________ _________________________ ________________ ____________ _________________ _____________ ____________ __________ ______________ ____________________ ___________________ _____________________ _______________________ _____________________________ ____________________ __________________________ ___________________________________________________________________________________________________________________________________________________________________ ____________________ __________________________ _______________________ ____________ ____________ ____________________________________________________________ ___________ ______________ __________ _____________ _______________ ____________________________ _________ 
01-DEC-23 03.07.16.000000000 AM                            oracle             rdbms                                                    5               16 opiexe:4125:2802784106    admin_ddl                     DBMS_SCHEDULER    395274                                                                                                                                                                  0                    0                          0 Completed: alter pluggable database application all except APP$CDB$PDBONLY$JDCS,APP$CDB$PDBONLY$OLTP,APP$CDB$PDBONLY$DSCS,APP$CDB$APEXPATCH sync from cdb$root                                                                                      700       141820 /u02/app/oracle/diag/rdbms/euk1pod/euk1pod2/alert/log.xml                                        0    2066206261             101 SYA6VPHK3PZLKHQ_EVENTTEST          101 

SQL>
```

外部表alertlog_ext_tblとv$diag_alert_extのレコード数、内容が一致していることが確認できました。

以上で、DBMS_CLOUD_PIPELINEを使用してAutonomous Databaseのアラートログを一定間隔でオブジェクト・ストレージに出力できることが確認できました。

# 参考情報
・[DBMS_CLOUD_PIPELINEパッケージ](https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/autonomous-pipeline.html)
・[Query External Data with ORC, Parquet, or Avro Source Files](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/query-external-parquet-avro.html#GUID-13E7037B-B772-4D34-ADBC-2504FB9B657D)