この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは。遅れてきたAWS新人、@yokatsukiです。
先日お客様より「Amazon RDS for Oracle(以下RDS Oracle)上で、負荷の高いSQLを投げてくるユーザがいる。セッションを強制切断したいのだがどうすればいいのか」という質問を頂きました。こちらに対応した内容をまとめたものです。
一般的な対処法
一般的なOracleのユーザセッション切断方法は、障害となっているセッションのセッション識別子(sid)と、セッションのオブジェクトを一意に識別するために使用されるセッション・シリアル番号(serial#)をデータ・ディクショナリより求め、ALTER SYSTEM KILL SESSION文を使います。
例えば、sid=500, serial#=3000の場合、以下のSQL文をデータベース管理者(SYSやSYSTEMユーザ)で発行します。
ALTER SYSTEM KILL SESSION '500, 3000';しかし、Oracle RDS環境では残念ながらORA-01031エラーが発生してしまいます。
ERROR at line 1:
ORA-01031: insufficient privilegesデータベース管理者なのに権限が足りないとは衝撃なのですが、代わりにどのような対応方法があるのか調べてみました。
【注意】本エントリでは、負荷の高いSQLを抽出する方法については触れません。
負荷の高いSQLを抽出する方法については、以下エントリ等が参考になるでしょう。
検証
使用環境
- RDS環境
- Amazon RDS for Oracle SE One 11.2.0.2.v7(license-included)
- ローカル環境
- Oracle Enterprise Edition 11.2.0.1.0 on Windows 7(OTN開発者ライセンス) ※本当は同じStandard Editionで確認すべきなんでしょうけどね…すみません
権限の比較
手元のOracleインスタンスからOracle RDSへのネットサービス設定、およびOracle RDS Master Userへのデータベース・リンクをMYRDSとして作成した後に、権限を比較する以下SQL文を実行してみました。
SELECT privilege FROM session_privs <-通常のOracle管理者SYSの権限
MINUS
SELECT privilege FROM session_privs@MYRDS; <-RDS Master Userの権限すると、以下権限がMaster Userに与えられていないことがわかります。
| PRIVILEGE |
|---|
| ALTER DATABASE |
| ALTER DATABASE LINK |
| ALTER PUBLIC DATABASE LINK |
| ALTER SYSTEM |
| CREATE ANY DIRECTORY |
| DROP ANY DIRECTORY |
| EXEMPT ACCESS POLICY |
| EXEMPT IDENTITY POLICY |
| GRANT ANY PRIVILEGE |
| GRANT ANY ROLE |
| SYSDBA |
| SYSOPER |
※繰り返しますが、本当は同じStandard Editionで確認すべきなんでしょうけどね…
ご存知の方も多いと思いますが、Oracleに限らずRDSはその性質上、データベースが動作している仮想マシンへのOSレベルでの操作を禁止しています。よってOracleインスタンス(メモリ設定)やデータベースファイルへの直接操作を防ぐ為に、ALTER SYSTEMやALTER DATABASE文の操作を封じている、という訳です。 ついでに触れると、RDSはOSと独立したインスタンスの起動停止も認めていないので、Oracleインスタンスの起動停止を可能にする権限 SYSDBAやSYSOPERも与えていないんですね。
RDS提供のPL/SQLパッケージ
じゃあどうすればいいのさ？ということでドキュメントを調べてみたところ、Appendixに代替となるPL/SQLパッケージおよびプロシージャの説明が記載されていました。
Killing a Session : Amazon Relational Database Service User Guide (API Version 2013-09-09)
書式は以下の通りです。
| Oracle Method | Amazon RDS Method |
|---|---|
| alter system kill session ' sid, serial#' IMMEDIATE; | exec rdsadmin.rdsadmin_util.kill(sid, serial#);
(11.2.0.3.v1 or higher) exec rdsadmin_util.kill(sid number, serial number, method varchar default null); |
Master UserでこちらのPL/SQLプロシージャを、データベース接続ユーザTESTに対して実行してみました。
SELECT sid, serial#, username
FROM v$session
WHERE username = 'TEST';
SID SERIAL# USERNAME
------ ---------- --------
71 51703 TEST
exec rdsadmin.rdsadmin_util.kill(71, 51703);
PL/SQL procedure successfully completed.TESTユーザは、セッションを切られた後に任意のSQL文を実行すると、以下のエラーを確認することになります。
ORA-00028: your session has been killed確かに、セッションが切られています。
あとがき
Oracle RDSのセッション切断方法として、RDS専用のPL/SQLパッケージを使用する方法を確認しました。 これ以外にもALTER SYSTEMやALTER DATABASE文で実行する管理操作、例えば
- 共有プールやデータベースバッファキャッシュのフラッシュ
- デフォルト表領域の変更
- チェックポイントの強制やREDOログファイルの強制スイッチ
等がPL/SQLプロシージャとして提供されているので、是非ドキュメントをご覧になってください。
5
