投稿者: yonayona

Oracleの実行計画を取得した時のメモ。

sqlplus DBユーザ名/DBパス名@サービス名

SQLPlusだけで実行計画を取得します。
通常のSQLPlusではautotraceをoff、termoutをonにすると綺麗に取得できます。

-- ログ初期設定
set autotrace on
set echo off
set timing on
set time on
set termout off
set feedback 1
set colsep 't'
set pagesize 30000
set linesize 30000
set trimspool on

col PLAN_PLUS_EXP format a200;

spool trace_test.log;

prompt ======================
prompt  実行計画
prompt ======================
prompt 【対応前】
SELECT XXXX FROM TEST_TBL;

prompt ======================
prompt  実行計画
prompt ======================
prompt 【対応後】
SELECT YYYY FROM TEST_TBL;

set autotrace off
spool off;

2回以上同じSQLを実行する際は以下のコマンドを実行し、
キャッシュと共有プールから履歴を削除すると良い。
ただし、他の人がSQLを実行していると影響がでてしまうので、
環境を占有していることが条件となる。

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

Oracleの実行計画を取得した時のメモ。

sqlplus DBユーザ名/DBパス名@サービス名

SQLPlusにログイン後に測定したいSQLを投げる。

ALTER SESSION SET SQL_TRACE=TRUE;
SELECT COMPANY_CD, USER_ID, SLIP_NO FROM TEST_TBL;
ALTER SESSION SET SQL_TRACE=FALSE;
exit;

tkprofコマンドでトレースファイルから実行計画を取得する。

# DIAGNOSTIC_DESTの初期化パラメータによる
cd $ORACLE_HOME/diag/rdbms/DB名/インスタンス名/trace
# 実行計画を取得
tkprof トレースファイル名 出力ファイル名 explain=DBユーザ名/DBパス名

tkprofはオプションでパフォーマンスの悪い順に並べたりできるようです。

Oracleデータ復旧をメモしたので、
ついにでOracleデータエクスポートとインポートメモ。

#!/bin/sh
# 環境設定
DB_INF="DBユーザ名/DBパスワード@サービス名"
# エクスポート
exp ${DB_INF} FILE=[テーブル名].dmp LOG=[テーブル名].log TABLES=([テーブル名]) ROWS=Y INDEXES=Y GRANTS=Y CONSTRAINTS=Y STATISTICS=NONE TRIGGERS=N QUERY="WHERE [カラム名] = '[値]' "

#!/bin/sh
# 環境設定
DB_INF="DBユーザ名/DBパスワード@サービス名"
# インポート
imp ${DB_INF} FILE=[テーブル名].dmp LOG=[テーブル名].log TABLES=([テーブル名]) ROWS=Y INDEXES=Y GRANTS=Y CONSTRAINTS=Y STATISTICS=NONE

オプション一部整理。

• 実行モード
  FULL／OWNER／TABLES

  FULL：データベース指定
  OWNER：ユーザ指定
  TABLES：テーブル指定
  

• 移行対象
  ROWS／INDEXES／GRANTS／CONSTRAINTS／TRIGGERS／STATISTICS／TRIGGERS

  ROWS：表データ移行フラグ、Nにすると表定義のみ(デフォルト：Y)
  INDEXES：索引移行フラグ(デフォルト：Y)
  GRANTS：権限移行フラグ(デフォルト：Y)
  CONSTRAINTS：制約移行フラグ(デフォルト：Y)
  STATISTICS：統計情報移行フラグ、移行したくない場合はNONEを指定
  TRIGGERS：トリガー移行フラグ、エクスポート時のみ指定可能(デフォルト：Y)
  

• その他

  QUERY：エクスポート条件条件
  

Oracleのデータ復旧メモ。
TEST_TBLからデータを削除してコミットしてしまったけど復旧したい。

１．バックアップしたデータからの復旧(全てのバージョン)
２．ログ・マイナーを使用した復旧(Oracle8i以降)
３．フラッシュバック･クエリを使用した復旧(Oracle9i以降)

フラッシュバック・クエリの例を記載します。

