Oracle Database

主なテーブル

テーブル名用途
V$SESSIONカレントセッション毎のセッション情報
V$ACTIVE_SESSION_HISTORYACTIVE SESSION HISTORY(10gからの機能)はOracleのセッション情報を1秒おきに自動的にログとして記録で、このテーブルで内容を確認可能
SGAに残ってない場合は、DBA_HIST_ACTIVE_SESS_HISTORY表を検索する)
V$〜は1秒毎の情報だが、DBA〜は_ash_disk_filter_ratioパラメータ(デフォルト10)の粒度の割り当て余力を確認でサンプル取得する
(つまり10分の1のデータしか残らない/10秒毎の集計になる)
DBA_HIST_ACTIVE_SESS_HISTORY
V$SESSION_WAITアクティブなセッションの待機イベント
V$SESSION_WAIT_HISTORYアクティブなセッションの過去10の待機イベント
V$SQL問い合わせが終了したSQL、5秒以上実行中のSQLの情報を持つ
LAST_LOAD_TIMEでライブラリキャッシュにロードされた時間(ハードパースされた時間)や実行回数(EXEMETHOD_OPTCUTIONS)、CPU時間(CPU_TIME)などが解る
DBA_HIST_SNAPSHOT自動ワークリポジトリの内容、SNAP_IDの開始終了時間の確認ができる
DBA_HIST_SQLSTATSQLの統計情報履歴
DBA_HIST_SQLTEXTSQLIDからSQL文を確認する場合などに利用。sql_text が CLOB型なので set long 20000などの指定が必要
V$PGASTATPGAの統計情報
V$SGASTATSGAの統計情報
V$RESOURCE_LIMITプロセスの合計数や、セッション合計数など各種リソースの初期値及び最大値


 

基本SQL操作

  • DATE型フォーマット変更
    ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
  • TIMESTAMP型(年(RR)とTIME ZONE(TZR)を非表示)
    ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='MM-DD HH24:MI:SSXFF'
  • 時間操作
    trunc(日付 [,'書式'])        ※ 書式で指定した単位まで日付を切り捨てる。省略時はDD(日にち)
    trunc(sysdate) - 1 + 22 / 24    ※ 1日前の22:00 sysdateを truncすることで、時分秒を切り捨てる(YYYY-MM-DD 22:00:00)
    systimestamp - interval '5' minute ※ 5分前
  • 初期化パラメータ変更
    alter system set <パラメータ名>=<変更値> scope=<spfile | memory | both > [sid='<インスタンス名 | *>'];
  • CLOB型の値をwhere句で絞り込む
    where dbms_lob.substr(CLOB属性のカラム名, 取得byte数, 取得位置) = '値'
    取得byte数は最大で4000byte(VARCHAR2型なので)これを超えるとエラーになる。
  • テーブルソースを取得する
    set long 2000000 pages 0  ※DDLが途中で切れるのを防ぐ
    select DBMS_METADATA.GET_DDL('オブジェクト種類', 'テーブル' [,'スキーマ']) from DUAL;
    
    ※オブジェクト種類は TABLE、INDEX、PACKAGE、PACKAGE_BODY、TRIGGER、VIEW の何れか指定
  • 隠しパラメータ表示
    select i.ksppinm name , v.ksppstvl value
    from x$ksppi i, x$ksppcv v
    where i.indx = v.indx;
  • 非推奨パラメータの表示
    SELECT NAME FROM V$PARAMETER WHERE isdeprecated = 'TRUE'
  • マスターノード確認(目安)
    select master_node,count(master_node) from V$GES_RESOURCE group by master_node;
    
    ※ノードIDの確認は olsnodes -n
  • パッチ情報確認
    opatch lsinventory -detail [-oh $ORACLE_HOME(製品のインストールディレクトリ)]
    
    ※ ORACLE_HOMEにGRIDのインストールディレクトリを入れると、GRIDのパッチ情報が確認できる

     

SETコマンド

