联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户因为system表空间有一个数据文件放在其他位置,当时没有正常拷贝出来(备份了oradata路径下面文件,遗漏了一个system文件),尝试启动库报ORA-01157 ORA-01147等错误
[oracle@xifenfei check_db]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 5 21:13:28 2025 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> recover datafile 1; Media recovery complete. SQL> recover datafile 2,3,4,5,6,7,8,9,10; Media recovery complete. SQL> alter database open; alter database open * ERROR at line 1: ORA-01157: cannot identify/lock data file 11 - see DBWR trace file ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf' SQL> alter database datafile 11 offline drop; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-01147: SYSTEM tablespace file 11 is offline ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf'
alert日志报错信息
Sun Oct 05 22:35:01 2025 alter database open Sun Oct 05 22:35:01 2025 Errors in file /data/app/oracle/diag/rdbms/mtxdb1/mtxdb1/trace/mtxdb1_dbw0_5946.trc: ORA-01157: cannot identify/lock data file 11 - see DBWR trace file ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /data/app/oracle/diag/rdbms/mtxdb1/mtxdb1/trace/mtxdb1_ora_11264.trc: ORA-01157: cannot identify/lock data file 11 - see DBWR trace file ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf' ORA-1157 signalled during: alter database open... Sun Oct 05 22:35:25 2025 alter database datafile 11 offline ORA-1145 signalled during: alter database datafile 11 offline ... alter database datafile 11 offline drop Completed: alter database datafile 11 offline drop alter database open Errors in file /data/app/oracle/diag/rdbms/mtxdb1/mtxdb1/trace/mtxdb1_ora_11264.trc: ORA-01147: SYSTEM tablespace file 11 is offline ORA-01110: data file 11: '/u01/app/oracle/product/11.2.0.4/db_1/dbs/path_to_datafile.dbf' ORA-1147 signalled during: alter database open...
由于11号文件是system表空间的一个数据文件,对于这种数据文件丢失无法offline该数据文件,然后open库(也就是说在open库的时候,system表空间的数据文件必须全部online,如果有部分文件offline就会报ORA-01147).对于这样的情况,以前有过类似恢复经历:bbed打开丢失部分system数据文件库,这次的编写了一个m_scn程序实现快速处理
[oracle@xifenfei tmp]$ cat 1.txt 1@/data/app/oracle/oradata/mtxdb1/system01.dbf 11@/tmp/11.dbf [oracle@xifenfei tmp]$ ./m_scn 1.txt -------------Is processing datafile:/tmp/11.dbf------------- 1+0 records in 1+0 records out 1048576 bytes (1.0 MB) copied, 0.000835728 s, 1.3 GB/s [oracle@xifenfei tmp]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 8 11:27:32 2025 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set numw 16 SQL> col CHECKPOINT_TIME for a40 SQL> set lines 150 SQL> set pages 1000 SQL> SELECT status, 2 to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#, 3 count(*) ROW_NUM 4 FROM v$datafile_header 5 GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy 6 ORDER BY status, checkpoint_change#, checkpoint_time; STATUS CHECKPOINT_TIME FUZ CHECKPOINT_CHANGE# ROW_NUM ------- ---------------------------------------- --- ------------------ ---------------- OFFLINE 2025-10-02 06:50:06 NO 17328662858685 1 ONLINE 2025-10-02 06:50:06 NO 17328662858685 10 SQL> alter database datafile 11 online; Database altered.
然后重建ctl,并尝试打开库
然后查询11号文件中涉及的对象情况
SQL> select distinct owner,segment_name,segment_type from dba_extents where file_id=11; OWNER SEGMENT_NAME SEGMENT_TYPE ------------------------------ -------------------------------------- ------------------ SYS SYSTEM ROLLBACK SYS I_COL1 INDEX SYS AUD$ TABLE SQL> select owner,segment_name from dba_segments where HEADER_FILE=11; no rows selected
证明丢失的11号文件（system表空间文件）,涉及的对象较少,而且不涉及核心字典,比如tab$,obj$,col$等非常核心对象,评估理论上应该不涉业务数据丢失,尝试直接expdp导出数据,但是很不幸,报ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018]错误
. . exported "XFF020"."OTHERBILLDETAIL_DEL" 6.405 MB 126048 rows
. . exported "XFF020"."POSSOLDOUT" 7.784 MB 281413 rows
ORA-31693: Table data object "XFF020"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=159:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_DATA [TABLE_DATA:"XFF020"."MATERIELTRAN"]
UPDATE "SYS"."SYS_EXPORT_FULL_01" SET processing_state = :1, processing_status = :2
WHERE process_order = :3 AND duplicate = 0
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 7866
ORA-31693: Table data object "XFF020"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=159:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9721
----- PL/SQL Call Stack -----
object line object
handle number name
0xef2fc508 21979 package body SYS.KUPW$WORKER
0xef2fc508 9742 package body SYS.KUPW$WORKER
0xef2fc508 3437 package body SYS.KUPW$WORKER
0xef2fc508 10436 package body SYS.KUPW$WORKER
0xef2fc508 1824 package body SYS.KUPW$WORKER
0xef2feb20 2 anonymous block
ORA-39097: Data Pump job encountered unexpected error -607
ORA-39065: unexpected master process exception in DISPATCH
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-31693: Table data object "XFF020"."ANALYSEREPORT" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=161:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_DATA [TABLE_DATA:"XFF020"."ANALYSEREPORT"]
UPDATE "SYS"."SYS_EXPORT_FULL_01" SET processing_state = :1, processing_status = :2
WHERE process_order = :3 AND duplicate = 0
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 7866
ORA-31693: Table data object "XFF020"."ANALYSEREPORT" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=161:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9721
----- PL/SQL Call Stack -----
object line object
handle number name
0xef2fc508 21979 package body SYS.KUPW$WORKER
0xef2fc508 9742 package body SYS.KUPW$WORKER
0xef2fc508 3437 package body SYS.KUPW$WORKER
0xef2fc508 10436 package body SYS.KUPW$WORKER
0xef2fc508 1824 package body SYS.KUPW$WORKER
0xef2feb20 2 anonymous block
ORA-31693: Table data object "XFF020CW"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=160:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_DATA [TABLE_DATA:"XFF020CW"."MATERIELTRAN"]
UPDATE "SYS"."SYS_EXPORT_FULL_01" SET processing_state = :1, processing_status = :2
WHERE process_order = :3 AND duplicate = 0
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 7866
ORA-31693: Table data object "XFF020CW"."MATERIELTRAN" failed to load/unload and is being skipped due to error:
ORA-39068: invalid master table data in row with PROCESS_ORDER=160:1000001
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [11], [3], [18018], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPF$FILE", line 3720
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 9721
----- PL/SQL Call Stack -----
object line object
handle number name
0xef2fc508 21979 package body SYS.KUPW$WORKER
0xef2fc508 9742 package body SYS.KUPW$WORKER
0xef2fc508 3437 package body SYS.KUPW$WORKER
0xef2fc508 10436 package body SYS.KUPW$WORKER
0xef2fc508 1824 package body SYS.KUPW$WORKER
0xef2feb20 2 anonymous block
Job "SYS"."SYS_EXPORT_FULL_01" stopped due to fatal error at Wed Oct 8 11:59:29 2025 elapsed 0 00:18:48
对ORA-600 kdBlkCheckError进行分析分析（11表示文件号,3表示block）,是由于导出生成的master表写入在system表空间,而system表空间中的file# 11是人工构造出来的,block 3 是位图分配信息（该信息和实际字典中存储信息不匹配）,所以导致出现该错误,对于这个问题解决方法为expdp写master表不在system表空间即可,通过该操作,顺利导出数据,完成本次恢复任务
