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

1
2
3
4
5
6
 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
2
3
4
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
2
3
4
5
6
7
 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 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
2
3
4
5
6
7
8
9
10
11
 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
2
3
4
5
6
7
8
9
10
11
12
 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
2
1 SQL> recover database until cancel; 2 
3 Media recovery complete.

7.尝试打开数据库

1
2
3
4
5
6
7
8
9
10
11
12
 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
2
3
4
5
6
7
 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
2
3
4
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 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
2
1 SQL> recover database until cancel; 2 
3 Media recovery complete.

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

1
2
3
4
1 SQL> alter database datafile 5 online; 2 
3 Database altered.
1 SQL> alter database open RESETLOGS; 2 
3 Database altered.

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

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

11.查看数据文件

1
SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;
查看数据文件状态

再查看下恢复视图

1
1 select * from v$recover_file

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

转载： 作者：david_zhang@sh 链接：http://www.cnblogs.com/david-zhang-index/archive/2012/04/03/2431490.html?updated=1