Oracle数据文件被误删除，无任何备份，如何导出剩余的数据？

最新推荐文章于 2025-11-24 20:48:20 发布

原创最新推荐文章于 2025-11-24 20:48:20 发布 · 577 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

Oracle 专栏收录该内容

78 篇文章

订阅专栏

环境说明:

DB:Oracle 11.2.0.4.0

误删除疑问:

SIMPLE_ORDERS表存在CJC表空间下5个数据文件里，误删除其中一个数据文件(rm -f 方式)，无任何备份情况下，SIMPLE_ORDERS表在其他数据文件内的数据，能否全部找回？

模拟误操作:

数据库版本:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

创建测试表空间、用户

select name from v$dbfile;
create tablespace cjc datafile '/u01/app/oracle/oradata/cjc/cjc01.dbf' size 1M autoextend on maxsize 2M;
alter tablespace cjc add datafile '/u01/app/oracle/oradata/cjc/cjc02.dbf' size 1M autoextend on maxsize 2M;
alter tablespace cjc add datafile '/u01/app/oracle/oradata/cjc/cjc03.dbf' size 1M autoextend on maxsize 2M;
alter tablespace cjc add datafile '/u01/app/oracle/oradata/cjc/cjc04.dbf' size 1M autoextend on maxsize 2M;
alter tablespace cjc add datafile '/u01/app/oracle/oradata/cjc/cjc05.dbf' size 1M autoextend on maxsize 2M;

create user cjc identified by "1" default tablespace cjc;
grant dba to cjc;

set line 300
col TABLESPACE_NAME for a15
col FILE_NAME for a50
select TABLESPACE_NAME,FILE_ID,FILE_NAME,BYTES/1024/1024 MB,AUTOEXTENSIBLE,MAXBYTES/1024/1024 MAX_MB from dba_data_files;
TABLESPACE_NAME    FILE_ID FILE_NAME						      MB AUT	 MAX_MB
--------------- ---------- -------------------------------------------------- ---------- --- ----------
......
CJC			 5 /u01/app/oracle/oradata/cjc/cjc01.dbf		       1 YES	      2
CJC			 6 /u01/app/oracle/oradata/cjc/cjc02.dbf		       1 YES	      2
CJC			 7 /u01/app/oracle/oradata/cjc/cjc03.dbf		       1 YES	      2
CJC			 8 /u01/app/oracle/oradata/cjc/cjc04.dbf		       1 YES	      2
CJC			 9 /u01/app/oracle/oradata/cjc/cjc05.dbf		       1 YES	      2

9 rows selected.

创建测试表simple_orders:

CREATE TABLE simple_orders (
    order_id    NUMBER,
    customer_id NUMBER NOT NULL,
    order_date  DATE DEFAULT SYSDATE NOT NULL,
    product_id  NUMBER NOT NULL,
    amount      NUMBER(10,2) NOT NULL
);

新增数据，将数据文件填满

DECLARE
  l_batch_size NUMBER := 100;  -- 每批插入量
  l_total_rows NUMBER := 1000000; -- 总行数
BEGIN
  FOR i IN 1..(l_total_rows/l_batch_size) LOOP
    INSERT INTO simple_orders (customer_id, order_date, product_id, amount)
    SELECT 
        MOD(ROWNUM, 10000) + 1,  -- 客户ID范围1-10000
        SYSDATE - DBMS_RANDOM.VALUE(0, 365),  -- 过去365天随机日期
        TRUNC(DBMS_RANDOM.VALUE(1, 1001)),    -- 产品ID范围1-1000
        ROUND(DBMS_RANDOM.VALUE(10, 1000), 2) -- 金额范围10-1000
    FROM dual 
    CONNECT BY LEVEL <= l_batch_size;
    
    COMMIT;  -- 每100条提交一次
    DBMS_OUTPUT.PUT_LINE('已插入: ' || i * l_batch_size || ' 条记录');
  END LOOP;
END;
/

