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

某系统备库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;