關於臨時資料檔案ORA-01187,ORA-01110處理

檢查alert 日誌發現以下錯誤

Sat Aug 05 20:41:16 2017
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\tndb\tndb\trace\tndb_ora_4776.trc:
ORA-01187: 鐢變簬楠岃瘉嫻嬭瘯澶辮觸鑰屾棤娉曚粠鏂囦歡  璇誨彇
ORA-01110: 鏁版嵁鏂囦歡 201: 'D:\APP\ADMINISTRATOR\ORADATA\TNDB\TEMP01.DBF'
Sat Aug 05 20:41:17 2017
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\tndb\tndb\trace\tndb_ora_4776.trc:
ORA-01187: 鐢變簬楠岃瘉嫻嬭瘯澶辮觸鑰屾棤娉曚粠鏂囦歡  璇誨彇
ORA-01110: 鏁版嵁鏂囦歡 201: 'D:\APP\ADMINISTRATOR\ORADATA\TNDB\TEMP01.DBF'
Sat Aug 05 20:41:18 2017

trace 檔案內容

Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
ORA-01187: 由於驗證測試失敗而無法從檔案  讀取
ORA-01110: 資料檔案 201: 'D:\APP\ADMINISTRATOR\ORADATA\TNDB\TEMP01.DBF'
Dump of memory from 0x00007FFE8D74D0E0 to 0x00007FFE8D74D2DD
7FFE8D74D0E0 20200A0D 20202020 69772020 41206874  [..        with A]
7FFE8D74D0F0 28736120 20200A0D 20202020 65732020  [ as(..        se]
7FFE8D74D100 7463656C 73696420 636E6974 6C502074  [lect distinct Pl]
7FFE8D74D110 44496775 6C50202C 6F436775 502C6564  [ugID, PlugCode,P]
7FFE8D74D120 4E67756C 2C656D61 67756C50 2C6C7255  [lugName,PlugUrl,]
7FFE8D74D130 65726150 4449746E 756C502C 726F5367  [ParentID,PlugSor]
7FFE8D74D140 72662074 76206D6F 5F776569 5F4D4942  [t from view_BIM_]
7FFE8D74D150 6F74704F 62755372 74737953 6C506D65  [OptorSubSystemPl]
7FFE8D74D160 20326775 20200A0D 20202020 68772020  [ug2 ..        wh]
7FFE8D74D170 20657265 4F706D49 6265576E 6120313D  [ere ImpOnWeb=1 a]
7FFE8D74D180 5020646E 5467756C 3D657079 6275533A  [nd PlugType=:Sub]
7FFE8D74D190 74737953 44496D65 646E6120 65704F20  [SystemID and Ope]
7FFE8D74D1A0 6F746172 3D444972 65704F3A 6F746172  [ratorID=:Operato]
7FFE8D74D1B0 20444972 20200A0D 20202020 0D292020  [rID ..        ).]
7FFE8D74D1C0 2020200A 20202020 6C657320 20746365  [.        select ]
7FFE8D74D1D0 74736964 74636E69 66202A20 286D6F72  [distinct * from(]
7FFE8D74D1E0 20200A0D 20202020 65732020 7463656C  [..        select]
7FFE8D74D1F0 66202A20 206D6F72 0A0D2041 20202020  [ * from A ..    ]
7FFE8D74D200 20202020 6F696E75 6C61206E 0A0D206C  [    union all ..]
7FFE8D74D210 20202020 20202020 656C6573 46207463  [        select F]
7FFE8D74D220 61204449 6C502073 44496775 756C502C  [ID as PlugID,Plu]
7FFE8D74D230 646F4367 6C502C65 614E6775 502C656D  [gCode,PlugName,P]
7FFE8D74D240 5567756C 502C6C72 6E657261 2C444974  [lugUrl,ParentID,]
7FFE8D74D250 74726F53 20736120 67756C50 74726F53  [Sort as PlugSort]
7FFE8D74D260 6F726620 6557206D 79535F62 6C505F73  [ from Web_Sys_Pl]
7FFE8D74D270 20206775 72656877 49462065 6E692044  [ug  where FID in]
7FFE8D74D280 200A0D20 20202020 28202020 656C6573  [ ..        (sele]
7FFE8D74D290 64207463 69747369 2074636E 65726150  [ct distinct Pare]
7FFE8D74D2A0 4449746E 6F726620 2941206D 20200A0D  [ntID from A)..  ]
7FFE8D74D2B0 20202020 0D292020 2020200A 20202020  [      )..       ]
7FFE8D74D2C0 64726F20 62207265 6C502079 6F536775  [ order by PlugSo]
7FFE8D74D2D0 0A0D7472 20202020 20202020 00000000  [rt..        ....]

