Oracleリファレンス: 4月 2011

2011年4月27日水曜日

Oracle RAC Standard Edition(SE)を使ってみて気づいたこと

※個人の感想に基づいています・・・

まずは、ストレージ共有型クラスタであること。
MySQL Clusterとは違い、データを1箇所に保管するストレージ共有型です。
ストレージの冗長化をストレージ側でしなければならないのが、最大の弱点だと思ってます。
代替ストレージ機を用意しておけばダウンタイムを減らせますが、ハードウェア交換が必要なので1時間単位のダウンは避けられません。
そもそもSEを買うような案件ではそこまで予算がありません。
EEはライセンスおよびサポート費用だけで価格がひと桁変わりますからね・・・。

スタンバイDBにレプリケーションしようにも、Enterprise Edition(EE)でなければできません。
増分バックアップやアーカイブREDOログファイルバックアップをするにしても、分単位でのバックアップは現実的ではないと思います

次、ライセンスのCPU制限。
SE RACでは、物理CPUの最大数が4つまでです。ライセンス
いまどき1CPUのサーバはあまり使わないので、2CPUだとノードの最大数が2つまで。
これが何を意味するかというと、failoverが運任せになりうることです。

RACは、ノード間の通信可否結果を投票ディスクというファイルとして共有型ストレージに格納しています。
クラスタソフトウェアは、この投票ディスクを元に多数決で孤立＝ネットワーク障害サーバを検知します。
ノードが2台だと多数決にならずどちらか一方をOS再起動します。
そのため、正常稼動しているノードを再起動してしまう可能性があります。

RACは3ノード以上で使うのがお薦めです。

最後に、商用であること。
SEだけの話ではありませんが、購入してみないとサポートを受けられない、購入してみないとどんな特性の製品かわからないことです。
SQL文の数が多すぎるとラッチ待機のせいでSGAを増やせば増やすほどパフォーマンスが落ちる、など買う前にはわからなかったことが多いです。

まとめると、EEでない限りOracle RACは使いたくないな、と。
今の案件、MySQLに移設したいなー。

2011年4月22日金曜日

ASMのファイルシステムを直接操作する

ASMにはASMのファイルシステムを操作するシェル、asmcmdが用意されています。
使い方はORACLE_SIDをASMにしてasmcmdを起動するだけ。
cdやlsだけでなくrmやduもあって非常に便利。

ASMCMD> help
　　　　　　　：
        commands:
        --------
        cd
        du
        find
        help
        ls
        lsct
        lsdg
        mkalias
        mkdir
        pwd
        rm
        rmalias

ASMディスクグループへディスクを追加するではSQLで確認していますが、容量確認だけであればasmcmdの方が楽です。

$ export ORACLE_SID=+sss01
$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N      N         512   4096  1048576    476843   334069                0          334069              0  DATA/
MOUNTED  EXTERN  N      N         512   4096  1048576    143055   130125                0          130125              0  LOG/

findも使えます。
要らないオンラインREDOログを削除する前の確認くらいにしか使いませんが。

ASMCMD> find -t ONLINELOG log redo1*.dbf  
+log/REDO/REDO1012.DBF
+log/REDO/REDO1022.DBF
+log/REDO/REDO1032.DBF
+log/REDO/REDO1042.DBF
+log/REDO/REDO1052.DBF
+log/REDO/REDO1062.DBF

2011年4月21日木曜日

ASMディスクグループへディスクを追加する

ASMはディスクグループという仮想的なディスクで管理しています。
それによりRAWデバイスの追加を容易にしています。

ここでは、データ領域用のディスクグループを拡張します。

まず、RAWデバイスを作成し、パーミッションを設定します。
この例ではDevice-Mapperを使っていますが説明は省きますが、環境によってはdmのパーミッション設定は要らないかもしれません。

# vi /etc/rc.d/rc.local
（下記追記）
chown oracle:oracle /dev/mapper/dm002
chmod 660 /dev/mapper/dm002

raw /dev/raw/raw9 /dev/mapper/dm002
chown oracle:oracle /dev/raw/raw9
chmod 660 /dev/raw/raw9

次に、パラメータasm_diskstringに追加したいRAWデバイス名が含まれているか確認します。
asm_diskstringは、アクセス許可するRAWデバイス名を記述します。
注意として、OracleインスタンスではなくASMインスタンスに接続する必要があります。

