设为首页收藏本站
开启辅助访问 切换到窄版

ORACLE SOS

 找回密码
 立即注册

QQ登录

只需一步，快速开始

搜索
ORACLE SOS»论坛 › ORACLE运维管理 › 备份与恢复 › 常规手段恢复 › 异机恢复数据，recover database失败
12下一页
返回列表 发新帖
查看: 13362|回复: 12

异机恢复数据，recover database失败

[复制链接]
Cyrill

6

主题

24

帖子

99

积分

注册会员

Rank: 2

积分
99
发表于 2014-3-27 12:40:45 | 显示全部楼层 |阅读模式
飞总你好，我在 生产库 拷了备份在测试库上恢复。两个环境一样。11G RAC ASM。
在rman下 restore database 正常，但是recover就报错了。如下：
RMAN> recover database;
Starting recover at 2014-03-27 12:39:46
using channel ORA_DISK_1

starting media recovery

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/jyzx/datafile/system.907.843212467'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/27/2014 12:39:48
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 2813 and starting SCN of 15806643173 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2812 and starting SCN of 15792541309 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2811 and starting SCN of 15784647655 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2784 and starting SCN of 15292730788 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2783 and starting SCN of 15286481946 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2782 and starting SCN of 15258323601 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2781 and starting SCN of 15249517391 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2780 and starting SCN of 15240318625 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2779 and starting SCN of 15229203241 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2778 and starting SCN of 15218509747 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2777 and starting SCN of 15181104153 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2776 and starting SCN of 15181098744 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2775 and starting SCN of 15176955241 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2774 and starting SCN of 15149115218 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2773 and starting SCN of 15140369728 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2772 and starting SCN of 15132540166 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 2771 and starting SCN of 15116908098 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2835 and starting SCN of 15807598807 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2834 and starting SCN of 15794019930 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2833 and starting SCN of 15791096480 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2832 and starting SCN of 15784650722 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2808 and starting SCN of 15292735737 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2807 and starting SCN of 15257702779 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2806 and starting SCN of 15247290179 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2805 and starting SCN of 15231981655 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2804 and starting SCN of 15227887170 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2803 and starting SCN of 15220968633 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2802 and starting SCN of 15181104156 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2801 and starting SCN of 15181098753 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2800 and starting SCN of 15159750560 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2799 and starting SCN of 15142816840 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2798 and starting SCN of 15137487942 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2797 and starting SCN of 15121405257 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2796 and starting SCN of 15116899163 found to restore

然后SQL> recover database using backup controlfile until cancel;
ORA-00279: change 13339201159 generated at 03/03/2014 08:33:35 needed for
thread 2
ORA-00289: suggestion : +DATA
ORA-00280: change 13339201159 for thread 2 is in sequence #2659


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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/jyzx/datafile/system.907.843212467'


ORA-01112: media recovery not started

.............................................................................................

是什么问题呢？
如何解决？
麻烦飞总看看，谢谢!
回复

使用道具 举报

xifenfei

95

主题

266

帖子

1719

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1719
发表于 2014-3-27 12:49:04 | 显示全部楼层
1. 我从db_recover_xifenfei.html文件中看，发现数据库有归档日志，你可以把归档日志拷贝到该测试机器上，然后使用catalog start with 注册进去备份集，然后进行recover


2. 如果确实没有归档日志，尝试如下操作：
１）使用_allow_resetlogs_corruption=true
２） recover database using backup controlfile until cancel;
３）ｒｅｓｅｔｌｏｇｓ

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有帐号？立即注册

x

Q Q:107644445
Tel:13429648788
Email:dba@xifenfei.com
个人Blog(惜分飞)
提供专业ORACLE技术支持(数据恢复,安装实施,升级迁移,备份容灾,故障诊断,系统优化等)
回复 支持 反对

使用道具 举报

Cyrill

6

主题

24

帖子

99

积分

注册会员

Rank: 2

积分
99
 楼主| 发表于 2014-3-27 14:51:33 | 显示全部楼层
xifenfei 发表于 2014-3-27 12:49
1. 我从db_recover_xifenfei.html文件中看，发现数据库有归档日志，你可以把归档日志拷贝到该测试机器上， ...

飞总，我用了第二个办法。数据库可以启动了。但是我查询一个表的时候出错了。而且也无法用PL/SQL developer连接到数据库。是什么问题？
SQL> startup
ORACLE instance started.

Total System Global Area 5044088832 bytes
Fixed Size                  2237048 bytes
Variable Size            1006636424 bytes
Database Buffers         4026531840 bytes
Redo Buffers                8683520 bytes
Database mounted.
Database opened.


SQL> select * from QYJYOA.t_oa_xxjl;
ERROR:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '+DATA/jyzx/datafile/qyjyoa.902.843212449'



no rows selected
回复 支持 反对

使用道具 举报

xifenfei

95

主题

266

帖子

1719

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1719
发表于 2014-3-27 14:54:38 | 显示全部楼层
[size=+2]Datafile Header Information


TS#FILE#TABLESPACE_NAMESTATUSERRORFORMATRECFUZCREATE_TIMESCNRESETLOGS SCN
0
1
SYSTEMONLINE
10
 YES2010-09-05 05:49:14
13339201159
2921281121
1
2
SYSAUXONLINE
10
 NO2010-09-05 05:49:23
13339201159
2921281121
2
3
UNDOTBS1ONLINE
10
 NO2010-09-05 06:24:14
13339201159
2921281121
4
4
USERSONLINE
10
 NO2010-09-05 05:49:43
