Oracle Standby环境ORA-01274 01119 27038报错处理

上次 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29154652/viewspace-772931/