只读数据文件损坏恢复实验记录.docx
- 文档编号:28798978
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:49
- 大小:38.05KB
只读数据文件损坏恢复实验记录.docx
《只读数据文件损坏恢复实验记录.docx》由会员分享,可在线阅读,更多相关《只读数据文件损坏恢复实验记录.docx(49页珍藏版)》请在冰豆网上搜索。
只读数据文件损坏恢复实验记录
只读表空间的数据文件指为只读数据文件(readonly),当一个表空间从readwrite更改为readonly时,该数据文件里已经产生的脏块儿都会由DBWn写到磁盘,完成一次不完整的完全检查点。
从这一刻起,该数据文件数据块和文件头信息都不再更新,包括检查点。
在以后open数据库时实例也忽略该只读数据文件文件头的检查点SCN与其他数据文件或联机REDO日志的同步。
我们可以通过以下命令在readonly和readwrite之间进行更改
readwrite–>readonly:
altertablespace[tablespace_name]readonly;
readonly–>readwrite:
altertablespace[tablespace_name]readwrite;
查看系统里的只读表空间及其只读数据文件:
sys@MAA>colTABLESPACE_NAMEfora20
sys@MAA>colFILE_NAMEfora65
sys@MAA>selectt.tablespace_name,d.file_id,d.file_namefromdba_tablespacest,dba_data_filesd
2wheret.tablespace_name=d.tablespace_name
3andt.status='READONLY'
4/
TABLESPACE_NAMEFILE_IDFILE_NAME
-----------------------------------------------------------------------------------------------
LTB12/u01/app/oracle/oradata/ltb01.dbf
下面我们针对以下几种场景进行恢复:
1)控制文件无损,数据库运行时丢失了只读数据文件,访问其内数据报错
2)控制文件无损,只读数据文件丢失,数据库无法OPEN
3)控制文件无损,只读数据文件头部损坏
4)控制文件无损,有读写状态时候的备份,后来更改为只读状态,只读状态时数据文件损坏
5)控制文件无损,有只读状态时候的备份,后来更改为读写状态,读写状态时数据文件损坏
6)控制文件损坏,只读数据文件损坏
7)控制文件损坏,有只读数据文件状态时候的控制文件备份,后来数据文件改为读写,读写状态时数据文件损坏
8)控制文件损坏,在线日志文件没有损坏,归档日志丢失,有旧的跟踪控制文件trace,数据库里有只读数据文件
前提条件,有相应的备份。
操作环境:
∙OS:
OracleEnterpriseLinux5.564Bit
∙DBType:
OracleRestart
∙DBVersion:
11.2.0.3
准备一个实验对象只读表空间
sys@MAA>createtablespaceltbdatafile'/u01/app/oracle/oradata/ltb01.dbf'size50M;
sys@MAA>createuserlidentifiedbyoracledefaulttablespaceltb;
sys@MAA>grantresource,connecttol;
sys@MAA>createtablel.luocs(domainvarchar2(100));
sys@MAA>insertintol.luocsvalues('');
sys@MAA>commit;
sys@MAA>altertablespaceltbreadonly;
场景1:
控制文件无损,数据库运行时只读数据文件损坏,访问其内数据报错
备份只读表空间ltb
RMAN>backuptablespaceltb;
Startingbackupat23-DEC-201218:
29:
30
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocatedchannel:
ORA_DISK_1
channelORA_DISK_1:
SID=44devicetype=DISK
channelORA_DISK_1:
startingfulldatafilebackupset
channelORA_DISK_1:
specifyingdatafile(s)inbackupset
inputdatafilefilenumber=00012name=/u01/app/oracle/oradata/ltb01.dbf
channelORA_DISK_1:
startingpiece1at23-DEC-201218:
29:
31
channelORA_DISK_1:
finishedpiece1at23-DEC-201218:
29:
32
piecehandle=/u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T182931_8ffq8cbz_.bkptag=TAG20121223T182931comment=NONE
channelORA_DISK_1:
backupsetcomplete,elapsedtime:
00:
00:
01
Finishedbackupat23-DEC-201218:
29:
32
数据库运行状态下,破坏只读数据文件以模拟损坏
[oracle@maa3~]$ddif=/dev/zeroof=/u01/app/oracle/oradata/ltb01.dbfbs=10Mcount=5
5+0recordsin
5+0recordsout
52428800bytes(52MB)copied,0.04918seconds,1.1GB/s
查看v$datafile内容时,返回正常
sys@MAA>colNAMEfora65
sys@MAA>selectcheckpoint_change#fromv$datafilewherefile#=12;
CHECKPOINT_CHANGE#
------------------
3567837
但日志里将报错:
Hexdumpof(file12,block1)intracefile/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_m000_5343.trc
Corruptblockrelativedba:
0x03000001(file12,block1)
Completelyzeroblockfoundduringkcvxfhv8
Readingdatafile'/u01/app/oracle/oradata/ltb01.dbf'forcorruptionatrdba:
0x03000001(file12,block1)
Reread(file12,block1)founddifferentcorruptdata(nologicalcheck)
Hexdumpof(file12,block1)intracefile/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_m000_5343.trc
Corruptblockrelativedba:
0x03000001(file12,block1)
Completelyzeroblockfoundduringreread
访问该数据文件里的数据:
sys@MAA>select*froml.luocs;
select*froml.luocs
*
ERRORatline1:
ORA-01578:
ORACLEdatablockcorrupted(file#12,block#138)
ORA-01110:
datafile12:
'/u01/app/oracle/oradata/ltb01.dbf'
告警日志也会抛出相应错误信息:
Hexdumpof(file12,block2)intracefile/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_5220.trc
Corruptblockrelativedba:
0x03000002(file12,block2)
Completelyzeroblockfoundduringbufferread
Readingdatafile'/u01/app/oracle/oradata/ltb01.dbf'forcorruptionatrdba:
0x03000002(file12,block2)
Reread(file12,block2)foundsamecorruptdata(nologicalcheck)
SunDec2319:
56:
002012
CorruptBlockFound
TSN=7,TSNAME=LTB
RFN=12,BLK=2,RDBA=50331650
OBJN=-1,OBJD=-1,OBJECT=,SUBOBJECT=
SEGMENTOWNER=,SEGMENTTYPE=
Errorsinfile/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_5220.trc(incident=31391):
ORA-01578:
ORACLEdatablockcorrupted(file#12,block#2)
ORA-01110:
datafile12:
'/u01/app/oracle/oradata/ltb01.dbf'
Hexdumpof(file12,block1)intracefile/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_5220.trc
Corruptblockrelativedba:
0x00000001(file12,block1)
Completelyzeroblockfoundduringvalidatingdatafileforblockrange
Rereadofblocknum=1,file=/u01/app/oracle/oradata/ltb01.dbf.foundsamecorruptdata
Rereadofblocknum=1,file=/u01/app/oracle/oradata/ltb01.dbf.foundsamecorruptdata
Rereadofblocknum=1,file=/u01/app/oracle/oradata/ltb01.dbf.foundsamecorruptdata
Rereadofblocknum=1,file=/u01/app/oracle/oradata/ltb01.dbf.foundsamecorruptdata
Rereadofblocknum=1,file=/u01/app/oracle/oradata/ltb01.dbf.foundsamecorruptdata
Errorsinfile/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_5220.trc:
ORA-19563:
datafileheadervalidationfailedforfile/u01/app/oracle/oradata/ltb01.dbf
ORA-01251:
UnknownFileHeaderVersionreadforfilenumber12
ORA-01578:
ORACLEdatablockcorrupted(file#12,block#2)
ORA-01110:
datafile12:
'/u01/app/oracle/oradata/ltb01.dbf'
SunDec2319:
56:
012012
Sweep[inc][31391]:
completed
但此时只读数据文件状态还是ONLINE
sys@MAA>colFILE_NAMEfora65
sys@MAA>selectfile_id,file_name,online_statusfromdba_data_fileswheretablespace_name='LTB';
FILE_IDFILE_NAMEONLINE_STATUS
-----------------------------------------------------------------------------------------
12/u01/app/oracle/oradata/ltb01.dbfONLINE
这里恢复需要注意,如果只是有数据讹误块儿导致数据访问报错,那可以简单通过RMAN的块恢复命令进行块级别恢复。
但这里我是将整个数据文件破坏掉了,因此需要RMAN来还原数据文件,恢复可以在数据文件OPEN模式进行
这时候我们直接RESTOREDATAFILE是不可行的,我们需要先将只读数据文件脱机处理,还原之后再ONLINE
RMAN>restoretablespaceltb;
Startingrestoreat23-DEC-201219:
46:
43
usingchannelORA_DISK_1
channelORA_DISK_1:
startingdatafilebackupsetrestore
channelORA_DISK_1:
specifyingdatafile(s)torestorefrombackupset
channelORA_DISK_1:
restoringdatafile00012to/u01/app/oracle/oradata/ltb01.dbf
channelORA_DISK_1:
readingfrombackuppiece/u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T184851_8ffrdmhg_.bkp
RMAN-00571:
===========================================================
RMAN-00569:
===============ERRORMESSAGESTACKFOLLOWS===============
RMAN-00571:
===========================================================
RMAN-03002:
failureofrestorecommandat12/23/201219:
46:
44
ORA-19870:
errorwhilerestoringbackuppiece/u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T184851_8ffrdmhg_.bkp
ORA-19573:
cannotobtainexclusiveenqueuefordatafile12
正确的做法
RMAN>run{
2>sql'alterdatabasedatafile12offline';
3>restoretablespaceltb;
4>sql'alterdatabasedatafile12online';
5>}
sqlstatement:
alterdatabasedatafile12offline
Startingrestoreat23-DEC-201219:
50:
41
usingchannelORA_DISK_1
channelORA_DISK_1:
startingdatafilebackupsetrestore
channelORA_DISK_1:
specifyingdatafile(s)torestorefrombackupset
channelORA_DISK_1:
restoringdatafile00012to/u01/app/oracle/oradata/ltb01.dbf
channelORA_DISK_1:
readingfrombackuppiece/u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T184851_8ffrdmhg_.bkp
channelORA_DISK_1:
piecehandle=/u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T184851_8ffrdmhg_.bkptag=TAG20121223T184851
channelORA_DISK_1:
restoredbackuppiece1
channelORA_DISK_1:
restorecomplete,elapsedtime:
00:
00:
03
Finishedrestoreat23-DEC-201219:
50:
44
sqlstatement:
alterdatabasedatafile12online
恢复后数据访问正常:
sys@MAA>select*froml.luocs;
DOMAIN
----------------------------------------------------------------------------------------------------
当然我们要是有镜像备份文件,也可以SWITCH
RMAN>backupascopytablespaceltb;
Startingbackupat23-DEC-201219:
54:
34
usingchannelORA_DISK_1
channelORA_DISK_1:
startingdatafilecopy
inputdatafilefilenumber=00012name=/u01/app/oracle/oradata/ltb01.dbf
outputfilename=/u01/recovery/MAA/datafile/o1_mf_ltb_8ffw7tk0_.dbftag=TAG20121223T195434RECID=23STAMP=802814075
channelORA_DISK_1:
datafilecopycomplete,elapsedtime:
00:
00:
01
Finishedbackupat23-DEC-201219:
54:
35
在发现只读数据文件损坏的情况下
RMAN>run{
2>sql'alterdatabasedatafile12offline';
3>switchdatafile12todatafilecopy'/u01/recovery/MAA/datafile/o1_mf_ltb_8ffw7tk0_.dbf';
4>sql'alterdatabasedatafile12online';
5>}
sqlstatement:
alterdatabasedatafile12offline
datafile12switchedtodatafilecopy
inputdatafilecopyRECID=23STAMP=802814075filename=/u01/recovery/MAA/datafile/o1_mf_ltb_8ffw7tk0_.dbf
sqlstatement:
alterdatabasedatafile12online
已经成功SWITCH,可查看v$datafile进行确认
sys@MAA>selectcheckpoint_change#,namefromv$datafilewherefile#=12;
CHECKPOINT_CHANGE#NAME
-----------------------------------------------------------------------------------
3567837/u01/recovery/MAA/datafile/o1_mf_ltb_8ffw7tk0_.dbf
查看数据也正常
sys@MAA>select*froml.luocs;
DOMAIN
----------------------------------------------------------------------------------------------------
最后别忘了将损坏了SWITCH之前的数据文件进行删除
[oracle@maa3~]$rm-rf/u01/app/oracle/oradata/ltb01.dbf
那有人可能会奇怪,如果直接删除掉只读数据文件,那不也是一样的效果吗?
但其实不然
删除只读数据文件以模拟丢失
[oracle@maa3~]$rm-rf/u01/app/oracle/oradata/ltb01.dbf
sys@MAA>altersystemcheckpoint;
Systemaltered.
sys@MAA>altersystemflushbuffer_cache;
Systemaltered.
sys@MAA>select*froml.luocs;
DOMAIN
----------------------------------------------------------------------------------------------------
可见我们数据依然正常访问,这里需要了解文件描述符的知识
EYGLE的《数据安全警示录》里有通过文件描述符进行恢复的案例,其
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 只读 数据文件 损坏 恢复 实验 记录