Oracle Standby環境ORA-01274 01119 27038報錯處理

dbinsight發表於2013-09-20
Oracle
上次 DB Tuning的時候個別DB在刪除表空間使用drop tablespace including contents而並沒有帶and datafiles的選項，導致資料檔案在Standby環境上並沒有被清除掉，當用同樣的檔名擴充套件表空間的時候，就碰到了麻煩。
主庫重用之前的資料檔案進行擴充套件表空間，在備庫應用日誌的時候，由於檔案還在，所以報錯，MRP程式異常退出。
具體的報錯資訊如下：
Media Recovery Log /arch/XXDB02/1_49893.arc
WARNING: File being created with same name as in Primary
Existing file may be overwritten
File #11 added to control file as 'UNNAMED00011'. Originally created as:
'/u/oracle/oradata/XXDB02/db_a0106.dbr'
Recovery was unable to create the file as:
'/u/oracle/oradata/XXDB02/db_a0106.dbr'
MRP0: Background Media Recovery terminated with error 1274
Wed Apr 28 22:03:46 2010
Errors in file /u/oracle/admin/XXDB02/bdump/XXDB02_mrp0_3228118.trc:
ORA-01274: cannot add datafile '/u/oracle/oradata/XXDB02/db_a0106.dbr' - file could not be created
ORA-01119: error in creating database file '/u/oracle/oradata/XXDB02/db_a0106.dbr'
ORA-27038: skgfrcre: file exists
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
MRP0: Background Media Recovery process shutdown

具體的處理步驟：
首先從OS上清除掉已經存在的舊的資料檔案
$rm /u/oracle/oradata/XXDB02/db_a0106.dbr
然後手工建立報錯的資料檔案，重新啟動同步即可
SQL> alter system set standby_file_management='MANUAL';  --執行這步確保下一步可以執行成功
SQL> alter database create datafile '/u/oracle/product/920/dbs/UNNAMED00011' as '/u/oracle/oradata/XXDB02/db_a0106.dbr';  --按照主庫的資料檔名重新建立資料檔案，具體的對應關係alert日誌中可以看到，注意藍色標註部分
SQL> alter system set standby_file_management='AUTO';  -- 恢復設定
SQL> alter database recover managed standby database disconnect from session; --重新同步
SQL> select process,status from v$managed_standby; --確認同步狀態是否OK

後臺alert日誌內容：
Media Recovery Log /arch/XXDB02/1_49893.arc
WARNING: File being created with same name as in Primary
Existing file may be overwritten
File #11 added to control file as 'UNNAMED00011'. Originally created as:
'/u/oracle/oradata/XXDB02/db_a0106.dbr'
Recovery was unable to create the file as:
'/u/oracle/oradata/XXDB02/db_a0106.dbr'
MRP0: Background Media Recovery terminated with error 1274
Wed Apr 28 22:03:46 2010
Errors in file /u/oracle/admin/XXDB02/bdump/XXDB02_mrp0_3228118.trc:
ORA-01274: cannot add datafile '/u/oracle/oradata/XXDB02/db_a0106.dbr' - file could not be created
ORA-01119: error in creating database file '/u/oracle/oradata/XXDB02/db_a0106.dbr'
ORA-27038: skgfrcre: file exists
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
MRP0: Background Media Recovery process shutdown
Thu Apr 28 22:10:41 2010
ALTER DATABASE RECOVER? standby database? 
Media Recovery Start
Thu Apr 28 22:10:42 2010
Media Recovery failed with error 1111
ORA-283 signalled during: ALTER DATABASE RECOVER? standby database? ...
Thu Apr 28 22:11:08 2010
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;
Thu Apr 28 22:12:30 2010
alter database create datafile '/u/oracle/product/920/dbs/UNNAMED00011' as '/u/oracle/oradata/XXDB02/db_a0106.dbr'
Thu Apr 28 22:12:51 2010
Starting control autobackup
Control autobackup failed with following error :
ORA-00236: snapshot operation disallowed: mounted controlfile is a backup
Completed: alter database create datafile '/u/oracle/product/
Thu Apr 28 22:13:00 2010
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;
Thu Apr 28 22:13:17 2010
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process
MRP0 started with pid=15, OS id=2863556
MRP0: Background Managed Standby Recovery process started
Media Recovery Log /arch/XXDB02/1_49893.arc
Thu Apr 28 22:13:23 2010
Completed: alter database recover managed standby database di
Thu Apr 28 22:13:25 2010
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Thu Apr 28 22:13:48 2010
Recovery created file /u/oracle/oradata/XXDB02/db_a0107.dbr
Successfully added datafile 12 to media recovery
Datafile #12: '/u/oracle/oradata/XXDB02/db_a0107.dbr'
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Thu Apr 28 22:14:14 2010
Recovery created file /u/oracle/oradata/XXDB02/db_a0108.dbr
Successfully added datafile 13 to media recovery
Datafile #13: '/u/oracle/oradata/XXDB02/db_a0108.dbr'
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Thu Apr 28 22:14:48 2010
Recovery created file /u/oracle/oradata/XXDB02/db_a0109.dbr
Successfully added datafile 14 to media recovery
Datafile #14: '/u/oracle/oradata/XXDB02/db_a0109.dbr'
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Thu Apr 28 22:15:17 2010
RFS: Possible network disconnect with primary database
Thu Apr 28 22:15:21 2010
Recovery created file /u/oracle/oradata/XXDB02/db_a0110.dbr
Successfully added datafile 15 to media recovery
Datafile #15: '/u/oracle/oradata/XXDB02/db_a0110.dbr'
Media Recovery Log /arch/XXDB02/1_49894.arc
Media Recovery Log /arch/XXDB02/1_49895.arc
同步至此恢復正常。
後記：鑑於目前的環境，後續在刪除表空間的時候需要帶上including contents and datafiles的選項，確保相關檔案在Standby環境上也被清除。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29154652/viewspace-772931/，如需轉載，請註明出處，否則將追究法律責任。

相關文章