[20200416]ORA-01187 cannot read from file because it failed verification tests.txt

--//上午測試時遇到的問題：
1.環境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.問題：
SCOTT@book> select * from dept
2 @ prxx
declare
*
ERROR at line 1:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '/mnt/ramdisk/book/temp01.dbf'
ORA-06512: at line 2

SCOTT@book> select * from dba_temp_files;
select * from dba_temp_files
*
ERROR at line 1:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '/mnt/ramdisk/book/temp01.dbf'

--//前幾天我使用重建過控制檔案，當時執行了ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ramdisk/book/temp01.dbf' REUSE;時正常的。
Tue Apr 07 11:59:10 2020
ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ramdisk/book/temp01.dbf' REUSE
Completed: ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ramdisk/book/temp01.dbf' REUSE
--//當時的記錄。

SCOTT@book> ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ramdisk/book/temp01.dbf' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ramdisk/book/temp01.dbf' REUSE
*
ERROR at line 1:
ORA-01537: cannot add file '/mnt/ramdisk/book/temp01.dbf' - file already part of database

--//感覺/mnt/ramdisk/book/temp01.dbf檔案壞了。
$ xxd -c 16 /mnt/ramdisk/book/temp01.dbf | head
0000000: 00a2 0000 0000 c0ff 0000 0000 0000 0000 ......?........
0000010: 6635 0000 0020 0000 00cf 0000 7d7c 7b7a f5... ...?.}|{z
0000020: a081 0000 0000 0000 0000 0000 0000 0000 ................
0000030: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000040: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000050: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000060: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000070: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000080: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000090: 0000 0000 0000 0000 0000 0000 0000 0000 ................}

$ oerr ora 01187
01187, 00000, "cannot read from file %s because it failed verification tests"
// *Cause: The data file did not pass the checks to insure it is part of the
//         database. Reads are not allowed until it is verified.
// *Action: Make the correct file available to the database. Then, either open
//         the database, or execute ALTER SYSTEM CHECK DATAFILES.

SCOTT@book> alter database tempfile '/mnt/ramdisk/book/temp01.dbf' drop;
alter database tempfile '/mnt/ramdisk/book/temp01.dbf' drop
*
ERROR at line 1:
ORA-02002: error while writing to audit trail
ORA-25153: Temporary Tablespace is Empty

ALTER SYSTEM CHECK DATAFILES
*
ERROR at line 1:
ORA-02002: error while writing to audit trail
ORA-25153: Temporary Tablespace is Empty
--//必須以sys使用者執行：

SYS@book> ALTER SYSTEM CHECK DATAFILES;
System altered.

--//檢視alert提示：
[2020-04-16 08:57:15] ORA-3261 signalled during: ALTER TABLESPACE TEMP drop TEMPFILE '/mnt/ramdisk/book/temp01.dbf' ...
[2020-04-16 09:01:17] Thu Apr 16 09:01:16 2020
[2020-04-16 09:01:17] alter database tempfile '/mnt/ramdisk/book/temp01.dbf' drop
[2020-04-16 09:01:17] Completed: alter database tempfile '/mnt/ramdisk/book/temp01.dbf' drop
--//可以看出前面報錯，實際上已經刪除。

3.刪除重新建立看看。

SCOTT@book> select * from dba_temp_files;
no rows selected

$ xxd -c 16 /mnt/ramdisk/book/temp01.dbf | head
0000000: 00a2 0000 0000 c0ff 0000 0000 0000 0000 ......?........
0000010: 6635 0000 0020 0000 00cf 0000 7d7c 7b7a f5... ...?.}|{z
0000020: a081 0000 0000 0000 0000 0000 0000 0000 ................
0000030: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000040: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000050: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000060: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000070: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000080: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0000090: 0000 0000 0000 0000 0000 0000 0000 0000 ................}

SCOTT@book> ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ramdisk/book/temp01.dbf' REUSE;
Tablespace altered.

SCOTT@book> select * from dba_temp_files
2 @ prxx
==============================
FILE_NAME                     : /mnt/ramdisk/book/temp01.dbf
FILE_ID                       : 1
TABLESPACE_NAME               : TEMP
BYTES                         : 434110464
BLOCKS                        : 52992
STATUS                        : ONLINE
RELATIVE_FNO                  : 1
AUTOEXTENSIBLE                : NO
MAXBYTES                      : 0
MAXBLOCKS                     : 0
INCREMENT_BY                  : 0
USER_BYTES                    : 433061888
USER_BLOCKS                   : 52864
PL/SQL procedure successfully completed.

SCOTT@book> select * from dept where deptno=40
2 @ prxx
==============================
DEPTNO                        : 40
DNAME                         : OPERATIONS
LOC                           : BOSTON
PL/SQL procedure successfully completed.

4.什麼原因導致這個問題呢？
--//前幾天做了nid修改DBID還原的測試，難道臨時檔案裡面也記錄dbid資訊嗎？，連結：http://blog.itpub.net/267265/viewspace-2684913/

SCOTT@book> select name,open_mode,dbid,db_unique_name from v$database;
NAME OPEN_MODE        DBID DB_UNIQUE_NAME
---- ---------- ---------- --------------
BOOK READ WRITE 1337401710 book

SCOTT@book> @ 10to16 1337401710
10 to 16 HEX      REVERSE16
----------------- -----------------------------------
000000004fb7216e 0x6e21b74f-00000000

$ xxd -c 16 /mnt/ramdisk/book/temp01.dbf | head -1000 | grep -i 6e21
0002010: de4d 0000 0000 0000 0004 200b 6e21 b74f 轒........ .n!.O
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//估計問題在這裡，重新看了前面nid的執行過程：
$ nid TARGET=/
DBNEWID: Release 11.2.0.4.0 - Production on Tue Apr 7 11:43:18 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database BOOK (DBID=1337401710)
Connected to server version 11.2.0

Control Files in database:
    /mnt/ramdisk/book/control01.ctl
    /mnt/ramdisk/book/control02.ctl

Change database ID of database BOOK? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1337401710 to 1477856022
    Control File /mnt/ramdisk/book/control01.ctl - modified
    Control File /mnt/ramdisk/book/control02.ctl - modified
    Datafile /mnt/ramdisk/book/system01.db - dbid changed
    Datafile /mnt/ramdisk/book/sysaux01.db - dbid changed
    Datafile /mnt/ramdisk/book/undotbs01.db - dbid changed
    Datafile /mnt/ramdisk/book/users01.db - dbid changed
    Datafile /mnt/ramdisk/book/example01.db - dbid changed
    Datafile /mnt/ramdisk/book/tea01.db - dbid changed
    Datafile /mnt/ramdisk/book/temp01.db - dbid changed
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Control File /mnt/ramdisk/book/control01.ctl - dbid changed
    Control File /mnt/ramdisk/book/control02.ctl - dbid changed
    Instance shut down
--//確實修改了臨時檔案的dbid。正是這個問題導致上面的情況發生。做一個記錄。

[20200416]ORA-01187 cannot read from file because it failed verification tests.

相關文章