Oracle Database error Ora-01157:cannot identify/lock data file results in tablespace not online

A friend and I talked to a case, a tablespace originally had a data file (already has a lot of data entered), then he added 7 data files, this time he found that he added more files, want to delete these data files, but do not understand the database, directly offline the table space, and then rm the relevant data files, The last table space cannot be online. I have simulated the recovery test for the scene, restoring it using bbed and Dul

Simulation scene
the tablespace has a data file with data in it, then adds a new data file, enters offline TBS, and then the RM data file
Offline The table space, the data files that exist in the table space are also offline

The code is as follows

SQL> create tablespace xifenfei datafile    2  '/u01/oracle/ora9i/oradata/xifenfei01.dbf' size 10M autoextend on next 16M;   Tablespace created.   SQL> create table chf.t_xifenfei01 tablespace xifenfei   2  as select * from dba_objects;   Table created.   SQL> create table chf.t_xifenfei02 tablespace xifenfei   2  as select * from dba_tables;   Table created.   SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI01;     COUNT(*) ----------      30758   SQL> C/01/02   1* SELECT COUNT(*) FROM CHF.T_XIFENFEI02 SQL> /     COUNT(*) ----------        865   SQL> select tablespace_name,segment_name from dba_segments                2  where segment_name like 'T_XIFENFEI%';   TABLESPACE_NAME                SEGMENT_NAME ------------------------------ ------------------------- XIFENFEI                       T_XIFENFEI01 XIFENFEI                       T_XIFENFEI02   SQL> ALTER tablespace xifenfei add datafile    2  '/u01/oracle/ora9i/oradata/xifenfei02.dbf' size 16m;   Tablespace altered.   SQL> alter tablespace xifenfei offline;   Tablespace altered.   SQL> select file#,status$ from file$;        FILE#    STATUS$ ---------- ----------          1          2          2          2          3          2          4          2          5          2          6          2          7          2          8          2          9          2         10          2         11          2         12          2   12 rows selected.   SQL> set pages 1000 SQL> /        FILE#    STATUS$ ---------- ----------          1          2          2          2          3          2          4          2          5          2          6          2          7          2          8          2          9          2         10          2         11          2         12          2   12 rows selected.   SQL> select file#,status from v$datafile;        FILE# STATUS ---------- -------          1 SYSTEM          2 ONLINE          3 ONLINE          4 ONLINE          5 ONLINE          6 ONLINE          7 ONLINE          8 ONLINE          9 ONLINE         10 ONLINE         11 OFFLINE         12 OFFLINE   12 rows selected.   SQL> select file#,status from v$datafile_header;        FILE# STATUS ---------- -------          1 ONLINE          2 ONLINE          3 ONLINE          4 ONLINE          5 ONLINE          6 ONLINE          7 ONLINE          8 ONLINE          9 ONLINE         10 ONLINE         11 OFFLINE         12 OFFLINE   12 rows selected.   SQL> select tablespace_name,status from dba_tablespaces;   TABLESPACE_NAME                STATUS ------------------------------ --------- SYSTEM                         ONLINE UNDOTBS1                       ONLINE TEMP                           ONLINE CWMLITE                        ONLINE DRSYS                          ONLINE EXAMPLE                        ONLINE INDX                           ONLINE ODM                            ONLINE TOOLS                          ONLINE USERS                          ONLINE XDB                            ONLINE XIFENFEI                       OFFLINE   12 rows selected.     SQL> select ts#,name,online$ from ts$;          TS# NAME                              ONLINE$ ---------- ------------------------------ ----------          0 SYSTEM                                  1          1 UNDOTBS1                                1          2 TEMP                                    1          3 CWMLITE                                 1          4 DRSYS                                   1          5 EXAMPLE                                 1          6 INDX                                    1          7 ODM                                     1          8 TOOLS                                   1          9 USERS                                   1         10 XDB                                     1         11 UNDOTBS2                                3         12 XIFENFEI                                2   13 rows selected.   SQL> col name for a50 SQL> select file#,name from v$datafile;        FILE# NAME ---------- --------------------------------------------------          1 /u01/oracle/ora9i/oradata/ora9i/system01.dbf          2 /u01/oracle/ora9i/oradata/ora9i/undotbs01.dbf          3 /u01/oracle/ora9i/oradata/ora9i/cwmlite01.dbf          4 /u01/oracle/ora9i/oradata/ora9i/drsys01.dbf          5 /u01/oracle/ora9i/oradata/ora9i/example01.dbf          6 /u01/oracle/ora9i/oradata/ora9i/indx01.dbf          7 /u01/oracle/ora9i/oradata/ora9i/odm01.dbf          8 /u01/oracle/ora9i/oradata/ora9i/tools01.dbf          9 /u01/oracle/ora9i/oradata/ora9i/users01.dbf         10 /u01/oracle/ora9i/oradata/ora9i/xdb01.dbf         11 /u01/oracle/ora9i/oradata/xifenfei01.dbf         12 /u01/oracle/ora9i/oradata/xifenfei02.dbf   12 rows selected.   SQL> !rm /u01/oracle/ora9i/oradata/xifenfei02.dbf   SQL> !ls -l /u01/oracle/ora9i/oradata/xifenfei* -rw-r-----  1 ora9i oinstall 10493952 Dec 25 20:19 /u01/oracle/ora9i/oradata/xifenfei01.dbf   SQL> alter tablespace xifenfei online; alter tablespace xifenfei online * ERROR at line 1: ORA-01157: cannot identify/lock data file 12 - see DBWR trace file ORA-01110: data file 12: '/u01/oracle/ora9i/oradata/xifenfei02.dbf'