$ export ORACLE_SID=+sss01
$ sqlplus sys as sysdba
SQL> show parameter asm_diskstring

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
asm_diskstring                       string
/dev/raw/raw[6-7]

raw9にはアクセスできない設定になっているので、ASMインスタンスの初期化パラメータファイルに追記します。
ASMを再起動すると、設定が有効になります。

asm_diskstring='/dev/raw/raw[6-7]'
　　　　　　　　↓
asm_diskstring='/dev/raw/raw[6-9]'

ASMからRAWデバイスにアクセスできるようになりました。
次のように追加したいディスクグループを確認します。
GNAMEがディスクグループ名、DNAMEが各RAWデバイスにつけられた論理ディスク名です。

$ export ORACLE_SID=+sss01
$ sqlplus sys as sysdba
SQL>
column GNAME FORMAT a15
column DNAME FORMAT a15
select g.name as gname,d.name as dname
 from v$asm_disk d,v$asm_diskgroup g
 where d.group_number=g.group_number order by g.name,d.name

GNAME           DNAME
--------------- ---------------
DATA            DATA_DG1
DATA            DATA_DG2
LOG             LOG_DG1
LOG             LOG_DG2

ディスクグループへの追加は次の通りです。

$ export ORACLE_SID=+sss01
$ sqlplus sys as sysdba
SQL> ALTER DISKGROUP DATA ADD DISK '/dev/raw/raw9' NAME DATA_DG3


GNAME           DNAME
--------------- ---------------
DATA            DATA_DG1
DATA            DATA_DG2
DATA            DATA_DG3
LOG             LOG_DG1
LOG             LOG_DG2

2011年4月10日日曜日

権限付与の特徴

Oracleの権限付与には、次の特徴があります。

・システム権限とオブジェクト権限の2種類ある
・ロールにロールを付与することもできる
・権限付与だけでなくcreate user等で表領域割り当て（quota）が必要
・UNLIMITED TABLESPACEはシステム表領域を操作できてしまう
・grant文はロールと権限を混在して記述できない
・ストアド内部ではロールの権限は無視される

まず1つ目。

・システム権限とオブジェクト権限の2種類ある

MySQLでいう、グローバルレベルとテーブルレベルのようなものです。
インスタンス全体の権限と、オブジェクト（テーブル、ビュー、パッケージ、ストアド等）の権限です。
テーブルもパッケージもすべて「オブジェクトの1種」という、Oracleの特徴的な考え方がよく表れていると思います。

次、ロールの再帰的付与が可能です。

・ロールにロールを付与することもできる

例を挙げると、IMP_FULL_DATABASEロールはSELECT_CATALOG_ROLEロールを付与しています。

次はquota。

・ロールにロールを付与することもできる

スキーマの表領域へのアクセス制限を確認するにあるように、権限とその対象は別々の設定なので注意が必要です。

次は特徴というより注意です。

・UNLIMITED TABLESPACEはシステム表領域を操作できてしまう

UNLIMITED TABLESPACEはquota設定を無視するのでできるだけ付与しないほうが安全かと思います。

その次もGRANT設定上の注意です。

・grant文はロールと権限を混在して記述できない

理由はわからないのですが、カンマ区切りで同時に付与しようとするとエラーになるようです。

最後に、一番ハマりやすい特徴です。

・ストアド内部ではロールの権限は無視される

この仕様のせいで、せっかくロールで管理したい権限をスキーマに対していちいち付与しなくてはならないです。
みなさんはどういう管理してるんでしょうかね・・・。
Webフロント用のスキーマ、バッチ用スキーマと使い分けてるんでしょうか。

UNDO表領域の縮小

UNDO表領域は縮小できない場合があります。
UNDO表領域を拡張するやデータファイルのサイズを縮小するで触れている通り、断片化しやすい表領域のためです。
自動拡張してしまった場合は、特に断片化が進んでいて縮小できないことが多いです。

また、UNDO表領域は削除することもできません。
そこで、縮小は、一時的に作ったUNDO表領域に切り替えてから古いものを再作成する必要があります。

まずは、今の表領域を確認します。
容量は、表領域の使用状況で確認します。
この例だと、32GBいっぱいまで自動拡張してますね・・・。

SQL> show parameter undo_tablespace
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
undo_tablespace                      string
UNDOTBS1

