場景
非歸檔模式，數據文件丟失或損壞，有備份，損壞前後日誌不存在
1 冷備數據庫，查看損壞前表的狀態，並插入數據
[oracle@jian datafile]$ cp jian.dbf jian.dbf.bak
SQL> select * from jian;
ID
----------
1
2
3
SQL> insert into jian values(4);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint
2 ;
System altered.
2 沖掉日誌，模擬日誌丟失
SQL> alter system switch logfile;
System altered.
SQL>
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
3 損壞數據文件，啟動數據庫報錯
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 285212672 bytes
Redo Buffers 6586368 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/JIAN/datafile/jian.dbf'
4 將損壞文件備份拷貝回來，開啟數據庫，修復時提示需要歸檔日誌
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/JIAN/datafile/jian.dbf'
SQL> recover datafile 6;
ORA-00279: change 1120295 generated at 10/26/2015 20:17:50 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/JIAN/archivelog/2015_10_26/o1_mf_1_6_%u_.arc
ORA-00280: change 1120295 for thread 1 is in sequence #6
Specify log: {
cancel
Media recovery cancelled.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/JIAN/datafile/jian.dbf'
5 由於未開啟歸檔所以recover database until cancel，之後提示需要resetlogs打開數據庫
SQL> recover database until cancel;
ORA-00279: change 1120295 generated at 10/26/2015 20:17:50 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/JIAN/archivelog/2015_10_26/o1_mf_1_6_%u_.arc
ORA-00280: change 1120295 for thread 1 is in sequence #6
Specify log: {
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/JIAN/archivelog/2015_10_26/o1_mf_1_6_%u_.arc
'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'/u01/app/oracle/oradata/JIAN/datafile/o1_mf_system_brltb74o_.dbf'
6 此處ORA-01152: file 1 was not restored from a sufficiently old backup需要通過設置隱藏參數_allow_resetlogs_corruption之後，使數據庫在Open過程中，跳過某些一致性檢查，之後打開數據庫，但是會報錯退出重新啟動
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1120302], [0],
[1121128], [4194432], [], [], [], [], [], []
Process ID: 69047
Session ID: 125 Serial number: 5
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@jian database]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 26 20:21:10 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 285212672 bytes
Redo Buffers 6586368 bytes
Database mounted.
Database opened.
7 數據庫啟動查看數據已恢復到備份時的狀態
SQL> select * from jian;
ID
----------
1
2
3
閱讀更多 代號香蕉 的文章