This has been constructed because the data file loss caused the Tablespace online report ora-01157:cannot identify/lock data file Error

Online exists data file
after the existing data file online, tablespace still offline

The code is as follows

SQL> alter database datafile 11 online;   Database altered.   SQL> select file#,status from v$datafile_header;        FILE# STATUS ---------- -------          1 ONLINE          2 ONLINE          3 ONLINE          4 ONLINE          5 ONLINE          6 ONLINE          7 ONLINE          8 ONLINE          9 ONLINE         10 ONLINE         11 ONLINE         12 OFFLINE   12 rows selected.   SQL> select file#,status from v$datafile;        FILE# STATUS ---------- -------          1 SYSTEM          2 ONLINE          3 ONLINE          4 ONLINE          5 ONLINE          6 ONLINE          7 ONLINE          8 ONLINE          9 ONLINE         10 ONLINE         11 ONLINE         12 OFFLINE   12 rows selected.   SQL> select tablespace_name,status from dba_tablespaces;   TABLESPACE_NAME                STATUS ------------------------------ --------- SYSTEM                         ONLINE UNDOTBS1                       ONLINE TEMP                           ONLINE CWMLITE                        ONLINE DRSYS                          ONLINE EXAMPLE                        ONLINE INDX                           ONLINE ODM                            ONLINE TOOLS                          ONLINE USERS                          ONLINE XDB                            ONLINE XIFENFEI                       OFFLINE   12 rows selected.   SQL> select ts#,name,online$ from ts$;          TS# NAME                                                  ONLINE$ ---------- -------------------------------------------------- ----------          0 SYSTEM                                                      1          1 UNDOTBS1                                                    1          2 TEMP                                                        1          3 CWMLITE                                                     1          4 DRSYS                                                       1          5 EXAMPLE                                                     1          6 INDX                                                        1          7 ODM                                                         1          8 TOOLS                                                       1          9 USERS                                                       1         10 XDB                                                         1         11 UNDOTBS2                                                    3         12 XIFENFEI                                                    2   13 rows selected.   SQL> create table chf.t_1 tablespace xifenfei   2  as select * from dual; as select * from dual                  * ERROR at line 2: ORA-01542: tablespace 'XIFENFEI' is offline, cannot allocate space in it

The table space assignment is ORA-01542 error because the tablespace cannot be online

Using bbed to construct data files to spoof a database

The code is as follows

