oracle特殊恢复-bbed修改某个数据文件头

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了oracle特殊恢复-bbed修改某个数据文件头，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

数据文件头中的scn要与控制文件中的scn一致，数据库才可以open，在open过程中我们可以通过bbed来修改某个数据文件头的scn，来欺骗oracle，来open库。

1、环境如下

使用Oracle 11gR2进行测试，具体版本为11.2.0.4

SYS@xbtstsql>selectfile#,name,checkpoint_change#,checkpoint_timefromv$datafile;

FILE#NAMECHECKPOINT_CHANGE#CHECKPOINT_TIME
---------------------------------------------------------------------------------------
1/dbdata/oradata/xbtst/system01.dbf12330662016-11-1011:23:44
2/dbdata/oradata/xbtst/sysaux01.dbf12330662016-11-1011:23:44
3/dbdata/oradata/xbtst/undotbs01.dbf12330662016-11-1011:23:44
4/dbdata/oradata/xbtst/users01.dbf12330662016-11-1011:23:44
5/dbdata/oradata/xbtst/moe01.dbf12330662016-11-1011:23:44

2、模拟某个数据文件在问题

shutdown immediate数据库，然后拷贝moe01.dbf这个数据文件，然后打开数据库，做日志切换，然后shutdown immediate，再把之前的拷贝替换现在的moe01.dbf数据文件

SYS@xbtstsql>shutdownimmediate
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.

[oracle@tstdb-25-220xbtst]$cpmoe01.dbfmoe01.dbf.bak
[oracle@tstdb-25-220xbtst]$ll
总用量3872076
-rw-r-----.1oracleoinstall974848011月1009:48control01.ctl
-rw-r-----.1oracleoinstall974848011月1009:48control02.ctl
-rw-r-----.1oracleoinstall107375001611月1009:48moe01.dbf
-rw-r-----.1oracleoinstall107375001611月1011:23moe01.dbf.bak
-rw-r-----.1oracleoinstall5242931211月913:13redo01.log
-rw-r-----.1oracleoinstall5242931211月922:00redo02.log
-rw-r-----.1oracleoinstall5242931211月1009:48redo03.log
-rw-r-----.1oracleoinstall58721075211月1009:48sysaux01.dbf
-rw-r-----.1oracleoinstall80741171211月1009:48system01.dbf
-rw-r-----.1oracleoinstall3041689611月922:00temp01.dbf
-rw-r-----.1oracleoinstall13108019211月1009:48undotbs01.dbf
-rw-r-----.1oracleoinstall11141939211月1009:48users01.dbf

SYS@xbtstsql>startup
ORACLEinstancestarted.

TotalSystemGlobalArea2455228416bytes
FixedSize2255712bytes
VariableSize620758176bytes
DatabaseBuffers1811939328bytes
RedoBuffers20275200bytes
Databasemounted.
DatabaSEOpened.

SYS@xbtstsql>altersystemswitchlogfile;

Systemaltered.

SYS@xbtstsql>altersystemswitchlogfile;

Systemaltered.

SYS@xbtstsql>altersystemswitchlogfile;

Systemaltered.

SYS@xbtstsql>shutdownimmediate
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.

[oracle@tstdb-25-220xbtst]$cpmoe01.dbfmoe01.dbf.bak.f
[oracle@tstdb-25-220xbtst]$cpmoe01.dbf.bakmoe01.dbf

启动数据库报错
SYS@xbtstsql>startup
ORACLEinstancestarted.

TotalSystemGlobalArea2455228416bytes
FixedSize2255712bytes
VariableSize620758176bytes
DatabaseBuffers1811939328bytes
RedoBuffers20275200bytes
Databasemounted.
ORA-01113:file5needsmediarecovery
ORA-01110:datafile5:'/dbdata/oradata/xbtst/moe01.dbf'

SYS@xbtstsql>recoverdatafile5;
ORA-00279:change1233063generatedat11/10/201609:48:02neededforthread1
ORA-00289:suggestion:/apps/oracle/11.2.0/db_1/dbs/arch1_24_925478204.dbf
ORA-00280:change1233063forthread1isinsequence#24


Specifylog:{<RET>=suggested|filename|AUTO|CANCEL}
auto
ORA-00308:cannotopenarchivedlog'/apps/oracle/11.2.0/db_1/dbs/arch1_24_925478204.dbf'
ORA-27037:unabletoobtainfilestatus
Linux-x86_64Error:2:Nosuchfileordirectory
Additionalinformation:3


ORA-00308:cannotopenarchivedlog'/apps/oracle/11.2.0/db_1/dbs/arch1_24_925478204.dbf'
ORA-27037:unabletoobtainfilestatus
Linux-x86_64Error:2:Nosuchfileordirectory
Additionalinformation:3

3、场景模拟出来了，下面是修复过程

