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

[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。正是这个问题导致上面的情况发生。做一个记录。