[OCI]Autonomous Databaseが毎日決まった時間に1日分のアラートログをメールで自動送信するようにしてみた

# はじめに

以前のこちらの記事では、OCIの通知サービスを使用してAutonomous Databaseからメールを送信しましたが、SQLの実行結果の内容が4,000バイトまでという制限がありました。

https://qiita.com/500InternalServerError/items/1596125e114ee88a1c87

そこで、今回はOCIの電子メール配信サービスとUTL_SMTPパッケージを使用して、SQLの実行結果が4000バイトを超える場合でも問題なくメールを送信できるようなPL/SQLプロシージャを作成しました。

なお、今回使用したAutonomous Databaseは、以下のSQLでDBTIMEZONEを変更しています。
```
ALTER DATABASE SET TIME_ZONE='+09:00';
```
＜参考記事＞

https://qiita.com/500InternalServerError/items/e3739cd272baaf406ef3


# 1.電子メール配信サービスの準備
コンソールメニューから「電子メール配信」に移動します。
![スクリーンショット 2022-02-27 17.39.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/96111b00-4bd7-e23a-b16a-0d195b982169.png)
SMTP送信情報が表示されるので、メモしておきます。
![スクリーンショット 2022-02-27 17.40.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/ee3b0833-1fa0-7549-89af-4b76949cd7d5.png)
画面左の「Approved Senders」をクリックします。
![スクリーンショット 2022-02-27 17.40.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/fce6f0c6-8abc-ec53-b4f6-bf63f9eab591.png)
今回Autonmous Databaseから送信するメールの送信者のメールアドレスを入力し、「承認済送信者の作成」をクリックします。
![スクリーンショット 2022-02-27 17.41.40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/74de8e2e-57da-f78f-7039-1e8c39e3c285.png)
承認済送信者が作成されました。
![スクリーンショット 2022-02-27 17.42.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/dd1ced12-aa0b-fdea-fe7e-16431e96ffa7.png)
作成した承認済み送信者の右端のメニューから「SPFの表示」を選択します。
![スクリーンショット 2022-02-27 18.19.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/470cb3f0-674c-43b5-4935-89b2155bb883.png)
送信元に対応したSPFレコードをコピーします。
![スクリーンショット 2022-02-27 18.22.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/fc9326bc-40f2-7cce-0019-24372e35e2bf.png)

コピーしたSPFレコードをDNSサーバのゾーンに追加します。
※ここでは、OCIのDNSを使用している場合の手順を示します。
コンソールメニューからDNS管理の「ゾーン」に移動します。
![スクリーンショット 2022-02-27 18.23.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/9641d470-0c6e-c205-3be3-f5c4fb3c9fe0.png)
ゾーン名をクリックします。
![スクリーンショット 2022-02-27 18.23.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/133d240a-42bb-bc7a-72cb-62587c234145.png)
リソース欄の「レコード」をクリックします。
![スクリーンショット 2022-02-27 18.24.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/b3bc3967-a264-777c-a580-21f6de2be7fb.png)
「レコードの追加」をクリックします。
![スクリーンショット 2022-02-27 18.24.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/98b4dec2-7020-b93e-914f-0d33359ef1ea.png)
以下のように入力して、「送信」をクリックします。
レコード型：TXT -テキスト
TTL：30秒
RDATAモード：基本
TEXT（テキスト）：先ほどコピーしたSPFレコード
![スクリーンショット 2022-02-27 18.25.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/5f709833-fa39-0147-2890-cf18157d422e.png)
SPFレコードがゾーンに追加されました。
![スクリーンショット 2022-02-27 18.26.46-1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/2352b421-1c38-2c60-dc18-079777a7aa45.png)
「変更の公開」をクリックして、ゾーンの変更を公開します。
![スクリーンショット 2022-02-27 18.26.46-2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/40bdcd0b-7628-68cc-dd4e-04fb0a249966.png)



# 2.SMTP資格証明の作成
電子メール配信サービスでメールを配信する場合は、OCIユーザのSMTP資格証明を使用します。

OCIコンソールでユーザの詳細画面を表示します。
![スクリーンショット 2022-02-27 17.34.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/ccc3c334-480b-f3ad-b4c6-0053507a9ee4.png)
リソース欄の「SMTP資格証明」をクリックします。
![スクリーンショット 2022-02-27 17.35.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/fdc8c474-b839-da85-11fc-7a23d6acb4a1.png)
「SMTP資格証明の作成」をクリックします。
![スクリーンショット 2022-02-27 17.36.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/15565091-477b-8bea-838c-463ffa2c5eea.png)
説明が必須となっているので、用途等の説明を入力します。
![スクリーンショット 2022-02-27 17.37.25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/e1f5cf2e-eaa8-4830-4c42-1284da50a3d0.png)
SMTP資格証明のユーザ名とパスワードをコピーしてメモしておきます。
「閉じる」をクリックすると2度と表示されないので注意してください。
![スクリーンショット 2022-02-27 17.37.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/e81a43f1-7f31-dc07-5852-64ae9bc7c8df.png)



