某系统备库alert报错信息:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/picclife/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 '/picclife/app/oracle/oradata/dgfmisdb/temp02.dbf' size 30G;
alter tablespace TEMP drop tempfile '/picclife/app/oracle/oradata/dgfmisdb/tempfile/temp.263.885312765';
alter tablespace TEMP add tempfile '/picclife/app/oracle/oradata/dgfmisdb/tempfile/temp.263.885312765' size 31G;
alter tablespace TEMP drop tempfile '/picclife/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
/picclife/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;