当前位置： 首页 > Oracle, oracle 11g > 正文

备份集缺少备份片导致RMAN在RECOVER过程中遇到ORA-01547和ORA-01194错误

Jun242015
作者：dbdream   发布：2015-06-24 18:17   分类：Oracle, oracle 11g     抢沙发  

在异机使用RMAN恢复数据库时,RESTORE完成后,RECOVER使用归档的时候遇到ORA-01547和ORA-01194错误，数据库版本11.2.0.4.0 64 bit for Linux。

Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/u01/uatdb/system.dbf'

released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/13/2015 15:19:15
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 1750 and starting SCN of 109797512192 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 1749 and starting SCN of 109796599486 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 1748 and starting SCN of 109794417457 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 1747 and starting SCN of 109792609746 found to r
estore
… …
RMAN-06025: no backup of archived log for thread 1 with sequence 1914 and starting SCN of 109768864693 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1913 and starting SCN of 109751979804 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1912 and starting SCN of 109751702337 found to restore

RMAN>

竟然报错说找不到归档日志的备份，可是我传输过来的备份中的确包含这些归档日志。

RMAN> list backup;
BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
751     2.66G      DISK        00:06:44     2015-06-11 04:29:46
        BP Key: 751   Status: EXPIRED  Compressed: YES  Tag: TAG20150611T042300
        Piece Name: /data/ivldb/lv2_20150611/level_2_ivldb_archcivelog_746_1

  List of Archived Logs in backup set 751
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    1908    109751081454 2015-06-10 03:05:45 109751319029 2015-06-10 03:33:17
  1    1909    109751319029 2015-06-10 03:33:17 109751601634 2015-06-10 04:13:26
  1    1910    109751601634 2015-06-10 04:13:26 109751696643 2015-06-10 04:24:42
  1    1911    109751696643 2015-06-10 04:24:42 109751702337 2015-06-10 04:25:03
  1    1912    109751702337 2015-06-10 04:25:03 109751979804 2015-06-10 04:59:26
  1    1913    109751979804 2015-06-10 04:59:26 109768864693 2015-06-10 11:00:07
  1    1914    109768864693 2015-06-10 11:00:07 109770633190 2015-06-10 14:09:48
  1    1915    109770633190 2015-06-10 14:09:48 109772735327 2015-06-10 17:45:24
  1    1916    109772735327 2015-06-10 17:45:24 109774533821 2015-06-10 21:36:12
  1    1917    109774533821 2015-06-10 21:36:12 109776127082 2015-06-11 00:22:12
  1    1918    109776127082 2015-06-11 00:22:12 109776202917 2015-06-11 00:29:21
  1    1919    109776202917 2015-06-11 00:29:21 109776307261 2015-06-11 00:36:06
  1    1920    109776307261 2015-06-11 00:36:06 109776411252 2015-06-11 00:46:14
  2    1717    109750976035 2015-06-10 02:50:08 109751080872 2015-06-10 03:05:37
  2    1718    109751080872 2015-06-10 03:05:37 109751164957 2015-06-10 03:14:20
  2    1719    109751164957 2015-06-10 03:14:20 109751222866 2015-06-10 03:20:17
  2    1720    109751222866 2015-06-10 03:20:17 109751317156 2015-06-10 03:33:09
  2    1721    109751317156 2015-06-10 03:33:09 109751378597 2015-06-10 03:41:56
  2    1722    109751378597 2015-06-10 03:41:56 109751430954 2015-06-10 03:48:02
  2    1723    109751430954 2015-06-10 03:48:02 109751599684 2015-06-10 04:13:18
  2    1724    109751599684 2015-06-10 04:13:18 109751698236 2015-06-10 04:24:46
  2    1725    109751698236 2015-06-10 04:24:46 109751702322 2015-06-10 04:24:58
  2    1726    109751702322 2015-06-10 04:24:58 109751784925 2015-06-10 04:33:09
  2    1727    109751784925 2015-06-10 04:33:09 109751894291 2015-06-10 04:48:00
  2    1728    109751894291 2015-06-10 04:48:00 109751979660 2015-06-10 04:59:15
  2    1729    109751979660 2015-06-10 04:59:15 109752926437 2015-06-10 07:21:59
  2    1730    109752926437 2015-06-10 07:21:59 109769191465 2015-06-10 11:30:03
  2    1731    109769191465 2015-06-10 11:30:03 109771748217 2015-06-10 15:54:37
  2    1732    109771748217 2015-06-10 15:54:37 109773800633 2015-06-10 20:05:02
  2    1733    109773800633 2015-06-10 20:05:02 109774988978 2015-06-10 22:09:14
  2    1734    109774988978 2015-06-10 22:09:14 109776308213 2015-06-11 00:36:15

经查看，所有归档备份都已经传到UAT服务器，而且权限都是正确的，那么提示找不到就很奇怪了。通过查找资料，说这个错误是由于控制文件比数据文件旧导致的，这在我的数据库中也是不存在的问题。