# 3.メール配信を行うDBユーザへの権限付与
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACEプロシージャ を使用して、メール配信を実行するDBユーザ（今回はadmin）に対して、電子メール配信サービスのエンドポイントの587番ポートにSMTP接続する権限を付与します。

```
SQL> BEGIN
  2  	DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  3  		host => 'smtp.email.ap-tokyo-1.oci.oraclecloud.com',
  4  		lower_port => 587,
  5  		upper_port => 587,
  6  		ace => xs$ace_type( privilege_list => xs$name_list('SMTP'),
  7  							principal_name => 'ADMIN',
  8  							principal_type => xs_acl.ptype_db)
  9  	);
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
```

# 4.UTL_SMTPを使用してメールを送信するPL/SQLプロシージャの作成
今回は、プロシージャ実行時から過去1日分のAutonomous Databaseのアラートログ（V$DIAG_ALERT_EXT）を検索し、検索結果をメールで送信するPl/SQLプロシージャsend_alertlogを作成しました。

なお、SMTP資格証明のユーザ名、パスワードに"?"が含まれる場合は、プロシージャ作成前に
```
set define off
```
を実行して、SQL*Plusの置換変数を無効化してきます。
```
CREATE OR REPLACE PROCEDURE SEND_ALERTLOG 
IS
	mail_conn UTL_SMTP.CONNECTION;
	-- usernameにSMTP資格証明のユーザ名をセット
	username 		VARCHAR2(1000)	:= 'ocid1.user.oc1..xxxxxxxxxxxxxxxxxxxxxxxx@ocid1.tenancy.oc1..yyyyyyyyyyyyyyyyyyyyy.mg.com';
	-- passwdにSMTP資格証明のパスワードをセット
	passwd 			VARCHAR2(50)	:= 'XXXXXXXXXX';
	-- msg_fromに承認済み送信者のメールアドレスをセット
	msg_from 		VARCHAR2(50) 	:= 'mail@500internalservererror.ga';
	-- mailhostに電子メール配信のパブリック・エンドポイントをセット
	mailhost 		VARCHAR2(50) 	:= 'smtp.email.ap-tokyo-1.oci.oraclecloud.com';
	-- msg_toに送信先メールアドレスをセット
	msg_to			VARCHAR2(50) 	:= 'xxxxxxxxxx@xyz.com';
	msg_subject		VARCHAR2(200);
	msg_text		VARCHAR2(200);
	sql_result		CLOB		 	:= EMPTY_CLOB;
	L_OFFSET    	NUMBER;
	L_AMMOUNT   	NUMBER;

	--送信するメールの本文に結果を記載したいSQL文を記述
	CURSOR result_cur IS 
        SELECT
			TO_CHAR(CAST(originating_timestamp AT TIME ZONE '+9:00' AS DATE),'YYYY-MM-DD HH24:MI:SS') timestamp,
			process_id,
			message_text
		FROM V$DIAG_ALERT_EXT
		WHERE ORIGINATING_TIMESTAMP > SYSDATE - 1;
	result_row          result_cur%ROWTYPE;

BEGIN
	EXECUTE IMMEDIATE 'ALTER SESSION SET SYSDATE_AT_DBTIMEZONE=TRUE';

	msg_subject		:= 'Daily Alertlog Report as of '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS');
	msg_text		:= 'Alert log from '||TO_CHAR(SYSDATE-1, 'YYYY-MM-DD HH24:MI:SS')||' to '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');
	OPEN result_cur;
		LOOP
			FETCH result_cur into result_row;
			EXIT WHEN result_cur%NOTFOUND;
			DBMS_OUTPUT.PUT_LINE(result_row.timestamp||' '||result_row.process_id||' '||result_row.message_text);
			sql_result :=  sql_result||result_row.timestamp||'  '||result_row.process_id||'  '||result_row.message_text||CHR(13) || CHR(10);
		END LOOP;
	CLOSE result_cur;

	mail_conn := UTL_SMTP.OPEN_CONNECTION(mailhost, 587);
	UTL_SMTP.STARTTLS(mail_conn);
	UTL_SMTP.AUTH(mail_conn, username, passwd, schemes => 'PLAIN');
	UTL_SMTP.MAIL(mail_conn, msg_from);
	UTL_SMTP.RCPT(mail_conn, msg_to);
	UTL_SMTP.OPEN_DATA(mail_conn);
	UTL_SMTP.WRITE_DATA(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
	UTL_SMTP.WRITE_DATA(mail_conn, 'To: ' || msg_to || UTL_TCP.crlf);
	UTL_SMTP.WRITE_DATA(mail_conn, 'From: ' || msg_from || UTL_TCP.crlf);
	UTL_SMTP.WRITE_DATA(mail_conn, 'Subject: ' || msg_subject || UTL_TCP.crlf);
	UTL_SMTP.WRITE_DATA(mail_conn, 'Reply-To: ' || msg_from || UTL_TCP.crlf || UTL_TCP.crlf);

	UTL_SMTP.WRITE_DATA(mail_conn, msg_text  || UTL_TCP.crlf || UTL_TCP.crlf);

	IF sql_result IS NOT NULL THEN
		L_OFFSET  := 1;
		L_AMMOUNT := 1900;

		WHILE L_OFFSET < DBMS_LOB.GETLENGTH(sql_result)
		LOOP
			UTL_SMTP.WRITE_DATA(mail_conn, DBMS_LOB.SUBSTR(sql_result,L_AMMOUNT,L_OFFSET));
			L_OFFSET  := L_OFFSET + L_AMMOUNT ;
			L_AMMOUNT := LEAST(1900,DBMS_LOB.GETLENGTH(sql_result) - L_AMMOUNT);
		END LOOP;
		UTL_SMTP.WRITE_DATA(mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
	END IF;

	UTL_SMTP.CLOSE_DATA(mail_conn);
	UTL_SMTP.QUIT(mail_conn);

EXCEPTION
  WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
    UTL_SMTP.QUIT(mail_conn);
    DBMS_OUTPUT.PUT_LINE(sqlerrm);
  WHEN OTHERS THEN
    UTL_SMTP.QUIT(mail_conn);
    DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/
```

