当前位置:首页 > 开发 > 数据库 > 正文

记一次Oracle 生产库还原归档日志经历(原创)

发表于: 2012-02-17   作者:czmmiao   来源:转载   浏览次数:
摘要: 中午刚去吃饭,就接到同事电话说急着要恢复生产库上的归档日志。系统环境是10.2.0.5,RAC,归档日志在node1上建立了nfs共享,而node2上没有建立nfs共享。另有一套查询库,通过DSG的同步软件将数据从生产库上同步到查询库上。软件原理是通过传说分析归档日志然后加以应用,总体感觉有点像DG。由于生产库上使用的是增量备份,每天都进行备份。且备份完后会自动删除归档日志。恢复生产库的归档日志的

中午刚去吃饭,就接到同事电话说急着要恢复生产库上的归档日志。系统环境是10.2.0.5,RAC,归档日志在node1上建立了nfs共享,而node2上没有建立nfs共享。另有一套查询库,通过DSG的同步软件将数据从生产库上同步到查询库上。软件原理是通过传说分析归档日志然后加以应用,总体感觉有点像DG。由于生产库上使用的是增量备份,每天都进行备份。且备份完后会自动删除归档日志。恢复生产库的归档日志的原因是,同步到查询库时失败(采用DSG的备份软件时,当遇到大量DML操作时经常遇到同步失败的情况),需要重新同步,但归档日志已经被删除,故需要还原归档日志。查询库昨日17:00后的数据同步失败,故需要还原从昨日至今的归档日志。系统环境交代到这,下面是具体的实施步骤。
在node1上进行操作

$ pwd
/arch_log/restore

root@fjlt_zgsc_db01:/#mount
  node       mounted        mounted over    vfs       date        options     
-------- ---------------  ---------------  ------ ------------ ---------------
         /dev/hd4         /                jfs2   Apr 21 19:42 rw,log=/dev/hd8
         /dev/hd2         /usr             jfs2   Apr 21 19:42 rw,log=/dev/hd8
         /dev/hd9var      /var             jfs2   Apr 21 19:42 rw,log=/dev/hd8
         /dev/hd3         /tmp             jfs2   Apr 21 19:42 rw,log=/dev/hd8
         /dev/hd1         /home            jfs2   Apr 21 19:43 rw,log=/dev/hd8
         /dev/hd11admin   /admin           jfs2   Apr 21 19:43 rw,log=/dev/hd8
         /proc            /proc            procfs Apr 21 19:43 rw             
         /dev/hd10opt     /opt             jfs2   Apr 21 19:43 rw,log=/dev/hd8
         /dev/livedump    /var/adm/ras/livedump jfs2   Apr 21 19:43 rw,log=/dev/hd8
         /dev/lv_oracle10g /oracle10g       jfs2   Apr 21 19:43 rw,log=/dev/hd8
         /dev/lv_zs_arch_01 /arch_log        jfs2   Apr 21 19:43 rw,log=/dev/loglv01
         /dev/lv_zs_db2bk_01 /rman_db2_bk     jfs2   Apr 21 19:43 rw,log=/dev/loglv01
         /dev/lv_zs_bck_01 /rman_data       jfs2   Apr 21 19:43 rw,log=/dev/loglv00
         /dev/lv_cdump    /oracle10g/app/oracle/admin/zgscdb/cdump jfs2   Nov 16 12:05 rw,log=/dev/loglv02
         /dev/lv_dsg1     /dsg1            jfs2   Nov 16 12:10 rw,log=/dev/loglv02
fjlt_zgsc_db02 /arch_log/zgsc_db02_arch /arch_log/zgsc_db02_arch nfs3   Jan 16 16:26 

