資料檔案OFFLINE的3種情況

前言：

1、測試前對資料庫進行一個完整備份

RMAN> backup database;

Starting backup at 28-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/oradata/dg1/system01.dbf
input datafile fno=00003 name=/oracle/oradata/dg1/sysaux01.dbf
input datafile fno=00005 name=/oracle/oradata/dg1/example01.dbf
input datafile fno=00006 name=/oracle/oradata/dg1/ws_app_data01.dbf
input datafile fno=00007 name=/oracle/oradata/dg1/ws_app_idx01.dbf
input datafile fno=00002 name=/oracle/oradata/dg1/undotbs01.dbf
input datafile fno=00008 name=/oracle/oradata/dg1/ws_app_sep01.dbf
input datafile fno=00009 name=/oracle/oradata/dg1/ws_app_oct01.dbf
input datafile fno=00010 name=/oracle/oradata/dg1/ws_app_nov01.dbf
input datafile fno=00011 name=/oracle/oradata/dg1/ws_app_dec01.dbf
input datafile fno=00012 name=/oracle/oradata/dg1/wdscr_part01.dbf
input datafile fno=00013 name=/oracle/oradata/dg1/wdscr_part02.dbf
input datafile fno=00004 name=/oracle/oradata/dg1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 28-MAR-09
channel ORA_DISK_1: finished piece 1 at 28-MAR-09
piece handle=/oracle/flash_recovery_area/DG1/backupset/2009_03_28/o1_mf_nnndf_TAG20090328T201934_4wxhnr1f_.bkp tag=TAG20090328T201934 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
Finished backup at 28-MAR-09

Starting Control File and SPFILE Autobackup at 28-MAR-09
piece handle=/oracle/flash_recovery_area/DG1/autobackup/2009_03_28/o1_mf_s_682719681_4wxhr320_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-MAR-09

2、歸檔模式下OFFLINE 資料檔案

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12

SQL> conn sys/oracle as sysdba
Connected.
SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' offline drop;

Database altered.

SQL> conn ws_app/ws_app
Connected.
SQL> select * from woodscrew;
select * from woodscrew
*
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/oracle/oradata/dg1/wdscr_part01.dbf'

歸檔模式下，日誌都存在，因此直接介質恢復，將資料檔案ONLINE就可以了。

SQL> conn sys/oracle as sysdba
Connected.
SQL> recover datafile 12;
Media recovery complete.
SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' online;

Database altered.

3、非歸檔聯機日誌存在情況下恢復

其實非歸檔在日誌組沒有被覆蓋之前和歸檔情況原理上是一樣的。

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Current log sequence           12

SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' offline drop;

Database altered.

SQL> conn ws_app/ws_app;
Connected.
SQL> select * from woodscrew;
select * from woodscrew
*
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/oracle/oradata/dg1/wdscr_part01.dbf'
SQL> conn sys/oracle as sysdba
Connected.
SQL> recover datafile 12;
Media recovery complete.
SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' online;

Database altered.

4、非歸檔聯機日誌不存在情況下恢復

很不幸，如果在沒有歸檔日誌，且聯機日誌被覆蓋的情況，要介質恢復了。

SQL> conn sys/oracle as sysdba
Connected.
SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' offline drop;

Database altered.