REDOログファイルのサイズ変更をする

REDOログファイルのサイズ変更の基本的な考え方は次の通りです。

・REDOログファイルのサイズ変更は不可
・変更は、新規追加（別ファイル）し、不要のものを削除する
・削除は、STATUS=ACTIVEだとできない
・削除は、内容がアーカイブREDOファイルとして出力されていないとできない
・OMF（Oracle Managed Files）を未使用の場合、物理削除も必要
・RACの場合、インスタンス単位でサイズや個数を変えることはできる

まず、REDOログファイルを確認します。

   THREAD#     GROUP#     MBYTES STATUS     MEMBER
---------- ---------- ---------- ---------- ------------------------------
         1          1       1024 INACTIVE   +DATA/redo/redo111.dbf
         1          1       1024 INACTIVE   +LOG/redo/redo112.dbf
         1          2       1024 ACTIVE     +DATA/redo/redo121.dbf
         1          2       1024 ACTIVE     +LOG/redo/redo122.dbf
         1          3       1024 CURRENT    +DATA/redo/redo131.dbf
         1          3       1024 CURRENT    +LOG/redo/redo132.dbf
         2          4       1024 INACTIVE   +LOG/redo/redo212.dbf
         2          4       1024 INACTIVE   +DATA/redo/redo211.dbf
         2          5       1024 ACTIVE     +LOG/redo/redo222.dbf
         2          5       1024 ACTIVE     +DATA/redo/redo221.dbf
         2          6       1024 CURRENT    +LOG/redo/redo232.dbf
         2          6       1024 CURRENT    +DATA/redo/redo231.dbf

次に、新規REDOログファイルを追加します。
ここでは、2ノードRACを想定し、1GB→2GBのサイズ変更を行います。

$ sqlplus sys as sysdba
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 7 ('+DATA/redo/redo141.dbf', '+LOG/redo/redo142.dbf') SIZE 2G;
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 8 ('+DATA/redo/redo151.dbf', '+LOG/redo/redo152.dbf') SIZE 2G;
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 9 ('+DATA/redo/redo161.dbf', '+LOG/redo/redo162.dbf') SIZE 2G;

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ('+DATA/redo/redo241.dbf', '+LOG/redo/redo242.dbf') SIZE 2G;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 11 ('+DATA/redo/redo251.dbf', '+LOG/redo/redo252.dbf') SIZE 2G;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 12 ('+DATA/redo/redo261.dbf', '+LOG/redo/redo262.dbf') SIZE 2G;

追加したら、REDOログファイルを強制的にローテートし、追加したREDOログがCURRENTになるまで繰り返します。
REDOログファイルはノードごとにあるので各ノードで実行します。

[oracle@sssdb1 ~]$ sqlplus sys as sysdba
SQL> alter system switch logfile;

[oracle@sssdb1 ~]$ sqlplus sys as sysdba
SQL> alter system switch logfile;

削除したいREDOログファイルをINACTIVEにし、アーカイブREDOログファイルを出力します。

SQL> ALTER SYSTEM CHECKPOINT;

不要になったREDOログファイルを削除します。

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
SQL> ALTER DATABASE DROP LOGFILE GROUP 5;
SQL> ALTER DATABASE DROP LOGFILE GROUP 6;

OMF（Oracle Managed Files）を利用していない場合、物理ファイルの削除をします。
この例では、ASMのファイルを削除します。

$ export ORACLE_SID=+sss011
$ asmcmd
ASMCMD> cd DATA/REDO
ASMCMD> rm REDO111.DBF
ASMCMD> rm REDO122.DBF
ASMCMD> rm REDO133.DBF
ASMCMD> rm REDO211.DBF
ASMCMD> rm REDO222.DBF
ASMCMD> rm REDO233.DBF

ASMCMD> cd ../../LOG/REDO
ASMCMD> rm REDO112.DBF
ASMCMD> rm REDO122.DBF
ASMCMD> rm REDO132.DBF
ASMCMD> rm REDO212.DBF
ASMCMD> rm REDO222.DBF
ASMCMD> rm REDO233.DBF

REDOログファイルを一覧表示する

REDOログファイルを一覧表示します。

set pages 10000
set lines 120
COL MEMBER FORMAT A30
COL STATUS FORMAT A10
SELECT LOG.THREAD#,LOG.GROUP#,
    LOG.BYTES/1024/1024 AS MBYTES,
    LOG.STATUS,LOG.ARCHIVED,LOGFILE.MEMBER 
 FROM   V$LOG LOG,V$LOGFILE LOGFILE
 WHERE LOG.GROUP# = LOGFILE.GROUP#
 ORDER BY THREAD#,GROUP#;

実行結果は次の通りです。
この例では2ノードRAC、3グループ、各1GBです。

STATUS=CURRECTが現在使用中のREDOログファイルです。
ACTIVEが使ったことがあるがアーカイブされていないもの、INACTIVEはアーカイブ済のものです。
UNUSEDはREDOログファイル追加後などまったく未使用のものです。

THREAD#     GROUP#     MBYTES STATUS     MEMBER
---------- ---------- ---------- ---------- ------------------------------
         1          1       1024 INACTIVE   +DATA/redo/redo111.dbf
         1          1       1024 INACTIVE   +LOG/redo/redo112.dbf
         1          2       1024 ACTIVE     +DATA/redo/redo121.dbf
         1          2       1024 ACTIVE     +LOG/redo/redo122.dbf
         1          3       1024 CURRENT    +DATA/redo/redo131.dbf
         1          3       1024 CURRENT    +LOG/redo/redo132.dbf
         2          4       1024 INACTIVE   +LOG/redo/redo212.dbf
         2          4       1024 INACTIVE   +DATA/redo/redo211.dbf
         2          5       1024 ACTIVE     +LOG/redo/redo222.dbf
         2          5       1024 ACTIVE     +DATA/redo/redo221.dbf
         2          6       1024 CURRENT    +LOG/redo/redo232.dbf
         2          6       1024 CURRENT    +DATA/redo/redo231.dbf