[Ora9i@xifenfei tmp]$ DD If=/dev/zero of=/u01/oracle/ora9i/oradata/xifenfei02.dbf > bs=8192 count=2049 2049+0 Records in 2049+0 Records out bbed> Copy File Block 1 to file 1 File:/u01/oracle/ora9i/oradata/xifenfei02.dbf (12) Block:1 offsets:0 to 511 dba:0x03000001 ------------------------------------------------------------------------ 0b020000 0100c002 00000000 00000104 1deb0000 00002009 00000008 329bf29e 4f524139 49000000 11010000 00050000 00200000 0b000300 00000000 00000000 <32 bytes per line> Bbed> Set Count 32 COUNT 32 Bbed> m/x 0c Warning:contents of previous bifile would be lost. Proceed? (y/n) Y File:/u01/oracle/ora9i/oradata/xifenfei02.dbf (12) Block:1 offsets:280 to 311 dba:0x03000001 ------------------------------------------------------------------------ 0c000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> Bbed> m/x 0c File:/u01/oracle/ora9i/oradata/xifenfei02.dbf (12) Block:1 offsets:52 to dba:0x03000001 ------------------------------------------------------------------------ 0c000300 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> bbed> sum apply Check value for File, Block 1: Current = 0xad21, required = 0xad21 sql> alter tablespace Xifenfei online; Alter Tablespace Xifenfei Online * ERROR at line 1: Ora-01122:database file failed verification check Ora-01110:data file: '/u01/oracle/ora9i/oradata/xifenfei02.dbf ' Ora-01251:unknown file Header Version read for File number 12 sql>!oerr ora 01251 01251, 00000, "Unknown file Header Version read for File number%s" *cause:read of the file header returned a record but its version cannot Be identified. Either the header has been corrupted, or the file is not a valid database file. *action:have the operating system make the correct file available to The database, or recover the file. Bbed> m/x 01000003 Warning:contents of previous bifile would be lost. Proceed? (y/n) Y File:/u01/oracle/ora9i/oradata/xifenfei02.dbf (12) Block:1 Offsets:4 to dba:0x03000001 ------------------------------------------------------------------------ 01000003 00000000 00000104 21ad0000 00002009 00000008 329bf29e 4f524139 <32 bytes per line> bbed> sum apply Check value for File, Block 1: Current = 0xace1, required = 0xace1 sql> alter tablespace Xifenfei online; Alter Tablespace Xifenfei Online * ERROR at line 1: Ora-01122:database file failed verification check Ora-01110:data file: '/u01/oracle/ora9i/oradata/xifenfei02.dbf ' Ora-01203:wrong incarnation of this file-wrong creation SCN Bbed> m/x 613b0300 Warning:contents of previous bifile would be lost. Proceed? (y/n) Y File:/u01/oracle/ora9i/oradata/xifenfei02.dbf (12) Block:1 offsets:100 to 131 dba:0x03000001 ------------------------------------------------------------------------ 613b0300 00000000 f35bb133 f39bae33 b46c0200 00000000 00000000 00000000 <32 bytes per line> bbed> sum apply Check value for File, Block 1: Current = 0XA0C9, required = 0XA0C9

--Prompt data file SCN is newer than control file (due to normal data file online)

The code is as follows

