Autonomous Database：直近のスナップショット間隔におけるAWRレポートをObject Storageに出力するプロシージャを作成してみた

# はじめに

Autonomous Databaseでは、デフォルトで1時間おきにAWRスナップショットが取得されています。
そこで今回は、直近のスナップショット間隔（1時間）におけるAWRレポートを生成してObject Storageに出力するPL/SQLプロシージャを作成してみました。

なお、こちらの記事の内容は執筆時点のものであり、将来まで動作を保証するものではありません。実環境で使用される場合は、エラー・ハンドリング等を実装した上で充分に検証を行なってください。また、こちらの記事の内容に関するOracle Supportへの問い合わせ等はご遠慮ください。

# 1.事前準備
以下のSQLで現在接続しているDBインスタンスのスナップIDとスナップショット時間間隔の開始時刻および終了時刻を確認できます。

```
SQL> col BEGIN_INTERVAL_TIME for a30
SQL> col END_INTERVAL_TIME for a30
SQL> 
SQL> SELECT * FROM
  2  (
  3  	 SELECT snap_id, begin_interval_time, end_interval_time
  4  	 FROM	dba_hist_snapshot
  5  	 WHERE	dbid = (SELECT dbid FROM v$database)
  6  	 AND	instance_number = (SELECT instance_number FROM v$instance)
  7  	 ORDER BY snap_id DESC
  8  )
  9  WHERE rownum < 11;

   SNAP_ID BEGIN_INTERVAL_TIME		      END_INTERVAL_TIME
---------- ------------------------------ ------------------------------
     30124 19-JUL-24 03.00.05.115 AM	  19-JUL-24 04.00.07.042 AM
     30123 19-JUL-24 02.00.29.456 AM	  19-JUL-24 03.00.05.115 AM
     30122 19-JUL-24 01.00.05.874 AM	  19-JUL-24 02.00.29.456 AM
     30121 19-JUL-24 12.00.02.999 AM	  19-JUL-24 01.00.05.874 AM
     30120 18-JUL-24 11.00.15.620 PM	  19-JUL-24 12.00.02.999 AM
     30119 18-JUL-24 10.00.09.012 PM	  18-JUL-24 11.00.15.620 PM
     30118 18-JUL-24 09.00.20.583 PM	  18-JUL-24 10.00.09.012 PM
     30117 18-JUL-24 08.00.07.275 PM	  18-JUL-24 09.00.20.583 PM
     30116 18-JUL-24 07.00.03.743 PM	  18-JUL-24 08.00.07.275 PM
     30115 18-JUL-24 06.00.26.881 PM	  18-JUL-24 07.00.03.743 PM

10 rows selected.

SQL>
```
AWRレポートを格納するObject Storageバケットを作成します。
ここではAWR_REPORTという名前のバケットを作成しました。

![スクリーンショット 2024-07-19 13.21.59.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/9dd14b21-4914-6f30-04c3-f0554e07fd47.png)

また、ここではObject Storageバケットにアクセスする際の認証にリソース・プリンシパルを使用しています。
リソース・プリンシパルの詳細については、こちらのマニュアルをご参照ください。

https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/serverless/adbsb/resource-principal.html


# 2.AWRレポートを取得してObject Storageバケットに出力するプロシージャの作成

準備ができましたので、最新の2つのAWRスナップショット間のAWRレポートを取得してObject Storageバケットに出力するプロシージャを作成します。

ここでは、get_awr_reportという名前のプロシージャを作成しました。
get_awr_reportプロシージャは、パラメータにHTMLを指定するとHTML形式でAWRレポートを出力し、TEXTを指定するとテキスト形式で出力します。パラメータにHTML、TEXT以外が指定された場合はエラーメッセージを表示して終了します。
また、変数bucket_uriにAWRレポートを出力するバケットのURIをセットします。

