RAC Standby激活后tempfile 重新建立

RAC Standby激活后的报错信息：

Fri Jan  1 21:06:35 2010
Cannot re-create tempfile /ocfs_data/orcl/temp01.dbf, the same name file exists
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 1
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=23, OS id=5034
Fri Jan  1 21:06:47 2010
db_recovery_file_dest_size of 2048 MB is 9.77% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Jan  1 21:06:48 2010
Completed: ALTER DATABASE OPEN
Fri Jan  1 21:43:31 2010
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 1 seq# 3 mem# 0: /ocfs_data/orcl/ORCL/onlinelog/o1_mf_1_5mxjgzb2_.log
Fri Jan  1 22:00:12 2010
Errors in file /u01/product/admin/orcl/bdump/orcl1_j000_5118.trc:
ORA-12012: error on auto execute of job 8950
ORA-01187: cannot read from file ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/ocfs_data/orcl/temp01.dbf'
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1347
ORA-06512: at "SYS.DBMS_SPACE", line 1566
 because it failed verification tests
Fri Jan  1 22:00:20 2010
Errors in file /u01/product/admin/orcl/bdump/orcl1_j003_26208.trc:
ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/ocfs_data/orcl/temp01.dbf'
Fri Jan  1 22:00:20 2010
GATHER_STATS_JOB encountered errors.  Check the trace file.
Fri Jan  1 22:00:20 2010
Errors in file /u01/product/admin/orcl/bdump/orcl1_j003_26208.trc:
ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/ocfs_data/orcl/temp01.dbf'
Fri Jan  1 22:00:20 2010
                                                                                             

SQL> select * from dba_temp_files;
select * from dba_temp_files
              *
ERROR at line 1:
ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/ocfs_data/orcl/temp01.dbf'

重新开启Server , temp file 还是有问题。

Fri Jan  1 14:55:31 2010
 LMS 0: 0 GCS shadows traversed, 0 replayed
Fri Jan  1 14:55:31 2010
 Submitted all GCS remote-cache requests
 Fix write in gcs resources
Reconfiguration complete
LCK0 started with pid=19, OS id=4978
Fri Jan  1 14:55:32 2010
ALTER DATABASE   MOUNT
Fri Jan  1 14:55:36 2010
Setting recovery target incarnation to 3
Fri Jan  1 14:55:37 2010
Successful mount of redo thread 2, with mount id 1234936425
Fri Jan  1 14:55:37 2010
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE   MOUNT
Fri Jan  1 14:55:38 2010
ALTER DATABASE OPEN
Picked broadcast on commit scheme to generate SCNs
Fri Jan  1 14:55:38 2010
Errors in file /u01/product/admin/orcl/bdump/orcl2_dbw0_4948.trc:
ORA-01186: file 201 failed verification tests
ORA-01122: database file 201 failed verification check
ORA-01110: data file 201: '/ocfs_data/orcl/temp01.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Fri Jan  1 14:55:38 2010
File 201 not verified due to error ORA-01122
Fri Jan  1 14:55:38 2010
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=21, OS id=5016
Fri Jan  1 14:55:38 2010
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=22, OS id=5018
Fri Jan  1 14:55:38 2010
Thread 2 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: /ocfs_data/orcl/ORCL/onlinelog/o1_mf_3_5mxjh039_.log
Successful open of redo thread 2
                                                                           

reuse tempfile 测试， 不行。

SQL> alter tablespace temp add tempfile  '/ocfs_data/orcl/temp01.dbf'  size 50m reuse;
alter tablespace temp add tempfile  '/ocfs_data/orcl/temp01.dbf'  size 50m reuse
*
ERROR at line 1:
ORA-01537: cannot add file '/ocfs_data/orcl/temp01.dbf' - file already part of
database

想trace出控制文件查看其中的tempfile的信息，报错：

SQL> alter database backup controlfile to trace ;
alter database backup controlfile to trace
*
ERROR at line 1:
ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/ocfs_data/orcl/temp01.dbf'

处理： 删除tempfile原文件，然后加入新的。

SQL> alter database tempfile '/ocfs_data/orcl/temp01.dbf' drop;

SQL> alter tablespace temp add tempfile '/ocfs_data/orcl/temp1.dbf' size 50m; 

SQL> alter database tempfile '/ocfs_data/orcl/temp1.dbf' online;

SQL> select * from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/ocfs_data/orcl/temp1.dbf
         1 TEMP                             52428800       6400 AVAILABLE
           1 NO           0          0            0   51380224        6272

可以手工删除原来的temp01.dbf 文件。

rac01sty$rm  temp01.dbf 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-624233/，如需转载，请注明出处，否则将追究法律责任。