dataguard中MRP無(wú)法啟動(dòng)的問(wèn)題分析和解決

自己手頭有一套dataguard環(huán)境，因?yàn)橐灿行┤兆記](méi)有用了，結(jié)果突然心血來(lái)潮準(zhǔn)備啟動(dòng)起來(lái)學(xué)習(xí)一下，突然發(fā)現(xiàn)在敲了命令 recover managed standby database disconnect from session之后,命令運(yùn)行正常,但是后臺(tái)卻報(bào)了ora錯(cuò)誤。
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
通過(guò)上面的日志我們可以看到，MRP進(jìn)程是在做數(shù)據(jù)恢復(fù)的時(shí)候報(bào)了ora錯(cuò)誤ora-01157
但是RFS還是沒(méi)有問(wèn)題，RFS主要是從主庫(kù)來(lái)傳輸歸檔文件的，可以看到能夠正常從主庫(kù)中傳輸歸檔日志，sequence#號(hào)為171,173,172的歸檔日志都傳輸?shù)搅藗鋷?kù)。

本來(lái)這個(gè)問(wèn)題沒(méi)有引起多大的關(guān)注，想可能是哪些歸檔文件沒(méi)有用到導(dǎo)致的，但是發(fā)現(xiàn)MRP壓根用不了。所以盡管歸檔傳輸完成了，但是數(shù)據(jù)變更還是應(yīng)用不到備庫(kù)。
查看v$archive_gap沒(méi)有任何記錄，說(shuō)明沒(méi)有歸檔日志apply的時(shí)候出現(xiàn)問(wèn)題。

我們來(lái)看看這個(gè)ora問(wèn)題的一些明細(xì)信息,提示是在7號(hào)數(shù)據(jù)文件的地方報(bào)了ora-01157錯(cuò)誤。
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'
從官方對(duì)于這個(gè)問(wèn)題的描述來(lái)看，似乎是數(shù)據(jù)文件出了問(wèn)題。
$ 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.
因?yàn)檫@個(gè)環(huán)境被折騰了不知道多少遍，反復(fù)切換，反復(fù)測(cè)試，我都不記得是哪些特殊的操作導(dǎo)致了這個(gè)問(wèn)題了。所以這個(gè)問(wèn)題還得從頭來(lái)分析。
首先查看了一下/u02/dg11g/oradata/DG11G/test_new01.dbf 這個(gè)文件，發(fā)現(xiàn)在文件系統(tǒng)中竟然不存在。
但是在數(shù)據(jù)字典信息中卻存在，使用的sql語(yǔ)句為，可以返回對(duì)應(yīng)的記錄來(lái)。
select name,file# from v$datafile where file#=7;

從這個(gè)情況來(lái)看，可能是在備庫(kù)端誤刪除了這個(gè)數(shù)據(jù)文件造成的。對(duì)于刪除的數(shù)據(jù)文件我們?cè)趺磥?lái)評(píng)估呢，首先得查看主庫(kù)，查看主庫(kù)中的文件情況,但是在主庫(kù)中這個(gè)數(shù)據(jù)文件和表空間壓根不存在。
這樣一來(lái)這個(gè)問(wèn)題就有些棘手了。
如果能夠修復(fù)MRP的問(wèn)題，看似這個(gè)問(wèn)題就引刃而解，如果修復(fù)不了，可能這個(gè)dataguard就不可用了，可能得考慮重建一個(gè)物理備庫(kù)了。
對(duì)此我們采取保守態(tài)度，帶著一絲嘗試看看備庫(kù)能不能啟動(dòng)到open read only狀態(tài)。
但是這三個(gè)操作的結(jié)果讓我有些迷茫了。
open不了，說(shuō)可能需要恢復(fù)，恢復(fù)的文件竟然是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'

對(duì)于這個(gè)問(wèn)題，如果有一個(gè)sql語(yǔ)句能夠一針見(jiàn)血的解決問(wèn)題就好了，自己在反復(fù)嘗試之后發(fā)現(xiàn)還是有的，問(wèn)題的解決思路就是先解決ORA-01157問(wèn)題，然后dataguard中的MRP問(wèn)題就能引刃而解。
對(duì)于ora-01157這個(gè)問(wèn)題中的數(shù)據(jù)文件在主庫(kù)中不存在，但是在備庫(kù)的數(shù)據(jù)字典中存在，我們可以直接在備庫(kù)中把數(shù)據(jù)字典中的問(wèn)題先解決了。
idle> alter database datafile '/u02/dg11g/oradata/DG11G/test_new01.dbf' offline drop;
Database altered.
然后dataguard的日志中就出現(xiàn)而來(lái)轉(zhuǎn)機(jī)，在后臺(tái)會(huì)去校驗(yàn)這個(gè)文件的問(wèn)題，只是拋出了一個(gè)警告。Warning: Datafile 7 (/u02/ora11g/oradata/TEST11G/test_new01.dbf) is offline during full database recovery and will not be recovered
然后MRP就正常啟動(dòng)了。后臺(tái)開(kāi)始使用歸檔文件做數(shù)據(jù)恢復(fù)了。

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.dbf
Media 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.dbf
Sat 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

比較有意思的是查看日志可以看到，數(shù)據(jù)文件被反復(fù)創(chuàng)建刪除了很多次。最后還是以drop終止。
然后就開(kāi)始使用一大堆的歸檔文件做數(shù)據(jù)恢復(fù)了。

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

在主庫(kù)中查看，redo的序列號(hào)185,備庫(kù)中的序列號(hào)是184。
sys@TEST11G> select sequence#,status from v$log;
 SEQUENCE# STATUS
---------- ----------------
       184 INACTIVE
       185 CURRENT
       183 INACTIVE

在備庫(kù)中查看后臺(tái)進(jìn)程的情況，可以看到MRP已經(jīng)記錄在冊(cè)了。
idle> select process,status,sequence# from v$managed_standby;
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      WAIT_FOR_LOG        186