自己手頭有一套dataguard環(huán)境，因?yàn)橐灿行┤兆記]有用了，結(jié)果突然心血來潮準(zhǔn)備啟動(dòng)起來學(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
通過上面的日志我們可以看到，MRP進(jìn)程是在做數(shù)據(jù)恢復(fù)的時(shí)候報(bào)了ora錯(cuò)誤ora-01157
但是RFS還是沒有問題，RFS主要是從主庫來傳輸歸檔文件的，可以看到能夠正常從主庫中傳輸歸檔日志，sequence#號(hào)為171,173,172的歸檔日志都傳輸?shù)搅藗鋷臁?br />
本來這個(gè)問題沒有引起多大的關(guān)注，想可能是哪些歸檔文件沒有用到導(dǎo)致的，但是發(fā)現(xiàn)MRP壓根用不了。所以盡管歸檔傳輸完成了，但是數(shù)據(jù)變更還是應(yīng)用不到備庫。
查看v$archive_gap沒有任何記錄，說明沒有歸檔日志apply的時(shí)候出現(xiàn)問題。

我們來看看這個(gè)ora問題的一些明細(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è)問題的描述來看，似乎是數(shù)據(jù)文件出了問題。
$ 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ù)測試，我都不記得是哪些特殊的操作導(dǎo)致了這個(gè)問題了。所以這個(gè)問題還得從頭來分析。
首先查看了一下/u02/dg11g/oradata/DG11G/test_new01.dbf 這個(gè)文件，發(fā)現(xiàn)在文件系統(tǒng)中竟然不存在。
但是在數(shù)據(jù)字典信息中卻存在，使用的sql語句為，可以返回對(duì)應(yīng)的記錄來。
select name,file# from v$datafile where file#=7;

從這個(gè)情況來看，可能是在備庫端誤刪除了這個(gè)數(shù)據(jù)文件造成的。對(duì)于刪除的數(shù)據(jù)文件我們怎么來評(píng)估呢，首先得查看主庫，查看主庫中的文件情況,但是在主庫中這個(gè)數(shù)據(jù)文件和表空間壓根不存在。
這樣一來這個(gè)問題就有些棘手了。
如果能夠修復(fù)MRP的問題，看似這個(gè)問題就引刃而解，如果修復(fù)不了，可能這個(gè)dataguard就不可用了，可能得考慮重建一個(gè)物理備庫了。
對(duì)此我們采取保守態(tài)度，帶著一絲嘗試看看備庫能不能啟動(dòng)到open read only狀態(tài)。
但是這三個(gè)操作的結(jié)果讓我有些迷茫了。
open不了，說可能需要恢復(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è)問題，如果有一個(gè)sql語句能夠一針見血的解決問題就好了，自己在反復(fù)嘗試之后發(fā)現(xiàn)還是有的，問題的解決思路就是先解決ORA-01157問題，然后dataguard中的MRP問題就能引刃而解。
對(duì)于ora-01157這個(gè)問題中的數(shù)據(jù)文件在主庫中不存在，但是在備庫的數(shù)據(jù)字典中存在，我們可以直接在備庫中把數(shù)據(jù)字典中的問題先解決了。
idle>alter database datafile '/u02/dg11g/oradata/DG11G/test_new01.dbf' offline drop;
Database altered.
然后dataguard的日志中就出現(xiàn)而來轉(zhuǎn)機(jī)，在后臺(tái)會(huì)去校驗(yàn)這個(gè)文件的問題，只是拋出了一個(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)開始使用歸檔文件做數(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終止。
然后就開始使用一大堆的歸檔文件做數(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

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

在備庫中查看后臺(tái)進(jìn)程的情況，可以看到MRP已經(jīng)記錄在冊了。
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

總結(jié)

以上是生活随笔為你收集整理的dataguard中MRP无法启动的问题分析和解决的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。