sql> alter tablespace Xifenfei online; Alter Tablespace Xifenfei Online * ERROR at line 1: Ora-01122:database file failed verification check Ora-01110:data file: '/u01/oracle/ora9i/oradata/xifenfei02.dbf ' Ora-01207:file is more recent than Controlfile-old Controlfile Sql> Set lines 160 Sql> col name for A50 Sql> Select ts#,file#,bytes/1024/1024/1024 status,enabled, 2 To_char (checkpoint_change#, ' 999999999999999 ') "SCN", 3 To_char (last_change#, ' 999999999999999 ') "STOP_SCN", 4 name from V$datafile; ts# file# STATUS ENABLED SCN stop_scn NAME ----- --- ---------- ---------- -------- ------------- --------------------------------------------- 0 1 37109375 READ WRITE 198231/u01/oracle/ora9i/oradata/ora9i/system01.dbf 1 2 1953125 READ WRITE 198231/u01/oracle/ora9i/oradata/ora9i/undotbs01.dbf 3 3 01953125 READ WRITE 198231/u01/oracle/ora9i/oradata/ora9i/cwmlite01.dbf 4 4 01953125 READ WRITE 198231/u01/oracle/ora9i/oradata/ora9i/drsys01.dbf 5 5 145874023 READ WRITE 198231/u01/oracle/ora9i/oradata/ora9i/example01.dbf 6 6 024414063 READ WRITE 198231/u01/oracle/ora9i/oradata/ora9i/indx01.dbf 7 7 01953125 READ WRITE 198231/u01/oracle/ora9i/oradata/ora9i/odm01.dbf 8 8 009765625 READ WRITE 198231/u01/oracle/ora9i/oradata/ora9i/tools01.dbf 9 9 024414063 READ WRITE 198231/u01/oracle/ora9i/oradata/ora9i/users01.dbf 043945313 READ WRITE 198231/u01/oracle/ora9i/oradata/ora9i/xdb01.dbf 009765625 DISABLED 228353 228353/u01/oracle/ora9i/oradata/xifenfei01.dbf 0 DISABLED 213917 213917/u01/oracle/ora9i/oradata/xifenfei02.dbf Rows selected. Sql> Set pages 100 Sql> Set Linesize 150 Sql> Select Ts#,file#,tablespace_name,status, 2 To_char (Creation_time, ' yyyy-mm-dd hh24:mi:ss ') Create_time, 3 To_char (checkpoint_change#, ' 9999999999999999 ') "SCN", 4 To_char (resetlogs_change#, ' 9999999999999999 ') "Resetlogs SCN", FUZZY From V$datafile_header; 5 ts# file# tablespace_name STATUS create_time SCN resetlogs SCN Fuz --- ----- ---------------- ------- ------------------- --------- ----------------- --- 0 1 SYSTEM ONLINE 2004-03-10 12:17:49 198231 158900 YES 1 2 UNDOTBS1 ONLINE 2004-03-10 20:30:20 198231 158900 YES 3 3 cwmlite ONLINE 2004-03-10 12:18:10 198231 158900 YES 4 4 drsys ONLINE 2004-03-10 12:18:11 198231 158900 YES 5 5 EXAMPLE ONLINE 2004-03-10 12:18:12 198231 158900 YES 6 6 INDX ONLINE 2004-03-10 12:18:16 198231 158900 YES 7 7 ODM ONLINE 2004-03-10 12:18:17 198231 158900 YES 8 8 TOOLS ONLINE 2004-03-10 12:18:19 198231 158900 YES 9 9 USERS ONLINE 2004-03-10 12:18:19 198231 158900 YES Ten XDB ONLINE 2004-03-10 12:18:20 198231 158900 YES Xifenfei OFFLINE 2014-12-25 17:57:07 228353 158900 NO 0 OFFLINE 0 0 Rows selected. Bbed> m/x 9d430300 File:/u01/oracle/ora9i/oradata/xifenfei02.dbf (12) Block:1 offsets:140 to 171 dba:0x03000001 ------------------------------------------------------------------------ 9d430300 00000000 f670b133 01000000 02000000 fc4e0000 10000000 02000000 <32 bytes per line> Bbed> SUM APPLY Check value for File, Block 1: Current = 0x9982, required = 0x9982 sql> ALTER tablespace Xifenfei ONLINE; Tablespace altered.

The following common errors occur during bbed recovery:

Ora-01122:database file failed verification check
Ora-01110:data file: '/u01/oracle/ora9i/oradata/xifenfei02.dbf '
Ora-01251:unknown file Header Version read for File number 12

This is mainly block number and block address mismatch, resulting in the database is not recognized

Ora-01122:database file failed verification check
Ora-01110:data file: '/u01/oracle/ora9i/oradata/xifenfei02.dbf '
Ora-01203:wrong incarnation of this file–wrong creation SCN

This is inconsistent with the creation of the data file header and the control files and the records in the file$