TABLESPACE_NAME             SIZEMB   USEDMB   FREEMB   RATIO AUTO
------------------------- -------- -------- -------- ------- ----
UNDOTBS1                    32,749       80   32,669     .25 YES

次に、一時的なUNDO表領域を作成し、切り替えます。

SQL> create undo tablespace UNDOTMP datafile '/DATA/DATABASE/UNDOTMP1.DBF' size 1G AUTOEXTEND ON;
SQL> alter system set undo_tablespace='UNDOTMP' sid='sss01dev';

SQL> show parameter undo_tablespace
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
undo_tablespace                      string
UNDOTMP

切り替え後の、サイズの大きいUNDO表領域は再作成します。

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
SQL> create undo tablespace UNDOTBS1 datafile '/DATA/DATABASE/UNDOTBS101.DBF' size 4G;

最後に、再作成したUNDO表領域に切り戻します。

SQL> alter system set undo_tablespace='UNDOTBS1' sid='sss01dev';

SQL> show parameter undo_tablespace
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
undo_tablespace                      string
UNDOTBS1

SQL> drop tablespace UNDOTMP including contents and datafiles;

2011年4月5日火曜日

SQLをトレースしTKPROFで整形する

EXPLAINで実行計画がわかりますが、バインド変数を使ったSQLの場合はデータ（レコード）を特定できないため、正確な実行計画が得られません。
ここでは実際に実行したSQLをトレースをすることにより実行計画を取得します。

次のように、トレースをONにして実行します。

var vcarrier number
var vleader number
exec :vcarrier := 2
exec :vleader := 2
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

SELECT
    *
FROM
    (SELECT
        rank() over (ORDER BY tt.regist_dt DESC) as rn,
        tt.team_nm AS team_nm ,
        tt.member_cnt AS member_cnt ,
        tt.team_status_id AS team_status_id ,
        tt.pr_comment AS pr_comment ,
        tt.team_id AS team_id ,
        tm.user_id AS user_id ,
        tt.regist_dt AS regist_dt ,
        wearing_avatar_kind,
        avatar_id,
        gender,
        avatar_session
    FROM
        T01_Test2 tt ,
        T01_TestMember2 tm ,
        T01_UserMyTest2 my
    WHERE
        tt.carrier_id = :vcarrier     AND
        tm.user_id = my.user_id     AND
        tt.team_id = tm.team_id     AND
        tm.leader_flg = :vleader
    ORDER BY
        tt.regist_dt DESC
    )
WHERE
    rn BETWEEN 1 AND
    30;

alter session set events '10046 trace name context off';
alter session set timed_statistics=false;

EVENT 10046のレベルは4種類あります。

level  1　　　alter session set sql_trace=trueと同じ
level  4　　　level 1にバインド変数情報
level  8　　　level 1に待機イベント情報
level 12　　　level 4 ＋ level 8

実行後、DBサーバにトレースファイルが出力されています。
そのトレースファイルそのままだと読みづらいので、TKPROFというツールで整形します。

$ ls -tlr /opt/app/oracle/admin/sss01dev/udump/ | tail -1
$ sudo scp /opt/app/oracle/admin/sss01dev/udump/sss01dev_ora_21243.trc snwdev1:/home/ito/tmp/
$ tkprof sss01dev_ora_21243.trc test.trc

ただし、TKPROFはバインド情報を無視するので必要であればトレースファイルを直接確認します。

$ grep -A4 "^ Bind" sss01dev_ora_21243.trc 
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=2b818341b4c0  bln=22  avl=02  flg=05
  value=2
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=2b818341b4d8  bln=22  avl=02  flg=01
  value=2

整形したファイル例は次の通りです。

TKPROF: Release 10.2.0.4.0 - Production on 火 4月 5 11:25:10 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: sss01dev_ora_29755.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SELECT
    *
FROM
    (SELECT
        rank() over (ORDER BY tt.regist_dt DESC) as rn,
        tt.team_nm AS team_nm ,
        tt.member_cnt AS member_cnt ,
        tt.team_status_id AS team_status_id ,
        tt.pr_comment AS pr_comment ,
        tt.team_id AS team_id ,
        tm.user_id AS user_id ,
        tt.regist_dt AS regist_dt ,
        wearing_avatar_kind,
        avatar_id,
        gender,
        avatar_session
    FROM
        T01_Team2 tt ,
        T01_TeamMember2 tm ,
        T01_UserMyRoom2 my
    WHERE
        tt.carrier_id = :vcarrier     AND
        tm.user_id = my.user_id     AND
        tt.team_id = tm.team_id     AND
        tm.leader_flg = :vleader
    ORDER BY
        tt.regist_dt DESC
    )
