数据文件OFFLINE的3种情况

最新推荐文章于 2024-06-15 21:29:52 发布

congyuji4066

最新推荐文章于 2024-06-15 21:29:52 发布

阅读量816

点赞数

前言：

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.