共计 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 行业资讯频道！