WHERE
    rn BETWEEN 1 AND
    30

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       31      0.14       0.14          0       6097          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.14       0.14          0       6097          0          30

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 113  

Rows     Row Source Operation
-------  ---------------------------------------------------
     30  VIEW  (cr=6097 pr=0 pw=0 time=140143 us)
   1153   WINDOW SORT PUSHED RANK (cr=6097 pr=0 pw=0 time=141286 us)
   1153    HASH JOIN  (cr=6097 pr=0 pw=0 time=129360 us)
   1164     TABLE ACCESS FULL T01_TEAM2 (cr=227 pr=0 pw=0 time=166 us)
   1153     HASH JOIN  (cr=5870 pr=0 pw=0 time=123033 us)
   1153      TABLE ACCESS FULL T01_TEAMMEMBER2 (cr=253 pr=0 pw=0 time=4643 us)
  86413      TABLE ACCESS FULL T01_USERMYROOM2 (cr=5617 pr=0 pw=0 time=86469 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      32        0.00          0.00
  SQL*Net message from client                    32        0.00          0.02



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       31      0.14       0.14          0       6097          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.14       0.14          0       6097          0          30

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      79        0.00          0.00
  SQL*Net message from client                    79       15.53         23.49


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    1  user  SQL statements in session.
    0  internal SQL statements in session.
    1  SQL statements in session.
********************************************************************************
Trace file: sss01dev_ora_29755.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       1  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       1  SQL statements in trace file.
       1  unique SQL statements in trace file.
     263  lines in trace file.
       0  elapsed seconds in trace file.

2011年4月4日月曜日

RACのサービス名一覧フル表示する

RACのサービス状況は、crs_statというコマンドで表示することが出来ます。
オプション-tでサマリ一覧表示ができますが、長い文字列は途中で切れてしまいます。

そこで、パイプなどを使ってサービス名をフル表示することができます。

$ crs_stat | awk '{print $1 "="  $4; }' RS="" FS="\n" | awk '{ printf "%-50s%s\n",$2,$4}' FS="="
ora.sss01.SSS01DBBATCH.cs                         ONLINE on sssdb1
ora.sss01.SSS01DBBATCH.sss011.srv                 ONLINE on sssdb1
ora.sss01.SSS01DBBATCH.sss012.srv                 ONLINE on sssdb2
ora.sss01.SSS01DBBATCH1.cs                        ONLINE on sssdb1
ora.sss01.SSS01DBBATCH1.sss011.srv                ONLINE on sssdb1
ora.sss01.SSS01DBBATCH2.cs                        ONLINE on sssdb2
ora.sss01.SSS01DBBATCH2.sss012.srv                ONLINE on sssdb2
ora.sss01.SSS01DBE.cs                             ONLINE on sssdb1
ora.sss01.SSS01DBE.sss011.srv                     ONLINE on sssdb1
ora.sss01.SSS01DBE.sss012.srv                     ONLINE on sssdb2
ora.sss01.SSS01DBI.cs                             ONLINE on sssdb1

2011年4月3日日曜日

マテリアライズドビューの自動同期を設定する

マテリアライズドビューの自動同期は、リフレッシュグループというジョブに対象テーブルを登録することにより実行されます。
自動同期は、1テーブルでも同期に失敗したら、リフレッシュグループまるごと同期がストップすることが特徴です。
そのため、スキーマ単位でリフレッシュグループを作るか、クリティカルな業務テーブルをひとまとめにするといいと思います。

次のように作成します。
ここでは1分インターバルのリフレッシュグループにします。

BEGIN
 DBMS_REFRESH.MAKE (
 name => 'SSS01DBEUSER.REFRESH_E',
 list => '',
 next_date => sysdate,
 interval => 'sysdate + 1/24/60*1440',
 implicit_destroy => FALSE,
 rollback_seg => '',
 push_deferred_rpc => TRUE,
 refresh_after_errors => FALSE);
END;
/

削除は次のようにします。

BEGIN
 DBMS_REFRESH.DESTROY (
 name => 'SSS01DBEUSER.REFRESH_E');
END;
/

シーケンスキャッシュのサイズを変更する

STATSPACKや待機しているセッションを調べるでenq: SQ - contentionが目立つ場合、シーケンスキャッシュが不足している場合があります。

担当案件にてユーザプロセスが急増したことがありますが、その際、ユーザプロセスのIDキャッシュサイズが小さいためにenq: SQ - contentionが多発しました。
（そもそもの原因はアプリケーションでしたが、急場しのぎのために設定変更しました）

今回の例では、ユーザプロセスのID発行に関わるキャッシュのサイズを大きくします。
ただし、これは諸刃の剣なので十分注意してください。
理由は、キャッシュサイズが大きくなるということは、キャッシュを使いきってリフレッシュする時のロック時間が長くなるということです。

まず、現在の設定値CACHE_SIZEを確認します。

set pages 10000
set lines 120
col SEQUENCE_OWNER for a15
col SEQUENCE_NAME for a15
select * from dba_sequences where sequence_name in ('AUDSES$','IDGEN1$');

SEQUENCE_OWNER  SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY CYC ORD CACHE_SIZE LAST_NUMBER
--------------- --------------- ---------- ---------- ------------ --- --- ---------- -----------
SYS             IDGEN1$                  1 1.0000E+27           50 N   N         2000  3.9574E+10
SYS             AUDSES$                  1 2000000000            1 Y   N         2000  1448207982

次のように値を10倍にします。

SQL> ALTER SEQUENCE SYS.AUDSES$ CACHE 20000;
SQL> ALTER SEQUENCE SYS.IDGEN1$ CACHE 20000;

sqlplusのログインプロンプトを変更する

開発や本番など、サーバが増えてくると怖いのがログインミス。
そこで、ログインプロンプトを弄ってみました。

まず、環境変数SQLPATHで設定ファイルを指定します。
ここでは、Linuxアカウントのホームディレクトリにします。

$ grep SQLPATH .bashrc
export SQLPATH=~

sqlplusは、ログインした時にlogin.sqlを実行するので、次のように記述します。
ここでは、ログインユーザ、インスタンス名、時刻を表示します。

$ cat login.sql 
set time on
set pages 10000
set lines 120
column sid0 new_value sid1
column user new_value uid1
select user from dual;
select rtrim(instance_name,chr(0)) sid0 from v$instance;
set sqlprompt '&uid1@&sid1>'

ログインするとこうなります。
最初にうつくしくないものが表示されるがきにしない。
（表示しない方法もありますがめんどうなので調べてません・・・）

$ sqlplus sss01dbiuser/sss01dbiorbital@SSS01DEV
USER
------------------------------
SSS01DBIUSER


SID0
--------------------------------------------------------------------------------
sss01dev

16:30:44 SSS01DBIUSER@sss01dev>

マテリアライズドビューの自動同期対象テーブルを探す

マテリアライズドビューの自動同期対象を探します。
自動同期のジョブは、リフレッシュグループにあるテーブルに対して実行します。
ジョブとリフレッシュグループの紐付けはマテリアライズドビューの同期エラーを解決するを参照。

リフレッシュグループのテーブル一覧は次の通り。

set pages 10000
set lines 120
column RNAME FORMAT a20
column NAME FORMAT a40
select r.job,r.rname,c.name,c.broken
 from DBA_REFRESH r, DBA_REFRESH_CHILDREN c
 where r.REFGROUP=c.REFGROUP
  and r.rowner='INDEP1USER'

実行例は、次の通り。
わかりにくいですが、BROKEN=Nであれば自動同期をします。

JOB RNAME                NAME                                     BRO
---------- -------------------- ---------------------------------------- ---
       118 REFRESH_INDEP1       TBL_CONSENTLOG1                          N
       118 REFRESH_INDEP1       TBL_EXPORTCONSENTLOG                     N
       118 REFRESH_INDEP1       TBL_MYROOMCOMMENT1                       N
       118 REFRESH_INDEP1       TBL_MYROOMCOMMENTCONSENT1                N
       118 REFRESH_INDEP1       TBL_USERMYROOMCOMMENT1                   N
       118 REFRESH_INDEP1       TBL_USERMYROOMCOMMENTCONSENT1            N

登録: 投稿 (Atom)

このブログを検索