$ df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4          10.00      9.56    5%    12767     1% /
/dev/hd2          10.00      6.75   33%    62385     4% /usr
/dev/hd9var       10.00      9.23    8%    13956     1% /var
/dev/hd3          10.00      9.09   10%     3512     1% /tmp
/dev/hd1          10.00      8.99   11%     8013     1% /home
/dev/hd11admin      0.25      0.25    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt      10.00      9.73    3%    10356     1% /opt
/dev/livedump      0.25      0.25    1%        8     1% /var/adm/ras/livedump
/dev/lv_oracle10g     30.00     11.84   61%   215252     7% /oracle10g
/dev/lv_zs_arch_01    997.00    989.75    1%       32     1% /arch_log
/dev/lv_zs_db2bk_01    998.00    432.63   57%      167     1% /rman_db2_bk
/dev/lv_zs_bck_01   2992.00   1836.57   39%      224     1% /rman_data
/dev/lv_cdump    200.00    199.97    1%       15     1% /oracle10g/app/oracle/admin/zgscdb/cdump
/dev/lv_dsg1      49.75     43.97   12%     8831     1% /dsg1
fjlt_zgsc_db02:/arch_log/zgsc_db02_arch    997.00    971.00    3%       34     1% /arch_log/zgsc_db02_arch

/arch_log挂载点下空余空间接近1T,目标将归档日志还原到/arch_log/restore下

第一反应,通过v$archived_log找到17:00以后的归档日志序列号

select sequence#,completion_time from v$archived_log where completion_time > to_date('2012-02-15','hh24:mi:ss');

查询得出从sequence#为16147开始的归档日志为2012-02-17 17:00以后的归档日志

执行

$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Feb 16 13:07:12 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ZGSCDB (DBID=3629726729)

RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate channel c3 type disk;
5> allocate channel c4 type disk;
6> set archivelog destination to '/arch_log/restore';
7> restore archivelog  from logseq 16147;
8> release channel c1 ;ype disk
9> release channel c2;
10> release channel c3;
11> release channel c4;
12> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=4834 instance=zgscdb1 devtype=DISK

allocated channel: c2
channel c2: sid=5333 instance=zgscdb1 devtype=DISK

allocated channel: c3
channel c3: sid=4879 instance=zgscdb1 devtype=DISK

allocated channel: c4
channel c4: sid=5317 instance=zgscdb1 devtype=DISK

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 16-FEB-12
released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/16/2012 13:10:53
RMAN-20242: specification does not match any archive log in the recovery catalog

RMAN> list archivelog from logseq 16147;

没有任何输出,郁闷。。。。。。。。。
RMAN> list backup of archivelog all;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5115    169.62M    DISK        00:00:09     12-FEB-12     
        BP Key: 12219   Status: AVAILABLE  Compressed: NO  Tag: TAG20120212T014331
        Piece Name: /rman_data/zgscdb_rman/ZGSCDB_arch_20120212_0tn33hqa_s5149_p1_C1

  List of Archived Logs in backup set 5115
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    13769   12663219622525 11-FEB-12 12663220068092 11-FEB-12
  1    13770   12663220068092 11-FEB-12 12663220426071 11-FEB-12
  1    13771   12663220426071 11-FEB-12 12663220887716 11-FEB-12
  1    13772   12663220887716 11-FEB-12 12663222831310 12-FEB-12

