022-06-30T04:04:17.542368+02:00 Errors in file /orainst/oracle/diag/rdbms/testdb01/testdb01/trace/testdb01_dbw0_130154.trc: ORA-01186: file 201 failed verification tests ORA-01122: database file 201 failed verification check ORA-01110: data file 201: '/data/oradata/testdb01/temp01.dbf' ORA-01203: wrong incarnation of this file - wrong creation SCN File 201 not verified due to error ORA-01122
Any operation you try against the database will give the following error stack in return:
ORA-01187: cannot read from file because it failed verification tests ORA-01110: data file 201: '/data/oradata/testdb01/temp01.dbf' ORA-06512: at "SYS.DBMS_LOB", line 741 ORA-06512: at "SYS.DBMS_DATAPUMP", line 5420 ORA-06512: at line 1
Check tempfile situation:
SYS@testdb01>SQL>select ts#,status,enabled, name from v$tempfile; TS# STATUS ENABLED NAME ---------- ------- ---------- --------------------------------- 3 ONLINE READ WRITE /data/oradata/testdb01/temp01.dbf 3 ONLINE READ WRITE /data/oradata/testdb01/temp02.dbfA query against dba_temp_files however, will give an error:
SYS@testdb01>SQL>SELECT tablespace_name, file_name FROM dba_temp_files WHERE tablespace_name = 'TEMP'; SELECT tablespace_name, file_name FROM dba_temp_files WHERE tablespace_name = 'TEMP' * ERROR at line 1: ORA-01187: cannot read from file because it failed verification tests ORA-01110: data file 201: '/data/oradata/testdb01/temp01.dbf'
Is the temporary tablespace database default?
SYS@testdb01>SQL> col property_value format a30 SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'; PROPERTY_VALUE ----------------------- TEMPYes it is, so we cannot drop it, but will have to add new files and drop the old ones
Add new tempfile:
SYS@testdb01>SQL>ALTER TABLESPACE temp ADD TEMPFILE '/data/oradata/testdb01/temp.dbf' size 256M; Tablespace altered.
Drop the old ones:
SYS@testdb01>SQL>ALTER DATABASE TEMPFILE '/data/oradata/testdb01/temp01.dbf' DROP INCLUDING DATAFILES; Database altered. SYS@testdb01>SQL>ALTER DATABASE TEMPFILE '/data/oradata/testdb01/temp02.dbf' DROP INCLUDING DATAFILES; Database altered.
Both of the old datafiles are now gone from the data dictionary:
SYS@pserser4>SQL> set lines 200 col tablespace_name format a20 col file_name format a50 SELECT tablespace_name, file_name FROM dba_temp_files WHERE tablespace_name = 'TEMP'; TABLESPACE_NAME FILE_NAME --------------------- -------------------------------------- TEMP /data/oradata/testdb01/temp.dbfNote that the old tempfiles are still on disk:
SYS@testdb01>SQL>!ls -la /data/oradata/testdb01/temp01.dbf -rw-r----- 1 oracle dba 114302976 Jun 30 04:04 /data/oradata/testdb01/temp01.dbf
If you want to add the old datafiles back to your temp tablespace you can do this with the REUSE keyword:
SYS@testdb01>SQL>ALTER TABLESPACE temp ADD TEMPFILE '/data/oradata/testdb01/temp01.dbf' size 256M reuse; Tablespace altered.In my case, the other tempfile /data/oradata/testdb01/temp02.dbf, was not present physically on disk.
The alert log confirms this:
2022-06-30T04:04:18.302852+02:00 Errors in file /data/oracle/diag/rdbms/testdb01/testdb01/trace/testdb01_dbw0_130154.trc: ORA-01157: cannot identify/lock data file 202 - see DBWR trace file ORA-01110: data file 202: '/data/oradata/testdb01/temp02.dbf' ORA-17503: ksfdopn:4 Failed to open file /data/oradata/testdb01/temp02.dbf ORA-17500: ODM err:File does not exist
No comments:
Post a Comment