LoginSignup
2
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@500InternalServerErrorin日本オラクル株式会社

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

Last updated at Posted at 2022-02-27

はじめに

以前のこちらの記事では、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.電子メール配信サービスの準備

コンソールメニューから「電子メール配信」に移動します。
スクリーンショット 2022-02-27 17.39.38.png
SMTP送信情報が表示されるので、メモしておきます。
スクリーンショット 2022-02-27 17.40.15.png
画面左の「Approved Senders」をクリックします。
スクリーンショット 2022-02-27 17.40.32.png
今回Autonmous Databaseから送信するメールの送信者のメールアドレスを入力し、「承認済送信者の作成」をクリックします。
スクリーンショット 2022-02-27 17.41.40.png
承認済送信者が作成されました。
スクリーンショット 2022-02-27 17.42.20.png
作成した承認済み送信者の右端のメニューから「SPFの表示」を選択します。
スクリーンショット 2022-02-27 18.19.58.png
送信元に対応したSPFレコードをコピーします。
スクリーンショット 2022-02-27 18.22.01.png

コピーしたSPFレコードをDNSサーバのゾーンに追加します。
※ここでは、OCIのDNSを使用している場合の手順を示します。
コンソールメニューからDNS管理の「ゾーン」に移動します。
スクリーンショット 2022-02-27 18.23.21.png
ゾーン名をクリックします。
スクリーンショット 2022-02-27 18.23.57.png
リソース欄の「レコード」をクリックします。
スクリーンショット 2022-02-27 18.24.24.png
「レコードの追加」をクリックします。
スクリーンショット 2022-02-27 18.24.51.png
以下のように入力して、「送信」をクリックします。
レコード型：TXT -テキスト
TTL：30秒
RDATAモード：基本
TEXT（テキスト）：先ほどコピーしたSPFレコード
スクリーンショット 2022-02-27 18.25.38.png
SPFレコードがゾーンに追加されました。
スクリーンショット 2022-02-27 18.26.46-1.png
「変更の公開」をクリックして、ゾーンの変更を公開します。
スクリーンショット 2022-02-27 18.26.46-2.png

2.SMTP資格証明の作成

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

OCIコンソールでユーザの詳細画面を表示します。
スクリーンショット 2022-02-27 17.34.48.png
リソース欄の「SMTP資格証明」をクリックします。
スクリーンショット 2022-02-27 17.35.41.png
「SMTP資格証明の作成」をクリックします。
スクリーンショット 2022-02-27 17.36.13.png
説明が必須となっているので、用途等の説明を入力します。
スクリーンショット 2022-02-27 17.37.25.png
SMTP資格証明のユーザ名とパスワードをコピーしてメモしておきます。
「閉じる」をクリックすると2度と表示されないので注意してください。
スクリーンショット 2022-02-27 17.37.37.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
想定通りのメールが送信されたことが確認できました。

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パッケージ

2
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?