13339201159
2921281121
5
5
UNDOTBS2ONLINE
10
 NO2011-10-08 12:12:28
13339201159
2921281121
6
6
ROEEEONLINE
10
 YES2011-10-09 14:17:57
13339201159
2921281121
7
7
STARFLOWOFFLINE
10
 YES2011-10-09 14:19:48
13339201159
2921281121
8
8
QYJYOAOFFLINE
10
 YES2011-10-09 14:25:26
13339201159
2921281121
9
9
JYZXZJKONLINE
10
 NO2011-10-09 14:26:36
13339201159
2921281121
10
10
INDEXESONLINE
10
 NO2011-10-09 14:27:24
13339201159
2921281121
23
11
SALARYONLINE
10
 NO2012-02-08 16:48:52
13339201159
2921281121
24
12
DELHFONLINE
10
 NO2012-03-30 10:52:12
13339201159
2921281121
文件已经offline状态，所以出现上面错误


Q Q:107644445
Tel:13429648788
Email:dba@xifenfei.com
个人Blog(惜分飞)
提供专业ORACLE技术支持(数据恢复,安装实施,升级迁移,备份容灾,故障诊断,系统优化等)
回复 支持 反对

使用道具 举报

Cyrill

6

主题

24

帖子

99

积分

注册会员

Rank: 2

积分
99
 楼主| 发表于 2014-5-20 16:33:15 | 显示全部楼层
xifenfei 发表于 2014-3-27 12:49
1. 我从db_recover_xifenfei.html文件中看，发现数据库有归档日志，你可以把归档日志拷贝到该测试机器上， ...

RMAN> recover database;

Starting recover at 2014-05-20 16:31:44
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=163 instance=jyzx1 device type=DISK

starting media recovery

archived log for thread 1 with sequence 3167 is already on disk as file /ora_bak/thread_1_seq_3167.447.848070515
archived log for thread 2 with sequence 3227 is already on disk as file /ora_bak/thread_2_seq_3227.399.848070633
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 5 needs more recovery to be consistent
ORA-01110: data file 5: '+DATA/jyzx/datafile/undotbs2.311.848054907'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/20/2014 16:31:52
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 2 with sequence 3183 and starting SCN of 21450080302 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 3182 and starting SCN of 21438204941 found to restore
RMAN-06025: no backup of archived log for thread 2 with sequence 3181 and starting SCN of 21401832384 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 3134 and starting SCN of 21401836732 found to restore

RMAN>



.................................
飞总，我使用第一种方法后，recover还是失败，报错如上。
如何解决？
回复 支持 反对

使用道具 举报

xifenfei

95

主题

266

帖子

1719

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1719
发表于 2014-5-20 16:40:33 | 显示全部楼层
还是确实归档日志，继续应用日志

Q Q:107644445
Tel:13429648788
Email:dba@xifenfei.com
个人Blog(惜分飞)
提供专业ORACLE技术支持(数据恢复,安装实施,升级迁移,备份容灾,故障诊断,系统优化等)
回复 支持 反对

使用道具 举报

Cyrill

6

主题

24

帖子

99

积分

注册会员

Rank: 2

积分
99
 楼主| 发表于 2014-5-20 16:46:08 | 显示全部楼层
xifenfei 发表于 2014-5-20 16:40
还是确实归档日志，继续应用日志

从生产库上只找到这两个归档日志，并复制到测试机上。没有其他归档日志了。
thread_1_seq_3167.447.848070515
thread_2_seq_3227.399.848070633
回复 支持 反对

使用道具 举报

xifenfei

95

主题

266

帖子

1719

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1719
发表于 2014-5-20 16:50:34 | 显示全部楼层
RMAN-06025: no backup of archived log for thread 2 with sequence 3181 and starting SCN of 21401832384 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 3134 and starting SCN of 21401836732 found to restore


1的3134   2的3181  都是需要的，证明已经被删除掉了

Q Q:107644445
Tel:13429648788
Email:dba@xifenfei.com
个人Blog(惜分飞)
提供专业ORACLE技术支持(数据恢复,安装实施,升级迁移,备份容灾,故障诊断,系统优化等)
回复 支持 反对

使用道具 举报

Cyrill

6

主题

24

帖子

99

积分

注册会员

Rank: 2

积分
99
 楼主| 发表于 2014-5-20 16:55:33 | 显示全部楼层
xifenfei 发表于 2014-5-20 16:50
RMAN-06025: no backup of archived log for thread 2 with sequence 3181 and starting SCN of 2140183238 ...

我查到生产库上 归档日志文件夹下 有个2014-05-20的文件。里面放着2个归档文件。那是不是只能用5月20日当天的备份来恢复。不能用5月20号之前的备份恢复？
回复 支持 反对

使用道具 举报

xifenfei

95

主题

266

帖子

1719

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1719
发表于 2014-5-20 17:43:00 | 显示全部楼层
Cyrill 发表于 2014-5-20 16:55
我查到生产库上 归档日志文件夹下 有个2014-05-20的文件。里面放着2个归档文件。那是不是只能用5月20日当 ...

恩，缺少归档，常规方法不行的

Q Q:107644445
Tel:13429648788
Email:dba@xifenfei.com
个人Blog(惜分飞)
提供专业ORACLE技术支持(数据恢复,安装实施,升级迁移,备份容灾,故障诊断,系统优化等)
回复 支持 反对

使用道具 举报

下一页 »
12下一页
返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|ORACLE SOS 技术论坛

GMT+8, 2024-5-2 17:40 , Processed in 0.024349 second(s), 21 queries .

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表