小编给大家分享一下数据库中数据文件不一致如何恢复,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!

还原数据文件无法recover 成功的场景:

1、数据库时在非归档的模式下运行:

SQL>archiveloglist

DatabaselogmodeNoArchiveMode

AutomaticarchivalDisabled

Archivedestination/u01/archivelog

Oldestonlinelogsequence1

Currentlogsequence2

2、移动其中的一个数据文件,然后将其 offline,具体操作如下:

colfile_namefora50;

selectfile_name,ONLINE_STATUSfromdba_data_files;

SQL>colfile_namefora50;

SQL>selectfile_name,ONLINE_STATUSfromdba_data_files;

FILE_NAMEONLINE_

---------------------------------------------------------

/u01/app/oradata/orclpri/users01.dbfONLINE

/u01/app/oradata/orclpri/undotbs01.dbfONLINE

/u01/app/oradata/orclpri/sysaux01.dbfONLINE

/u01/app/oradata/orclpri/system01.dbfSYSTEM

/u01/app/oradata/orclpri/datafileep_scs_idx.dbfONLINE

/u01/app/oradata/orclpri/bpep_scs.dbfONLINE

/u01/bpep_caweb.dbfONLINE

/u01/app/oradata/orclpri/bpep_caweb_idx.dbfONLINE

/u01/app/oradata/orclpri/readonly01.dbfONLINE

/u01/app/oradata/orclpri/readwrite01.dbfONLINE

/u01/test.dbfONLINE

FILE_NAMEONLINE_

---------------------------------------------------------

/u01/app/oradata/orclpri/test2.dbfONLINE

/u01/app/oradata/orclpri/test3.dbfONLINE

我们还是以 test数据文件来做实验。

先做 一个MV操作:

SQL>!mv/u01/test.dbf/u02/

然后将MV过之后的数据文件 offline

SQL>alterdatabasedatafile'/u01/test.dbf'offlinedrop;

Databasealtered.

切换日志文件:

SQL>altersystemswitchlogfile;

Systemaltered.

多切换几次日志文件。

关闭数据库:

SQL>shutdownimmediate;

Databaseclosed.

Databasedismounted.

ORACLEinstanceshutdown.

将数据库启动到mount 状态:

SQL>startupmount;

ORACLEinstancestarted.

TotalSystemGlobalArea849530880bytes

FixedSize1339824bytes

VariableSize566234704bytes

DatabaseBuffers276824064bytes

RedoBuffers5132288bytes

Databasemounted.

将MV过的数据文件做rename 操作:

SQL>alterdatabaserenamefile'/u01/test.dbf'to'/u02/test.dbf';

Databasealtered.

将数据库打开:

SQL>alterdatabaseopen;

查看各个数据文件的状态:

SQL>selectfile_name,ONLINE_STATUSfromdba_data_files;

FILE_NAMEONLINE_

---------------------------------------------------------

/u01/app/oradata/orclpri/users01.dbfONLINE

/u01/app/oradata/orclpri/undotbs01.dbfONLINE

/u01/app/oradata/orclpri/sysaux01.dbfONLINE

/u01/app/oradata/orclpri/system01.dbfSYSTEM

/u01/app/oradata/orclpri/datafileep_scs_idx.dbfONLINE

/u01/app/oradata/orclpri/bpep_scs.dbfONLINE

/u01/bpep_caweb.dbfONLINE

/u01/app/oradata/orclpri/bpep_caweb_idx.dbfONLINE

/u01/app/oradata/orclpri/readonly01.dbfONLINE

/u01/app/oradata/orclpri/readwrite01.dbfONLINE

/u02/test.dbfRECOVER

FILE_NAMEONLINE_

---------------------------------------------------------

/u01/app/oradata/orclpri/test2.dbfONLINE

/u01/app/oradata/orclpri/test3.dbfONLINE

将数据文件做 online 操作,会产生如下操作:

SQL>alterdatabasedatafile'/u02/test.dbf'online;

alterdatabasedatafile'/u02/test.dbf'online

*

ERRORatline1:

ORA-01113:file11needsmediarecovery

ORA-01110:datafile11:'/u02/test.dbf'