```sql:get_awr_reportプロシージャ
CREATE OR REPLACE PROCEDURE get_awr_report(format IN VARCHAR2 DEFAULT 'HTML')
IS
    db_id NUMBER;
    inst_id NUMBER;
    begin_snap_id NUMBER;
    end_snap_id NUMBER;
    awr_report CLOB;
    extension VARCHAR2(10);
    -- bucket_uriにAWRレポートを格納するバケットのURIを指定
    bucket_uri VARCHAR2(200) := 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/XXXXXXXX/b/AWR_REPORT';

BEGIN
    SELECT dbid INTO db_id FROM v$database; -- DB IDを取得
    SELECT instance_number INTO inst_id FROM v$instance; -- インスタンス番号を取得
    
    -- 最新のスナップショットIDと1つ前のスナップショットIDを取得
    SELECT MAX(snap_id)-1, MAX(snap_id) INTO begin_snap_id, end_snap_id
    FROM   dba_hist_snapshot
    WHERE  dbid = db_id
    AND    instance_number = inst_id;

    -- formatにHTMLが指定された場合、HTML形式でAWRレポートを取得
    IF format = 'HTML'
      THEN
        FOR v_awr IN (SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(db_id,inst_id,begin_snap_id, end_snap_id))) 
        LOOP
            awr_report := awr_report||v_awr.output||CHR(10);
        END LOOP;
        extension := '.html';
    -- formatにTEXTが指定された場合、テキスト形式でAWRレポートを取得
	ELSIF format = 'TEXT'
	  THEN
        FOR v_awr IN (SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(db_id,inst_id,begin_snap_id, end_snap_id))) 
        LOOP
            awr_report := awr_report||v_awr.output||CHR(10);
        END LOOP;
        extension := '.txt';
    -- formatにHTML、TEXT以外が指定された場合、エラーメッセージを出力
	ELSE
        DBMS_OUTPUT.PUT_LINE('Format is invalid.');
        DBMS_OUTPUT.PUT_LINE('Valid formats are HTML or TEXT.');
        RETURN;
	END IF;
 
    -- bucket_uriで指定したバケットにAWRレポートを出力
    DBMS_CLOUD.PUT_OBJECT(
         credential_name => 'OCI$RESOURCE_PRINCIPAL',
         object_uri => bucket_uri||'/o/awr_report_'||begin_snap_id||'_'||end_snap_id||extension,
         contents => APEX_UTIL.CLOB_TO_BLOB(awr_report)
    );
END;
/
```
上記を実行して、PL/SQLプロシージャget_awr_reportを作成します。
```
SQL> CREATE OR REPLACE PROCEDURE get_awr_report(format IN VARCHAR2 DEFAULT 'HTML')
  2  IS
  3  	 db_id NUMBER;
  4  	 inst_id NUMBER;
  5  	 begin_snap_id NUMBER;
  6  	 end_snap_id NUMBER;
  7  	 awr_report CLOB;
  8  	 extension VARCHAR2(10);
  9  	 -- bucket_uriにAWRレポートを格納するバケットのURIを指定
 10  	 bucket_uri VARCHAR2(200) := 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/XXXXXXXX/b/AWR_REPORT';
 11  
 12  BEGIN
 13  	 SELECT dbid INTO db_id FROM v$database; -- DB IDを取得
 14  	 SELECT instance_number INTO inst_id FROM v$instance; -- インスタンス番号を取得
 15  	 -- 最新のスナップショットIDと1つ前のスナップショットIDを取得
 16  	 SELECT MAX(snap_id)-1, MAX(snap_id) INTO begin_snap_id, end_snap_id
 17  	 FROM	dba_hist_snapshot
 18  	 WHERE	dbid = db_id
 19  	 AND	instance_number = inst_id;
 20  	 -- formatにHTMLが指定された場合、HTML形式でAWRレポートを取得
 21  	 IF format = 'HTML'
 22  	   THEN
 23  	     FOR v_awr IN (SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(db_id,inst_id,begin_snap_id, end_snap_id))) LOOP
 24  		 awr_report := awr_report||v_awr.output||CHR(10);
 25  	     END LOOP;
 26  	     extension := '.html';
 27  	 -- formatにTEXTが指定された場合、テキスト形式でAWRレポートを取得
 28  	     ELSIF format = 'TEXT'
 29  	       THEN
 30  	     FOR v_awr IN (SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(db_id,inst_id,begin_snap_id, end_snap_id))) LOOP
 31  		 awr_report := awr_report||v_awr.output||CHR(10);
 32  	     END LOOP;
 33  	     extension := '.txt';
 34  	 -- formatにHTML、TEXT以外が指定された場合、エラーメッセージを出力
 35  	     ELSE
 36  	     DBMS_OUTPUT.PUT_LINE('Format is invalid.');
 37  	     DBMS_OUTPUT.PUT_LINE('Valid formats are HTML or TEXT.');
 38  	     RETURN;
 39  	     END IF;
 40  	 -- bucket_uriで指定したバケットにAWRレポートを出力
 41  	 DBMS_CLOUD.PUT_OBJECT(
 42  	      credential_name => 'OCI$RESOURCE_PRINCIPAL',
 43  	      object_uri => bucket_uri||'/o/awr_report_'||begin_snap_id||'_'||end_snap_id||extension,
 44  	      contents => APEX_UTIL.CLOB_TO_BLOB(awr_report)
 45  	 );
 46  END;
 47  /

Procedure created.

SQL>
```
PL/SQLプロシージャget_awr_reportが問題なく作成できました。