追蹤日誌

Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED
Fri Jul 21 00:28:03 2017
Read of datafile 'D:\APP\ADMINISTRATOR\ORADATA\TNDB\TEMP01.DBF' (fno 201) header failed with ORA-01202
Rereading datafile 201 header failed with ORA-01202
Fri Jul 21 00:28:03 2017
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\tndb\tndb\trace\tndb_dbw0_2484.trc:
ORA-01186: ?? 201 ??????
ORA-01122: ????? 201 ????
ORA-01110: ???? 201: 'D:\APP\ADMINISTRATOR\ORADATA\TNDB\TEMP01.DBF'
ORA-01202: ???????? - ??????
File 201 not verified due to error ORA-01122
Starting background process TMON
Fri Jul 21 00:28:03 2017
TMON started with pid=29, OS id=2904
Fri Jul 21 00:28:03 2017
Thread 1 opened at log sequence 66
   Current log# 3 seq# 66 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\TNDB\REDO03.LOG
Successful open of redo thread 1
Fri Jul 21 00:28:03 2017
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Jul 21 00:28:03 2017
SMON: enabling cache recovery
Fri Jul 21 00:28:04 2017
[3056] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:463785234 end:463785640 diff:406 ms (0.4 seconds)
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Fri Jul 21 00:28:04 2017
SMON: enabling tx recovery
Fri Jul 21 00:28:04 2017
Read of datafile 'D:\APP\ADMINISTRATOR\ORADATA\TNDB\TEMP01.DBF' (fno 201) header failed with ORA-01202
Rereading datafile 201 header failed with ORA-01202
Starting background process SMCO
Fri Jul 21 00:28:04 2017
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\tndb\tndb\trace\tndb_dbw0_2484.trc:
ORA-01186: ?? 201 ??????
ORA-01122: ????? 201 ????
ORA-01110: ???? 201: 'D:\APP\ADMINISTRATOR\ORADATA\TNDB\TEMP01.DBF'
ORA-01202: ???????? - ??????
File 201 not verified due to error ORA-01122
Fri Jul 21 00:28:04 2017
Cannot re-create tempfile D:\APP\ADMINISTRATOR\ORADATA\TNDB\TEMP01.DBF, the same name file exists
Fri Jul 21 00:28:04 2017
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\tndb\tndb\trace\tndb_dbw0_2484.trc:
ORA-01157: ????/?????? 202 - ??? DBWR ????
ORA-01110: ???? 202: 'D:\APP\ADMINISTRATOR\ORADATA\TNDB\TNTICKET2017_TEMP.DBF'
ORA-27041: ??????
OSD-04002: 無法開啟檔案
O/S-Error: (OS 2) 系統找不到指定的檔案。
Fri Jul 21 00:28:04 2017
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\tndb\tndb\trace\tndb_dbw0_2484.trc:
ORA-01186: ?? 202 ??????
ORA-01157: ????/?????? 202 - ??? DBWR ????
ORA-01110: ???? 202: 'D:\APP\ADMINISTRATOR\ORADATA\TNDB\TNTICKET2017_TEMP.DBF'
Fri Jul 21 00:28:04 2017
File 202 not verified due to error ORA-01157
Fri Jul 21 00:28:04 2017
SMCO started with pid=28, OS id=1772
Fri Jul 21 00:28:04 2017
Re-creating tempfile D:\APP\ADMINISTRATOR\ORADATA\TNDB\TNTICKET2017_TEMP.DBF

解決方法

檔案頭檢驗出問題，無法使用此資料檔案，解決辦法，只需新建一個臨時資料檔案刪除老的臨時資料檔案

select * from v$tempfiles;

新增臨時資料檔案

alter tablespace temp file add datafile 'D:\APP\ADMINISTRATOR\ORADATA\TNDB\TEMP02.DBF';

刪除老的臨時資料檔案

alter database tempfile  1 drop;