AWRのお掃除 #SQL

はじめに

とあるDBでAWR関連の問題あってAWRデータをクリーンアップしたときの備忘録的なもの、です。

このあたりを参考にしています。

How to Remove Workload Repository Information Related to an old DBID From the Automatic Workload Repository (Doc ID 1251795.1)
How to Recreate the Automatic Workload Repository (AWR)? (Doc ID 782974.1)

テストデータベースの作成

「AWR内の不要なDBIDをクリーンアップするテスト」をするために、まずはRMAN等でクローンデータベースを作るかNIDでDBIDを変更して、意図的に不要DBIDの登録状態を作りだします (^^)。

RMANで簡単にACTIVE DUPLICATEした例

参考：Active Database Duplicateで静的リスナーが不要になっていた件

RMANで複製

$ rman TARGET sys/mysystem@serverA/DB12C AUXILIARY sys/mysystem NOCATALOG

RMAN> STARTUP CLONE NOMOUNT

RMAN> DUPLICATE TARGET DATABASE TO TESTDB FROM ACTIVE DATABASE NOFILENAMECHECK;
...
...
database opened
Finished Duplicate Db at 2019/03/01

NIDでDBIDだけ変更した例

参考： Changing Only the Database ID

NIDでDBIDの変更

$ sqlplus / as sysdba
SQL> SHUTDOWN IMMEDAITE
SQL> STARTUP MOUNT

$ nid TARGET=SYS
Password:
Connected to database TESTDB (DBID=2785434983)
...
...
Database ID for database TESTDB changed to 2785526799.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

$ sqlplus / as sysdba
SQL> STARTUP MOUNT
SQL> ALTER DATABASE OPEN RESETLOGS;

DBIDとAWRの状態確認

まずは、DBA_HIST_WR_CONTROLで登録されているDBIDを確認します。AWRのDBID登録は自動なので、古いDBIDが残ったまま新しいDBIDが登録されています。

SQL> SELECT name, c.dbid, snap_interval, retention FROM dba_hist_wr_control c, v$database d WHERE c.dbid = d.dbid(+);

NAME            DBID SNAP_INTERVAL        RETENTION
--------- ---------- -------------------- --------------------
TESTDB    2785519794 +00000 01:00:00.0    +00008 00:00:00.0
          3899828635 +00000 01:00:00.0    +00008 00:00:00.0     <-- 古いDBID

現在のDBIDでスナップショットを作成してからAWRを覗いてみます。新しいDBIDと古いDBIDのエントリが混在しています。

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
PL/SQL procedure successfully completed.

SQL> SELECT dbid, MIN(snap_id), MAX(snap_id), COUNT(*) FROM sys.WRH$_SYSMETRIC_HISTORY GROUP BY dbid;
      DBID MIN(SNAP_ID) MAX(SNAP_ID)   COUNT(*)
---------- ------------ ------------ ----------
3899828635         7643         7644        460     <-- 古いスナップショット
2785519794            1            1       1173

古いデータを削除しDBIDを登録解除

DBIDを登録解除すると、そのDBIDに関連したデータの削除ができなくなるのでDBID削除の前にデータを削除します。まぁ、手動で削除しなくても期限が来れば削除されるはずですが、問題が起きたときの対処用クリーンナップなのでしっかり削除しておきます。

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(7643, 7644, 3899828635);
PL/SQL procedure successfully completed.

SQL> SELECT dbid, MIN(snap_id), MAX(snap_id), COUNT(*) FROM sys.WRH$_SYSMETRIC_HISTORY GROUP BY dbid;
      DBID MIN(SNAP_ID) MAX(SNAP_ID)   COUNT(*)
---------- ------------ ------------ ----------
2785519794            1            1       1173

さらに、関連データが消去されてもDBID自体は登録されたまま残っているのでこれも削除します。

SQL> SELECT dbid FROM dba_hist_wr_control;
      DBID
----------
2785519794
3899828635

-- AWRを制限モードに設定してデータベースを再起動
SQL> ALTER SYSTEM SET "_AWR_RESTRICT_MODE"=TRUE SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

-- AWR制限モードを確認
SQL> SHOW PARAMETER _AWR_RESTRICT_MODE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_awr_restrict_mode                   boolean     TRUE

