Oracle SCN(System Commit Number)を調べる

Oracleは、SCN（System Commit Number）という内部的なタイムスタンプによりデータの整合性をとっています。
バックアップデータを時間指定してリカバリする際に使うことがあります。

current_scnは今現在、checkpoint_change#はcheckpoin済みのSCNです。

col current_scn for 999999999999999
col checkpoint_change# for 999999999999999
select current_scn,checkpoint_change# from v$database;

実行結果は次の通りです。

     CURRENT_SCN CHECKPOINT_CHANGE#
---------------- ------------------
     35521648642        33022114382

いつのSCNでリカバリすればいいかわからない場合、日時からあたりをつけることができます。
timestamp_to_scn関数で概算SCNを求められます。

col SCN for 999999999999999
select timestamp_to_scn(to_date('2011/06/15 12:00:00','yyyy/mm/dd hh24:mi:ss')) as SCN
from dual;

実行結果は次の通りです。

             SCN
----------------
     33021237709

マテビューのマスタ側テーブルの更新時刻を調べる

マテビューで簡易レプリケーションしている場合、マスタ側かレプリケーション側か問題を切り分ける時に使います。
マスタ側にてスナップショットを取得した時刻を表示します。
この時刻が更新されてもレプリケーション側に反映されない場合、次の問題が考えられます。

・ネットワーク
・DBリンク（サーバ間マテビューの場合）
・レプリケーション側のリフレッシュグループが止まっている
など

slog$はSYS権限が要るので注意が必要です。

set pages 10000
set lines 110
col mowner for a15
col master for a30
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select mowner, master, snaptime from slog$
 where master='M01_PCCOMMONUSER' and mowner='M01XXXUSER'
 order by snaptime;

実行結果は次の通りです。

MOWNER          MASTER                         SNAPTIME
--------------- ------------------------------ -------------------
M01XXXUSER      M01_PCCOMMONUSER               2011/06/15 14:17:47

INVALIDオブジェクトを探す

INVALIDになっているオブジェクトを探します。
パッチ（PSUなど）を当てた時はdba_objectsを調べるかと思います。
僕はスキーマ単位で作業することが多いのでuser_objectsをよく使います。

set pages 10000
set lines 110
col object_name for a30
col status for a10
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select object_name, status, object_type, created, last_ddl_time, temporary
from dba_objects
where status='INVALID'
order by object_name;

実行結果は次の通りです。

OBJECT_NAME        STATUS     OBJECT_TYPE   CREATED             LAST_DDL_TIME       T
------------------ ---------- ------------- ------------------- ------------------- -
LOGIC_ITOTEST      INVALID    PROCEDURE     2009/11/13 14:47:16 2009/12/30 18:55:32 N
STATSPACK          INVALID    SYNONYM       2009/12/14 11:48:27 2010/07/30 22:13:47 N
VIEW_EXPIRATION4   INVALID    VIEW          2010/05/17 17:15:14 2010/12/16 16:16:43 N

ASMディスクグループの容量や使用率を調べる

v$asm_diskgroupでディスクグループの情報を得られます。
RATIOが一定量を超えないように監視スクリプトを作成しています。

set pages 10000
set linesize 100
col name format a20
col total_mb for 999,999,999
col free_mb for 999,999,999
col ratio for 999.99
col state for a10
select group_number, name, total_mb, free_mb,
  round(((total_mb-free_mb)/total_mb)*100 ,2) as ratio,
  state
from v$asm_diskgroup;

実行結果は次の通り。

GROUP_NUMBER NAME                     TOTAL_MB      FREE_MB   RATIO STATE
------------ -------------------- ------------ ------------ ------- -----------
           1 DATA                      708,468      500,535   29.35 CONNECTED
           2 LOG                       493,804      481,292    2.53 CONNECTED

表領域にオブジェクトが残っているか調べる

使っていない表領域のはずなのに何かが残ってる・・・というときの調査です。
セグメントのサイズを確認するの応用です。

Oracleは、スキーマのデフォルト表領域とは別の表領域にオブジェクトを作成することができます。
そのため、うっかりミス等でこういうことが起こりえます。

dba_segmentsを表領域で検索し、スキーマ（OWNER）およびセグメント名を表示します。

set pages 10000
set lines 100
col owner for a30
col segment_name for a30
col type for a10
select owner,segment_name,segment_type as type from dba_segments
where tablespace_name='SSS01_S_DATA'
order by owner,segment_name;

実行例は次の通りです。
一番下のTESTUSERは、テスト用スキーマなのですが、誤ってこの表領域に作られてしまっています。
（人名や日付がついていたり酷いですね・・・）

OWNER                          SEGMENT_NAME                   TYPE
------------------------------ ------------------------------ ----------
SSS01D01USER                   BAK_FILE2_20101220             TABLE
SSS01D01USER                   M01_BATCHSTATE                 TABLE
SSS01D01USER                   SYS_C00157262                  INDEX
SSS01D01USER                   T01_FILEACCESSBATCH2_M         TABLE
SSS01D01USER                   T01_MAGICAPPITEM2_KEN          TABLE
SSS01D01USER                   T01_MAGIC_APPITEM_LIMIT2_KEN   TABLE
TESTUSER                       TEMP_USERLIST                  TABLE

シングル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しているだけです。
（アラートログを見るとわかります）

LIKE句でアンダースコアをマッチングさせる

OracleのLIKE句では、'_'（アンダースコア）は任意の1文字でマッチングします。
アンダースコアそのものをマッチングしたい場合は、ESCAPE句を使います。

例として表領域のデータファイル情報を取得します。

SQL> set pages 10000
set lines 120
col tablespace_name for a20
col file_name for a50
col bytes for 999,999,999,999
select tablespace_name,file_name,bytes,status from DBA_DATA_FILES
where tablespace_name like 'SSS01+_S+_%' escape '+'
order by tablespace_name,file_name;

実行結果は次の通りです。

TABLESPACE_NAME   FILE_NAME                                      BYTES STATUS
----------------- ----------------------------------- ---------------- ---------
SSS01_S_DATA      +DATA/database/sss01_s_data_01.dbf     2,147,483,648 AVAILABLE
SSS01_S_DATA      +DATA/database/sss01_s_data_02.dbf     2,147,483,648 AVAILABLE
SSS01_S_IDX       +DATA/database/sss01_s_idx_01.dbf      1,589,641,216 AVAILABLE
SSS01_S_IDX       +DATA/database/sss01_s_idx_02.dbf      1,588,592,640 AVAILABLE
SSS01_S_IDX       +DATA/database/sss01_s_idx_03.dbf      1,073,741,824 AVAILABLE
SSS01_S_IDX       +DATA/database/sss01_s_idx_04.dbf      1,073,741,824 AVAILABLE

6行が選択されました。