1 问题现象

Oracle DG 备库查询TEMP 报错如下：http://www.cndba.cn/dave/article/131639

SQL> select file_id,file_name,bytes/1024/1024 MB,status,tablespace_name from dba_temp_files;
select file_id,file_name,bytes/1024/1024 MB,status,tablespace_name from dba_temp_files
                                                                        *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1001 - see DBWR trace file
ORA-01110: data file 1001: '/u01/app/oracle/oradata/hisdb/tempfile/temp.342.1110205751'

因为从库是RAC 恢复过来的，所以temp 文件并不存在。

2 解决方法

先取消MRP：

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter system set standby_file_management='MANUAL' scope=both;

System altered.

添加新的temp 数据文件：

SQL> alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/hisdb/temp01.dbf' size 30G;

Tablespace altered.

删除旧的不存在的文件：http://www.cndba.cn/dave/article/131639http://www.cndba.cn/dave/article/131639http://www.cndba.cn/dave/article/131639

SQL> alter tablespace TEMP drop tempfile '/u01/app/oracle/oradata/hisdb/tempfile/temp.342.1110205751'; 

Tablespace altered.

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
---------- ------------------------------------------------------------ ---------- ------- ------------------------------
     2 /u01/app/oracle/oradata/hisdb/temp01.dbf                 30720 ONLINE  TEMP

启动MRP:

SQL> alter system set standby_file_management='AUTO' scope=both;

System altered.

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

Database altered.

版权声明：本文为博主原创文章，未经博主允许不得转载。