# 3.作成したPL/SQLプロシージャの実行

PL/SQLプロシージャが作成できたので、実行してみます
はじめに、パラメータにHTMLを指定してget_awr_reportプロシージャを実行し、HTML形式でAWRレポートを作成してみます。
```
SQL> set serveroutput on
SQL> EXEC get_awr_report('HTML');

PL/SQL procedure successfully completed.

SQL>
```
get_awr_reportプロシージャが問題なく実行できました。

バケットAWR_REPORTの内容を確認してみます。
![スクリーンショット 2024-07-19 13.23.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/83e17020-8342-1e40-9a09-2a6a63948802.png)

awr_report_30123_30124.htmlというファイルが出力されていることが確認できました。

awr_report_30123_30124.htmlをダウンロードして、ブラウザで開いてみます。

![スクリーンショット 2024-07-19 13.26.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/439066fd-ee99-04fb-5dd7-bd43cbad7951.png)

HTML形式のAWRレポートが表示されました。

次に、パラメータにTEXTを指定してget_awr_reportプロシージャを実行し、テキスト形式でAWRレポートを作成してみます。

```
SQL> set serveroutput on
SQL> EXEC get_awr_report('TEXT');

PL/SQL procedure successfully completed.

SQL>
```
get_awr_reportプロシージャが問題なく実行できました。

バケットAWR_REPORTの内容を確認してみます。

![スクリーンショット 2024-07-19 13.27.34.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/df5c303f-fd26-e34d-93c0-9d7858e852e2.png)

awr_report_30123_30124.txtというファイルが出力されていることが確認できました。

awr_report_30123_30124.txtをダウンロードして、テキストエディタで開いてみます。

![スクリーンショット 2024-07-19 13.30.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/2c054a0c-10f6-70cf-9b36-33ae97084a44.png)

テキスト形式のAWRレポートが表示されました。


なお、パラメータにHTML、TEXT以外を指定すると、以下のようにエラーメッセージが表示されます。
```
SQL> set serveroutput on
SQL> EXEC get_awr_report('AAA');
Format is invalid.
Valid formats are HTML or TEXT.

PL/SQL procedure successfully completed.

SQL>
```

# 4.まとめ
直近のスナップショット間隔におけるAWRレポートをObject Storageに出力するPL/SQLプロシージャを作成することができました。
こちらのPL/SQLプロシージャをDBMS_SCHEDULERで自動実行することにより、AWRレポートを一定間隔でObject Storageバケットに出力することが可能になるかと思います。

# 参考情報
・[DBA_HIST_SNAPSHOTビュー](https://docs.oracle.com/cd/F19136_01/refrn/DBA_HIST_SNAPSHOT.html)
・[V$DATABASEビュー](https://docs.oracle.com/cd/F19136_01/refrn/V-DATABASE.html)
・[V$INSTANCEビュー](https://docs.oracle.com/cd/F19136_01/refrn/V-INSTANCE.html)
・[DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTMLファンクション](https://docs.oracle.com/cd/F19136_01/arpls/DBMS_WORKLOAD_REPOSITORY.html#GUID-46354A31-8D0C-409F-B8C1-0733B29B5B64)
・[DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXTファンクション](https://docs.oracle.com/cd/F19136_01/arpls/DBMS_WORKLOAD_REPOSITORY.html#GUID-8E465B65-1E2B-4E03-A78B-B01744B006C7)
・[DBMS_CLOUD.PUT_OBJECTプロシージャ](https://docs.oracle.com/cd/F19136_01/arpls/DBMS_CLOUD.html#GUID-716F0DE7-C669-477E-8AB8-EA42E41ACB12)