Ora-01122:database file failed verification check
Ora-01110:data file: '/u01/oracle/ora9i/oradata/xifenfei02.dbf '
Ora-01207:file is more recent than Controlfile–old Controlfile

This is due to the normal data file online, or you are constructing a header using the SCN is greater than the need to construct the file in the control file recorded in the SCN value

Subsequent processing

The code is as follows
Sql> SELECT COUNT (*) from CHF. t_xifenfei01; COUNT (*) ---------- 30758 Sql> c/01/02 1* SELECT COUNT (*) from CHF. T_xifenfei02 Sql>/ COUNT (*) ---------- 865 sql> ALTER DATABASE datafile OFFLINE DROP; Database altered.

Proof of bbed, analog data files, using the perfect recovery data content

Dul Recovery

The code is as follows

sql> ALTER tablespace Xifenfei OFFLINE; Tablespace altered. [Ora9i@xifenfei dul]$./dul Data unloader:10.2.0.5.33-internal only-on Thu Dec 25 21:18:54 2014 With 64-bit IO functions Copyright (c) 1994 2014 Bernard van Duijnen all rights reserved. Strictly Oracle Internal Use only Within one week you'll need a more recent DUL version to this OS Found db_id = 2666699570 Found db_name = ora9i Dul> Bootstrap; Probing file = 1, block = 417 Database version 9 bootstrap$ at file 1, block 377 . Unloading table bootstrap$ DUL:Warning:block number is non zero but marked deferred trying to process it anyhow Rows unloaded DUL:Warning:Dictionary Cache Dc_bootstrap is empty Reading BOOTSTRAP.dat Entries Loaded Parsing bootstrap$ Contents Generating DICT.DDL for version 9 obj$: Segobjno, file 1 Block 121 tab$: Segobjno 2, tabno 1, file 1 Block 25 col$: Segobjno 2, tabno 5, file 1 Block 25 user$: Segobjno, tabno 1, file 1 block 89 Running generated file "@dict. DDL" to unload the dictionary tables . Unloading table obj$ 31490 rows unloaded . Unloading table tab$ 909 rows unloaded . Unloading table col$ 35582 rows unloaded . Unloading table user$ rows unloaded Reading USER.dat Entries Loaded Reading OBJ.dat 31490 entries loaded and sorted 31490 entries Reading TAB.dat 909 Entries loaded Reading COL.dat 35582 entries loaded and sorted 35582 entries Reading BOOTSTRAP.dat Entries Loaded DUL:Warning:Recreating file "Dict.ddl" Generating DICT.DDL for version 9 obj$: Segobjno, file 1 Block 121 tab$: Segobjno 2, tabno 1, file 1 Block 25 col$: Segobjno 2, tabno 5, file 1 Block 25 user$: Segobjno, tabno 1, file 1 block 89 tabpart$: Segobjno 230, file 1 block 1657 indpart$: Segobjno 234, file 1 block 1689 tabcompart$: Segobjno 249, file 1 Block 1809 indcompart$: Segobjno 253, file 1 Block 1841 tabsubpart$: Segobjno, file 1 block 1737 indsubpart$: Segobjno 245, file 1 Block 1777 ind$: Segobjno 2, tabno 3, file 1 Block 25 icol$: Segobjno 2, tabno 4, file 1 Block 25 lob$: Segobjno 2, tabno 6, file 1 Block 25 coltype$: Segobjno 2, tabno 7, file 1 Block 25 type$: Segobjno 296, tabno 1, file 1 block 2129 collection$: Segobjno 296, tabno 2, file 1 block 2129 attribute$: Segobjno 296, tabno 3, file 1 block 2129 lobfrag$: Segobjno 258, file 1 Block 1881 lobcomppart$: Segobjno 261, file 1 block 1905 undo$: Segobjno, file 1 Block 105 ts$: Segobjno 6, Tabno 2, file 1 Block 57 props$: Segobjno, file 1 block 769 Running generated file "@dict. DDL" to unload the dictionary tables . Unloading table obj$ DUL:Warning:Recreating file "Obj.ctl" 31490 Rows unloaded . Unloading table tab$ DUL:Warning:Recreating file "Tab.ctl" 909 rows unloaded . Unloading table col$ DUL:Warning:Recreating file "Col.ctl" 35582 Rows unloaded . Unloading table user$ DUL:Warning:Recreating file "User.ctl" Unloaded rows . Unloading table tabpart$ rows unloaded . Unloading table indpart$ 128 rows unloaded . Unloading table tabcompart$ 0 rows unloaded . Unloading table indcompart$ 0 rows unloaded . Unloading table tabsubpart$ 0 rows unloaded . Unloading table indsubpart$ 0 rows unloaded . Unloading table ind$ 1396 rows unloaded . Unloading table icol$ 1842 rows unloaded . Unloading table lob$ 427 rows unloaded . Unloading table coltype$ 1160 rows unloaded . Unloading table type$ 956 rows unloaded . Unloading table collection$ rows unloaded . Unloading table attribute$ 3623 rows unloaded . Unloading table lobfrag$ 0 rows unloaded . Unloading table lobcomppart$ 0 rows unloaded . Unloading table undo$ rows unloaded . Unloading table ts$ rows unloaded . Unloading table props$ rows unloaded Reading USER.dat Entries Loaded Reading OBJ.dat 31490 entries loaded and sorted 31490 entries Reading TAB.dat 909 Entries loaded Reading COL.dat 35582 entries loaded and sorted 35582 entries Reading TABPART.dat entries loaded and sorted entries Reading TABCOMPART.dat 0 Entries loaded and sorted 0 entries Reading TABSUBPART.dat 0 Entries loaded and sorted 0 entries Reading INDPART.dat 128 entries loaded and sorted 128 entries Reading INDCOMPART.dat 0 Entries loaded and sorted 0 entries Reading INDSUBPART.dat 0 Entries loaded and sorted 0 entries Reading IND.dat 1396 Entries loaded Reading LOB.dat 427 Entries loaded Reading ICOL.dat 1842 Entries loaded Reading COLTYPE.dat 1160 Entries loaded Reading TYPE.dat 956 Entries loaded Reading ATTRIBUTE.dat 3623 Entries loaded Reading COLLECTION.dat Entries Loaded Reading BOOTSTRAP.dat Entries Loaded Reading LOBFRAG.dat 0 Entries loaded and sorted 0 entries Reading LOBCOMPPART.dat 0 Entries loaded and sorted 0 entries Reading UNDO.dat Entries Loaded Reading TS.dat Entries Loaded Reading PROPS.dat Entries Loaded Database Character Set is ZHS16GBK Database national Character set is AL16UTF16 dul> unload table chf.t_xifenfei01; . Unloading table t_xifenfei01 30758 rows unloaded dul> unload table chf.t_xifenfei02; . Unloading table t_xifenfei02 865 rows unloaded Dul>

Here it proves that in the case of table space can not be online, dul handling similar failures is easy (but need to rebuild the table space, bbed processing from the principle can not rebuild the table space), of course, if the object involved a lot, and the missing files are very few, may bbed is also a good choice.
In addition: If the tablespace is missing a file containing data, it can also be handled by a similar bbed and Dul method: Bbed to consider skipping lost files after the table space online; Dul processing is exactly the same

Summary:
ORA-01157 are usually generated by the background process DBWR locking.
This prompt appears when the database is not open because one or more data files cannot be opened during recovery, such as when the databases have been mount.
Data file loss, data file licensing issues, such as data files Oracle users do not have write permissions, etc. will produce ORA-01157.
In the case of the open state, ORA-01157 data files that are not listed are not affected.

Supplementary Note:

A careful friend might have found that when checking the corresponding data file, only the ASM disk was checked for the corresponding data file.
Because the error database is RAC, the data file for the file system in the check prompt is ignored. It's still not careful enough, Khan.
This should be the case when using Swingbench, creating an SOE table space directly to the next, resulting in the creation of the data file to the file system, and the file system is unshared. (RAC Environment)