共計 5806 個字符，預計需要花費 15 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下數據庫中數據文件不一致如何恢復，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
還原數據文件無法 recover 成功的場景：
1、數據庫時在非歸檔的模式下運行：
SQL archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/archivelog
Oldest online log sequence 1
Current log sequence 2
2、移動其中的一個數據文件，然后將其 offline，具體操作如下：
col file_name for a50;
select file_name,ONLINE_STATUS from dba_data_files;
SQL col file_name for a50;
SQL select file_name,ONLINE_STATUS from dba_data_files;
FILE_NAME ONLINE_
————————————————– ——-
/u01/app/oradata/orclpri/users01.dbf ONLINE
/u01/app/oradata/orclpri/undotbs01.dbf ONLINE
/u01/app/oradata/orclpri/sysaux01.dbf ONLINE
/u01/app/oradata/orclpri/system01.dbf SYSTEM
/u01/app/oradata/orclpri/datafileep_scs_idx.dbf ONLINE
/u01/app/oradata/orclpri/bpep_scs.dbf ONLINE
/u01/bpep_caweb.dbf ONLINE
/u01/app/oradata/orclpri/bpep_caweb_idx.dbf ONLINE
/u01/app/oradata/orclpri/readonly01.dbf ONLINE
/u01/app/oradata/orclpri/readwrite01.dbf ONLINE
/u01/test.dbf ONLINE
FILE_NAME ONLINE_
————————————————– ——-
/u01/app/oradata/orclpri/test2.dbf ONLINE
/u01/app/oradata/orclpri/test3.dbf ONLINE
我們還是以 test 數據文件來做實驗。
先做 一個 MV 操作：
SQL !mv /u01/test.dbf /u02/
然后將 MV 過之后的數據文件 offline
SQL alter database datafile /u01/test.dbf offline drop;
Database altered.
切換日志文件：
SQL alter system switch logfile;
System altered.
多切換幾次日志文件。
關閉數據庫：
SQL shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
將數據庫啟動到 mount 狀態：
SQL startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 566234704 bytes
Database Buffers 276824064 bytes
Redo Buffers 5132288 bytes
Database mounted.
將 MV 過的數據文件做 rename 操作：
SQL alter database rename file /u01/test.dbf to /u02/test.dbf
Database altered.
將數據庫打開：
SQL alter database open;
查看各個數據文件的狀態：
SQL select file_name,ONLINE_STATUS from dba_data_files;
FILE_NAME ONLINE_
————————————————– ——-
/u01/app/oradata/orclpri/users01.dbf ONLINE
/u01/app/oradata/orclpri/undotbs01.dbf ONLINE
/u01/app/oradata/orclpri/sysaux01.dbf ONLINE
/u01/app/oradata/orclpri/system01.dbf SYSTEM
/u01/app/oradata/orclpri/datafileep_scs_idx.dbf ONLINE
/u01/app/oradata/orclpri/bpep_scs.dbf ONLINE
/u01/bpep_caweb.dbf ONLINE
/u01/app/oradata/orclpri/bpep_caweb_idx.dbf ONLINE
/u01/app/oradata/orclpri/readonly01.dbf ONLINE
/u01/app/oradata/orclpri/readwrite01.dbf ONLINE
/u02/test.dbf RECOVER
FILE_NAME ONLINE_
————————————————– ——-
/u01/app/oradata/orclpri/test2.dbf ONLINE
/u01/app/oradata/orclpri/test3.dbf ONLINE
將數據文件做 online 操作，會產生如下操作：
SQL alter database datafile /u02/test.dbf online;
alter database datafile /u02/test.dbf online
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: /u02/test.dbf
嘗試 做 recover 數據文件操作：
SQL recover datafile /u02/test.dbf
ORA-00279: change 2684134 generated at 12/04/2016 21:49:15 needed for thread 1
ORA-00289: suggestion : /u01/archivelog/1_2_929742548.dbf
ORA-00280: change 2684134 for thread 1 is in sequence #2
Specify log: {RET =suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log /u01/archivelog/1_2_929742548.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log /u01/archivelog/1_2_929742548.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
發現 做 recover 操作的時候 報上面的錯誤。
這個時候 想要正常的將數據文件 online，可能就需要放棄數據的一致性，需要用到 _allow_resetlogs_corruption 參數。
將 _allow_resetlogs_corruption 參數設置為 true，然后可以將數據文件 online
修改這個參數：
SQL alter system set _allow_resetlogs_corruption =true scope=spfile;
SQL show parameter allow
NAME TYPE VALUE
———————————— ———– ——————————
_allow_resetlogs_corruption boolean TRUE
將數據庫重啟到 mount 狀態，然后使用 RMAN 進入，查看 incarnation
RMAN list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 ORCLPRI 1094561153 PARENT 1 13-AUG-09
2 2 ORCLPRI 1094561153 PARENT 754488 13-JUL-16
3 3 ORCLPRI 1094561153 PARENT 2663124 04-DEC-16
4 4 ORCLPRI 1094561153 PARENT 2683818 04-DEC-16
5 5 ORCLPRI 1094561153 CURRENT 2684130 04-DEC-16
我們將數據庫 reset 到上個還原點：
RMAN reset database to Incarnation 4;
然后 重新將庫啟動到 mount 狀態：
將數據文件 online：
alter database datafile /u02/test.dbf online;
這個時候不能直接打開數據庫：
SQL alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 11 is from before the last RESETLOGS
ORA-01110: data file 11: /u02/test.dbf
SQL alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
我們可以 recover database：
使用下面的兩個命令：
SQL recover database until cancel;
SQL recover database using backup controlfile until cancel;
然后可以打開數據庫：
SQL alter database open resetlogs;
Database altered.
查看數據文件均是 online 狀態：
SQL select file_name,ONLINE_STATUS from dba_data_files;
FILE_NAME ONLINE_
————————————————– ——-
/u01/app/oradata/orclpri/users01.dbf ONLINE
/u01/app/oradata/orclpri/undotbs01.dbf ONLINE
/u01/app/oradata/orclpri/sysaux01.dbf ONLINE
/u01/app/oradata/orclpri/system01.dbf SYSTEM
/u01/app/oradata/orclpri/datafileep_scs_idx.dbf ONLINE
/u01/app/oradata/orclpri/bpep_scs.dbf ONLINE
/u01/bpep_caweb.dbf ONLINE
/u01/app/oradata/orclpri/bpep_caweb_idx.dbf ONLINE
/u01/app/oradata/orclpri/readonly01.dbf ONLINE
/u01/app/oradata/orclpri/readwrite01.dbf ONLINE
/u02/test.dbf ONLINE
FILE_NAME ONLINE_
————————————————– ——-
/u01/app/oradata/orclpri/test2.dbf ONLINE
/u01/app/oradata/orclpri/test3.dbf ONLINE
13 rows selected.
以上是“數據庫中數據文件不一致如何恢復”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！
