oracle 启动mrp进程,dataguard中MRP无法启动的问题分析和解决

自己手头有一套dataguard环境,因为也有些日子没有用了,结果突然心血来潮准备启动起来学习一下,突然发现在敲了命令 recover managed standby database disconnect from session之后,命令运行正常,但是后台却报了ora错误。

Sat Jun 27 23:16:39 2015

Recovery Slave PR00 previously exited with exception 1157

Errors in file /u02/dg11g/diag/rdbms/dg11g/DG11G/trace/DG11G_mrp0_6514.trc:

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/u02/dg11g/oradata/DG11G/test_new01.dbf'

MRP0: Background Media Recovery process shutdown (DG11G)

Sat Jun 27 23:16:39 2015

Completed: ALTER DATABASE RECOVER  managed standby database disconnect from session

RFS[162]: Opened log for thread 1 sequence 171 dbid 1028247664 branch 880742847

RFS[161]: Opened log for thread 1 sequence 173 dbid 1028247664 branch 880742847

RFS[160]: Opened log for thread 1 sequence 172 dbid 1028247664 branch 880742847

通过上面的日志我们可以看到,MRP进程是在做数据恢复的时候报了ora错误ora-01157

但是RFS还是没有问题,RFS主要是从主库来传输归档文件的,可以看到能够正常从主库中传输归档日志,sequence#号为171,173,172的归档日志都传输到了备库。

本来这个问题没有引起多大的关注,想可能是哪些归档文件没有用到导致的,但是发现MRP压根用不了。所以尽管归档传输完成了,但是数据变更还是应用不到备库。

查看v$archive_gap没有任何记录,说明没有归档日志apply的时候出现问题。

我们来看看这个ora问题的一些明细信息,提示是在7号数据文件的地方报了ora-01157错误。

Errors in file /u02/dg11g/diag/rdbms/dg11g/DG11G/trace/DG11G_mrp0_6514.trc:

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7:'/u02/dg11g/oradata/DG11G/test_new01.dbf'从官方对于这个问题的描述来看,似乎是数据文件出了问题。

$ oerr ora 01157

01157, 00000, "cannot identify/lock data file %s - see DBWR trace file"

// *Cause:  The background process was either unable to find one of the data

//         files or failed to lock it because the file was already in use.

//         The database will prohibit access to this file but other files will

//         be unaffected. However the first instance to open the database will

//         need to access all online data files. Accompanying error from the

//         operating system describes why the file could not be identified.

// *Action: Have operating system make file available to database. Then either

//         open the database or do ALTER SYSTEM CHECK DATAFILES.

因为这个环境被折腾了不知道多少遍,反复切换,反复测试,我都不记得是哪些特殊的操作导致了这个问题了。所以这个问题还得从头来分析。

首先查看了一下/u02/dg11g/oradata/DG11G/test_new01.dbf这个文件,发现在文件系统中竟然不存在。

但是在数据字典信息中却存在,使用的sql语句为,可以返回对应的记录来。

select name,file# from v$datafile where file#=7;

从这个情况来看,可能是在备库端误删除了这个数据文件造成的。对于删除的数据文件我们怎么来评估呢,首先得查看主库,查看主库中的文件情况,但是在主库中这个数据文件和表空间压根不存在。

这样一来这个问题就有些棘手了。

如果能够修复MRP的问题,看似这个问题就引刃而解,如果修复不了,可能这个dataguard就不可用了,可能得考虑重建一个物理备库了。

对此我们采取保守态度,带着一丝尝试看看备库能不能启动到open read only状态。

但是这三个操作的结果让我有些迷茫了。

open不了,说可能需要恢复,恢复的文件竟然是system01.dbf,尝试recover until cancel也未果。

idle> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-10458: standby database requires recovery

ORA-01196: file 1 is inconsistent due to a failed media recovery session

ORA-01110: data file 1: '/u02/dg11g/oradata/DG11G/system01.dbf'

idle> recover database until cancel;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

idle> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-10458: standby database requires recovery

ORA-01196: file 1 is inconsistent due to a failed media recovery session

ORA-01110: data file 1: '/u02/dg11g/oradata/DG11G/system01.dbf'

对于这个问题,如果有一个sql语句能够一针见血的解决问题就好了,自己在反复尝试之后发现还是有的,问题的解决思路就是先解决ORA-01157问题,然后dataguard中的MRP问题就能引刃而解。

对于ora-01157这个问题中的数据文件在主库中不存在,但是在备库的数据字典中存在,我们可以直接在备库中把数据字典中的问题先解决了。

