system表空间丢失部分文件恢复---惜分飞

有客户因为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表空间即可,通过该操作,顺利导出数据,完成本次恢复任务