SQL> select * from v$recovery_log;

   THREAD#  SEQUENCE# TIME                ARCHIVE_NAME
---------- ---------- ------------------- --------------------------------------------------
         1       1909 2015-06-10 03:33:17
         1       1910 2015-06-10 04:13:26
… …
         1       1974 2015-06-12 04:24:23
         1       1975 2015-06-12 04:24:44
         1       1976 2015-06-12 04:31:10 +ARCH2/ivldb/1_1976_875510192.dbf
         1       1977 2015-06-12 04:46:00 +ARCH2/ivldb/1_1977_875510192.dbf
… …
         1       1991 2015-06-13 02:52:41 +ARCH2/ivldb/1_1991_875510192.dbf
         1       1992 2015-06-13 02:59:39 +ARCH2/ivldb/1_1992_875510192.dbf
         2       1723 2015-06-10 03:48:02
         2       1724 2015-06-10 04:13:18
… …
         2       1758 2015-06-12 04:24:29
         2       1759 2015-06-12 04:24:40
         2       1760 2015-06-12 04:46:08 +ARCH2/ivldb/2_1760_875510192.dbf
         2       1761 2015-06-12 10:58:37 +ARCH2/ivldb/2_1761_875510192.dbf
… …
         2       1788 2015-06-13 03:07:01 +ARCH2/ivldb/2_1788_875510192.dbf
         2       1789 2015-06-13 03:08:37 +ARCH2/ivldb/2_1789_875510192.dbf

151 rows selected.

此时还原归档日志也报同样的报找不到归档的有效备份错误。

RMAN> run{
2> set archivelog destination to '/data/ivldb/arch';
restore archivelog from logseq 1902 until logseq 1921 thread 1;
3> 4> }

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 2015-06-13 16:49:59
using channel ORA_DISK_1

starting media recovery
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/13/2015 15:19:15
RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 1922 and starting SCN of 109796850118 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1921 and starting SCN of 109794839078 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1920 and starting SCN of 109793162900 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1919 and starting SCN of 109790717884 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1918 and starting SCN of 109778362856 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1917 and starting SCN of 109778362790 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1916 and starting SCN of 109777921226 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1915 and starting SCN of 109777876855 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1914 and starting SCN of 109777832235 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1913 and starting SCN of 109777787808 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1912 and starting SCN of 109777745078 found to restore

RMAN>

仔细想了想，UAT服务器上的备份和生产服务器上的备份有什么不同，想到，我只把我需要的备份拷贝到了UAT服务器，不需要的备份我并没有拷贝过来，可能是因为备份集缺少备份片，导致整个备份集都失效了，删除不存在的备份片试试。

RMAN> CROSSCHECK BACKUP;

RMAN> delete expired backupset;

删除不存在的备份片后，归档日志已经可以还原。

RMAN> run{
2> set archivelog destination to '/data/ivldb/arch';
restore archivelog from logseq 1912 until logseq 1921 thread 1;
3> 4> }

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 2015-06-13 16:49:59
using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/data/ivldb/arch
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1912
channel ORA_DISK_1: reading from backup piece /data/ivldb/lv2_20150611/level_2_ivldb_archcivelog_746_1
channel ORA_DISK_1: piece handle=/data/ivldb/lv2_20150611/level_2_ivldb_archcivelog_746_1 tag=TAG20150611T042300
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:05
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/data/ivldb/arch
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1913
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1914
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1915
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1916
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1917
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1918
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1919
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1920
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1921
channel ORA_DISK_1: reading from backup piece /data/ivldb/lv2_20150612/level_2_ivldb_archcivelog_755_1
channel ORA_DISK_1: piece handle=/data/ivldb/lv2_20150612/level_2_ivldb_archcivelog_755_1 tag=TAG20150612T042449
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:25
Finished restore at 2015-06-13 17:04:30

SQL> select * from v$recovery_log  where rownum<20;

   THREAD#  SEQUENCE# TIME                ARCHIVE_NAME
---------- ---------- ------------------- --------------------------------------------------
         1       1909 2015-06-10 03:33:17 /data/ivldb/arch/1_1909_875510192.dbf
         1       1910 2015-06-10 04:13:26 /data/ivldb/arch/1_1910_875510192.dbf
         1       1911 2015-06-10 04:24:42 /data/ivldb/arch/1_1911_875510192.dbf
         1       1912 2015-06-10 04:25:03 /data/ivldb/arch/1_1912_875510192.dbf
         1       1913 2015-06-10 04:59:26 /data/ivldb/arch/1_1913_875510192.dbf
         1       1914 2015-06-10 11:00:07 /data/ivldb/arch/1_1914_875510192.dbf
         1       1915 2015-06-10 14:09:48 /data/ivldb/arch/1_1915_875510192.dbf
         1       1916 2015-06-10 17:45:24 /data/ivldb/arch/1_1916_875510192.dbf
         1       1917 2015-06-10 21:36:12 /data/ivldb/arch/1_1917_875510192.dbf
         1       1918 2015-06-11 00:22:12 /data/ivldb/arch/1_1918_875510192.dbf
         1       1919 2015-06-11 00:29:21 /data/ivldb/arch/1_1919_875510192.dbf
         1       1920 2015-06-11 00:36:06 /data/ivldb/arch/1_1920_875510192.dbf
         1       1921 2015-06-11 00:46:14 /data/ivldb/arch/1_1921_875510192.dbf
         1       1922 2015-06-11 00:59:08
         1       1923 2015-06-11 01:09:31
         1       1924 2015-06-11 01:16:49
         1       1925 2015-06-11 01:25:26
         1       1926 2015-06-11 01:37:18
         1       1927 2015-06-11 01:42:10