省略部分输出

  List of Archived Logs in backup set 5197
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  2    16176   12670019198216 15-FEB-12 12670023192712 15-FEB-12
  2    16177   12670023192712 15-FEB-12 12670025565356 16-FEB-12
  2    16178   12670025565356 16-FEB-12 12670026143737 16-FEB-12
  2    16179   12670026143737 16-FEB-12 12670026897385 16-FEB-12
  2    16180   12670026897385 16-FEB-12 12670027305649 16-FEB-12
  2    16181   12670027305649 16-FEB-12 12670027693987 16-FEB-12

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5198    13.00G     DISK        00:01:07     16-FEB-12     


 List of Archived Logs in backup set 5193
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  2    16148   12670010177819 15-FEB-12 12670011207570 15-FEB-12
  2    16149   12670011207570 15-FEB-12 12670012283915 15-FEB-12
  2    16150   12670012283915 15-FEB-12 12670012754302 15-FEB-12
  2    16151   12670012754302 15-FEB-12 12670012969887 15-FEB-12
  2    16152   12670012969887 15-FEB-12 12670013212670 15-FEB-12
  2    16153   12670013212670 15-FEB-12 12670013780868 15-FEB-12
  2    16154   12670013780868 15-FEB-12 12670013976009 15-FEB-12
  2    16155   12670013976009 15-FEB-12 12670015183858 15-FEB-12
  2    16156   12670015183858 15-FEB-12 12670015441965 15-FEB-12
  2    16157   12670015441965 15-FEB-12 12670015599451 15-FEB-12
  2    16158   12670015599451 15-FEB-12 12670015722094 15-FEB-12
  2    16159   12670015722094 15-FEB-12 12670015785593 15-FEB-12
  2    16160   12670015785593 15-FEB-12 12670015847887 15-FEB-12
  2    16161   12670015847887 15-FEB-12 12670016000750 15-FEB-12

  Backup Set Copy #1 of backup set 5193
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:03:55     16-FEB-12       NO         TAG20120216T010430

    List of Backup Pieces for backup set 5193 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    12560   1   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_39n3e111_s5225_p1_C1
    12561   2   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_39n3e111_s5225_p2_C1
    12562   3   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_39n3e111_s5225_p3_C1


  List of Archived Logs in backup set 5198
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  2    16134   12668052207072 15-FEB-12 12668052339209 15-FEB-12
  2    16135   12668052339209 15-FEB-12 12668052456743 15-FEB-12
  2    16136   12668052456743 15-FEB-12 12668052579080 15-FEB-12
  2    16137   12668052579080 15-FEB-12 12668052695760 15-FEB-12
  2    16138   12668052695760 15-FEB-12 12668056672468 15-FEB-12
  2    16139   12668056672468 15-FEB-12 12668060445745 15-FEB-12
  2    16140   12668060445745 15-FEB-12 12668062444755 15-FEB-12
  2    16141   12668062444755 15-FEB-12 12668660576479 15-FEB-12
  2    16142   12668660576479 15-FEB-12 12668660718987 15-FEB-12
  2    16143   12668660718987 15-FEB-12 12668660871830 15-FEB-12
  2    16144   12668660871830 15-FEB-12 12668661303733 15-FEB-12
  2    16145   12668661303733 15-FEB-12 12668661585953 15-FEB-12
  2    16146   12668661585953 15-FEB-12 12668662079217 15-FEB-12
  2    16147   12668662079217 15-FEB-12 12670010177819 15-FEB-12

  Backup Set Copy #1 of backup set 5198
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:01:07     16-FEB-12       NO         TAG20120216T010430

    List of Backup Pieces for backup set 5198 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    12573   1   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_3an3e18j_s5226_p1_C1
    12574   2   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_3an3e18j_s5226_p2_C1
    12575   3   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_3an3e18j_s5226_p3_C1

  List of Archived Logs in backup set 5195
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  2    16162   12670016000750 15-FEB-12 12670016088204 15-FEB-12
  2    16163   12670016088204 15-FEB-12 12670016308969 15-FEB-12
  2    16164   12670016308969 15-FEB-12 12670016653192 15-FEB-12
  2    16165   12670016653192 15-FEB-12 12670017027396 15-FEB-12
  2    16166   12670017027396 15-FEB-12 12670017225549 15-FEB-12
  2    16167   12670017225549 15-FEB-12 12670017317060 15-FEB-12
  2    16168   12670017317060 15-FEB-12 12670017393441 15-FEB-12
  2    16169   12670017393441 15-FEB-12 12670017462567 15-FEB-12
  2    16170   12670017462567 15-FEB-12 12670017541021 15-FEB-12
  2    16171   12670017541021 15-FEB-12 12670017654244 15-FEB-12
  2    16172   12670017654244 15-FEB-12 12670017735163 15-FEB-12
  2    16173   12670017735163 15-FEB-12 12670017811611 15-FEB-12
  2    16174   12670017811611 15-FEB-12 12670017888323 15-FEB-12
  2    16175   12670017888323 15-FEB-12 12670019198216 15-FEB-12

  Backup Set Copy #1 of backup set 5195
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:03:56     16-FEB-12       NO         TAG20120216T010430

    List of Backup Pieces for backup set 5195 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    12566   1   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_38n3e111_s5224_p1_C1
    12567   2   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_38n3e111_s5224_p2_C1
    12568   3   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_38n3e111_s5224_p3_C1

可以看到从日志序列号为16147到16181的归档日志备份全部存。那为什么执行list backup of archivelog from logseq 16147;时没有输出呢?

