如何在含有recover 狀態的數據文件環境中，做異機恢復？

背景：

? 我們在一些恢復測試案例中，會經常遇到一些奇怪的問題，其中有的是源端數據文件不規范而導致恢復過程出錯，比較常見的錯誤有：

1. 數據文件名稱重復（如：/oradata1/user01.dbf 和 /oradata2/user01.dbf），這種情況下，我們在做恢復測試時候，如果往同一塊盤中恢復數據文件，就會報錯，需要我們重新rename 重名的datafile。
2. 數據文件狀態為recover，非online，通常發生在客戶使用RAC環境，增加數據文件時候，錯誤的將datafile加到了本地磁盤，而發現錯誤后，又執行了offline datafile操作。

本文，主要測試，在數據庫中，含有recover 狀態的數據文件，如果做恢復測試？

[TOC]

1.創建測試表空間

SQL> CREATE TABLESPACE test DATAFILE  '/u01/app/oracle/oradata/TEST/test01.dbf' size 10M autoextend on EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL> alter tablespace test add datafile  '/u01/app/oracle/oradata/TEST/test02.dbf' size 10M;

Tablespace altered.

2.在測試表空間中創建表

SQL> create table sxc.test1 tablespace test as select * from dba_objects;

Table created.

SQL> select count(*) from sxc.test1 ;

  COUNT(*)
----------
     78918

3.模擬offline datafile

3.1 執行offline datafile

SQL> set line222
SQL> col name for a80
SQL> select file#,name,status  from v$datafile;

     FILE# NAME                                                                             STATUS
---------- -------------------------------------------------------------------------------- --------------
         1 /u01/app/oracle/oradata/TEST/system01.dbf                                        SYSTEM
         2 /u01/app/oracle/oradata/TEST/sysaux01.dbf                                        ONLINE
         3 /u01/app/oracle/oradata/TEST/undotbs01.dbf                                       ONLINE
         4 /u01/app/oracle/oradata/TEST/users01.dbf                                         ONLINE
         5 /u01/app/oracle/oradata/TEST/example01.dbf                                       ONLINE
         6 /u01/app/oracle/oradata/TEST/test01.dbf                                          ONLINE
         7 /u01/app/oracle/oradata/TEST/test02.dbf                                          ONLINE


SQL> alter database datafile 7 offline;

Database altered.

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

     FILE# NAME                                                                             STATUS
---------- -------------------------------------------------------------------------------- --------------
         1 /u01/app/oracle/oradata/TEST/system01.dbf                                        SYSTEM
         2 /u01/app/oracle/oradata/TEST/sysaux01.dbf                                        ONLINE
         3 /u01/app/oracle/oradata/TEST/undotbs01.dbf                                       ONLINE
         4 /u01/app/oracle/oradata/TEST/users01.dbf                                         ONLINE
         5 /u01/app/oracle/oradata/TEST/example01.dbf                                       ONLINE
         6 /u01/app/oracle/oradata/TEST/test01.dbf                                          ONLINE
         7 /u01/app/oracle/oradata/TEST/test02.dbf                                          RECOVER

7 rows selected.

?此時，我們可以看到datafile 7 的狀態已經變為recover。

3.2 再次查詢，報錯

SQL> select count(*) from sxc.test1 ;
select count(*) from sxc.test1
                         *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/u01/app/oracle/oradata/TEST/test02.dbf'

3.3 繼續創建測試表

SQL> create table sxc.test2 tablespace test as select * from dba_objects;

Table created.


SQL> select count(*) from sxc.test2 ;

  COUNT(*)
----------
     78919

3.4 手動刪除歸檔日志

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

-- 手動刪除歸檔日志
[oracle@ora11204 ~]$ rm -rf 1_*

3.5 繼續插入數據，切換日志

SQL> insert into  sxc.test2  select * from dba_objects;

78919 rows created.

SQL> select count(*) from sxc.test2 ;

  COUNT(*)
----------
    157838

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

結論：雖然datafile 7 的狀態變成了recover ，但是受影響的只是原來的表test1，而新表test2不受影響，可以繼續寫入。 其實也就是說，如果offline的datafile中不含有對象，那么對數據的使用，沒有影響。?

3.6 嘗試recover datafile 7

SQL> recover datafile 7;
ORA-00279: change 4592370 generated at 07/09/2022 12:38:37 needed for thread 1
ORA-00289: suggestion : /home/oracle/1_37_1104664055.dbf
ORA-00280: change 4592370 for thread 1 is in sequence #37

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log '/home/oracle/1_37_1104664055.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log '/home/oracle/1_37_1104664055.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

