シングルDBでサービスを追加する

Oracle RACではサービスという論理的な接続先を作ることにより、ノードごとに負荷特性の分離をすることができます。
例えば、APPサービスでWebサイトの処理、BATCHサービスで夜間バッチの処理、などです。
※物理的なノードとはレイヤが少し違います。

シングルDBでもサービスを追加することができます。
用途としては次のようなことが考えられますが、たぶん他にもあるでしょう。
（専用サーバ接続と共用サーバ接続を使い分けるとかできるんでしょうか）

・AWR（STATSPACKのようなもの）をサービスごとに分ける（EEなのでやったことありません）
・再インストールしたら、インスタンス名を間違えた

以前、やってしまったのが2番目。
サービス名は、デフォルトではインスタンス名を引き継ぎます。
そのため、サービス名が変わってしまったためクライアントから接続エラーが発生しました。
クライアント側は変更したくないとの要望があったため、サービス名を追加しました。

手順は次にようにします。

まずはサービス名を確認します。

SQL> show parameter service_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      SSS01DB2

次に、サービス名を追加します。
今回は、間違えてしまったサービス名は残したまま、OODEVを追加します。

ALTER SYSTEM SET service_names='SSS01DB2','OODEV' SCOPE=BOTH;

追加されたかどうか確認します。

SQL> show parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      SSS01DB2, OODEV

余談ですが、RACでのサービス追加コマンドsrvctl start serviceは、内部的にalter system setしているだけです。
（アラートログを見るとわかります）

共有プールのサイズ変更履歴を確認する

例えばWebリクエストと分析系バッチなど、特性が違いすぎる処理を実行しているとします。
自動共有メモリ管理（ASMM）を使っている場合、Oracleは共有プールとデータベースバッファの最適化に迷ってしまうことがあります。
特性の異なる最適化を繰り返してしまうのは、本当の意味で最適化とは言えません。

メモリ（SGA）のリサイズ結果を判断の1つにすることができます。

set time on 
set pages 10000
set lines 120
col component for a25
col oper_type for a12
col parameter for a25
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select component,oper_type,parameter,modsize,end_time from (
  select row_number() over (order by end_time desc) as rn,
    component,oper_type,parameter,(final_size-initial_size) as modsize,end_time
  from v$SGA_RESIZE_OPS
) where rn<=20;

実行結果は次の通り。 OPER_TYPEは増減（GROW/SHRINK）です。 頻度や量（MODSIZE）を確認するといいと思います。

COMPONENT                 OPER_TYPE    PARAMETER                    MODSIZE END_TIME
------------------------- ------------ ------------------------- ---------- -------------------
shared pool               SHRINK       shared_pool_size          -100663296 2011/03/05 04:14:31
DEFAULT buffer cache      GROW         db_cache_size              100663296 2011/03/05 04:14:31
shared pool               SHRINK       shared_pool_size          -100663296 2011/03/05 03:01:26
DEFAULT buffer cache      GROW         db_cache_size              100663296 2011/03/05 03:01:26
DEFAULT buffer cache      SHRINK       db_cache_size             -100663296 2011/03/04 18:57:11
shared pool               GROW         shared_pool_size           100663296 2011/03/04 18:57:11
shared pool               SHRINK       shared_pool_size          -100663296 2011/03/04 04:32:11
DEFAULT buffer cache      GROW         db_cache_size              100663296 2011/03/04 04:32:11
DEFAULT buffer cache      SHRINK       db_cache_size             -201326592 2011/03/03 16:12:45
shared pool               GROW         shared_pool_size           201326592 2011/03/03 16:12:45

SGAの大きさを変更する

SGAはインスタンスの再起動が必要です。
まず現在の設定を確認します。

SQL> show parameter sga_
NAME             TYPE         VALUE
---------------- ------------ ------
sga_max_size     big integer  1536M
sga_target       big integer  1536M

SGAサイズsga_max_sizeは動的変更（scope=both）できません。
初期化パラメータをサーバパラメータファイルで管理している、かつ、SGAの自動共有メモリ管理（ASMM）を使っている場合、sga_targetは動的変更できます。

SQL> alter system set sga_max_size = 4G scope=spfile
SQL> alter system set sga_target = 4G scope=spfile

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2089472 bytes
Variable Size            3707768320 bytes
Database Buffers          570425344 bytes
Redo Buffers               14684160 bytes
Database mounted.
Database opened.

作業後、確認します。

SQL> show parameter sga_
NAME             TYPE         VALUE
---------------- ------------ ------
sga_max_size     big integer  4G
sga_target       big integer  4G