-- 標準出力させる場合のテンプレート
set line 10000 pages 50000 num 20 colsep "," trim on
-- 空白行なく結果レコードのみ出力させる
set line 10000 pages 0  num 20 colsep "," verify off feedback off trims on term off
コマンド説明
linesize <数値>1行に表示する横幅のサイズ
pages <数値>ヘッダー表示を何行毎に行うか
time onプロンプトにタイムスタンプを表示
timing onSQL実行結果時間を表示
trimspool onspoolコマンドの出力結果で行末の空白を削除する
trimout on標準出力結果で行末の空白を削除する
termout off外部ファイルでSQLを実行(SQL> @test.sql)した際に実行結果を標準出力しない
colsep "区切り文字"区切り文字
long <数値>LONG型(BLOB、CLOB、BFILEなど)データ最大表示バイト数
feedback off検索結果のレコード表示をやめる
verify off置換変数を値に置き換える前後の値を非表示にする
numwidth <数値>NUMBER型の指数表示になる桁数を指定(デフォルトは10ケタで指数表示になる)
SQL*Plusコマンド説明
column <カラム名> format a<数値>カラムのサイズを変更する
list <n>n番目の行をカレント行にする。nを省略した場合は全行リスト表示する
a[ppend] テキストカレント行末にテキストを追加する
del行を削除する。
i[nput] テキストカレント行の後にテキストを追加する


RAC環境のリソース操作

DB名や、インスタンス名は、show parameter の db_name / instance_name で確認

  • リソース状態確認(-initでクラスターに必要なデーモンリソースの状態確認)
    crs_stat -t
    crsctl stat res -t [-init]
    crsctl check crs
  • データベース
    srvctl { start | stop } database -d <DB名>
  • インスタンス単位
    srvctl { start | stop } instance -d <DB名> -i "<インスタンス名>,<インスタンス名>..."
  • ASMインスタンス
    srvctl { start | stop } asm -n <ホスト名>
  • リスナー
    srvctl { start | stop } lsnr [-l リスナー名] -n <ホスト名>
  • ノードアプリケーション
    srvctl { start | stop } nodeapps -n <ホスト名>
  • クラスタウェア
    crsctl { start | stop } crs
  • クラスタ関連リソース
    -- リソースの各種パラメータ確認
    crsctl stat res -init -p
    crsctl -p [リソース名]
-- リソースの起動(ora.crsdの例)
crsctl start resource ora.crsd -init
  • サービス
    サービスが起動してるインスタンスでは、service_name初期化パラメータ にサービス名が載る。
    -- サービス実行中のインスタンス確認
    srvctl status -d <DB名> [-s サービス名]
    
    -- サービス再配置
    srvctl relocate service -d <DB名> -s <サービス名> -i <移動元インスタンス> -t <移動先インスタンス>
    
    -- サービス設定確認
    srvctl config service -d <DB名>

     

トラブルシューティング

現行セッションのSPID及び出力先トレースファイル確認

select value from v$diag_info where name='Default Trace File';

-- 接続中のPIDを確認する
select p.spid PID from v$session s, v$process p
where s.paddr=p.addr and s.sid=(select distinct sid from v$mystat);

イベント設定確認

oradebug setmypid
oradebug eventdump system

HANGANALYZE

DBハング時に、どのプロセスが原因か特定する場合に取得。
プロセス間の待機の連鎖を構築することでプロセスの依存関係を確認する。

alter session set events 'immediate trace name hanganalyze level 10';    -- 10g
alter session set events 'immediate hanganalyze(10)';                    -- 11g
-- 全インスタンス取得
radebug setinst all ←要らないかも
oradebug -g all hanganalyze 4
レベル出力情報
1待機チェーン
2Process state object
3ハングと判断されたプロセス情報(最初はこのレベルの取得を推奨)
4複数ノードで構成される待機チェーンのリーフ・プロセス情報
5チェーンを構成しないアクティブセッションを持つプロセス情報
6複数ノードで構成される待機チェーンのリーフ以外のプロセス情報
10全てのプロセス情報

level XX を off にすることで無効化。

State Dump

ハングやスピンの原因を究明するのに有用。スピンの場合は複数回取得が必要(状況が随時変化する為)
SGA内のメモリ構造で、プロセス、セッション、トランザクション、ロックなどのオブジェクト。

-- SQL*Plusで接続できない(接続時にプロンプトが返ってこない)場合は、-prelim でログインして取得する
sqlplus -prelim / as sysdba
-- プロセス単位で取得
oradebug setospid [プロセスID(SPID)]
oradebug dump processstate 10;
-- インスタンス内全てのプロセス取得
alter session set events 'immediate trace name systemstate level 266';  -- 10g
alter session set events 'immediate systemstate(266)';                  -- 11g

oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
※ traceフォルダ配下にSQL*Plus接続してるプロセスのトレースに出力される(<SID>_ora_<PID>.trc)

oradebug tracefile_name
※ 出力されるトレースファイルのフルパス表示
-- 全てのインスタンスの全プロセス取得
alter session set events 'immediate trace name systemstate_global level 266';  -- 10g
alter session set events 'immediate systemstate_global(266)';                  -- 11g