查了下rman的list语法,执行如下命令

RMAN>list backup of archivelog from  time  "to_date('2012-02-15 17:00:00','yyyy-mm-dd hh24:mi:ss')";

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5189    287.12M    DISK        00:00:04     16-FEB-12     
        BP Key: 12552   Status: AVAILABLE  Compressed: NO  Tag: TAG20120216T010430
        Piece Name: /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_37n3e111_s5223_p1_C1

  List of Archived Logs in backup set 5189
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    14153   12670027303388 16-FEB-12 12670027694268 16-FEB-12

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5190    9.98G      DISK        00:02:45     16-FEB-12     

  List of Archived Logs in backup set 5190
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    14131   12668662080221 15-FEB-12 12670012207947 15-FEB-12
  1    14132   12670012207947 15-FEB-12 12670012971251 15-FEB-12
  1    14133   12670012971251 15-FEB-12 12670013978444 15-FEB-12
  1    14134   12670013978444 15-FEB-12 12670015440149 15-FEB-12
  1    14135   12670015440149 15-FEB-12 12670015722540 15-FEB-12
  1    14136   12670015722540 15-FEB-12 12670016001416 15-FEB-12

  Backup Set Copy #1 of backup set 5190
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:02:45     16-FEB-12       NO         TAG20120216T010430

    List of Backup Pieces for backup set 5190 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    12553   1   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_35n3e111_s5221_p1_C1
    12554   2   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_35n3e111_s5221_p2_C1

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5192    12.30G     DISK        00:03:09     16-FEB-12     

  List of Archived Logs in backup set 5192
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    14137   12670016001416 15-FEB-12 12670016653312 15-FEB-12
  1    14138   12670016653312 15-FEB-12 12670017030219 15-FEB-12
  1    14139   12670017030219 15-FEB-12 12670017394454 15-FEB-12
  1    14140   12670017394454 15-FEB-12 12670017657280 15-FEB-12
  1    14141   12670017657280 15-FEB-12 12670017890908 15-FEB-12
  1    14142   12670017890908 15-FEB-12 12670018260012 15-FEB-12
  1    14143   12670018260012 15-FEB-12 12670020257257 15-FEB-12
  1    14144   12670020257257 15-FEB-12 12670022421290 15-FEB-12
  1    14145   12670022421290 15-FEB-12 12670023190650 15-FEB-12
  1    14146   12670023190650 15-FEB-12 12670024544870 15-FEB-12
  1    14147   12670024544870 15-FEB-12 12670026609312 16-FEB-12
  1    14148   12670026609312 16-FEB-12 12670026743414 16-FEB-12
  1    14149   12670026743414 16-FEB-12 12670026894479 16-FEB-12
  1    14150   12670026894479 16-FEB-12 12670027048502 16-FEB-12
  1    14151   12670027048502 16-FEB-12 12670027176883 16-FEB-12
  1    14152   12670027176883 16-FEB-12 12670027303388 16-FEB-12

  Backup Set Copy #1 of backup set 5192
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:03:09     16-FEB-12       NO         TAG20120216T010430

    List of Backup Pieces for backup set 5192 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    12557   1   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_32n3e111_s5218_p1_C1
    12558   2   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_32n3e111_s5218_p2_C1
    12559   3   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_32n3e111_s5218_p3_C1

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5193    13.27G     DISK        00:03:55     16-FEB-12     

  List of Archived Logs in backup set 5193
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  2    16148   12670010177819 15-FEB-12 12670011207570 15-FEB-12
  2    16149   12670011207570 15-FEB-12 12670012283915 15-FEB-12
  2    16150   12670012283915 15-FEB-12 12670012754302 15-FEB-12
  2    16151   12670012754302 15-FEB-12 12670012969887 15-FEB-12
  2    16152   12670012969887 15-FEB-12 12670013212670 15-FEB-12
  2    16153   12670013212670 15-FEB-12 12670013780868 15-FEB-12
  2    16154   12670013780868 15-FEB-12 12670013976009 15-FEB-12
  2    16155   12670013976009 15-FEB-12 12670015183858 15-FEB-12
  2    16156   12670015183858 15-FEB-12 12670015441965 15-FEB-12
  2    16157   12670015441965 15-FEB-12 12670015599451 15-FEB-12
  2    16158   12670015599451 15-FEB-12 12670015722094 15-FEB-12
  2    16159   12670015722094 15-FEB-12 12670015785593 15-FEB-12
  2    16160   12670015785593 15-FEB-12 12670015847887 15-FEB-12
  2    16161   12670015847887 15-FEB-12 12670016000750 15-FEB-12

  Backup Set Copy #1 of backup set 5193
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:03:55     16-FEB-12       NO         TAG20120216T010430

    List of Backup Pieces for backup set 5193 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    12560   1   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_39n3e111_s5225_p1_C1
    12561   2   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_39n3e111_s5225_p2_C1
    12562   3   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_39n3e111_s5225_p3_C1

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5195    13.28G     DISK        00:03:56     16-FEB-12     

  List of Archived Logs in backup set 5195
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  2    16162   12670016000750 15-FEB-12 12670016088204 15-FEB-12
  2    16163   12670016088204 15-FEB-12 12670016308969 15-FEB-12
  2    16164   12670016308969 15-FEB-12 12670016653192 15-FEB-12
  2    16165   12670016653192 15-FEB-12 12670017027396 15-FEB-12
  2    16166   12670017027396 15-FEB-12 12670017225549 15-FEB-12
  2    16167   12670017225549 15-FEB-12 12670017317060 15-FEB-12
  2    16168   12670017317060 15-FEB-12 12670017393441 15-FEB-12
  2    16169   12670017393441 15-FEB-12 12670017462567 15-FEB-12
  2    16170   12670017462567 15-FEB-12 12670017541021 15-FEB-12
  2    16171   12670017541021 15-FEB-12 12670017654244 15-FEB-12
  2    16172   12670017654244 15-FEB-12 12670017735163 15-FEB-12
  2    16173   12670017735163 15-FEB-12 12670017811611 15-FEB-12
  2    16174   12670017811611 15-FEB-12 12670017888323 15-FEB-12
  2    16175   12670017888323 15-FEB-12 12670019198216 15-FEB-12

  Backup Set Copy #1 of backup set 5195
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:03:56     16-FEB-12       NO         TAG20120216T010430

    List of Backup Pieces for backup set 5195 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    12566   1   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_38n3e111_s5224_p1_C1
    12567   2   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_38n3e111_s5224_p2_C1
    12568   3   AVAILABLE   /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_38n3e111_s5224_p3_C1

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5197    3.02G      DISK        00:00:26     16-FEB-12     
        BP Key: 12572   Status: AVAILABLE  Compressed: NO  Tag: TAG20120216T010430
        Piece Name: /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_3bn3e18n_s5227_p1_C1

  List of Archived Logs in backup set 5197
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  2    16176   12670019198216 15-FEB-12 12670023192712 15-FEB-12
  2    16177   12670023192712 15-FEB-12 12670025565356 16-FEB-12
  2    16178   12670025565356 16-FEB-12 12670026143737 16-FEB-12
  2    16179   12670026143737 16-FEB-12 12670026897385 16-FEB-12
  2    16180   12670026897385 16-FEB-12 12670027305649 16-FEB-12
  2    16181   12670027305649 16-FEB-12 12670027693987 16-FEB-12