Data Pumpによる過去分データをインポートする

今担当している案件では頻繁に過去データの調査があるため、ときどきバックアップから開発DBにインポートしています。
インポートにはData Pumpを使っています。
Recovery Managerではない理由は次の通り。

・インポート先(開発DB)の容量不足でエクスポートデータを置けない
・サーバ間マテリアライズドビューが多数あるので、テーブルとインデックス以外をリカバリしたくない
・週次でRMANフルバックアップという運用をしているため、リカバリがめんどうw

1.はじめに
Oracleには2種類のエクスポート／インポートコマンドがあります。
Data Pumpを使っている理由は、速度とスキーマ変更機能です。

2.作業の流れ
ここでは、マテビューのマスタ側サーバのエクスポートデータをインポートします。
そのままインポートするとマテビューログや統計情報も含まれるので、次の流れで 行います。

(1)バックアップサーバからエクスポートデータをコピー
(2)DDLの抽出ディレクトリの確認
(3)エクスポートデータからDDLだけをファイル出力
(4)DDLファイルを編集し、テーブルおよびインデックス以外を削除
(5)編集したDDLを実行してcreate table|index
(6)エクスポートデータからレコードデータのみをインポート
(7)analyze

(1)バックアップサーバからエクスポートデータをコピー
エクスポートデータのコピー例

$ ssh sssdev1
$ /backup/databasefile/201008/sssdb3/
$ scp sss01_db_i_dp20100820.dmp.gz  oracle@sssdb11:/usr/local/dbdata/

(2)DDLの抽出ディレクトリの確認
ディレクトリ設定がされているか確認します。
ここではEXPDP_DATAを使います。

select * from dba_directories;
OWNER      DIRECTORY_NAME            DIRECTORY_PATH
---------- ------------------------- ---------------------------------------------
SYS        ORACLE_OCM_CONFIG_DIR     /opt/app/oracle/product/10.2.0/db/ccr/state
SYS        DATA_PUMP_DIR             /opt/app/oracle/product/10.2.0/db/rdbms/log/
SYS        EXPDP_DATA                /usr/local/dbdata

(3)エクスポートデータからDDLだけをファイル出力
本番システムで使っていないスキーマにインポートしたい時はremap_schemaが非常に便利です。
remap_tableの方が便利ですが、たしか11gからだったと思います。
ここではtestdocomoという調査用に作ったスキーマに変換します。

$ vi testdocomo.par1_0820 
directory=expdp_data
dumpfile=sss01_db_i_dp20100820.dmp　　　★エクスポートしたデータ
logfile=sss01_db_i_sql20100820.log
job_name=job_impdp
sqlfile=sss01_db_i_20100820.sql　　　★DDLのファイル出力
remap_schema=sss01docomo:testdocomo　　　★別スキーマにも変換可能
remap_tablespace=sss01_i_data:test_i_data　　　★別表領域にも変換可能
remap_tablespace=sss01_i_idx:test_i_idx
remap_tablespace=mvlog_i_data:test_i_data
INCLUDE=TABLE:"IN ('T_ACCESS1','T_USER1')"　　　★テーブル指定も可能

$ impdp testdocomo/xxxxxxxxxxxx parfile=testdocomo.par1_0820 

(4)DDLファイルを編集し、テーブルおよびインデックス以外を削除
TABLE, INDEX, CONSTRAINT以外を削除します。
特に、マテビューログのSYS用DDLは、元のスキーマ名で設定するので注意。
また、ビットマップインデックスは、通常のインデックスとは別項目で出力することも注意。

$ grep "^-- new obj" sss01_db_i_20100902.sql 
-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- new object type path is: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- new object type path is: SCHEMA_EXPORT/TABLE/COMMENT
-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
-- new object type path is: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path is: SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
-- new object type path is: SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG

(5)編集したDDLを実行してcreate table|index
テーブルおよびインデックス作成例

$ sqlplus testdocomo/xxxxxxxxxxxx
SQL> @sss01_db_i_20100820.sql

(6)エクスポートデータからレコードデータのみをインポート
レコードデータのインポート例

$ cat testdocomo.par2_0820 
directory=expdp_data
dumpfile=sss01_db_i_dp20100820.dmp
logfile=sss01_db_i_sql20100820.log
job_name=job_impdp
remap_schema=sss01docomo:testdocomo
remap_tablespace=sss01_i_data:test_i_data
remap_tablespace=sss01_i_idx:test_i_idx
remap_tablespace=mvlog_i_data:test_i_data
CONTENT=DATA_ONLY　　　★レコードデータのみを指定
INCLUDE=TABLE:"IN ('T_ACCESS1','T_USER1')"　　　★テーブル指定も可能