oradebug -g all dump systemstate 266
※ traceフォルダ配下にあるdiagプロセスのトレースが出力される(<SID>_diag_<PID>.trc)
レベル出力情報
1各プロセスのprocess state objectのみ
2他タイプのstate objectを含めた階層構造
10ロックやピンのstate objectを出力する度にライブラリキャッシュオブジェクトの情報出力
2〜9は重複するライブラリキャッシュオブジェクトは出力されない
11RAC環境でリソース情報をダンプ
+256上記レベルに 256 を加算すると、short stach(呼び出された関数のスタック情報)が出力される

level XXX を off にすることで無効化。

  • Current Wait Stack でセッションの待機状況を確認
    • waiting for '待機イベント' ⇒dump取得時点で待機イベントによる待機が発生していた
    • no in wait ⇒dump取得した時点で待機は終了していた
  • Session Wait History にて直近10個の待機イベント遷移を確認
  • (enqueue)と同じアドレスで、req(リクエスト)とmode(保持)からロック状況の確認
  • cursorの待機では同じハッシュ値(idn=XXX、Hash=XXX)でどのプロセス(SQL文)の影響か確認

ERROR STACK

ORA-600などのクリティカルエラーが発生した時に自動で出力される情報(スタックや、PROCESS STATE、カーソル情報等)。
全てのインスタンスに設定され、インスタンス再起動したら設定は解除される。

alter { session | system } set events 'XXXXX trace name errorstack { level 3 | off }';  -- 10g
alter { session | system } set events 'XXXXX [occurence:end_after 1] errorstack(3)';    -- 11g
※ XXX は ORA-XXXXのエラー番号
※ occurence:end_after 1 は 1回イベント取得したら終了する(デフォルトはforeverで永続)
-- 特定セッションで ERROR STACK取得
oradebug setospid [プロセスID]
oradebug unlimit
oradebug dump errorstack 3
レベル出力情報
1カレントSQL、PL/SQLコールスタック、関数コールスタック
2process state
3context area(カーソル情報)
  • Call Stack Trace エラーに至るまでに使用された関数のスタック情報。エラーハンドリングの関数もある為、注意(インシデントファイルからもスタック情報確認可能)

リソースダンプ

以下の待機イベントで処理が進まない場合に取得する
・gc cr request
・gc current request
・gc buffer busy

-- 引数のパラメータ確認(P1RAWとP2RAWの値を確認する)
select sid, event, p1, p1raw, p2, p2raw, state from gv$session where event = 'gc buffer busy';

 SID        EVENT           P1         P1RAW            P2         P2RAW            STATE
 ---------- --------------- ---------- ---------------- ---------- ---------------- ---------
 25         gc buffer busy  4          0000000000000004 26         000000000000001A WAITING
-- リソースダンプを取得する
oradebug lkdebug -O <P2RAWの値> <P1RAWの値(必ず5桁)> BL
(例) oradebug lkdebug -O 0x1A 0x00004 BL

sqlトレース

AWRでは解らない、解析(Parse)、実行(Excute)、フェッチ(Fetch)の処理時間が確認できる
ファイルはtraceフォルダに、<SID>_ora_<PID>.trc というファイル名で作成される。
ファイル名の中にSQL_IDがあるので、それらを元に該当SQLのトレースを確認する。
設定されてるトレースを確認する方法はイベント設定確認参照。

初期化パラメータ説明
DIAGNOSTIC_DESTユーザートレースファイルの作成ディレクトリ
MAX_DUMP_FILE_SIZEユーザートレースファイルの最大サイズ(デフォルトはUNLIMITED)
TIMED_STATISTICSCPU時間、経過時間を取得
STATISTICS_LEVEL統計の収集レベルをTYPICALまたはALLにする

セッション単位

