Oracle重建DG库临时表空间文件

某系统备库alert报错信息：

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'

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

处理过程如下：
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 31G;

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
     31744 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;