$ impdp testdocomo/xxxxxxxxxxxx parfile=testdocomo.par2_0820 

(7)analyze
データ量が多いときはやった方が精神衛生上よいです。

analyze table TBL_SG1_0820 compute statistics;　　　★インデックスもanalyze

共有プールの利用状況を確認する

負荷特性の異なる処理を混在させている場合、共有プールの変化をウォッチしておくとボトルネックを見つけられることがあります。

sql area   ・・・   SQL文そのもののキャッシュ
CCursor/PCursor   ・・・   カーソルキャッシュ
library cache   ・・・   コンパイル済SQLのキャッシュ
free memory   ・・・   空きメモリ

set pages 10000
set lines 100
set time on
col name for a30
col bytes for 999,999,999,999
select name,bytes from (
 select row_number() over (order by bytes desc) as rn,name,bytes from v$sgastat
 where pool = 'shared pool'
) where rn <= 20;

NAME                                      BYTES
------------------------------ ----------------
sql area                          2,484,421,928
free memory                       1,734,338,672
CCursor                             678,436,864
PCursor                             472,727,184
library cache                       352,787,688
sql area:PLSQL                      346,800,984
kglsim object batch                 182,272,608
gcs resources                       128,586,016
kglsim heap                         104,469,120
Cursor Stats                         98,273,576
gcs shadows                          76,999,136
db_block_hash_buckets                47,185,920
ASH buffers                          30,408,704
ges enqueues                         19,842,976
ges big msg buffers                  15,936,168
trace buffer                         14,057,472
KGLS heap                            13,290,704
event statistics per sess            12,296,000
FileOpenBlock                        11,575,096
ges resource                         11,342,888

似たSQLが多いWebサイト処理と、SQL数が多くなりがちな大量更新バッチを混在させていると次のグラフのようになることがあります。 バッチを実行していない時はfreeがかなり多いためボトルネックを見つけづらいと思います。

表領域の使用状況

表領域単位にデータファイルの使用状況を確認します。
outer joinにしているのは、空き容量が枯渇した場合にdba_free_spaceのレコードがなくなるため。

UNDO表領域は実際の使用量よりはかなり多めに計算されることに注意してください。
これは、解放されたブロックをすぐに再利用できるように、設定された期間（900秒が一般的）は論理削除をしているためです。
再利用可能なexpiredエクステントも使用中とみなされています。
　
実運用監視では、UNDO表領域を監視対象から外し、別の手段で検知するのがベターです。

set time on
set pagesize 10000
set linesize 100
col autoextensible heading "AUTO" for a4
col tablespace_name format a25
col sizemb for 999,999
col usedmb for 999,999
col freemb for 999,999
col ratio for 999.99
select dbf.tablespace_name,
  dbf.bytes/1024/1024 as sizemb,
  (dbf.bytes - sum(nvl(fsp.bytes,0)))/1024/1024 as usedmb,
  sum(nvl(fsp.bytes,0))/1024/1024 as freemb,
  (1-(sum(nvl(fsp.bytes,0))/dbf.bytes))*100 ratio,
  dbf.autoextensible
from sys.dba_free_space fsp
  right outer join (
    select tablespace_name,sum(bytes) as bytes,autoextensible
    from sys.dba_data_files
    group by tablespace_name,autoextensible
  ) dbf
  on dbf.tablespace_name=fsp.tablespace_name
group by dbf.tablespace_name,dbf.bytes,dbf.autoextensible
order by dbf.tablespace_name,autoextensible;

実行例は次の通り。

SIZEMB・・・表領域サイズ
USEDMB・・・使用サイズ
FREEMB・・・未使用サイズ
RATIO・・・使用率
AUTO・・・自動拡張

TABLESPACE_NAME             SIZEMB   USEDMB   FREEMB   RATIO AUTO
------------------------- -------- -------- -------- ------- ----
FUKA_MASTER_DATA             2,048    1,335      713   65.19 YES
FUKA_MASTER_IDX              1,024      442      582   43.16 YES
MVLOG_MASTER_DATA              512      121      391   23.63 YES
PERFSTAT_DATA                1,024      689      335   67.29 YES
SYSAUX                       2,048      285    1,763   13.90 YES
SYSTEM                       2,048      352    1,696   17.17 YES
UNDOTBS1                    32,749       44   32,706     .13 YES