環境說明:
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》
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
