MRP进程无法启动,ORA-19909错误,ORA-01194,ORA-00328,gap等问题综合解决

环境:
Oracle11.2.0.4 Rac       一主两备模式

第一部分:  修改incarnation

前因:前几天在备2上面做Flashback下的Failover恢复实验,但影响了备1下面的adg恢复,错误如下
备1的alert日志如下:
Fri Nov 06 17:01:37 2015

Warning: Recovery target destination is in a sibling branch

of the controlfile checkpoint. Recovery will only recover

changes to datafiles.

Datafile 1 (ckpscn 42994662) is orphaned on incarnation#=2

MRP0: Detected orphaned datafiles!

Recovery will possibly be retried after flashback...

Errors in file /u01/app/oracle/diag/rdbms/bebe/bb1/trace/bb1_pr00_47537.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: '+DATA2/bebe/datafile/system.260.883412457'

Managed Standby Recovery not using Real Time Apply

1.1> 查询主1此时对应的incarnation;
主1: 
RMAN> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BBTECH   2298175399       PARENT  1          2013-08-24 11:37:30
2       2       BBTECH   2298175399       CURRENT 925702     2015-06-16 18:25:51
而备1:
RMAN> list incarnation;  当前值跟主一不一致了,所以报告了上面错误
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BBTECH   2298175399       PARENT  1                2013-08-24 11:37:30
2       2       BBTECH   2298175399       PARENT  925702      2015-06-16 18:25:51

3       3       BBTECH   2298175399       CURRENT 42990620 2015-11-05 17:06:23

1.2> 现在试着通过rman修改回去
SQL> shutdown immediate;

SQL> startup mount;     <--在mount状态才能修改

RMAN > reset database to incarnation 2;

新的备1:
RMAN> list incarnation;  显示已经修改成功了
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BBTECH   2298175399       PARENT  1                2013-08-24 11:37:30
2       2       BBTECH   2298175399       CURRENT 925702      2015-06-16 18:25:51

3       3       BBTECH   2298175399       ORPHAN   42990620 2015-11-05 17:06:23

观察备1对应的alert可以找到修改incarnation的记录如下:Setting recovery target incarnation to 2

1.3> 
SQL > alter database open;
SQL > alter database recover managed standby database using current logfile disconnect from session;

到处可以成功打开并应用日志了.

第二部分:备库ORA-00328错误 处理

继续观察备1的日志,出现了新的错误
ORA-00328: archived log ends at change 42990622, need later change 42994662

参考Mos做了如下步骤 :  How to resolve ORA:00328 in a standby database (文档 ID 864364.1)

2.1> 主库查询丢失的归档名字

select name, thread#, sequence#, archived, applied, status from

v$archived_log

where 42994662 between FIRST_CHANGE# and NEXT_CHANGE#;

2.2>把主1上对应的归档日志2_523_882555951.dbf拷贝出来注册的备库1上
 主库从asm里拷贝出来

ASMCMD> cp 2_532_882555951.dbf /home/grid
并传到备1上,试图注册报告如下错误

SQL> alter database register or replace logfile '/home/oracle/2_523_882555951.dbf';

ORA-16090: archive log to be replaced not created by managed standby process

2.3>试着用rman来拷贝文件到备1的asm里再注册
RMAN> catalog archivelog '/home/oracle/2_523_882555951.dbf';

cataloged archived log

archived log file name=/home/oracle/2_523_882555951.dbf RECID=1456 STAMP=895316858

RMAN> copy archivelog '/home/oracle/2_523_882555951.dbf' to '+data2';

Starting backup at 2015-11-09 11:09:23

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=2 sequence=523 RECID=1456 STAMP=895316858

output file name=+DATA2/bebe/archivelog/2015_11_09/thread_2_seq_523.716.895316965 RECID=1457 STAMP=895316965

channel ORA_DISK_1: archived log copy comp

 

SQL> alter database register or replace logfile '+DATA2/bebe/archivelog/2015_11_09/thread_2_seq_523.716.895316965';

Database altered.

这一次注册成功了耶!!!


第三部分: 备库gap问题处理

