非归档模式数据恢复 oracle11g r2

转自：https://www.cnblogs.com/david-zhang-index/archive/2012/04/03/2431490.html

1.创建实验表空间数据文件

SQL> conn /as sysdba

Connected.

SQL> create tablespace data01 datafile '/u01/app/oracle/oradata/oracle/data01.dbf'size 10m;

Tablespace created.

SQL> create table david_01 tablespace data01 as select * from dba_objects;

Table created.

2.数据文件offline

 

SQL> alter database datafile '/u01/app/oracle/oradata/oracle/data01.dbf' offline;

Database altered.

SQL> alter system switch logfile;

System altered.

 3.数据文件online

SQL> alter database datafile '/u01/app/oracle/oradata/oracle/data01.dbf' online;

alter database datafile '/u01/app/oracle/oradata/oracle/data01.dbf' online

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/u01/app/oracle/oradata/oracle/data01.dbf'

 4.数据文件进行介质恢复

SQL> recover datafile 5;

ORA-00279: change 1179122 generated at 04/03/2012 23:35:49 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORACLE/archivelog/2012_04_03/o1_mf_1_25_7qp6 nqxt_.arc

ORA-00280: change 1179122 for thread 1 is in sequence #25

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto（自动搜索归档日志，本实验无归档日志）

ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/ORACLE/archivelog/2012_04_03/o1_mf_1_25_7qp 6nqxt_.arc'

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 '/u01/app/oracle/flash_recovery_area/ORACLE/archivelog/2012_04_03/o1_mf_1_25_7qp 6nqxt_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

5.加上until cancle参数再次尝试恢复，恢复数据原则要进程不断尝试

SYS SQL> recover datafile 5 until cancel;

ORA-00274: illegal recovery option UNTIL

 

SQL> alter database datafile 5 online;

alter database datafile 5 online

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/u01/app/oracle/oradata/oracle/data01.dbf'

6.将库加载到mount状态，再次尝试介质恢复

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  534462464 bytes

Fixed Size                  2215064 bytes

Variable Size             406848360 bytes

Database Buffers          121634816 bytes

Redo Buffers                3764224 bytes

Database mounted.

 恢复成功

SQL> recover database until cancel;
 
Media recovery complete.

7.尝试打开数据库

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

SQL> alter database open RESETLOGS;

Database altered.

将数据文件online

SQL> alter database datafile 5 online;

alter database datafile 5 online

*

ERROR at line 1:

ORA-01190: control file or data file 5 is from before the last RESETLOGS

ORA-01110: data file 5: '/u01/app/oracle/oradata/oracle/data01.dbf'

报错信息提示：控制文件或文件5不在同一个resetlogs版本，再次尝试恢复

SQL> recover datafile 5;

ORA-00283: recovery session canceled due to errors

ORA-19909: datafile 5 belongs to an orphan incarnation

ORA-01110: data file 5: '/u01/app/oracle/oradata/oracle/data01.dbf'

还是失败

8.修改参数，尝试手动调整SCN,手动调整SCN以后必须用resetlogs模式打开数据库，所以需要设置参数"_allow_resetlogs_corruption"=true

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  534462464 bytes

Fixed Size                  2215064 bytes

Variable Size             406848360 bytes

Database Buffers          121634816 bytes

Redo Buffers                3764224 bytes

Database mounted.

 9.再次介质恢复

1 SQL> recover database until cancel;
2 
3 Media recovery complete.

备注：因为用RESETLOGS来打开数据库，所以上面这一步很又做了一遍介质恢复（虽然不做这个操作也能打开数据库），但个人觉得是必须要的，然后再将数据文件状态联机

SQL> alter database datafile 5 online;

Database altered.
SQL> alter database open RESETLOGS;
 
Database altered.

10.修改参数，将该参数修改回原来状态

1 SQL> alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';
2 
3 System altered.

 11.查看数据文件

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;

再查看下恢复视图

select * from v$recover_file

无任何内容显示，到此本实验结束

总结：本实验讨论在非归档情况下数据恢复，实验中需注重oracle提示的错误信息，针对提示信息采取措施同时还需不断尝试打开数据库。