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

12176 ワード
oci autonomous_database alertlog oracle oraclecloud oracle テキストリンク

はじめに

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

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

なお、今回使用したAutonomous Databaseは、以下のSQLでDBTIMEZONEを変更しています。

ALTER DATABASE SET TIME_ZONE='+09:00';

＜参考記事＞

1.電子メール配信サービスの準備

コンソールメニューから「電子メール配信」に移動します。

SMTP送信情報が表示されるので、メモしておきます。

画面左の「Approved Senders」をクリックします。

今回Autonmous Databaseから送信するメールの送信者のメールアドレスを入力し、「承認済送信者の作成」をクリックします。

承認済送信者が作成されました。

作成した承認済み送信者の右端のメニューから「SPFの表示」を選択します。

送信元に対応したSPFレコードをコピーします。

コピーしたSPFレコードをDNSサーバのゾーンに追加します。
※ここでは、OCIのDNSを使用している場合の手順を示します。
コンソールメニューからDNS管理の「ゾーン」に移動します。

ゾーン名をクリックします。

リソース欄の「レコード」をクリックします。

「レコードの追加」をクリックします。

以下のように入力して、「送信」をクリックします。
レコード型：TXT -テキスト
TTL：30秒
RDATAモード：基本
TEXT（テキスト）：先ほどコピーしたSPFレコード

SPFレコードがゾーンに追加されました。

「変更の公開」をクリックして、ゾーンの変更を公開します。

2.SMTP資格証明の作成

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

OCIコンソールでユーザの詳細画面を表示します。

リソース欄の「SMTP資格証明」をクリックします。

「SMTP資格証明の作成」をクリックします。

説明が必須となっているので、用途等の説明を入力します。

SMTP資格証明のユーザ名とパスワードをコピーしてメモしておきます。
「閉じる」をクリックすると2度と表示されないので注意してください。

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) 	:= '[email protected]';
	-- mailhostに電子メール配信のパブリック・エンドポイントをセット
	mailhost 		VARCHAR2(50) 	:= 'smtp.email.ap-tokyo-1.oci.oraclecloud.com';
	-- msg_toに送信先メールアドレスをセット
	msg_to			VARCHAR2(50) 	:= '[email protected]';
	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>

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

想定した内容のメールが送信されたか、メールボックスを確認してみます。

想定通りのメールが送信されたことが確認できました。

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 電子メール配信
Oracle Cloud Infrastructure DNS
Autonomous Databaseでの電子メール配信を使用したメールの送信
DBMS_NETWORK_ACL_ADMINパッケージ
UTL_SMTPパッケージ