オンラインREDOログ破損や消失の障害リカバリ

リカバリ要件

  • データベースの全体バックアップ及びアーカイブログのバックアップがある。
  • 制御ファイルとSPFILEがある。
  • オンラインREDOログが破損また消失した。

リカバリ検証

障害を作るためにオンラインREDOログを全部削除
[oracle@db-node01 PROD]$ rm redo0*.log
[oracle@db-node01 PROD]$ ls -l
total 813020
-rw-r-----  1 oracle oinstall  12926976 Apr  9 01:21 control01.dbf
-rw-r-----  1 oracle oinstall  12926976 Apr  9 01:21 control02.dbf
-rw-r-----  1 oracle oinstall  12926976 Apr  9 01:21 control03.dbf
-rw-r-----  1 oracle oinstall 262148096 Apr  9 01:15 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314576896 Apr  9 01:15 system01.dbf
-rw-r-----  1 oracle oinstall 104861696 Apr  8 22:13 temp01.dbf
-rw-r-----  1 oracle oinstall 134221824 Apr  9 01:15 undotbs01.dbf
-rw-r-----  1 oracle oinstall  67112960 Apr  9 01:15 users01.dbf
[oracle@db-node01 PROD]$

alertログにエラーメッセージが出力された。
Thu Apr  9 01:24:04 2009
ORA-16038: log 1 sequence# 17 cannot be archived
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01_01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01_02.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01_03.log'
Thu Apr  9 01:24:04 2009
Errors in file /u01/app/oracle/admin/PROD/bdump/prod_arc1_2792.trc:
ORA-16038: log 1 sequence# 17 cannot be archived
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01_01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01_02.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01_03.log'
障害をリカバリ
-- データベースをマウントにして、RMANに接続
[oracle@db-node01 work]$ rman target / catalog rman/oracle@OMR
 
Recovery Manager: Release 10.2.0.2.0 - Production on49 01:33:02 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
connected to target database: PROD (DBID=93443355, not open)
connected to recovery catalog database
 
RMAN> restore database;        -- データベースをリストア
 
Starting restore at 09-04-09
using channel ORA_DISK_1
 
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/PROD/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/PROD/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/PROD/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/PROD/users01.dbf
restoring datafile 00005 to /u02/app/oracle/oradata/PROD/batch_01.dbf
restoring datafile 00006 to /u02/app/oracle/oradata/PROD/indx_01.dbf
restoring datafile 00007 to /u02/app/oracle/oradata/PROD/example_01.dbf
restoring datafile 00008 to /u02/app/oracle/oradata/PROD/oltp_01.dbf
channel ORA_DISK_1: reading from backup piece /u04/app/oracle/oradata/PROD/rman/PROD_cokc0fpe_1_1_20090409.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/app/oracle/oradata/PROD/rman/PROD_cokc0fpe_1_1_20090409.bak tag=TAG20090409T011525
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 09-04-09
 
RMAN>
-- SQL*PLUSでV$LOG_HISTORYを問い合わせして、REDOログのシーケンス番号を確認
SQL> select * from v$log_history;
     RECID      STAMP    THREAD#  SEQUENCE# FIRST_CHANGE# FIRST_TI NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOG
---------- ---------- ---------- ---------- ------------- -------- ------------ ----------------- --------
       155  683680856          1         12        473949 09-04-08       473966            466212 09-04-08
       156  683687526          1         13        473966 09-04-08       475827            466212 09-04-08
       157  683687547          1         14        475827 09-04-09       475853            466212 09-04-08
       158  683687723          1         15        475853 09-04-09       475980            466212 09-04-08
       159  683687744          1         16        475980 09-04-09       476002            466212 09-04-08
       160  683688244          1         17        476002 09-04-09       476114            466212 09-04-08
-- RMANコンソールに戻して、シーケンス17までのアーカイブログをリストア
RMAN> restore archivelog until sequence 17;
 