# 5.PL/SQLプロシージャの動作確認

```
SQL> exec send_alertlog;
2022-02-26 20:15:27 87599 KZAM_LAF: Loading spillover files of current instance
only

2022-02-27 09:12:34 49096 TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED
INTERVAL PARTITION SYS_P2300 (44618) VALUES LESS THAN (TO_DATE(' 2022-02-28
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

＜略＞

2022-02-27 16:01:58 22023 alter pluggable database application all except
APP$CDB$PDBONLY$DWCS,APP$CDB$PDBONLY$JDCS,APP$CDB$PDBONLY$DSCS sync from
cdb$root 

2022-02-27 16:01:59 22023 Completed: alter pluggable database application all
except APP$CDB$PDBONLY$DWCS,APP$CDB$PDBONLY$JDCS,APP$CDB$PDBONLY$DSCS sync from
cdb$root 


PL/SQL procedure successfully completed.

SQL> 
```
問題なく実行できたようです。

想定した内容のメールが送信されたか、メールボックスを確認してみます。
![スクリーンショット 2022-02-27 19.35.03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/4c4c22f1-7586-f786-3bf1-ae5e0e186b03.png)
想定通りのメールが送信されたことが確認できました。

# 6.作成したストアド・プロシージャの自動実行
作成したストアド・プロシージャ"send_alertlog"を毎朝8時に自動実行してメールを送信するように、DBMS_SCHEDULERのジョブ"send_alertlog_job_job"を作成します。

```
SQL> BEGIN
  2  	DBMS_SCHEDULER.CREATE_JOB (
  3  	job_name           =>  'SEND_ALERTLOG_JOB',
  4  	job_type           =>  'STORED_PROCEDURE',
  5  	job_action         =>  'ADMIN.SEND_ALERTLOG', 
  6  	start_date         =>  TO_TIMESTAMP_TZ('2022-02-28 08:00:00 +9:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'),
  7  	repeat_interval    =>  'FREQ=DAILY;INTERVAL=1',
  8  	auto_drop          =>  FALSE,
  9  	enabled            =>  TRUE
 10    	);
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL>
```
以上で、Autonomous Databaseが毎朝8時に自動的に過去1日分のアラートログの内容ををメールで送信するように設定できました。

# 参考
[Oracle Cloud Infrastructure 電子メール配信](https://docs.oracle.com/ja-jp/iaas/Content/Email/home.htm)
[Oracle Cloud Infrastructure DNS](https://docs.oracle.com/ja-jp/iaas/Content/DNS/home.htm)
[Autonomous Databaseでの電子メール配信を使用したメールの送信](https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/smtp-send-mail.html#GUID-CBBA9AD1-2FE2-418D-B155-CAB7C61E176E)
[DBMS_NETWORK_ACL_ADMINパッケージ](https://docs.oracle.com/cd/F19136_01/arpls/DBMS_NETWORK_ACL_ADMIN.html#GUID-254AE700-B355-4EBC-84B2-8EE32011E692)
[UTL_SMTPパッケージ](https://docs.oracle.com/cd/F19136_01/arpls/UTL_SMTP.html)