重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
数据库中数据文件不一致如何恢复-创新互联
小编给大家分享一下数据库中数据文件不一致如何恢复，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
还原数据文件无法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: {
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.
以上是“数据库中数据文件不一致如何恢复”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注创新互联-成都网站建设公司行业资讯频道！
本文题目：数据库中数据文件不一致如何恢复-创新互联
网站地址：http://cqcxhl.com/article/ccjojd.html