oracle非系统数据文件丢失恢复

x-i-x

  数据文件丢失,alert.log 提示

ORA-00376: file ORA-00376: file 8 cannot be read at this time

 

  数据字典 dba_data_files 提示MISSING00008 文件名信息:

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- -------------------
         8 OFFLINE OFFLINE FILE MISSING                                                               0

 

恢复步骤：

1. 拷贝备份文件到对应的数据目录下

SQL> alter database rename file '$ORACLE_HOME/dbs/MISSING00008' to '/$PATH/oradata/logtbs03.dbf';                  

Database altered.

2. 重启至mount状态，设置scn参数

SQL> shutdown immediate;

SQL> startup mount;
Database mounted.

SQL> alter system set "_allow_resetlogs_corruption"=TRUE SCOPE=SPFILE;  

System altered.

参数"_allow_resetlogs_corruption"=必须设置，否则恢复完成将无法打开数据库。因为控制文件的scn和数据文件的scn恢复完之后仍然不一致。

SQL> alter database open;

SQL> Alter session set events 'immediate trace name adjust_scn level 1';

3. 重启至mount状态，恢复数据文件

SQL> shutdown immediate;
SQL> startup mount;

SQL> recover until cancel;
Media recovery complete.
SQL> alter database datafile 8 online;

Database altered.

 

4. 用resetlog打开数据库

 SQL> alter database open resetlogs;

Database altered.

 

5. 备份数据库