Starting restore at 09-04-09
using channel ORA_DISK_1
 
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=6
channel ORA_DISK_1: reading from backup piece /u04/app/oracle/oradata/PROD/rman/PROD_c5kc08h1_1_1_20090408.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/app/oracle/oradata/PROD/rman/PROD_c5kc08h1_1_1_20090408.bak tag=TAG20090408T231129
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /u04/app/oracle/oradata/PROD/rman/PROD_c7kc08hk_1_1_20090408.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/app/oracle/oradata/PROD/rman/PROD_c7kc08hk_1_1_20090408.bak tag=TAG20090408T231148
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=8
channel ORA_DISK_1: reading from backup piece /u04/app/oracle/oradata/PROD/rman/PROD_c9kc08hu_1_1_20090408.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/app/oracle/oradata/PROD/rman/PROD_c9kc08hu_1_1_20090408.bak tag=TAG20090408T231158
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=9
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=10
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11
channel ORA_DISK_1: reading from backup piece /u04/app/oracle/oradata/PROD/rman/PROD_cfkc0908_1_1_20090408.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/app/oracle/oradata/PROD/rman/PROD_cfkc0908_1_1_20090408.bak tag=TAG20090408T231936
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /u04/app/oracle/oradata/PROD/rman/PROD_chkc092p_1_1_20090408.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/app/oracle/oradata/PROD/rman/PROD_chkc092p_1_1_20090408.bak tag=TAG20090408T232057
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=13
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=14
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=15
channel ORA_DISK_1: reading from backup piece /u04/app/oracle/oradata/PROD/rman/PROD_cnkc0fpb_1_1_20090409.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/app/oracle/oradata/PROD/rman/PROD_cnkc0fpb_1_1_20090409.bak tag=TAG20090409T011523
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=16        -- シーケンス16までのログがリストアされた
channel ORA_DISK_1: reading from backup piece /u04/app/oracle/oradata/PROD/rman/PROD_cpkc0fq0_1_1_20090409.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/app/oracle/oradata/PROD/rman/PROD_cpkc0fq0_1_1_20090409.bak tag=TAG20090409T011544
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-04-09
 
RMAN>
-- SQL*PLUSに戻してリカバリを実行
SQL> recover database until cancel;    -- UNTIL CANCELでリカバリ
ORA-00279: change 475992 generated at 04/09/2009 01:15:26 needed for thread 1
ORA-00289: suggestion : /u04/app/oracle/oradata/PROD/arch2/PROD_1_16_683610448.arc
ORA-00280: change 475992 for thread 1 is in sequence #16
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}    -- シーケンス16のログがあるため、Enterキーを押す。
 
ORA-00279: change 476002 generated at 04/09/2009 01:15:44 needed for thread 1
ORA-00289: suggestion : /u04/app/oracle/oradata/PROD/arch2/PROD_1_17_683610448.arc
ORA-00280: change 476002 for thread 1 is in sequence #17
ORA-00278: log file '/u04/app/oracle/oradata/PROD/arch2/PROD_1_16_683610448.arc' no longer needed for this recovery
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}    -- シーケンス17のログが無い(オンラインREDO)ため、CANCELを入力
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;    -- RESETLOGSオプションでデータベースをオープン
 
Database altered.
 
SQL>
-- RESETLOGSオプションによって、消失したREDOログが自動に再作成された。
[oracle@db-node01 PROD]$ ls -l
total 1735784
-rw-r-----  1 oracle oinstall  12992512 Apr  9 01:55 control01.dbf
-rw-r-----  1 oracle oinstall  12992512 Apr  9 01:55 control02.dbf
-rw-r-----  1 oracle oinstall  12992512 Apr  9 01:55 control03.dbf
-rw-r-----  1 oracle oinstall 104858112 Apr  9 01:54 redo01_01.log
-rw-r-----  1 oracle oinstall 104858112 Apr  9 01:54 redo01_02.log
-rw-r-----  1 oracle oinstall 104858112 Apr  9 01:54 redo01_03.log
-rw-r-----  1 oracle oinstall 104858112 Apr  9 01:54 redo02_01.log
-rw-r-----  1 oracle oinstall 104858112 Apr  9 01:54 redo02_02.log
-rw-r-----  1 oracle oinstall 104858112 Apr  9 01:54 redo02_03.log
-rw-r-----  1 oracle oinstall 104858112 Apr  9 01:55 redo03_01.log
-rw-r-----  1 oracle oinstall 104858112 Apr  9 01:55 redo03_02.log
-rw-r-----  1 oracle oinstall 104858112 Apr  9 01:55 redo03_03.log
-rw-r-----  1 oracle oinstall 262148096 Apr  9 01:54 sysaux01.dbf
-rw-r-----  1 oracle oinstall 314576896 Apr  9 01:54 system01.dbf
-rw-r-----  1 oracle oinstall 104861696 Apr  8 22:13 temp01.dbf
-rw-r-----  1 oracle oinstall 134221824 Apr  9 01:54 undotbs01.dbf
-rw-r-----  1 oracle oinstall  67112960 Apr  9 01:54 users01.dbf
[oracle@db-node01 PROD]$

これによって、障害をリカバリしたが、消失されたオンラインREDOログにアーカイブされていない情報が失ったので、データの損失が生じる。
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License