使用BBED，将文件头的SCN等关键信息修改到与控制文件control file相匹配即可

SYS@xbtstsql>selectfile#,CHECKPOINT_CHANGE#fromv$datafile;

FILE#CHECKPOINT_CHANGE#
----------------------------
11233692
21233692
31233692
41233692
51233692

SYS@xbtstsql>selectCHECKPOINT_CHANGE#fromv$database;

CHECKPOINT_CHANGE#
------------------
1233692

SYS@xbtstsql>selectfile#,recover,fuzzy,CHECKPOINT_CHANGE#fromv$datafile_header;

FILE#RECFUZCHECKPOINT_CHANGE#
----------------------------------
1NONO1233692
2NONO1233692
3NONO1233692
4NONO1233692
5YESNO1233063

控制文件中datafile5的scn是：1233692，而数据文件头中的scn是：1233063

使用bbed将datafile5的数据文件头对应的SCN修改为与其他文件相同，我们先看一下users01.dbf这个文件的文件头

BBED>setfilename'/dbdata/oradata/xbtst/users01.dbf'
FILENAME/dbdata/oradata/xbtst/users01.dbf

BBED>setblock1
BLOCK#1

BBED>map
File:/dbdata/oradata/xbtst/users01.dbf(0)
Block:1Dba:0x00000000
------------------------------------------------------------
DataFileHeader

structkcvfh,860bytes@0

ub4tailchk@8188

因为我们要修改数据文件头，因此我们需要关注四个偏移量offset点，分别为484、492、140和148

1、datafile 的file header 存储在第一个block里

2、Oracle considers four attributes of this data structure when determining if a datafile is sync with the other data files of the database:（不同oracle版本offset可能不同）

（1）kscnbas (at offset 484) � SCN of last change to the datafile.

（2）kcvcptim (at offset 492) -Time of the last change to the datafile.

（3）kcvfhcpc (at offset 140) � Checkpoint count.

（4）kcvfhccc (at offset 148) � Unknown,but is always 1 less than thecheckpoint point count.

Oracle有4个属性来判断datafile 是否和其他的datafile 一致，如果都一致，可以正常操作，如果不一致，那么会报ORA-01113错误

下面我们看下这几个offset

BBED>setoffset484
OFFSET484

BBED>dump
File:/dbdata/oradata/xbtst/users01.dbf(0)
Block:1Offsets:484to995Dba:0x00000000
------------------------------------------------------------------------
1cd3120000000000bd384937010000001b00000030010000100051f202000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0d000d000d000100000000000000000000000000020000010300000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000

<32bytesperline>

BBED>setoffset492
OFFSET492

BBED>dump
File:/dbdata/oradata/xbtst/users01.dbf(0)
Block:1Offsets:492to1003Dba:0x00000000
------------------------------------------------------------------------
bd384937010000001b00000030010000100051f2020000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000d000d000d000100
0000000000000000000000000200000103000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000

<32bytesperline>

BBED>setoffset140
OFFSET140

BBED>dump
File:/dbdata/oradata/xbtst/users01.dbf(0)
Block:1Offsets:140to651Dba:0x00000000
------------------------------------------------------------------------
7800000014ff4737770000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0400000005005553455253000000000000000000000000000000000000000000
0000000004000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000007ac921310100000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000001cd3120000000000
bd384937010000001b00000030010000100051f2020000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000d000d000d000100

<32bytesperline>

BBED>setoffset148
OFFSET148

BBED>dump
File:/dbdata/oradata/xbtst/users01.dbf(0)
Block:1Offsets:148to659Dba:0x00000000
------------------------------------------------------------------------
7700000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000400000005005553
4552530000000000000000000000000000000000000000000000000004000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000007ac9213101000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000001cd3120000000000bd38493701000000
1b00000030010000100051f20200000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000d000d000d0001000000000000000000

<32bytesperline>

其中，位于484和488偏移量的是数据文件对应的SCN编号。在Oracle内部，SCN是使用wrap*4*1024*1024*1024+base来进行标示的。通常我们看到的数据库wrap都是0。位于492偏移量的是最后一次检查点对应的时间信息。位于140和148偏移量的是检查点次数。这些信息都是会由于时间推动和检查点动作引起变化，我们严格情况下，需要保证文件头块的信息和控制文件信息一致。


另外一点，由于Linux是Little字节系统，要关注写入时候的格式问题。最简单的方式是dump一下偏移量，看看是怎么保存的。

BBED>dump
File:/dbdata/oradata/xbtst/users01.dbf(0)
Block:1Offsets:484to995Dba:0x00000000
------------------------------------------------------------------------
1cd3120000000000bd384937010000001b00000030010000100051f202000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000

然后我们修改moe01.dbf的文件头（修改这几个offset，我这里只修改了484、492就可以open库了）

