はじめに

Autonomous Databaseをご利用予定のお客様から、
「コスト削減のために、毎朝Autonomous Databaseを自動起動してDBMS_SCHEDULERでジョブを自動実行し、ジョブ終了後にAutonomous Databaseを自動停止したい」
という要望を伺いました。

そこで、毎朝の自動起動はAutonomous Databaseの持つスケジュールの自動起動の機能を利用し、ジョブ処理終了後の自動停止の際にはPL/SQL SDKを使用してAutonomoous Databaseに自分自身を停止させればよいのではないかと考えました。

ということで、Autonomous Databaseが自分自身を停止するPL/SQLプロシージャを書いてみました。

1.Autonomous Databaseの情報の取得

PL/SQL SDKにはAutonomous Databaseを停止するためのファンクションDBMS_CLOUD_OCI_DB_DATABASE.STOP_AUTONOMOUS_DATABASEが用意されています。
こちらのファンクションは、パラメータとして以下の値を渡す必要があります。

パラメータ	値
region	停止対象のAutonomous Databaseが存在するリージョンの識別子
autonomous_database_id	停止対象のAutonomous DatabaseのOCID

これらの値は、v$pdbsビューのcloud_identity列から取得できます。

実際にSQLを実行して、v$pdbsのcloud_identity列の内容を確認してみます。

SQL> set pagesize 100
SQL> SELECT cloud_identity FROM v$pdbs;

CLOUD_IDENTITY
--------------------------------------------------------------------------------
{
  "DATABASE_NAME" : "FREEATP",
  "REGION" : "ap-tokyo-1",
  "TENANT_OCID" : "OCID1.TENANCY.OC1..AAAXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXAA",
  "DATABASE_OCID" : "OCID1.AUTONOMOUSDATABASE.OC1.AP-TOKYO-1.ANXXXXXXXXXXXXXXXXXXXXXXXXXXXXHQQ",
  "COMPARTMENT_OCID" : "ocid1.compartment.oc1..aaaaxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxpmq",
  "OUTBOUND_IP_ADDRESS" :
  [
    "xxx.xxx.xxx.xxx"
  ],
  "PUBLIC_DOMAIN_NAME" : "adb.ap-tokyo-1.oraclecloud.com",
  "TENANT_ACCOUNT_NAME" : "xxxxxx",
  "AUTOSCALABLE_STORAGE" : false,
  "BASE_SIZE" : 21474836480,
  "INFRASTRUCTURE" : "Shared",
  "SERVICE" : "ATP",
  "APPLICATIONS" :
  [
    "GRAPH",
    "ODI",
    "ORDS",
    "OMLMOD",
    "OML"
  ],
  "AVAILABILITY_DOMAIN" : "AD-1"
}

SQL>

このように、Autonomous Databaseの情報がJSON形式で返されますので、それぞれのパラメータは以下のような形で取得できます。

パラメータ	値
region	json_value(cloud_identity, '$.REGION')
autonomous_database_id	LOWER(json_value(cloud_identity, '$.DATABASE_OCID')

autonomous_database_idは小文字で指定する必要があるため、LOWER関数を使用して取得した文字列を小文字に変換しています。

2. PL/SQLプロシージャの作成

上記の方法でリージョン、OCIDを取得し、そちらを使用してAutonomous Database自身を停止するPL/SQLプロシージャstop_this_adbを作成します。
ここでは認証にリソース・プリンシパルを使用していますので、credential_nameにはOCI$RESOURCE_PRINCEPALを指定しています。

CREATE OR REPLACE PROCEDURE stop_this_adb
IS
	response    dbms_cloud_oci_db_database_stop_autonomous_database_response_t;
	adb_region  VARCHAR2(20);
	adb_ocid    VARCHAR2(100);

BEGIN
	SELECT json_value(cloud_identity, '$.REGION'), LOWER(json_value(cloud_identity, '$.DATABASE_OCID'))
	INTO adb_region, adb_ocid FROM v$pdbs;

	response := DBMS_CLOUD_OCI_DB_DATABASE.STOP_AUTONOMOUS_DATABASE (
		region => adb_region,
		autonomous_database_id => adb_ocid,
		credential_name => 'OCI$RESOURCE_PRINCIPAL'
	);
END;
/

プロシージャを作成します。

SQL> CREATE OR REPLACE PROCEDURE stop_this_adb
  2  IS
  3  	response    dbms_cloud_oci_db_database_stop_autonomous_database_response_t;
  4  	adb_region  VARCHAR2(20);
  5  	adb_ocid    VARCHAR2(100);
  6  
  7  BEGIN
  8  	SELECT json_value(cloud_identity, '$.REGION'), LOWER(json_value(cloud_identity, '$.DATABASE_OCID'))
  9  	INTO adb_region, adb_ocid FROM v$pdbs;
 10  
 11  	response := DBMS_CLOUD_OCI_DB_DATABASE.STOP_AUTONOMOUS_DATABASE (
 12  		region => adb_region,
 13  		autonomous_database_id => adb_ocid,
 14  		credential_name => 'OCI$RESOURCE_PRINCIPAL'
 15  	);
 16  END;
 17  /

プロシージャが作成されました。

SQL>

問題なくプロシージャstop_this_adbが作成できました。

3. 動作確認

OCIコンソールで、Autonomous Databaseの状態が「使用可能」になっていることを確認します。

プロシージャstop_this_adbを実行します。

SQL> EXEC stop_this_adb

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

SQL>

再度、OCIコンソールでAutonomous Databaseの状態を確認します。

Autonomous Databaseの状態が「停止中」に変わりました。

しばらく待つと、Autonomous Databaseの状態が「停止済」に変わりました。

まとめ

PL/SQL SDKを使用して、Autonomous Databaseに自分自身を停止させるプロシージャを作成することができました。
こちらのプロシージャは、固定値を一切埋め込んでおらず、パラメータをv$pdbsから取得していますので、リソース・プリンシパルが有効になっているAutonomous Databaseでは汎用的に利用できるものになっています。

#参考資料
・Autonomous Database：テナンシ詳細の取得
・STOP_AUTONOMOUS_DATABASE Function
・DBMS_CLOUD_OCI_DB_DATABASE_STOP_AUTONOMOUS_DATABASE_RESPONSE_T Type