看到都需要的归档日志都包含进去了,满心欢喜的执行如下命令

RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate channel c3 type disk;
5> allocate channel c4 type disk;
6> set archivelog destination to '/arch_log/restore';
7> restore archivelog from time  "to_date('2012-02-15 17:00:00','yyyy-mm-dd hh24:mi:ss')";
8> release channel c1;
9> release channel c2;
10> release channel c3;
11> release channel c4;
12> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=4849 instance=zgscdb1 devtype=DISK

allocated channel: c2
channel c2: sid=5326 instance=zgscdb1 devtype=DISK

allocated channel: c3
channel c3: sid=4946 instance=zgscdb1 devtype=DISK

allocated channel: c4
channel c4: sid=4879 instance=zgscdb1 devtype=DISK

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 16-FEB-12

archive log thread 1 sequence 14155 is already on disk as file /arch_log/zgsc_db01_arch/1_14155_743864713.dbf
archive log thread 1 sequence 14156 is already on disk as file /arch_log/zgsc_db01_arch/1_14156_743864713.dbf
archive log thread 1 sequence 14157 is already on disk as file /arch_log/zgsc_db01_arch/1_14157_743864713.dbf
archive log thread 1 sequence 14158 is already on disk as file /arch_log/zgsc_db01_arch/1_14158_743864713.dbf
archive log thread 1 sequence 14159 is already on disk as file /arch_log/zgsc_db01_arch/1_14159_743864713.dbf
archive log thread 1 sequence 14160 is already on disk as file /arch_log/zgsc_db01_arch/1_14160_743864713.dbf
archive log thread 1 sequence 14161 is already on disk as file /arch_log/zgsc_db01_arch/1_14161_743864713.dbf
archive log thread 1 sequence 14162 is already on disk as file /arch_log/zgsc_db01_arch/1_14162_743864713.dbf
archive log thread 1 sequence 14163 is already on disk as file /arch_log/zgsc_db01_arch/1_14163_743864713.dbf
archive log thread 1 sequence 14164 is already on disk as file /arch_log/zgsc_db01_arch/1_14164_743864713.dbf
archive log thread 1 sequence 14165 is already on disk as file /arch_log/zgsc_db01_arch/1_14165_743864713.dbf