-- 古いDBIDの登録解除
SQL> EXEC DBMS_SWRF_INTERNAL.UNREGISTER_DATABASE(3899828635);
PL/SQL procedure successfully completed.

-- AWRモードを解除設定してデータベースを再起動
SQL> ALTER SYSTEM RESET "_AWR_RESTRICT_MODE" SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

-- 登録DBIDの確認
SQL> SELECT dbid FROM dba_hist_wr_control;
      DBID
----------
2785519794

_AWR_RESTRICT_MODEはAWRのスナップショットを作成しないようにする隠しパラメータです。AWRを利用するライセンスがないとかAWRそのものが不要な場合は、これを設定してデータベースのオーバーヘッドを低下させることができます。その他にもAWRやASH等を止める隠しパラメータがいくつかあるのですが、詳しくは以下を参考にしてください。

How To Avoid the AWR Usage Without Having Diagnostic Pack License (Doc ID 2276199.1)

あと、間違って現行のDBIDを登録解除してもデータベース起動時に自動で再登録されるので問題ありません。

SQL> EXEC DBMS_SWRF_INTERNAL.UNREGISTER_DATABASE(2785519794);
PL/SQL procedure successfully completed.

SQL> select dbid from dba_hist_wr_control;
no rows selected

-- ここでデータベースを再起動

SQL> SELECT dbid FROM dba_hist_wr_control;
      DBID
----------
2785519794

AWRの再構築

上記の方法で問題が解決しないときは、AWRそのものを一度削除して再構築します。

まずは、SPFILEのバックアップをとってパラメータを変更します。なんだかいろいろ設定するより単にAWRを無効化すればいいような気もするんですが、オラクルのインストラクションがそのようになっているので、従っておきます。

データベースを制限モードで再起動しますが、12.2の場合は以後実行するスクリプトがエラーになるので、アプリケーションのアクティビティに気をつけながらノーマルモードで起動しておきます。

-- SPFILEのバックアップ
$ cd $ORACLE_HOME/dbs
$ cp -p spfileTESTDB.ora spfileTESTDB.ora.bk
-- または
SQL> CREATE PFILE='pfile.ora' FROM SPFILE;

-- パラメータの変更。エラーは気にしない。
SQL> ALTER SYSTEM SET shared_pool_size = 200m SCOPE = SPFILE;
SQL> ALTER SYSTEM SET db_cache_size = 300m SCOPE = SPFILE;
SQL> ALTER SYSTEM SET java_pool_size = 100m SCOPE = SPFILE;
SQL> ALTER SYSTEM SET large_pool_size = 50m SCOPE = SPFILE;
SQL> ALTER SYSTEM SET cluster_database = FALSE SCOPE = SPFILE;
SQL> ALTER SYSTEM RESET sga_target SCOPE = SPFILE;
SQL> ALTER SYSTEM RESET memory_target SCOPE = SPFILE;
SQL> ALTER SYSTEM RESET memory_max_target SCOPE = SPFILE;

-- 制限モードで再起動 (12.2はstartup)
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP RESTRICT

SQL> ALTER SYSTEM SET statistics_level=basic SCOPE=BOTH;

AWRオブジェクトを一旦削除し再生成します。

-- AWRオブジェクトの削除
SQL> @?/rdbms/admin/catnoawr.sql

-- もしAWR関連テーブルが残っていれば、追加で削除
SQL> SELECT table_name FROM dba_tables WHERE table_name LIKE 'WRM$_%' OR table_name LIKE 'WRH$_%';
TABLE_NAME
--------------------------------------------------------------------------------
WRH$_SYSMETRIC_HISTORY_BL

SQL> DROP TABLE WRH$_SYSMETRIC_HISTORY_BL;
Table dropped.

-- AWRオブジェクトの再生成とコンパイル
SQL> @?/rdbms/admin/catawrtb.sql
SQL> @?/rdbms/admin/utlrp.sql

-- AWR関連プログラムの再生成
SQL> @?/rdbms/admin/execsvrm.sql

あとはSPFILEをもとに戻し再起動して終了です。

-- SPFILEのリストア
$ mv spfileTESTDB.ora.bk spfileTESTDB.ora
-- または
SQL> CREATE SPFILE FROM PFILE='pfile.ora';

-- データベースの再起動
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

おわりに

ということで、クローンを作るときには自動で掃除しておくのがいいのかもしれません。

以上です。