スワップしづらいようにRMANバックアップをする

担当案件では、RMAN→tar→圧縮→2GBファイル分割という手順でバックアップファイルを作成していました。
ですが、tarと圧縮にてOSのファイルキャッシュを大量に消費するため、スワップの発生が問題になっていました。

というのは、DBサーバのメモリのほとんどをSGAおよびOracleプロセスに割り当てていたためです。
ファイル操作用のOSファイルキャッシュが不足してスワップ→さらに負荷上昇という悪循環でした。

そこで、OSファイルキャッシュが効かないように、RAWデバイス側で圧縮およびファイル分割まですることにしました。
ファイル分割が終わるまでの時間は長くなりましたが、スワップは発生しなくなりました。

ここでは、バックアップセットを圧縮し、バックアップピースの最大サイズを2GBに制限する設定をします。
分割ファイル名が重複しないよう、formatでピース番号を指定するよう注意してください。

CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;

backup database format
  '${BKUPDIR}/%d_database_full_%T_%s_%p.bkp'
  tag = 'full_database_backup';

出力したファイルは次のようになります。

-rw-r--r-- 1 backup backup 2.0G  6月 27 07:27 201106/s01db3/S01_database_full_20110627_4730_1.bkp
-rw-r--r-- 1 backup backup 2.0G  6月 27 07:33 201106/s01db3/S01_database_full_20110627_4730_2.bkp
-rw-r--r-- 1 backup backup 2.0G  6月 27 07:40 201106/s01db3/S01_database_full_20110627_4730_3.bkp
-rw-r--r-- 1 backup backup 139M  6月 27 07:41 201106/s01db3/S01_database_full_20110627_4730_4.bkp
-rw-r--r-- 1 backup backup 2.0G  6月 27 07:47 201106/s01db3/S01_database_full_20110627_4731_1.bkp
-rw-r--r-- 1 backup backup 2.0G  6月 27 07:52 201106/s01db3/S01_database_full_20110627_4731_2.bkp
-rw-r--r-- 1 backup backup 2.0G  6月 27 07:58 201106/s01db3/S01_database_full_20110627_4731_3.bkp
-rw-r--r-- 1 backup backup 470M  6月 27 08:01 201106/s01db3/S01_database_full_20110627_4731_4.bkp
-rw-r--r-- 1 backup backup 1.4M  6月 27 08:01 201106/s01db3/S01_database_full_20110627_4732_1.bkp

共有プールをクリアする

共有プールをインスタンス単位でクリアすることができます。
ただし、キャッシュヒット率が激減し、一時的にパフォーマンスダウンが予想されるのでタイミングに注意。
RACの場合は、一度に全ノードクリアするのではなく、クリア後ある程度キャッシュがたまってきてからの方がベターです。

共有プールのサイズが大きすぎるとエージアウトした際のロック（ラッチ）の時間が長くなり、DBサーバの応答が遅くなります。
この例のようにsql area（SQL数）が多いことが根本原因なのでアプリを直すべきですが、障害対応として紹介します。
（とはいえ、リスクの方が大きいので僕はめったにクリアしません）
共有プールのラッチは、CPU使用率もI/O負荷も上昇しないので検知しづらく潜在リスクは非常に高いと思っています。

まず、現状の共有プールの状況を確認します。

set pages 10000
set lines 120
set time on
col name for a40
select * from (
 select name, bytes from v$sgastat
 where pool = 'shared pool'
 order by bytes desc
) where rownum <= 20;

例えば、次のようにsql areaが3.5GBほどあるインスタンスをクリアすることにします。

NAME                                          BYTES
---------------------------------------- ----------
sql area                                 3690078544
free memory                               957169136
PCursor                                   637214224
CCursor                                   623044928
library cache                             499544096
sql area:PLSQL                            268971096
gcs resources                             233915744
gcs shadows                               140065792
kglsim object batch                       137342016
db_block_hash_buckets                      94371840
kglsim heap                                82446336
Cursor Stats                               64968688
ASH buffers                                30408704
transaction                                19464072
ges enqueues                               18813632
trace buffer                               16891904
ges big msg buffers                        15936168
KCL name table                             12582912
event statistics per sess                  12296000
FileOpenBlock                              11575096

共有プールのクリアはインスタンスごとにalter systemします。

$ sqlplus sys as sysdba
SQL> alter system flush shared_pool;

次のようにsql areaが消え、free memoryが増加します。

NAME                                          BYTES
---------------------------------------- ----------
free memory                              6376176928
gcs resources                             233915744
kglsim object batch                       191237088
library cache                             152614448
gcs shadows                               140065792
kglsim heap                               108622080
db_block_hash_buckets                      94371840
Cursor Stats                               85479368
sql area                                   44653328
ASH buffers                                30408704
transaction                                19490568
ges enqueues                               18813632
trace buffer                               16891904
ges big msg buffers                        15936168
KCL name table                             12582912
event statistics per sess                  12296000
FileOpenBlock                              11575096
CCursor                                    11276472
ges resource                               11221136

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