ERROR at line 1:
ORA-01653: unable to extend table CJC.SIMPLE_ORDERS by 128 in tablespace CJC
ORA-06512: at line 6

10MB数据文件，存储了210200条数据

SQL> SELECT COUNT(*) FROM SIMPLE_ORDERS;

  COUNT(*)
----------
    210200

查看数据在数据文件的分布:

SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) file_no,COUNT(ROWID) FROM SIMPLE_ORDERS group by DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) order by 1;

   FILE_NO COUNT(ROWID)
---------- ------------
	 5	  66328
	 6	  35409
	 7	  35405
	 8	  35408
	 9	  37650

通过rowid可以获取对应的对象号、文件号、块号、rowid信息:

dbms_rowid.rowid_object(ROWID)
dbms_rowid.rowid_relative_fno(ROWID)
dbms_rowid.rowid_block_number(ROWID)
dbms_rowid.rowid_row_number(ROWID)

对应的数据文件如下:

select FILE_ID,FILE_NAME from dba_data_files where tablespace_name='CJC';

   FILE_ID FILE_NAME
---------- --------------------------------------------------
	 5 /u01/app/oracle/oradata/cjc/cjc01.dbf
	 6 /u01/app/oracle/oradata/cjc/cjc02.dbf
	 7 /u01/app/oracle/oradata/cjc/cjc03.dbf
	 8 /u01/app/oracle/oradata/cjc/cjc04.dbf
	 9 /u01/app/oracle/oradata/cjc/cjc05.dbf

模拟 FILE_ID 6 号文件cjc02.dbf丢失:

停库:

shutdown immediate

冷备

[oracle@cjc-db-02 oracle]$ cp -r oradata oradata_bak
[oracle@cjc-db-02 oracle]$ du -sh oradata*

模拟误删除

[oracle@cjc-db-02 cjc]$ rm -f cjc02.dbf

启动数据库:报错

SQL> startup
ORACLE instance started.

Total System Global Area 1152450560 bytes
Fixed Size		    2252584 bytes
Variable Size		  738197720 bytes
Database Buffers	  402653184 bytes
Redo Buffers		    9347072 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/cjc/cjc02.dbf'

告警日志如下:

[oracle@cjc-db-02 trace]$ tail -10f alert_cjc.log 
......
ALTER DATABASE OPEN
Sat Aug 09 05:11:06 2025
Errors in file /u01/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_dbw0_13997.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/cjc/cjc02.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_14361.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/cjc/cjc02.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
Sat Aug 09 05:11:06 2025
Checker run found 1 new persistent data failures

启动数据库:

将误删除的数据文件离线（Offline）并标记为DROP：

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/cjc/cjc02.dbf' OFFLINE DROP;

SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
	 1 SYSTEM
	 2 ONLINE
	 3 ONLINE
	 4 ONLINE
	 5 ONLINE
	 6 OFFLINE
	 7 ONLINE
	 8 ONLINE
	 9 ONLINE

9 rows selected.

启动数据库：

SQL> ALTER DATABASE OPEN;
Database altered.

查看:

SQL> SELECT FILE#,CHECKPOINT_CHANGE#,STATUS FROM V$DATAFILE;

     FILE# CHECKPOINT_CHANGE# STATUS
---------- ------------------ -------
	 1	       967782 SYSTEM
	 2	       967782 ONLINE
	 3	       967782 ONLINE
	 4	       967782 ONLINE
	 5	       967782 ONLINE
	 6	       967779 OFFLINE
	 7	       967782 ONLINE
	 8	       967782 ONLINE
	 9	       967782 ONLINE

9 rows selected.

丢失数据疑问?

查询数据:

SELECT * FROM CJC.SIMPLE_ORDERS;

  ORDER_ID CUSTOMER_ID ORDER_DAT PRODUCT_ID	AMOUNT
---------- ----------- --------- ---------- ----------
		    30 06-APR-25	 44	159.51
		    31 30-JAN-25	265	602.66
		    32 26-FEB-25	440	791.82
		    33 01-JUL-25	919	 351.2
