一个Oracle bug的手工修复(r6笔记第59天)
一个Oracle bug的手工修复(r6笔记第59天)
在上周五的时候，本来一个例行巡检，想扩充一些表空间，结果弄巧成拙，因为一个drop datafile的操作直接导致了一主两备的两个备库MRP直接抛出了ORA-600错误。 在尝试了一些方法和查看了MOS之后，除了重建备库，暂时还没有找到其它相对更快捷的方法。 因为是10.2.0.4.0的环境，为了先修复问题，自己先使用rman在主库做了备份，然后在备库直接做duplicate操作还原恢复。先搭好了一个备库，另外一个备库则先留下来，观察一下，看看有没有其它的方法，如果还是没有找到，就继续重新搭建备库。 结果在这种试试看的时候，竟然还是找到了一线希望，也非常感谢微信群内的好友都出谋划策，还是找到了一种可行的方案。 初始的问题，可以参见http://blog.itpub.net/23718752/viewspace-1797653/ 修复的思路是因为在主库中数据文件的配置是没有问题的，直接在主库生成备份控制文件，然后在备库做还原，这个时候还原成功后，如果尝试启动MRP肯定会报错，会有一个文件存在不一致的情况，这个时候我们就需要让dataguard端知道这个不一致，直接使用alter database drop datafile的操作就会把原来不一致的文件从数据字典级进行了更新。 这个过程有点类似于alter tablespace xxx drop datafile的过程，因为alter tablespace drop datafile需要在数据open阶段完成，所以我们通过这种方式也能达到同样的效果。 尝试的步骤如下： 把备库启动到nomount阶段，开始controlfile的还原。
$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Sep 14 17:43:03 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
RMAN> restore controlfile from '/U01/backup_stage/ctl_oaqgu616_1_1';
Starting restore at 14-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2984 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/U01/app/oracle/oradata/test/control01.ctl
output filename=/U01/app/oracle/oradata/test/control02.ctl
output filename=/U01/app/oracle/oradata/test/control03.ctl
Finished restore at 14-SEP-15还原之后，启动到mount阶段。 RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> exit 这个时候开始尝试应用日志，即MRP开始唤醒MRP开始工作。 可以看到alert日志中的内容变化：
ALTER DATABASE RECOVER managed standby database disconnect from session
Mon Sep 14 17:45:04 2015
Attempt to start background Managed Standby Recovery process (p)
MRP0 started with pid=16, OS id=27255
Mon Sep 14 17:45:04 2015
MRP0: Background Managed Standby Recovery process started (peak)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1110
Mon Sep 14 17:45:09 2015
Errors in file /U01/app/oracle/admin/peak/bdump/test_mrp0_27255.trc:
ORA-01110: data file 21: '/U01/app/oracle/oradata/test/test_new_index04.dbf'
ORA-01122: database file 21 failed verification check
ORA-01110: data file 21: '/U01/app/oracle/oradata/test/test_new_index04.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Mon Sep 14 17:45:09 2015
Errors in file /U01/app/oracle/admin/peak/bdump/test_mrp0_27255.trc:
ORA-01110: data file 21: '/U01/app/oracle/oradata/test/test_new_index04.dbf'
ORA-01122: database file 21 failed verification check
ORA-01110: data file 21: '/U01/app/oracle/oradata/test/test_new_index04.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Mon Sep 14 17:45:09 2015
MRP0: Background Media Recovery process shutdown (test)
Mon Sep 14 17:45:10 2015
Completed: ALTER DATABASE RECOVER managed standby database disconnect from session
Mon Sep 14 17:46:21 2015
这个时候还是会和预想的差不多，MRP依旧会失败，但是不同的是，这个时候错误已经不是ORA-600的错误了。
既然这个文件存在不一致的情况，而且我们确实知道这个文件是需要手工删除的。我们就可以直接删除数据文件。
idle> alter database datafile '/U01/app/oracle/oradata/peak/peak_new_index04.dbf' offline drop;
Database altered.
尝试取消日志应用
idle> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active
可见刚刚的MRP启动是失败的。
再次启动MRP
idle> ALTER DATABASE RECOVER managed standby database disconnect from session ;
Database altered.
再次启动MRP的时候回发现日志中出现了转机，这个时候备库这边和主库基本一致了，但是还是存在归档GAP.
alter database datafile '/U01/app/oracle/oradata/test/test_new_index04.dbf' offline drop
Mon Sep 14 17:46:21 2015
Completed: alter database datafile '/U01/app/oracle/oradata/test/test_new_index04.dbf' offline drop
Mon Sep 14 17:46:48 2015
ALTER DATABASE RECOVER managed standby database cancel
Mon Sep 14 17:46:48 2015
ORA-16136 signalled during: ALTER DATABASE RECOVER managed standby database cancel ...
Mon Sep 14 17:47:01 2015
ALTER DATABASE RECOVER managed standby database disconnect from session
Mon Sep 14 17:47:01 2015
Attempt to start background Managed Standby Recovery process (test)
MRP0 started with pid=16, OS id=27547
Mon Sep 14 17:47:01 2015
MRP0: Background Managed Standby Recovery process started (test)
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 15 processes
Mon Sep 14 17:47:06 2015
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 7414
Fetching gap sequence in thread 1, gap sequence 7414-7416
Mon Sep 14 17:47:07 2015
Completed: ALTER DATABASE RECOVER managed standby database disconnect from session
Mon Sep 14 17:48:06 2015
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 7414-7416
DBID 1731005384 branch 680697352
这个时候发现了GAP,但是还没有开始从上次ORA-600错误的日志开始应用日志。
直接开启broker的验证会事半功倍。
DGMGRL>add database stest2 as
connect identifier is stest2
maintained as physical;
DGMGRL>enable database stest;
这个时候日志中就开始忙碌起来了,关键的就是从上次失败的归档开始开启RFS接受日志了。
Mon Sep 14 17:53:19 2015
RFS[1]: Archived Log: '/U01/app/oracle/flash_recovery_area/STEST2/archivelog/2015_09_14/o1_mf_1_7414_bzf68cq2_.arc'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 28706
RFS[2]: Identified database type as 'physical standby'
RFS[2]: Archived Log: '/U01/app/oracle/flash_recovery_area/STEST2/archivelog/2015_09_14/o1_mf_1_7415_bzf68h9y_.arc'
RFS[2]: Archived Log: '/U01/app/oracle/flash_recovery_area/STEST2/archivelog/2015_09_14/o1_mf_1_7416_bzf68hgr_.arc'
RFS[2]: Archived Log: '/U01/app/oracle/flash_recovery_area/STEST2/archivelog/2015_09_14/o1_mf_1_7426_bzf68jt8_.arc'
.....
RFS[2]: Archived Log: '/U01/app/oracle/flash_recovery_area/STEST2/archivelog/2015_09_14/o1_mf_1_7420_bzf69g71_.arc'
Mon Sep 14 17:53:51 2015
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 15 processes
Mon Sep 14 17:53:51 2015
Waiting for all non-current ORLs to be archived...
Media Recovery Log /U01/app/oracle/flash_recovery_area/STEST2/archivelog/2015_09_14/o1_mf_1_7414_bzf68cq2_.arc
Mon Sep 14 17:53:52 2015
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Mon Sep 14 17:53:52 2015
MRP也可以继续应用日志了,从上次失败的地方开始。 这个时候使用DG broker来做一个简单验证。
DGMGRL> show configuration;
Configuration
Name: test
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
test - Primary database
stest4 - Physical standby database
stest2 - Physical standby database
Current status for "peak":
SUCCESS 当然了问题修复了，来看看数据文件的情况，这个时候就没有问题了。
idle> select file#,df.name,df.ts#,ts.name,df.RFILE# from v$datafile df,v$tablespace ts where df.ts#=ts.ts#;
20 /U01/app/oracle/oradata/test/test_new_data04.dbf 9 TEST_NEW_DATA 20
21 /U01/app/oracle/oradata/test/test_new_index04.dbf 10 TEST_NEW_INDEX 21所以通过这个案例我们可以看到，在某些情况下踩雷的时候，还是不要气馁，在不影响全局的情况下，可以根据自己的分析大胆假设，小心求证，没准还真能有所发现。