released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/16/2012 13:47:51
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 2 seq 16209 lowscn 12670043591603 found to restore
RMAN-06025: no backup of log thread 2 seq 16208 lowscn 12670043181908 found to restore
RMAN-06025: no backup of log thread 2 seq 16207 lowscn 12670043081120 found to restore
RMAN-06025: no backup of log thread 2 seq 16206 lowscn 12670042973201 found to restore
RMAN-06025: no backup of log thread 2 seq 16205 lowscn 12670042654302 found to restore
RMAN-06025: no backup of log thread 2 seq 16204 lowscn 12670041610965 found to restore
RMAN-06025: no backup of log thread 2 seq 16203 lowscn 12670040751853 found to restore
RMAN-06025: no backup of log thread 2 seq 16202 lowscn 12670040584982 found to restore
RMAN-06025: no backup of log thread 2 seq 16201 lowscn 12670040305341 found to restore
RMAN-06025: no backup of log thread 2 seq 16200 lowscn 12670039987074 found to restore
RMAN-06025: no backup of log thread 2 seq 16199 lowscn 12670039745774 found to restore
RMAN-06025: no backup of log thread 2 seq 16198 lowscn 12670039250107 found to restore
RMAN-06025: no backup of log thread 2 seq 16197 lowscn 12670038751777 found to restore
RMAN-06025: no backup of log thread 2 seq 16196 lowscn 12670038122926 found to restore
RMAN-06025: no backup of log thread 2 seq 16195 lowscn 12670037110291 found to restore
RMAN-06025: no backup of log thread 2 seq 16194 lowscn 12670036384317 found to restore
RMAN-06025: no backup of log thread 2 seq 16193 lowscn 12670036130529 found to restore
RMAN-06025: no backup of log thread 2 seq 16192 lowscn 12670035930022 found to restore
RMAN-06025: no backup of log thread 2 seq 16191 lowscn 12670035736951 found to restore
RMAN-06025: no backup of log thread 2 seq 16190 lowscn 12670034378583 found to restore
RMAN-06025: no backup of log thread 2 seq 16189 lowscn 12670033507562 found to restore
RMAN-06025: no backup of log thread 2 seq 16188 lowscn 12670032888750 found to restore
RMAN-06025: no backup of log thread 2 seq 16187 lowscn 12670031934562 found to restore
RMAN-06025: no backup of log thread 2 seq 16186 lowscn 12670031295273 found to restore
RMAN-06025: no backup of log thread 2 seq 16185 lowscn 12670029669200 found to restore
RMAN-06025: no backup of log thread 2 seq 16184 lowscn 12670029122812 found to restore
RMAN-06025: no backup of log thread 2 seq 16183 lowscn 12670027774997 found to restore
RMAN-06025: no backup of log thread 2 seq 16182 lowscn 12670027693987 found to restore