ERROR:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/cjc/cjc02.dbf'

103965 rows selected.

查询出 103965 行数据，总数据量 210200 行，少了 106235 行数据。

而实际上 FILE_NO=6 cjc02.dbf 数据文件只有 35409 行数据，106235-35409=70826条数据哪去了？

   FILE_NO COUNT(ROWID)
---------- ------------
	 5	  66328
	 6	  35409
	 7	  35405
	 8	  35408
	 9	  37650

查看现有数据文件对应的数据:

select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (5);   ---30900;
select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (6);  ---0
select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (7);  ---35400
select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (8);  ---35400
select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (9);  ---2235

再看误删除之前的数据分布:

SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) file_no,COUNT(ROWID) FROM SIMPLE_ORDERS group by DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) order by 1;

   FILE_NO COUNT(ROWID)
---------- ------------
	 5	  66328
	 6	  35409
	 7	  35405
	 8	  35408
	 9	  37650

可以看到，虽然只删除了 FILE_NO=6 文件，结果导致其他数据文件也丢失了很多数据！！！

初步怀疑是因为表空间使用 ASSM（自动段空间管理），空间分配由位图块管理，位图块分布在所有数据文件中（包括文件号 6），若文件号 6 存储了管理文件号 5、7、8、9数据文件空间的位图块，会导致Oracle 无法确认这些文件中的空闲块或数据块状态，部分本应可访问的数据块被标记为“损坏”或“不可用”，导致数据丢失。

模拟误删除块之前，查询了段头块位置:存储在FILE_NO=5

SELECT header_file, header_block 
FROM dba_segments 
WHERE segment_name = 'SIMPLE_ORDERS' AND owner = 'CJC';

HEADER_FILE HEADER_BLOCK
----------- ------------
	  5	      10

检查区的分布：

SELECT file_id, COUNT(*) 
FROM dba_extents 
WHERE segment_name = 'SIMPLE_ORDERS' AND owner = 'CJC'
GROUP BY file_id ORDER BY 1;
   FILE_ID   COUNT(*)
---------- ----------
	 5	   16
	 6	    1
	 7	    1
	 8	    1
	 9	    2

查看位图块位置 (ASSM 表空间):

SELECT file_id, relative_fno, block_id, blocks 
FROM dba_free_space 
WHERE tablespace_name = 'CJC' ;
   FILE_ID RELATIVE_FNO   BLOCK_ID     BLOCKS
---------- ------------ ---------- ----------
	 6	      6 	 8	  120
	 7	      7 	 8	  120
	 8	      8 	 8	  120
	 9	      9 	16	  112

可以看到，FILE_ID=6 存在位图块，管理120个块。

导出现有数据:

尝试将已存在的数据导出:
添加数据文件，用来存储恢复的数据

alter tablespace cjc add datafile '/u01/app/oracle/oradata/cjc/cjc06.dbf' size 1M autoextend on maxsize 2000M;

尝试直接用CTAS找回数据，报错:

SQL> conn / as sysdba
Connected.
SQL> create table TMP_SIMPLE_ORDERS as select * from cjc.SIMPLE_ORDERS;
create table TMP_SIMPLE_ORDERS as select * from cjc.SIMPLE_ORDERS
                                                    *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/cjc/cjc02.dbf'

SQL> select * from TMP_SIMPLE_ORDERS;
select * from TMP_SIMPLE_ORDERS
              *
ERROR at line 1:
ORA-00942: table or view does not exist

指定数据文件，进行插入，也不行:

SQL> create table TMP_SIMPLE_ORDERS as select * from cjc.SIMPLE_ORDERS where 1=2;
SQL> insert into TMP_SIMPLE_ORDERS select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (5) ;
insert into TMP_SIMPLE_ORDERS select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (5)
            *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/cjc/cjc02.dbf'

exp导出，报错:

[oracle@cjc-db-02 tmp]$ exp cjc/1 file=table.dmp file=table.log tables=cjc.SIMPLE_ORDERS