3.1> 经过上面的处理,可以启动adg模式了,但主库切换日志,备库仍然无法实时响应,日志有如下提示,出现Gap:
Mon Nov 09 11:15:03 2015
FAL[client]: Failed to request gap sequence
 GAP - SCN range: 0x0000.02900be6 - 0x0000.02900be6
 DBID 2298175399 branch 882555951
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
 下面的通过主库增量备份数据到备库应用,重建备库控制文件
3.2>
备库查询当前scn, (注:如果v$datafile_header里不一致了,则要找最小的scn)

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

-----------

   42994661

备库停止日志应用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


3.3> 主库增量备份并传输到备库上

RMAN > BACKUP INCREMENTAL FROM SCN  42994661 DATABASE FORMAT '/home/oracle/bak_%U';
并把备份文件拷贝到备1上
[root@W1 oracle]# scp  -P 4022 bak_*  192.168.20.4:/home/oracle/bak
3.4> 备库注册增量备份的文件
RMAN> catalog start with '/home/oracle/bak';
RMAN > shutdown immedaite;
RMAN > startup mount;      <---只有在mount状态下才能恢复日志,两个节点都要

3.5>执行恢复操作
RMAN> recover database noredo;

3.6> 尝试打开备库1
SQL> alter database open;   <---无法打开,需要更多的日志来恢复

ORA-10458: standby database requires recovery

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '+DATA2/bebe/datafile/system.260.883412457'

因为备库此时的控制文件跟数据文件头的scn已经不一致了


3.7> 主库产生备库的控制文件
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY format '/home/oracle/std_ctl.bck';
scp拷贝控制文件到备库

3.8> 在备库恢复主库传过来的控制文件

RMAN> shutdown immediate;

RMAN> startup nomount;

RMAN> restore standby controlfile from '/home/oracle/std_ctl.bck';

RMAN> alter database mount;


3.9> 此时备库用了主库过来的控制文件,因为主备数据文件名字是不一致的,需要修改(asm别名生成)
SQL> select name from v$dbfile;
SQL> alter database rename file '+DATA2/bebe/datafile/users.259.882555675' to '+DATA2/bebe/datafile/USERS.263.883412459'; 
........
SQL> alter database open;

此时打开仍然报错ora-01194

3.10 >干脆备库开启日志接受并应用日志

SQL> alter database recover managed standby database disconnect from session;

可以看到有自动清理备库在线日志的操作

Errors in file /u01/app/oracle/diag/rdbms/bebe/bb1/trace/bb1_mrp0_39324.trc:
ORA-00313: open failed for members of log group 9 of thread 2
ORA-00312: online log 9 thread 2: '+DATA2/bebe/onlinelog/group_9.365.883673197'
ORA-17503: ksfdopn:2 Failed to open file +DATA2/bebe/onlinelog/group_9.365.883673197
ORA-15173: entry 'group_9.365.883673197' does not exist in directory 'onlinelog'
Deleted Oracle managed file +DATA2/bebe/onlinelog/group_9.365.883673197
Completed: alter database recover managed standby database disconnect from session

3.11>等所有的在线日志都清理完成后

SQL> alter database recover managed standby database cancel;

启用日志恢复   <----这是解决ora-01194的关键步骤

SQL> alter database recover managed standby database using current logfile disconnect from session;
可以观察的有日志恢复及应用的操作
Completed: alter database recover managed standby database using current logfile disconnect from session
Media Recovery Log +DATA2/bebe/arch/1_741_882555951.dbf
Media Recovery Log +DATA2/bebe/arch/2_543_882555951.dbf
Media Recovery Log +DATA2/bebe/arch/2_544_882555951.dbf
Media Recovery Log +DATA2/bebe/arch/1_742_882555951.dbf
Media Recovery Log +DATA2/bebe/arch/1_743_882555951.dbf
Media Recovery Log +DATA2/bebe/arch/1_744_882555951.dbf
Media Recovery Waiting for thread 2 sequence 545 (in transit)

3.12> 
等所有日志的应用后,再次 open,并在线实时应用日志 ,这次主备库终于一致了!!!

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

SQL> alter database recover managed standby database using current logfile disconnect from session;


完毕.

备库Gap问题处理参考:
  http://www.xifenfei.com/2011/07/data-guard%E5%87%BA%E7%8E%B0gap-sequence%E4%BF%AE%E5%A4%8D.html





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/61604/viewspace-1826828/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/61604/viewspace-1826828/

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值