結論：這個時候，如果我們想去recover datafile 7，恢復正常的online狀態，已經不行了，因為歸檔日志都已經被手動刪除了。

所以正確的做法應該是： 在3.1手動執行offline datafile之后，立即做recover datafile，這樣不管到什么時候，都可以online datafile，即使歸檔日志已經被刪除。

此時我們的數據庫中就包含了一個recover 狀態的數據文件，那么我們接下來看，如何備份恢復？?

4. 模擬備份恢復

4.1 rman備份

-- 備份前，先刪掉expired archivelog
[oracle@ora11204 backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 9 12:47:26 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LHR11G (DBID=2007947551)

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/home/oracle/1_18_1104664055.dbf RECID=30 STAMP=1107010872
deleted archived log
archived log file name=/home/oracle/1_19_1104664055.dbf RECID=31 STAMP=1107084082
deleted archived log
archived log file name=/home/oracle/1_20_1104664055.dbf RECID=32 STAMP=1107084317
deleted archived log
archived log file name=/home/oracle/1_21_1104664055.dbf RECID=33 STAMP=1107248395
deleted archived log
archived log file name=/home/oracle/1_23_1104664055.dbf RECID=35 STAMP=1107248458
deleted archived log
archived log file name=/home/oracle/1_24_1104664055.dbf RECID=36 STAMP=1107248477
deleted archived log
archived log file name=/home/oracle/1_25_1104664055.dbf RECID=37 STAMP=1107248494
deleted archived log
archived log file name=/home/oracle/1_26_1104664055.dbf RECID=38 STAMP=1107248708
deleted archived log
archived log file name=/home/oracle/1_27_1104664055.dbf RECID=39 STAMP=1107249210
deleted archived log
archived log file name=/home/oracle/1_28_1104664055.dbf RECID=40 STAMP=1107249384
deleted archived log
archived log file name=/home/oracle/1_29_1104664055.dbf RECID=41 STAMP=1107594979
deleted archived log
archived log file name=/home/oracle/1_30_1104664055.dbf RECID=42 STAMP=1107965313
deleted archived log
archived log file name=/home/oracle/1_31_1104664055.dbf RECID=43 STAMP=1109430200
deleted archived log
archived log file name=/home/oracle/1_32_1104664055.dbf RECID=44 STAMP=1109400986
deleted archived log
archived log file name=/home/oracle/1_33_1104664055.dbf RECID=45 STAMP=1109514747
deleted archived log
archived log file name=/home/oracle/1_34_1104664055.dbf RECID=46 STAMP=1109515327
deleted archived log
archived log file name=/home/oracle/1_35_1104664055.dbf RECID=47 STAMP=1109593810
deleted archived log
archived log file name=/home/oracle/1_36_1104664055.dbf RECID=48 STAMP=1109593862
deleted archived log
archived log file name=/home/oracle/1_37_1104664055.dbf RECID=49 STAMP=1109594665
deleted archived log
archived log file name=/home/oracle/1_38_1104664055.dbf RECID=50 STAMP=1109594666
Deleted 20 EXPIRED objects

-- 正式備份
RMAN> run{ 
2> allocate channel c1 device type disk;
3> backup database filesperset 3 format '/home/oracle/backup/db_%d_%T_%U'; 
4> backup archivelog all format '/home/oracle/backup/arc_%t_%s';  
5> backup current controlfile format '/home/oracle/backup/cntrl_%s_%p_%s'; 
6> release channel c1; 
7> } 

released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=33 device type=DISK

Starting backup at 2022-07-09 12:48:34
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/TEST/system01.dbf
channel c1: starting piece 1 at 2022-07-09 12:48:34
channel c1: finished piece 1 at 2022-07-09 12:48:41
piece handle=/home/oracle/backup/db_LHR11G_20220709_4k1264p2_1_1 tag=TAG20220709T124834 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/TEST/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/TEST/example01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/TEST/test02.dbf
channel c1: starting piece 1 at 2022-07-09 12:48:41
channel c1: finished piece 1 at 2022-07-09 12:48:44
piece handle=/home/oracle/backup/db_LHR11G_20220709_4l1264p9_1_1 tag=TAG20220709T124834 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/TEST/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/TEST/users01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/TEST/test01.dbf
channel c1: starting piece 1 at 2022-07-09 12:48:44
channel c1: finished piece 1 at 2022-07-09 12:48:47
piece handle=/home/oracle/backup/db_LHR11G_20220709_4m1264pc_1_1 tag=TAG20220709T124834 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 2022-07-09 12:48:48
channel c1: finished piece 1 at 2022-07-09 12:48:49
piece handle=/home/oracle/backup/db_LHR11G_20220709_4n1264pf_1_1 tag=TAG20220709T124834 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-07-09 12:48:49

Starting backup at 2022-07-09 12:48:50
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=39 RECID=51 STAMP=1109594808
input archived log thread=1 sequence=40 RECID=52 STAMP=1109594809
input archived log thread=1 sequence=41 RECID=53 STAMP=1109594930
channel c1: starting piece 1 at 2022-07-09 12:48:50
channel c1: finished piece 1 at 2022-07-09 12:48:51
piece handle=/home/oracle/backup/arc_1109594930_152 tag=TAG20220709T124850 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-07-09 12:48:51

Starting backup at 2022-07-09 12:48:51
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 2022-07-09 12:48:52
channel c1: finished piece 1 at 2022-07-09 12:48:53
piece handle=/home/oracle/backup/cntrl_153_1_153 tag=TAG20220709T124851 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-07-09 12:48:53

released channel: c1

4.2. 恢復DB

?

? ? ?利用備份做異機恢復（拷貝備份、pfile文件、密碼文件等過程省略…）

4.2.1 啟動數據庫至nomount狀態

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             331350920 bytes
Database Buffers          729808896 bytes
Redo Buffers                5517312 bytes

4.2.2 恢復控制文件

[oracle@ora11204 backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 9 12:51:00 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: LHR11G (not mounted)

RMAN> restore controlfile from '/home/oracle/backup/cntrl_153_1_153';

Starting restore at 2022-07-09 12:51:01
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TEST/control01.ctl
Finished restore at 2022-07-09 12:51:03

4.2.3 mount數據庫

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

4.2.4 restore database

RMAN> restore database;

Starting restore at 2022-07-09 12:51:23
Starting implicit crosscheck backup at 2022-07-09 12:51:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 22 objects
Finished implicit crosscheck backup at 2022-07-09 12:51:24

Starting implicit crosscheck copy at 2022-07-09 12:51:24
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2022-07-09 12:51:24

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/TEST/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db_LHR11G_20220709_4k1264p2_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/db_LHR11G_20220709_4k1264p2_1_1 tag=TAG20220709T124834
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/TEST/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/TEST/example01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/TEST/test02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db_LHR11G_20220709_4l1264p9_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/db_LHR11G_20220709_4l1264p9_1_1 tag=TAG20220709T124834
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/TEST/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/TEST/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/TEST/test01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db_LHR11G_20220709_4m1264pc_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/db_LHR11G_20220709_4m1264pc_1_1 tag=TAG20220709T124834
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 2022-07-09 12:51:33

4.2.5 在RMAN中執行recover database

RMAN> recover database;

Starting recover at 2022-07-09 12:51:49
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 39 is already on disk as file /home/oracle/1_39_1104664055.dbf
archived log for thread 1 with sequence 40 is already on disk as file /home/oracle/1_40_1104664055.dbf
archived log for thread 1 with sequence 41 is already on disk as file /home/oracle/1_41_1104664055.dbf
Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup 
ORA-01110: data file 1: '/u01/app/oracle/oradata/TEST/system01.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/09/2022 12:51:49
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 38 and starting SCN of 4593111 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 37 and starting SCN of 4591558 found to restore

結論：我們直接用傳統的方式，在rman中執行recover database，會報錯，需要37、38 兩個已經被刪除的archive log，無法完成recover。

4.2.6 在SQLPLUS中執行recover database

-- 先恢復歸檔全備中的歸檔日志
RMAN> restore archivelog from logseq 39 thread 1;

Starting restore at 2022-07-09 13:02:02
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=39
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=40
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=41
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/arc_1109594930_152
channel ORA_DISK_1: piece handle=/home/oracle/backup/arc_1109594930_152 tag=TAG20220709T124850
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2022-07-09 13:02:03

-- 執行 recover database操作
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4592370 generated at 07/09/2022 12:38:37 needed for thread 1
ORA-00289: suggestion : /home/oracle/1_37_1104664055.dbf
ORA-00280: change 4592370 for thread 1 is in sequence #37

結論：我們在sqlplus中執行recover database，也會報錯，需要37、38 兩個已經被刪除的archive log，無法完成recover。

4.3. 解決辦法

4.3.1 查詢數據文件狀態

SQL> set line222
SQL> col name for a60
SQL> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status  FROM v$datafile_header a;

     FILE# NAME                                                      RECOVE CHECKPOINT_CHANGE# STATUS
---------- --------------------------------------------------------- ------ ------------------ -------------
         1 /u01/app/oracle/oradata/TEST/system01.dbf                                   4593921 ONLINE
         2 /u01/app/oracle/oradata/TEST/sysaux01.dbf                                   4593926 ONLINE
         3 /u01/app/oracle/oradata/TEST/undotbs01.dbf                                  4593924 ONLINE
         4 /u01/app/oracle/oradata/TEST/users01.dbf                                    4593926 ONLINE
         5 /u01/app/oracle/oradata/TEST/example01.dbf                                  4593924 ONLINE
         6 /u01/app/oracle/oradata/TEST/test01.dbf                                     4593926 ONLINE
         7 /u01/app/oracle/oradata/TEST/test02.dbf                                     4592370 ONLINE

7 rows selected.

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

     FILE# NAME                                                      STATUS
---------- --------------------------------------------------------- --------------
         1 /u01/app/oracle/oradata/TEST/system01.dbf                 SYSTEM
         2 /u01/app/oracle/oradata/TEST/sysaux01.dbf                 ONLINE
         3 /u01/app/oracle/oradata/TEST/undotbs01.dbf                ONLINE
         4 /u01/app/oracle/oradata/TEST/users01.dbf                  ONLINE
         5 /u01/app/oracle/oradata/TEST/example01.dbf                ONLINE
         6 /u01/app/oracle/oradata/TEST/test01.dbf                   ONLINE
         7 /u01/app/oracle/oradata/TEST/test02.dbf                   RECOVER

7 rows selected.

我們可以看到在v$datafile_header視圖中，datafile 7 狀態仍然為online,在v$datafile中狀態為recover。

4.3.2 執行offline datafile 操作

SQL> alter database datafile 7 offline drop;

Database altered.

SQL> set line222
SQL> col name for a60
SQL> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status  FROM v$datafile_header a;

     FILE# NAME                                                 RECOVE CHECKPOINT_CHANGE# STATUS
---------- ---------------------------------------------------- ------ ------------------ --------------
         1 /u01/app/oracle/oradata/TEST/system01.dbf                              4593921 ONLINE
         2 /u01/app/oracle/oradata/TEST/sysaux01.dbf                              4593926 ONLINE
         3 /u01/app/oracle/oradata/TEST/undotbs01.dbf                             4593924 ONLINE
         4 /u01/app/oracle/oradata/TEST/users01.dbf                               4593926 ONLINE
         5 /u01/app/oracle/oradata/TEST/example01.dbf                             4593924 ONLINE
         6 /u01/app/oracle/oradata/TEST/test01.dbf                                4593926 ONLINE
         7 /u01/app/oracle/oradata/TEST/test02.dbf                                4592370 OFFLINE

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4593921 generated at 07/09/2022 12:48:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/1_41_1104664055.dbf
ORA-00280: change 4593921 for thread 1 is in sequence #41


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 4593946 generated at 07/09/2022 12:48:50 needed for thread 1
ORA-00289: suggestion : /home/oracle/1_42_1104664055.dbf
ORA-00280: change 4593946 for thread 1 is in sequence #42
ORA-00278: log file '/home/oracle/1_41_1104664055.dbf' no longer needed for
this recovery


ORA-00308: cannot open archived log '/home/oracle/1_42_1104664055.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

結論：recover不在需要37、38 兩個已經被刪除的archive log，正常完成recover 操作。

4.3.3 查看redo logfile & tempfile

-- 確保redo logfile & tempfile路徑存在或者rename file
SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/TEST/redo01.log
/u01/app/oracle/oradata/TEST/redo02.log
/u01/app/oracle/oradata/TEST/redo03.log

SQL> select name from v$tempfile;

NAME
------------------------------------------------------------
/u01/app/oracle/oradata/TEST/temp01.dbf

4.3.4 open數據庫

SQL> alter database open resetlogs;

Database altered.

4.4 驗證數據

SQL> select count(*) from sxc.test2 ;

  COUNT(*)
----------
    157838

SQL> select count(*) from sxc.test1 ;
select count(*) from sxc.test1
                         *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/u01/app/oracle/oradata/TEST/test02.dbf'

4.5 嘗試刪除datafile 7

SQL> alter tablespace test drop datafile '/u01/app/oracle/oradata/TEST/test02.dbf';
alter tablespace test drop datafile '/u01/app/oracle/oradata/TEST/test02.dbf'
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace

結論：datafile 7 刪除報錯，如果一定要處理的話，可以通過bbed的方式去修復后刪除。