报错,郁闷,超级郁闷
$ ls /arch_log/zgsc_01_arch
1_14155_743864713.dbf  1_14158_743864713.dbf  1_14161_743864713.dbf  1_14164_743864713.dbf  1_14167_743864713.dbf
1_14156_743864713.dbf  1_14159_743864713.dbf  1_14162_743864713.dbf  1_14165_743864713.dbf
1_14157_743864713.dbf  1_14160_743864713.dbf  1_14163_743864713.dbf  1_14166_743864713.dbf

观察报错发现没有找到的备份信息主要都是thread 2的备份信息,结合上面ls命令的输出分析得出,线程为1日志序列号从14155至14166的归档日志虽然为2012-02-15 17:00之后的归档日志,但却仍再使用。使用restore archivelog from time  "to_date('2012-02-15 17:00:00','yyyy-mm-dd hh24:mi:ss')";命令恢复时会提示already on disk。
结合list backup of archivelog from  time  "to_date('2012-02-15 17:00:00','yyyy-mm-dd hh24:mi:ss')";

node1
$ ls /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_39n3e111_s5225_p1_C1
ls: 0653-341 The file /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_39n3e111_s5225_p1_C1 does not exist

node2
$ ls /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_35n3e111_s5221_p1_C1
ls: 0653-341 The file /rman_data/zgscdb_rman/ZGSCDB_arch_20120216_35n3e111_s5221_p1_C1 does not exist.
发现双节点都只有各自的备份文件,备份文件没有进行存储共享的配置(出于安全考虑,确实不应该把备份配置成共享的,不过设置成nfs挂载应该是没问题)。呵呵,犯了个低级错误。

经过以上分析得出,restore archivelog from time  "to_date('2012-02-15 17:00:00','yyyy-mm-dd hh24:mi:ss')";命令会自动查找所需的备份文件进行还原,但是由于双节点无法同时访问所有需要备份文件,故需在特定节点还原指定的日志序列号,进行逐个还原,即 需要在相应的实例上还原相应thread对应的归档日志。

thread的意义: RAC环境下,多台主机同时访问位于共有的磁盘阵列的共同的数据库结构。每台主机上的数据库例程分别写自己的日志文件,不同的例程通过thread来区分。

于是根据list backup of archivelog from  time  "to_date('2012-02-15 17:00:00','yyyy-mm-dd hh24:mi:ss')"
命令得出的归档日志和备份信息,执行如下命令恢复

在RAC节点node 1上执行
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
set archivelog destination to '/arch_log/restore';
restore archivelog from logseq 14131 until logseq 14153 thread 1;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

在RAC节点node 2上执行
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
set archivelog destination to '/arch_log/restore';
restore archivelog from logseq 16148 until logseq 16181 thread 2;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

还原成功。

这里顺带说一句,如果需要进行数据库恢复,那么必须在能够访问所有归档日志的节点上进行,这里是node1

感谢惜分飞,边城,杨帅君,select.简单的指导

本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

记一次Oracle 生产库还原归档日志经历(原创)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
同事打电话跟我说,数据库CPU过高、swap交换频繁,要我马上看看,这里记录下整个过程以供大家参考,
其实事情蛮简单的,看看图片就明白了, 本来在讨论重量级框架YII和ZF,说是java 和ROR的风格,效率可能
操作系统 数据库 问题 当程序部署到这个系统之后,发现应用运行效率很差。一般这种情况,多数问题出
第一次宕机时,初始以为是系统内存溢出,于是重启应用服务器,发现应用服务器在启动时报错,错误为
记一次性能调优经历 最近项目需要将数据库表的数据生成报文,报文以XML文件形式保存。由于这些数据
我的操作系统是win7旗舰版,.net开发环境是Visual Studio 2010,数据库是Sql Server 2008。 最近一
最近一直在做测试方面的事情,被测的一些功能需要连接到FTP服务器上。而我在做本地测试时为了方便,
我的操作系统是win7旗舰版,.net开发环境是Visual Studio 2010,数据库是Sql Server 2008。 最近一
这几天在准备升级JDK版本到1.6,对目前线上JVM(版本是1.5.0_08-b03) GC日志进行了分析,发现一些
1)缘起 之前我在mac上自己搞了个shell脚本,一条龙完成svn代码更新、打包、应用部署、jboss启动等
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号