尝试 做recover数据文件操作:

SQL>recoverdatafile'/u02/test.dbf';

ORA-00279:change2684134generatedat12/04/201621:49:15neededforthread1

ORA-00289:suggestion:/u01/archivelog/1_2_929742548.dbf

ORA-00280:change2684134forthread1isinsequence#2

Specifylog:{<RET>=suggested|filename|AUTO|CANCEL}

auto

ORA-00308:cannotopenarchivedlog'/u01/archivelog/1_2_929742548.dbf'

ORA-27037:unabletoobtainfilestatus

LinuxError:2:Nosuchfileordirectory

Additionalinformation:3

ORA-00308:cannotopenarchivedlog'/u01/archivelog/1_2_929742548.dbf'

ORA-27037:unabletoobtainfilestatus

LinuxError:2:Nosuchfileordirectory

Additionalinformation:3

发现 做recover操作的时候 报上面的错误。

这个时候 想要正常的将数据文件online,可能就需要放弃数据的一致性,需要用到_allow_resetlogs_corruption参数。

将_allow_resetlogs_corruption 参数设置为 true ,然后可以将数据文件online

修改这个参数:

SQL>altersystemset"_allow_resetlogs_corruption"=truescope=spfile;

SQL>showparameterallow

NAMETYPEVALUE

-----------------------------------------------------------------------------

_allow_resetlogs_corruptionbooleanTRUE

将数据库重启到 mount状态,然后使用RMAN进入,查看incarnation

RMAN>listincarnation;

ListofDatabaseIncarnations

DBKeyIncKeyDBNameDBIDSTATUSResetSCNResetTime

-------------------------------------------------------------

11ORCLPRI1094561153PARENT113-AUG-09

22ORCLPRI1094561153PARENT75448813-JUL-16

33ORCLPRI1094561153PARENT266312404-DEC-16

44ORCLPRI1094561153PARENT268381804-DEC-16

55ORCLPRI1094561153CURRENT268413004-DEC-16

我们将数据库reset到上个还原点:

RMAN>resetdatabasetoIncarnation4;

然后 重新将库启动到mount状态:

将数据文件online :

alterdatabasedatafile'/u02/test.dbf'online;

这个时候不能直接打开数据库:

SQL>alterdatabaseopen;

alterdatabaseopen

*

ERRORatline1:

ORA-01190:controlfileordatafile11isfrombeforethelastRESETLOGS

ORA-01110:datafile11:'/u02/test.dbf'

SQL>alterdatabaseopenresetlogs;

alterdatabaseopenresetlogs

*

ERRORatline1:

ORA-01139:RESETLOGSoptiononlyvalidafteranincompletedatabaserecovery

我们可以recover database:

使用下面的两个命令:

SQL>recoverdatabaseuntilcancel;

SQL>recoverdatabaseusingbackupcontrolfileuntilcancel;

然后可以打开数据库:

SQL>alterdatabaseopenresetlogs;

Databasealtered.

查看数据文件均是online 状态:

SQL>selectfile_name,ONLINE_STATUSfromdba_data_files;

FILE_NAMEONLINE_

---------------------------------------------------------

/u01/app/oradata/orclpri/users01.dbfONLINE

/u01/app/oradata/orclpri/undotbs01.dbfONLINE

/u01/app/oradata/orclpri/sysaux01.dbfONLINE

/u01/app/oradata/orclpri/system01.dbfSYSTEM

/u01/app/oradata/orclpri/datafileep_scs_idx.dbfONLINE

/u01/app/oradata/orclpri/bpep_scs.dbfONLINE

/u01/bpep_caweb.dbfONLINE

/u01/app/oradata/orclpri/bpep_caweb_idx.dbfONLINE

/u01/app/oradata/orclpri/readonly01.dbfONLINE

/u01/app/oradata/orclpri/readwrite01.dbfONLINE

/u02/test.dbfONLINE

FILE_NAMEONLINE_

---------------------------------------------------------

/u01/app/oradata/orclpri/test2.dbfONLINE

/u01/app/oradata/orclpri/test3.dbfONLINE

13rowsselected.

以上是“数据库中数据文件不一致如何恢复”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!