CREATE TABLE TMP_TEST_TBL AS SELECT COMPANY_CD, USER_ID, SLIP_NO,
TO_DATE(TO_CHAR(UPD_DT, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') AS UPD_DT
FROM TEST_TBL AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '100' MINUTE);

100分前の状態に戻します。
「AS OF TIMESTAMP」句からフラッシュバック･クエリとなっている。
「SYSTIMESTAMP」はシステムの日時を戻す関数なので、
「SYSTIMESTAMP」から「INTERVAL ‘100’ MINUTE」を引くことで「今から100分前」を表している。
「INTERVAL」には「MINUTE」の他にも「HOUR」や「DAY」を扱えるが、遡るのに限度はあるようです。

INSERT INTO TEST_TBL SELECT COMPANY_CD, USER_ID, SLIP_NO,
TO_DATE(TO_CHAR(UPD_DT, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') AS UPD_DT
FROM TMP_TEST_TBL

これで助かったことがあるので、メモ。

実行中のセッションとSQLを確認します。

• 実行中のセッション

  SELECT SID, SERIAL#, USERNAME, STATUS, MACHINE, PROGRAM, SQL_ID FROM V$SESSION WHERE USERNAME IS NOT NULL AND SID != USERENV('SID');
  

• 実行中のSQL

  SELECT SQL_TEXT FROM V$SQLTEXT WHERE SQL_ID='実行中のセッションで確認したSQLIDを指定する。';
  

Oracleのインスタンスの起動停止とリスナーの起動停止の方法です。

• インスタンス起動

  su - oracle
  ※パスワードなし
  sqlplus / as sysdba
  

  startup
  

  以下の内容が画面に出力されます。

  ORACLEインスタンスが起動しました。
  
  Total System Global Area  413372416 bytes
  Fixed Size                  2227040 bytes
  Variable Size             306185376 bytes
  Database Buffers          100663296 bytes
  Redo Buffers                4296704 bytes
  データベースがマウントされました。
  データベースがオープンされました。
  

• リスナー起動

  su - oracle
  ※パスワードなし
  lsnrctl start listener
  

  listenerを省略するとデフォルトリスナーを指定したことになります。

• リスナー停止

  su - oracle
  ※パスワードなし
  lsnrctl stop listener
  

  listenerを省略するとデフォルトリスナーを指定したことになります。

• インスタンス停止

  su - oracle
  ※パスワードなし
  sqlplus / as sysdba
  

  shutdown
  

  以下の内容が画面に出力されます。

  データベースがクローズされました。
  データベースがディスマウントされました。
  ORACLEインスタンスがシャットダウンされました。
  

• インスタンス停止(セッションがすべてINCACTIVEになるのを待つ場合)
  immediateをつけることで以下の動作となる。

  新しい接続は許可されず、新しいトランザクションは開始できません。
  すべての未コミットのトランザクションは、ロールバックされます。
  現在データベースに接続しているユーザーが切断されるのを待機しません。
  既存の接続を全て切断しますが、SQL実行中やロールバック中などのアクティブなセッションが存在すると、その完了を待機します。
  1時間たっても完了しない場合、「ORA-01013」が発生する。
  

  「ORA-01013」が発生すると既に新規セッションを受け付けていないため、
  どのセッションが残っているのか調査が難しいため、セッションが残っているかを確認してからの方が良い。

  shutdown immediate
  

• インスタンス停止(ACTIVEやKILLEDセッションが合っても強制終了したい場合)

  shutdown abort
  

• Oracle取得
  Oracleを導入したくなったので、VMware上のCentOSに導入します。
  導入するバージョンは「Oracle Database XE」です。
  Oracle 11gの無料版でLinuxは64bit版のみのようです。

  インストールするのにCentOSに下記が必要。

  glibcは2.3.4-2.41以上
  makeは3.80以上
  binutilsは2.16.91.0.5以上
  gccは4.1.2以上
  libaioは0.3.104以上
  

• Oracle導入
  yumコマンドでインストール済のパッケージを全て確認します。

  yum list installed
  

  glibc.x86_64            2.12-1.107.el6_4.5
  make.x86_64             1:3.81-20.el6
  binutils.x86_64         2.20.51.0.2-5.36.el6
  gcc.x86_64              4.4.7-3.el6
  libaio.x86_64           0.3.107-10.el6
  

  問題なしということで、インストール作業を進めます。
  このOracleは外とは繋がないので、専用ユーザは作成せずにいきました。

  Linux64bit版のzipファイルをCentOSにアップロードします。

  cd /home/ユーザ名/oracle
  unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
  

  Disk1ディレクトリの中に解凍されているので、
  中にあるrpmパッケージをインストールします。

  cd Disk1
  rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
  

  準備中...                ########################################### [100%]
  
  This system does not meet the minimum requirements for swap space.  Based on
  the amount of physical memory available on the system, Oracle Database 11g
  Express Edition requires 1976 MB of swap space. This system has 1854 MB
  of swap space.  Configure more swap space on the system and retry the
  installation.
  
  エラー: %pre(oracle-xe-11.2.0-1.0.x86_64) scriptlet failed, exit status 1
  エラー:   install: スクリプト %pre の実行に失敗しました (2)。oracle-xe-11.2.0-1.0 をスキップします。
  

  失敗・・・。
  スワップ領域が不足しているようです。。
  スワップ領域を調べてみることに。

  swapon -s
  

  既にいくつかスワップ領域を消費していました。
  ずっとサスペンドだったからでしょうか。
  再起動するとスワップ領域の消費が0になりました。
  というわけで、再度インストールします。

  cd Disk1
  rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
  

  準備中...                ########################################### [100%]
     1:oracle-xe              ########################################### [100%]
  Executing post-install steps...
  You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.
  

  無事インストールできました。

• Oracle導入後の設定
  次に「/etc/init.d/oracle-xe configure」を実行しろと記載があるので、実行します。

  /etc/init.d/oracle-xe configure
  

  ポート：XXXX(デフォルトの8080は他と競合しそうなので変えました。)
  リスナー：1522(気分で変えました。)
  DBパスワード：パスワード
  OS起動時に自動起動：y

  Oracleの環境変数は下記で確認できます。

  view /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
  

  Oracleを使うユーザに適用しておきます。

  vi /etc/profile
  

  下記内容を最終行に追加
  . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
  

• Oracle導入後の動作確認(サーバ側)
  TeraTermを再起動し、「/etc/profile」を読み込みなおし、
  動作確認を行います。
  インストール時にOracleユーザとしてsystemが作られています。

  sqlplus system/パスワード@サービス名
  

  SQLに接続できれば成功です。
  インストール時のファイルを削除しておきます。

  rm -fr /home/ユーザ名/oracle
  

  無料版なのにプロシージャまで試せるようなので、なかなか満足です。
  インストール手順詳細は下記参照。
  http://docs.oracle.com/cd/E36055_01/install.112/b66469/toc.htm#CIHHJEHF

• Oracle導入後の動作確認(クライアント側)
  VMwareをインストールしているPCにもOracleクライアントをインストールし
  sqlplusで接続できるように
  サーバ側のリスナーポートを開放しておきます。

  iptables -I INPUT 23 -p tcp -m tcp --dport 1522 -j ACCEPT
  

  23は「iptables -L –line-numbers」で確認し、
  挿入したい行番号の次番号を指定しました。