Export: Release 11.2.0.4.0 - Production on Sat Aug 9 05:46:17 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                  SIMPLE_ORDERS
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/cjc/cjc02.dbf'
Export terminated successfully with warnings.

expdp导出，报错:

CREATE DIRECTORY expdir AS '/home/oracle/tmp'; 
grant read,write on directory expdir to public;

[oracle@cjc-db-02 tmp]$ expdp cjc/1 directory=expdir file=xxx.dmp logfile=xxx.log tables=cjc.SIMPLE_ORDERS

Export: Release 11.2.0.4.0 - Production on Sat Aug 9 05:48:28 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=xxx.dmp" Location: Command Line, Replaced with: "dumpfile=xxx.dmp"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "CJC"."SYS_EXPORT_TABLE_01":  cjc/******** directory=expdir dumpfile=xxx.dmp logfile=xxx.log tables=cjc.SIMPLE_ORDERS reuse_dumpfiles=true 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "CJC"."SIMPLE_ORDERS" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/cjc/cjc02.dbf'
Master table "CJC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CJC.SYS_EXPORT_TABLE_01 is:
  /home/oracle/tmp/xxx.dmp
Job "CJC"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Sat Aug 9 05:48:31 2025 elapsed 0 00:00:02

因为在读取到103965行数据以后，发现data file 6丢失，终止了导出操作，可以使用rownum，即导出完103965行数据以后就人为终止了操作。

SQL> create table cjc.bak_t1 as select * from cjc.SIMPLE_ORDERS where rownum<=103965;
Table created.

也可以导出新恢复的数据:

[oracle@cjc-db-02 tmp]$ expdp cjc/1 directory=expdir file=t1.dmp logfile=t1.log tables=bak_t1

Export: Release 11.2.0.4.0 - Production on Sat Aug 9 19:18:43 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=t1.dmp" Location: Command Line, Replaced with: "dumpfile=t1.dmp"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "CJC"."SYS_EXPORT_TABLE_01":  cjc/******** directory=expdir dumpfile=t1.dmp logfile=t1.log tables=bak_t1 reuse_dumpfiles=true 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "CJC"."BAK_T1"                              2.465 MB  103965 rows
Master table "CJC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CJC.SYS_EXPORT_TABLE_01 is:
  /home/oracle/tmp/t1.dmp
Job "CJC"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 9 19:18:50 2025 elapsed 0 00:00:02

思考题:

段头块位于file_id=5中，那么如果丢失的是file_id=5数据文件，其他4个数据文件完好，是否会导致TMP_SIMPLE_ORDERS表数据，全部丢失？

SELECT header_file, header_block 
FROM dba_segments 
WHERE segment_name = 'SIMPLE_ORDERS' AND owner = 'CJC';

HEADER_FILE HEADER_BLOCK
----------- ------------
	  5	      10

SQL> shutdown immediate;
[oracle@cjc-db-02 cjc]$ mv cjc01.dbf cjc01.dbf.bak

SQL> startup

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/cjc/cjc01.dbf' OFFLINE DROP;

SQL> alter database open;
Database altered.

SQL> SELECT * FROM CJC.SIMPLE_ORDERS;
SELECT * FROM CJC.SIMPLE_ORDERS
                  *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/cjc/cjc01.dbf'


SQL> select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (5); 
select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (5)
                  *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/cjc/cjc01.dbf'


SQL> select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (6);
select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (6)
                  *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/cjc/cjc01.dbf'

SQL> select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (7);
select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (7)
                  *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/cjc/cjc01.dbf'


SQL> select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (8); 
select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (8)
                  *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/cjc/cjc01.dbf'


SQL> select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (9); 
select * from cjc.SIMPLE_ORDERS where DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) IN (9)
                  *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/cjc/cjc01.dbf'

确实无法直接查询CJC.SIMPLE_ORDERS表的任何数据了！！！
后面有时间测试下通过bbed或ODU等工具是否能挽回更多的数据，如有更好的方法，请留言，谢谢！

欢迎关注我的公众号《IT小Chen》