【原】非归档模式数据恢复 oracle11g r2
作者：david_zhang@sh 【转载时请以超链接形式标明文章】
链接：http://www.cnblogs.com/david-zhang-index/archive/2012/04/03/2431490.html?updated=1
1.创建实验表空间数据文件
1 SQL> conn /as sysdba 2 3 Connected. 4 5 SQL> create tablespace data01 datafile '/u01/app/oracle/oradata/oracle/data01.dbf'size 10m; 6 7 Tablespace created. 8 9 SQL> create table david_01 tablespace data01 as select * from dba_objects; 10 11 Table created.
2.数据文件offline
1 SQL> alter database datafile '/u01/app/oracle/oradata/oracle/data01.dbf' offline; 2 3 Database altered. 4 5 SQL> alter system switch logfile; 6 7 System altered.
3.数据文件online
1 SQL> alter database datafile '/u01/app/oracle/oradata/oracle/data01.dbf' online; 2 3 alter database datafile '/u01/app/oracle/oradata/oracle/data01.dbf' online 4 5 * 6 7 ERROR at line 1: 8 9 ORA-01113: file 5 needs media recovery 10 11 ORA-01110: data file 5: '/u01/app/oracle/oradata/oracle/data01.dbf'
4.数据文件进行介质恢复
1 SQL> recover datafile 5; 2 3 ORA-00279: change 1179122 generated at 04/03/2012 23:35:49 needed for thread 1 4 5 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORACLE/archivelog/2012_04_03/o1_mf_1_25_7qp6 nqxt_.arc 6 7 ORA-00280: change 1179122 for thread 1 is in sequence #25 8 9 10 11 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 12 13 auto（自动搜索归档日志，本实验无归档日志） 14 15 ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/ORACLE/archivelog/2012_04_03/o1_mf_1_25_7qp 6nqxt_.arc' 16 17 ORA-27037: unable to obtain file status 18 19 Linux-x86_64 Error: 2: No such file or directory 20 21 Additional information: 3 22 23 24 25 ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/ORACLE/archivelog/2012_04_03/o1_mf_1_25_7qp 6nqxt_.arc' 26 27 ORA-27037: unable to obtain file status 28 29 Linux-x86_64 Error: 2: No such file or directory 30 31 Additional information: 3
5.加上until cancle参数再次尝试恢复，恢复数据原则要进程不断尝试
1 SYS SQL> recover datafile 5 until cancel; 2 3 ORA-00274: illegal recovery option UNTIL 4 5 6 7 SQL> alter database datafile 5 online; 8 9 alter database datafile 5 online 10 11 * 12 13 ERROR at line 1: 14 15 ORA-01113: file 5 needs media recovery 16 17 ORA-01110: data file 5: '/u01/app/oracle/oradata/oracle/data01.dbf'
6.将库加载到mount状态，再次尝试介质恢复
1 SQL> shutdown immediate; 2 3 Database closed. 4 5 Database dismounted. 6 7 ORACLE instance shut down. 8 9 SQL> startup mount; 10 11 ORACLE instance started. 12 13 Total System Global Area 534462464 bytes 14 15 Fixed Size 2215064 bytes 16 17 Variable Size 406848360 bytes 18 19 Database Buffers 121634816 bytes 20 21 Redo Buffers 3764224 bytes 22 23 Database mounted.
恢复成功
1 SQL> recover database until cancel; 2 3 Media recovery complete.
7.尝试打开数据库
1 SQL> alter database open; 2 3 alter database open 4 5 * 6 7 ERROR at line 1: 8 9 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 10 11 12 13 SQL> alter database open RESETLOGS; 14 15 Database altered.
将数据文件online
1 SQL> alter database datafile 5 online; 2 3 alter database datafile 5 online 4 5 * 6 7 ERROR at line 1: 8 9 ORA-01190: control file or data file 5 is from before the last RESETLOGS 10 11 ORA-01110: data file 5: '/u01/app/oracle/oradata/oracle/data01.dbf'
报错信息提示：控制文件或文件5不在同一个resetlogs版本，再次尝试恢复
1 SQL> recover datafile 5; 2 3 ORA-00283: recovery session canceled due to errors 4 5 ORA-19909: datafile 5 belongs to an orphan incarnation 6 7 ORA-01110: data file 5: '/u01/app/oracle/oradata/oracle/data01.dbf'
还是失败
8.修改参数，尝试手动调整SCN,手动调整SCN以后必须用resetlogs模式打开数据库，所以需要设置参数"_allow_resetlogs_corruption"=true
1 SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; 2 3 System altered. 4 5 6 7 SQL> shutdown immediate; 8 9 Database closed. 10 11 Database dismounted. 12 13 ORACLE instance shut down. 14 15 SQL> startup mount; 16 17 ORACLE instance started. 18 19 Total System Global Area 534462464 bytes 20 21 Fixed Size 2215064 bytes 22 23 Variable Size 406848360 bytes 24 25 Database Buffers 121634816 bytes 26 27 Redo Buffers 3764224 bytes 28 29 Database mounted.
9.再次介质恢复
1 SQL> recover database until cancel; 2 3 Media recovery complete.
备注：因为用RESETLOGS来打开数据库，所以上面这一步很又做了一遍介质恢复（虽然不做这个操作也能打开数据库），但个人觉得是必须要的，然后再将数据文件状态联机
1 SQL> alter database datafile 5 online; 2 3 Database altered.
1 SQL> alter database open RESETLOGS; 2 3 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;
再查看下恢复视图
1 select * from v$recover_file
无任何内容显示，到此本实验结束
总结：本实验讨论在非归档情况下数据恢复，实验中需注重oracle提示的错误信息，针对提示信息采取措施同时还需不断尝试打开数据库。