-- 現行セッション
alter session set events '10046 trace name context forever, level 12';
alter session set events '10046 trace name context off'
※ level は 1(実行時間+実行計画)4(1+バインド変数表示)8(1+待機イベント表示)12(1+4+8)が指定可能
exec DBMS_MONITOR.SESSION_TRACE_ENABLE;
exec DBMS_MONITOR.SESSION_TRACE_DISABLE;
-- 別セッション指定
exec DBMS_SYSTEM.SET_EV(<sid>, <serial#>, 10046, 12, '');
exec DBMS_SYSTEM.SET_EV(<sid>, <serial#>, 10046,  0, '');
exec DBMS_MONITOR.SESSION_TRACE_ENABLE(<sid>, <serial#>, true, true);
exec DBMS_MONITOR.SESSION_TRACE_DISABLE;
-- 3番目の引数は、待機情報を出力。4番目は、バインド変数の内容を出力する

ユーザー単位

exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('ユーザー名', true, true);
exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('ユーザー名');

SQL_ID単位(11.1以降)

alter { session | system } set events 'sql_trace [sql:SQL_ID] wait=true, bind=true';
alter { session | system } set events 'sql_trace [sql:SQL_ID] off';
※ []はそのまま入力すること

DB全体

exec DBMS_MONITOR.DATABASE_TRACE_ENABLE;
exec DBMS_MONITOR.DATABASE_TRACE_DISABLE;

sqlトレースファイルの整形

tkprof トレースファイル名 出力ファイル名  [オプションパラメータ]

引数を何も指定しないで実行すればヘルプが表示される

オプション説明
sys=noSYSユーザーとして実行されたSQL(再帰的SQL)文を結果に含めない
sort=ソートオプション指定したオプションでSQL文を降順にソートする
print=出力数トレースファイルの先頭から出力数分のSQL文のみ結果に含める
aggregate=no or yes同一のSQL文が見つかった時、個別集計(no)するか、集計(yes)するか
explain=UID/PASSトレース取得時の実行計画(最初に表示)と、tkprofによる変換時の実行計画の2つを表示する
統計情報説明
count各フェーズ(解析、実行、フェッチ)におけるコール回数
cpuCPU使用時間(秒単位)
elapsed経過時間(秒単位)ロック待機なども含まれている時間
diskデータファイルから読み込まれた物理データブロック数
query一貫読み込みモード(SELECT)で取得された論理データブロック数
current現行モード(DML)で取得された論理データブロック数
rows処理された行数


EMが利用できない

Database Control(リポジトリ及び構成ファイル)を再作成する

emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos recreate

セッションを切断

  • 基本的な情報を確認する(もっと詳細な確認はこちら
    select inst_id, sql_id, username, program, osuser, logon_time, sql_exec_start, sid, serial#,
        blocking_session, blocking_session_status, blocking_instance
    from gv$session
    where sql_id is not null
    order by sql_id, sid, serial#
  • テーブル名から調査する
    select s.sql_id, username, logon_time, osuser, sql_text, sid, serial#,
        blocking_session, blocking_se ssion_status, blocking_instance
    from v$session s, v$sql q
    where s.sql_id=q.sql_id and sql_fulltext like'%テーブル名%'
    order by s.sql_id, sid, serial#
  • セッションを切断する
    alter system kill session 'SID,SERIAL#';

UNDOTBSのリカバリー

UNDOTBSで使用している DATAFILE の STATUS が「RECOVER」となっている場合のリカバリ手順(RAC環境でも可能)

-- DBを停止させる
-- DBをマウントさせる
startup mount

-- リカバリーを行う
recover datafile 'UNDOTBSのファイル';
※ 下記のようなログが出力される。最後にENTER押下しないとプロンプト戻ってこないので、時々ENTER押下する
==============================================================================
ORA-00279: 変更xxxxxxxxx(MM/DD/YYYY HH:MM:SSで生成)にはスレッドxが必要です
ORA-00289: 検討すべきログ・ファイル〜〜
ORA-00280: 変更xxxxxxxxx(スレッドx)は順序番号xxxxxxxに存在します。

ログの指定: {<RET>=suggested | filename | AUTO | CANCEL}
〜〜
ログが適用されました。
メディア・リカバリが完了しました。
==============================================================================

-- DBをOPENする
alter database open;
select status from v$instance;

-- DATAFILEの状態確認(OFFLINEになってると思う)
select name, status from v$datafile where name = 'UNDOTBSのファイル';

-- DATAFILEのONLINE化
alter database datafile '/data/data/undotbs01.dbf' online;

-- DATAFILEの状態確認(ONLINEになってること)
select name, status from v$datafile where name = 'UNDOTBSのファイル';

-- 表領域の状態確認
select tablespace_name,status from dba_tablespaces where tablespace_name='UNDOTBS名';


 

トピック

特定のSQLを繰り返し実行

都度 sqlplusでログインすると、その度にサーバープロセスが作成されリソースの無駄になる為、SQL*Plusにechoの出力をパイプで渡し、SQLに接続したまま実行させる

(
    echo "conn <UID>/<password>"
    echo "set time on timing on"
    while [ 1 ]
    do
        echo "select count(*) from v\$session;"
        sleep 5
    done
) | sqlplus /nolog


 

トップ   編集 凍結解除 差分 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2017-12-09 (土) 22:45:38 (7d)