idle>alter database datafile '/u02/dg11g/oradata/DG11G/test_new01.dbf' offline drop;

Database altered.

然后dataguard的日志中就出现而来转机,在后台会去校验这个文件的问题,只是抛出了一个警告。Warning: Datafile 7 (/u02/ora11g/oradata/TEST11G/test_new01.dbf) is offline during full database recovery and will not be recovered

然后MRP就正常启动了。后台开始使用归档文件做数据恢复了。

alter database datafile '/u02/dg11g/oradata/DG11G/test_new01.dbf' offline drop

Completed: alter database datafile '/u02/dg11g/oradata/DG11G/test_new01.dbf' offline drop

Sat Jun 27 23:24:08 2015

ALTER DATABASE RECOVER  managed standby database disconnect from session

Attempt to start background Managed Standby Recovery process (DG11G)

Sat Jun 27 23:24:08 2015

MRP0 started with pid=25, OS id=8431

MRP0: Background Managed Standby Recovery process started (DG11G)

started logmerger process

Sat Jun 27 23:24:13 2015

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 2 slaves

Warning: Datafile 7 (/u02/ora11g/oradata/TEST11G/test_new01.dbf) is offline during full database recovery and will not be recovered

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_121_880742847.dbf

Completed: ALTER DATABASE RECOVER  managed standby database disconnect from session

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_122_880742847.dbf

Sat Jun 27 23:24:31 2015

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_123_880742847.dbf

Recovery deleting file #7:'/u02/dg11g/oradata/DG11G/test_new01.dbf' from controlfile.

Deleted file /u02/dg11g/oradata/DG11G/test_new01.dbf

Recovery dropped tablespace 'TEST_NEW'

Recovery created file /u02/dg11g/oradata/DG11G/test_new01.dbf

Successfully added datafile 7 to media recovery

Datafile #7: '/u02/dg11g/oradata/DG11G/test_new01.dbf'

Recovery deleting file #7:'/u02/dg11g/oradata/DG11G/test_new01.dbf' from controlfile.

Deleted file /u02/dg11g/oradata/DG11G/test_new01.dbf

Recovery dropped tablespace 'TEST_NEW'

Recovery deleting file #7:'/u02/dg11g/oradata/DG11G/test_new01.dbf' from controlfile.

Deleted file /u02/dg11g/oradata/DG11G/test_new01.dbf

Recovery dropped tablespace 'TEST_NEW'

Recovery deleting file #7:'/u02/dg11g/oradata/DG11G/test_new01.dbf' from controlfile.

Deleted file /u02/dg11g/oradata/DG11G/test_new01.dbf

Recovery dropped tablespace 'TEST_NEW'Media Recovery Log /u02/dg11g/switchover/DG11G/archivelog/1_124_880742847.dbfMedia Recovery Log /u02/dg11g/switchover/DG11G/archivelog/1_125_880742847.dbf

Recovery deleting file #7:'/u02/dg11g/oradata/DG11G/test_new01.dbf' from controlfile.

Deleted file /u02/dg11g/oradata/DG11G/test_new01.dbf

Recovery dropped tablespace 'TEST_NEW'Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_126_880742847.dbfSat Jun 27 23:24:49 2015

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_127_880742847.dbf

Sat Jun 27 23:25:01 2015

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_128_880742847.dbf

Sat Jun 27 23:25:17 2015

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_129_880742847.dbf

Sat Jun 27 23:25:29 2015

比较有意思的是查看日志可以看到,数据文件被反复创建删除了很多次。最后还是以drop终止。

然后就开始使用一大堆的归档文件做数据恢复了。

Sat Jun 27 23:28:30 2015

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_172_880742847.dbf

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_173_880742847.dbf

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_174_880742847.dbf

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_175_880742847.dbf

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_176_880742847.dbf

Sat Jun 27 23:28:40 2015

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_177_880742847.dbf

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_178_880742847.dbf

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_179_880742847.dbf

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_180_880742847.dbf

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_181_880742847.dbf

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_182_880742847.dbf

Sat Jun 27 23:28:52 2015

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_183_880742847.dbf

Media Recovery Log /u02/dg11g/flash_recovery_area/DG11G/archivelog/1_184_880742847.dbf

在主库中查看,redo的序列号185,备库中的序列号是184。

sys@TEST11G> select sequence#,status from v$log;

SEQUENCE# STATUS

---------- ----------------

184 INACTIVE

185 CURRENT

183 INACTIVE

在备库中查看后台进程的情况,可以看到MRP已经记录在册了。

idle> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

--------- ------------ ----------

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

MRP0WAIT_FOR_LOG        186

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值