问题概述

oracle dg备库alter日志出现报错

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/dgfmisdb/tempfile/temp.263.885312765'

问题原因

造成该报错的原因是DG备库上不存在该号文件，当备库有事务发生需要使用临时表空间时，数据库会发出该报错；这里我们可以通过重建临时表空间数据文件解决该问题。

解决方案

处理过程如下：
1.关闭备库mrp进程

alter database recover managed standby database cancel;

2.修改standby_file_management参数

alter system set standby_file_management=MANUAL;

3.重建数据文件

alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/dgfmisdb/temp02.dbf' size 30G;

alter tablespace TEMP drop tempfile '/u01/app/oracle/oradata/dgfmisdb/tempfile/temp.263.885312765'; 

alter tablespace TEMP add tempfile  '/u01/app/oracle/oradata/dgfmisdb/tempfile/temp.263.885312765' size 30G;

alter tablespace TEMP drop tempfile '/u01/app/oracle/oradata/dgfmisdb/temp02.dbf';

4.查询相关信息

SQL> select file_id,file_name,bytes/1024/1024 MB,status,tablespace_name from dba_temp_files;

   FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        MB STATUS               TABLESPACE_NAME
---------- -------------------- ------------------------------
         1
/u01/app/oracle/oradata/dgfmisdb/tempfile/temp.263.885312765
     30720 ONLINE               TEMP

5.恢复standby_file_management参数

alter system set standby_file_management=AUTO;

6.打开备库mrp进程

alter database recover managed standby database using current logfile disconnect from session;