19 rows selected.

此时恢复可以正常进行。

RMAN> run{
2> sql 'alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"';
3> set until time  '2015-06-11 23:59:59';
4> recover database;
5> }

sql statement: alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"

executing command: SET until clause

Starting recover at 2015-06-13 17:06:01
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /data/u01/uatdb/user01.dbf
destination for restore of datafile 00007: /data/u01/uatdb/ivldb2_undo.dbf
destination for restore of datafile 00009: /data/u01/uatdb/ts_inx_bsc01.dbf
destination for restore of datafile 00014: /data/u01/uatdb/ts_ful_if01.dbf
destination for restore of datafile 00017: /data/u01/uatdb/ts_task01.dbf
destination for restore of datafile 00020: /data/u01/uatdb/ts_mig01.dbf
destination for restore of datafile 00023: /data/u01/uatdb/ts_ful02.dbf
destination for restore of datafile 00025: /data/u01/uatdb/ts_inx_ful02.dbf
destination for restore of datafile 00027: /data/u01/uatdb/ts_inx_ord02.dbf
destination for restore of datafile 00030: /data/u01/uatdb/ts_ful06.dbf
destination for restore of datafile 00033: /data/u01/uatdb/ts_inx_ful06.dbf
channel ORA_DISK_1: reading from backup piece /data/ivldb/lv2_20150611/level_2_ivldb_datafile_740_1
channel ORA_DISK_1: piece handle=/data/ivldb/lv2_20150611/level_2_ivldb_datafile_740_1 tag=TAG20150611T040013
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:30:55
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /data/u01/uatdb/system.dbf
destination for restore of datafile 00003: /data/u01/uatdb/ts_inx_ful03.dbf
destination for restore of datafile 00004: /data/u01/uatdb/ts_inx_ful04.dbf
destination for restore of datafile 00010: /data/u01/uatdb/ts_ord01.dbf
destination for restore of datafile 00012: /data/u01/uatdb/ts_ful01.dbf
destination for restore of datafile 00015: /data/u01/uatdb/ts_inx_ful_if01.dbf
destination for restore of datafile 00018: /data/u01/uatdb/ts_mc01.dbf
destination for restore of datafile 00021: /data/u01/uatdb/ts_bsc02.dbf
destination for restore of datafile 00024: /data/u01/uatdb/ts_ful03.dbf
destination for restore of datafile 00028: /data/u01/uatdb/ts_ful04.dbf
destination for restore of datafile 00031: /data/u01/uatdb/ts_bsc03.dbf
destination for restore of datafile 00034: /data/u01/uatdb/ts_inx_ful07.dbf
channel ORA_DISK_1: reading from backup piece /data/ivldb/lv2_20150611/level_2_ivldb_datafile_741_1

最终确认是因为备份集缺少备份片，导致RMAN认为整个备份集都失效了，也就不会去失效的备份中查看备份片是否包含所需的归档日志，就抛出这个错误，并不是因为控制文件的文件头比数据文件的文件头旧导致的。

本文固定链接: https://www.dbdream.com.cn/2015/06/%e5%a4%87%e4%bb%bd%e9%9b%86%e7%bc%ba%e5%b0%91%e5%a4%87%e4%bb%bd%e7%89%87%e5%af%bc%e8%87%b4rman%e5%9c%a8recover%e8%bf%87%e7%a8%8b%e4%b8%ad%e9%81%87%e5%88%b0ora-01547%e5%92%8cora-01194%e9%94%99%e8%af%af/ | 信春哥，系统稳，闭眼上线不回滚！

该日志由 dbdream 于2015年06月24日发表在 Oracle, oracle 11g 分类下， 你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 备份集缺少备份片导致RMAN在RECOVER过程中遇到ORA-01547和ORA-01194错误 | 信春哥，系统稳，闭眼上线不回滚！
关键字: , , , ,
【上一篇】
【下一篇】

您可能还会对这些文章感兴趣！

备份集缺少备份片导致RMAN在RECOVER过程中遇到ORA-01547和ORA-01194错误：等您坐沙发呢！

发表评论

快捷键：Ctrl+Enter