BBED>setfilename'/dbdata/oradata/xbtst/moe01.dbf'
FILENAME/dbdata/oradata/xbtst/moe01.dbf

BBED>setblock1
BLOCK#1

BBED>setmodeedit
MODEEdit

BBED>setoffset484
OFFSET484

BBED>dump
File:/dbdata/oradata/xbtst/moe01.dbf(0)
Block:1Offsets:484to995Dba:0x00000000
------------------------------------------------------------------------
a7d01200000000005220493701000000180000004d8d0000100051f202000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0d000d000d000100000000000000000000000000020040010000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000

<32bytesperline>

BBED>m/x1cd31200484
Warning:contentsofprevIoUsBIFILEwillbelost.Proceed?(Y/N)y
File:/dbdata/oradata/xbtst/moe01.dbf(0)
Block:1Offsets:484to995Dba:0x00000000
------------------------------------------------------------------------
1cd31200000000005220493701000000180000004d8d0000100051f202000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0d000d000d000100000000000000000000000000020040010000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000

<32bytesperline>

BBED>m/xbd384937492
BBED-00209:invalidnumber(bd384937)


BBED>m/xbd38492
File:/dbdata/oradata/xbtst/moe01.dbf(0)
Block:1Offsets:492to1003Dba:0x00000000
------------------------------------------------------------------------
bd38493701000000180000004d8d0000100051f2020000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000d000d000d000100
0000000000000000000000000200400100000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000

<32bytesperline>

BBED>m/x4937494
File:/dbdata/oradata/xbtst/moe01.dbf(0)
Block:1Offsets:494to1005Dba:0x00000000
------------------------------------------------------------------------
493701000000180000004d8d0000100051f20200000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000d000d000d0001000000
0000000000000000000002004001000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000

<32bytesperline>

BBED>sumapply
CheckvalueforFile0,Block1:
current=0x1683,required=0x1683

BBED>verify
DBVERIFY-Verificationstarting
FILE=/dbdata/oradata/xbtst/moe01.dbf
BLOCK=1


DBVERIFY-Verificationcomplete

TotalBlocksExamined:1
TotalBlocksProcessed(Data):0
TotalBlocksFailing(Data):0
TotalBlocksProcessed(Index):0
TotalBlocksFailing(Index):0
TotalBlocksEmpty:0
TotalBlocksMarkedCorrupt:0
TotalBlocksInflux:0
Message531notfound;product=RDBMS;facility=BBED

这时我们再次查看各个数据文件头的scn，发现scn一致了

SYS@xbtstsql>selectfile#,CHECKPOINT_CHANGE#fromv$datafile_header;

FILE#RECFUZCHECKPOINT_CHANGE#
----------------------------------
1NONO1233692
2NONO1233692
3NONO1233692
4NONO1233692
5YESNO1233692

试着open库

SYS@xbtstsql>alterdatabaSEOpen
2;
alterdatabaSEOpen
*
ERRORatline1:
ORA-01113:file5needsmediarecovery
ORA-01110:datafile5:'/dbdata/oradata/xbtst/moe01.dbf'


SYS@xbtstsql>recoverdatafile5;
Mediarecoverycomplete.
SYS@xbtstsql>alterdatabaSEOpen;

Databasealtered.

看一下alert文件

ALTERDATABASERECOVERdatafile5
MediaRecoveryStart
SerialMediaRecoverystarted
MediaRecoveryComplete(xbtst)
Completed:ALTERDATABASERECOVERdatafile5
ThuNov1013:47:242016
alterdatabaSEOpen
ThuNov1013:47:252016
Thread1openedatlogsequence27
Currentlog#3seq#27mem#0:/dbdata/oradata/xbtst/redo03.log
Successfulopenofredothread1
MTTRadvisoryisdisabledbecauseFAST_START_MTTR_TARGETisnotset
ThuNov1013:47:252016
SMON:enablingcacherecovery
[20910]SuccessfullyonlinedUndoTablespace2.
Undoinitializationfinishedserial:0start:3710149144end:3710149224diff:80(0seconds)
Verifyingfileheadercompatibilityfor11gtablespaceencryption..
Verifying11gfileheadercompatibilityfortablespaceencryptioncompleted
SMON:enablingtxrecovery
DatabaseCharactersetisAL32UTF8
NoResourceManagerplanactive
replication_dependency_trackingturnedoff(noasyncmultimasterreplicationfound)
StartingbackgroundprocessQMNC
ThuNov1013:47:252016
QMNCstartedwithpid=20,OSid=21409
Completed:alterdatabaSEOpen

在本次恢复过程中，recover是可行的，原因是该库并没做过resetlog，如果datafile是在 resetlog之前就已经offline的数据文件，那recover是不可行的。

原文链接：https://www.f2er.com/oracle/211866.html

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问