标签云
asm恢复 asm 恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,601)
- DB2 (22)
- MySQL (70)
- Oracle (1,466)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle 23ai (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (531)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- Oracle 23ai rm redo*.log恢复
- Oracle 发布计划—包含Oracle 23ai版本
- Oracle 23ai 变化之—-默认数据文件变为bigfile
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
月归档:三月 2013
undo异常事务回滚规则分析
undo事务具体是如何回滚,这里提供了大概的异常undo事务回滚的一个过程(更加准确的说,这个过程是在以下几种情况中发生的过程:1.数据库非正常关闭后启动,2.事务未提交会话终止),数据库先扫描所有回滚段,然后发现有事务未提交回滚段,然后根据这个回滚段定位到undo block,然后定位到data block,当一个undo block回滚完成之后,利用undo的链表规则完成下一个undo block的回滚操作,依次类此,从而实现数据库的回滚操作;回滚的过程是先回滚后操作的块(先进后出原则)
创建测试表
SQL> create table chf.t_xifenfei(a varchar2(4000)); Table created. SQL> insert into chf.t_xifenfei values (lpad('www.xifenfei.com',4000,'a')); 1 row created. SQL> insert into chf.t_xifenfei values (lpad('www.xifenfei.com',4000,'b')); 1 row created. SQL> commit; Commit complete. SQL> update chf.t_xifenfei set a=lpad('www.xifenfei.com',4000,'F'); 2 rows updated. SQL> select 2 dbms_rowid.rowid_relative_fno(rowid) rel_fno, 3 dbms_rowid.rowid_block_number(rowid) block_no 4 from chf.t_xifenfei; REL_FNO BLOCK_NO ---------- ---------- 9 421 9 422
确保表中有两条记录,存储在两个block中
查询测试表相关信息
SQL> select object_id,data_object_id from dba_objects where owner='CHF' AND OBJECT_NAME='T_XIFENFEI'; OBJECT_ID DATA_OBJECT_ID ---------- -------------- 53917 53917 SQL> SELECT TO_CHAR(53917,'XXXXX') FROM DUAL; TO_CHA ------ D29D SQL> SELECT XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC FROM v$transaction; XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC ---------- ---------- ---------- ---------- ---------- ---------- ---------- 2 28 513 2 3627 253 1 SQL> select to_char(28,'xxx') from dual; TO_C ---- 1c SQL> select to_char(513,'xxx') from dual; TO_C ---- 201 ------------------------ --xid=0002.01c.00000201 ------------------------ SQL> variable dba varchar2(30) SQL> exec :dba := dbms_utility.make_data_block_address(2,3627); PL/SQL procedure successfully completed. SQL> print dba DBA -------------------------------- 8392235 SQL> select to_char(253,'xxx') from dual; TO_C ---- fd --------------------- --uda=800e2a.00fd.01 ---------------------
通过这些查询可以得知:
1)chf.t_xifenfei的object_id/data_object_id的值为53917/D29D
2)xid=0002.01c.00000201(Xid=usn.slot.wrap)
3)uda=800e2a.00fd.01(Uba=undo block dba.undo record number.undo block sequence number)
dump rollback header
SQL> select * from v$rollname where usn=2; USN NAME ---------- ------------------------------ 2 _SYSSMU2$ SQL> alter system dump undo header "_SYSSMU2$"; System altered. index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x0201 0x0012 0x0b2c.c02c9b85 0x00800e27 0x0000.000.00000000 0x00000001 0x00000000 1358780575 ……………… 0x1b 9 0x00 0x0201 0x0028 0x0b2c.c02c9bf4 0x00800e27 0x0000.000.00000000 0x00000001 0x00000000 1358780575 0x1c 10 0x80 0x0201 0x0002 0x0b2c.c02ca2a8 0x00800e2b 0x0000.000.00000000 0x00000002 0x00000000 0 0x1d 9 0x00 0x0200 0x0004 0x0b2c.c02c9a8b 0x00800e26 0x0000.000.00000000 0x00000001 0x00000000 1358780444 ……………… 0x2f 9 0x00 0x0201 0x001a 0x0b2c.c02ca1da 0x00800e29 0x0000.000.00000000 0x00000001 0x00000000 1358784176 ------------------------------------------------------------------------------ ==> Transaction Table ==> state 0 = IDLE 1 = Collecting 2 = Prepared 3 = Committed 4 = Forced Abort 5 = Forced Commit 6 = Forced Mixed 7 = try again later 9 = No TX (Committed) 10= 'a' = Active local TX ==> cflags 1 = TX has started storing collecting information 2 = TX has forced the collecting information 4 = Prepared TX needs distributed recovery 10= Rollback failed on this TX - mark SMON for recover 20= TX has rolled back its updates ==> wrap# is incremented TX slot reuse. ==> uel ? ==> scn SCN for the TX prepare / commit ==> dba is DBA of HEAD of the REDO - Ie: The MOST RECENT CHANG ------------------------------------------------------------------------------ --通过上面的提示可以知道index为0x1c为未提交事务 SQL> select to_number('800e2b','xxxxxxx') from dual; TO_NUMBER('800E2B','XXXXXXX') ----------------------------- 8392235 SQL> select dbms_utility.data_block_address_block(8392235) "block", 2 dbms_utility.data_block_address_file(8392235) "file" from dual; block file ---------- ---------- 3627 2 --v$transaction表中查询出来的UBAFIL与UBABLK一致
dump undo block
SQL> alter system dump datafile 2 block 3627; System altered. UNDO BLK: xid: 0x0002.01c.00000201 seq: 0xfd cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x0fdc *----------------------------- * Rec #0x1 slt: 0x1c objn: 53917(0x0000d29d) objd: 53917 tblspc: 9(0x00000009) * Layer: 11 (Row) opc: 1 rci 0x00 <--- 表明指向下一个block Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00800e2a <--- 为下一个undo block rdba *----------------------------- KDO undo record: KTB Redo op: 0x03 ver: 0x01 op: Z Array Update of 1 rows: tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 71 ncol: 1 nnew: 1 size: 0 KDO Op code: 21 row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x024001a6 hdba: 0x024001a3 itli: 2 ispac: 0 maxfr: 4858 --bdba表示undo对应的data block,这里对应的是datafile 9 block 422 --hdba表示chf.t_xifenfei的extent 0的 first block(first mapbit) SQL> select to_number('800e2a','xxxxxxx') from dual; TO_NUMBER('800E2A','XXXXXXX') ----------------------------- 8392234 SQL> select dbms_utility.data_block_address_block(8392234) "block", 2 dbms_utility.data_block_address_file(8392234) "file" from dual; block file ---------- ---------- 3626 2 SQL> alter system dump datafile 2 block 3626; System altered. UNDO BLK: xid: 0x0002.01c.00000201 seq: 0xfd cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x0fac *----------------------------- * Rec #0x1 slt: 0x1c objn: 53917(0x0000d29d) objd: 53917 tblspc: 9(0x00000009) * Layer: 11 (Row) opc: 1 rci 0x00 <--- 表明指向下一个block Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 <--- 下一个block为 0x00000000,表示事务到此为止 *----------------------------- uba: 0x00800e29.00fd.24 ctl max scn: 0x0b2c.c02c99cc prv tx scn: 0x0b2c.c02c9a27 txn start scn: scn: 0x0b2c.c02ca0b3 logon user: 0 prev brb: 8392230 prev bcl: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 op: Z Array Update of 1 rows: tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 71 ncol: 1 nnew: 1 size: 0 KDO Op code: 21 row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x024001a5 hdba: 0x024001a3 itli: 2 ispac: 0 maxfr: 4858 --bdba表示undo对应的data block,这里对应的是datafile 9 block 421
XID: this is the transaction ID, which matches the value made up previously from the query on V$TRANSACTION.
CNT: this is the number of undo records in this block.
IRB: this is the index if the first record to be considered in case of a rollback.
OBJN: this is the object number being altered by the undo record. This matches the value previously queried from DBA_OBJECTS.
RCI: this is the next undo record in the chain to be examined. When rolling back transactions, these undo records are applied one by one on the data blocks to undo the changes.
1. undo链表的意思为:从IRB表示undo 回滚的开始,RCI表示未IRB对应的下一条记录,依次类推。当RCI为0,而且rdba不为0的时候,表示undo 记录关联到下一个block;当RCI为0,而且rdba也为0表示undo结束
2. undo事务回滚是倒序的,这里可以看到事务先读取block 422,再读取block 421
dump data block
SQL> alter system dump datafile 9 block 421; System altered. Block header dump: 0x024001a5 Object id on Block? Y seg/obj: 0xd29d csc: 0xb2c.c02ca2bc itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x24001a1 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.01d.00000232 0x0083dbe0.02be.22 C--- 0 scn 0x0b2c.c02ca2a8 0x02 0x0002.01c.00000201 0x00800e2a.00fd.01 ---- 1 fsc 0x0000.00000000 SQL> alter system dump datafile 9 block 422; System altered. Block header dump: 0x024001a6 Object id on Block? Y seg/obj: 0xd29d csc: 0xb2c.c02ca2bd itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x24001a1 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.01d.00000232 0x0083dbe0.02be.23 C--- 0 scn 0x0b2c.c02ca2a8 0x02 0x0002.01c.00000201 0x00800e2b.00fd.01 ---- 1 fsc 0x0000.00000000
这里可以看到Xid,Uba和上面计算出来的一致,看出来事务未提交标记和上述一致.
关于blockrecover 解决坏块相关测试与总结
悲剧的客户因为IBM p系列小机更换电源导致主机直接掉电,起来后发现数据库出现不少坏块,而且还有部分坏块中含有回滚事务,导致alert日志一直报smon回滚遇到坏块错误,该数据库版本是9.2.0.8 RAC,根据客户的备份情况,为了减少对业务的影响,决定使用blockrecover对其处理.这里通过10g数据库大概模拟出现含事务坏块的情况以及处理过程,重现了我们在处理的时候不确定的一些知识.
创建测试表
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> conn /as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 231 Next log sequence to archive 233 Current log sequence 233 SQL> conn chf/xifenfei Connected. SQL> create table t_xifenfei 2 as 3 select * from dba_objects where rownum<10; Table created. SQL> select rowid, 2 dbms_rowid.rowid_relative_fno(rowid) rel_fno, 3 dbms_rowid.rowid_block_number(rowid) block 4 from chf.t_xifenfei; ROWID REL_FNO BLOCK ------------------ ---------- ---------- AAANIqAAEAAAAAcAAA 4 28 AAANIqAAEAAAAAcAAB 4 28 AAANIqAAEAAAAAcAAC 4 28 AAANIqAAEAAAAAcAAD 4 28 AAANIqAAEAAAAAcAAE 4 28 AAANIqAAEAAAAAcAAF 4 28 AAANIqAAEAAAAAcAAG 4 28 AAANIqAAEAAAAAcAAH 4 28 AAANIqAAEAAAAAcAAI 4 28 9 rows selected.
当前的seq是233(也就是说我在233归档上创建了t_xienfei表)
dbv检查block
[oracle@xifenfei ~]$ dbv file='/u01/oracle/oradata/XFF/users01.dbf' DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 22:16:16 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf DBVERIFY - Verification complete Total Pages Examined : 1280 Total Pages Processed (Data) : 904 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 38 Total Pages Failing (Index): 0 Total Pages Processed (Other): 44 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 294 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Highest block SCN : 3224018224 (2860.3224018224)
证明无任何坏块
切换归档
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 233 Next log sequence to archive 235 Current log sequence 235
现在已经切换seq到235
rman备份我们需要测试block(file 4 block 28)对应的数据文件
RMAN> backup datafile 4 format '/u01/oracle/oradata/xff_4.rman';
具体见:rman制造坏块,bbed修复坏块
模拟数据库进行其他操作
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 235 Next log sequence to archive 237 Current log sequence 237 SQL> conn chf/xifenfei Connected. SQL> create table t_xff 2 as 3 select * from dba_objects; Table created. SQL> alter system switch logfile; System altered.
主要是为了模拟对其他block操作,对于block 28的恢复影响
对block 28进行操作
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 237 Next log sequence to archive 239 Current log sequence 239 SQL> update chf.t_xifenfei set object_name='www.xifenfei.com'; 9 rows updated. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered.
在seq为239的时候对block 28进行了一次update操作
模拟其他业务操作
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 240 Next log sequence to archive 242 Current log sequence 242 SQL> delete from chf.t_xff ; 50491 rows deleted. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 242 Next log sequence to archive 244 Current log sequence 244 SQL> alter system switch logfile; System altered. SQL> / System altered.
这里可以知道在seq为246的时候做了备份归档操作
备份归档操作
RMAN> backup archivelog all format '/u01/oracle/oradata/xff_arch_%U' delete input;
模拟继续操作
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 248 Next log sequence to archive 250 Current log sequence 250 SQL> update chf.t_xifenfei set object_name='www.orasos.com' where rownum<5; 4 rows updated.
这里可以发现,在seq为250的时候我们再次对block 28进行了操作
使用rman制造坏块
RMAN> BLOCKRECOVER DATAFILE 4 block 28 clear;
dbv检查坏块
[oracle@xifenfei ~]$ dbv file='/u01/oracle/oradata/XFF/users01.dbf' DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:01:24 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf Page 28 is influx - most likely media corrupt Corrupt block relative dba: 0x0100001c (file 4, block 28) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0100001c last change scn: 0x0b2c.c02ab081 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xb0c4a6ea check value in block header: 0x393f computed block checksum: 0xc917 DBVERIFY - Verification complete Total Pages Examined : 1280 Total Pages Processed (Data) : 903 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 38 Total Pages Failing (Index): 0 Total Pages Processed (Other): 47 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 291 Total Pages Marked Corrupt : 1 Total Pages Influx : 1 Highest block SCN : 3224022228 (2860.3224022228)
强制kill数据库
[oracle@xifenfei ~]$ ps -ef|grep pmon oracle 9744 9638 0 23:03 pts/1 00:00:00 grep pmon oracle 32156 1 0 14:17 ? 00:00:10 ora_pmon_XFF [oracle@xifenfei ~]$ kill -9 32156 [oracle@xifenfei ~]$ ps -ef|grep pmon oracle 9751 9638 0 23:03 pts/1 00:00:00 grep pmon
为了模拟含事务的block出现坏块
启动数据库
SQL> startup mount ORACLE instance started. Total System Global Area 306184192 bytes Fixed Size 1267164 bytes Variable Size 109054500 bytes Database Buffers 188743680 bytes Redo Buffers 7118848 bytes Database mounted. SQL> ALTER DATABASE OPEN; Database altered.
数据库启动正常
查询坏块
SQL> select count(*) from chf.t_xifenfei; select count(*) from chf.t_xifenfei * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 4, block # 28) ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'
alert日志
Sun Jan 20 23:04:37 2013 SMON: enabling tx recovery Sun Jan 20 23:04:37 2013 Database Characterset is ZHS16GBK Sun Jan 20 23:04:37 2013 Hex dump of (file 4, block 28) in trace file /u01/oracle/admin/XFF/bdump/xff_smon_9775.trc Corrupt block relative dba: 0x0100001c (file 4, block 28) Fractured block found during buffer read Data in bad block: type: 6 format: 2 rdba: 0x0100001c last change scn: 0x0b2c.c02ab081 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xb0c4a6ea check value in block header: 0x393f computed block checksum: 0xc917 Reread of rdba: 0x0100001c (file 4, block 28) found same corrupted data ORACLE Instance XFF (pid = 8) - Error 1578 encountered while recovering transaction (9, 37) on object 53802. Sun Jan 20 23:04:38 2013 Errors in file /u01/oracle/admin/XFF/bdump/xff_smon_9775.trc: ORA-01578: ORACLE data block corrupted (file # 4, block # 28) ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'
通过试验步骤和alert日志可以发现因为block有事务,但是被标记为了坏块,所以smon无法回滚该事务,从而出现alert中类似提示
继续切换归档
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 252 Next log sequence to archive 254 Current log sequence 254
移走归档
[oracle@xifenfei archivelog]$ ls -l total 2224 -rw-r----- 1 oracle oinstall 360960 Jan 20 22:59 1_247_792679299.dbf -rw-r----- 1 oracle oinstall 1024 Jan 20 22:59 1_248_792679299.dbf -rw-r----- 1 oracle oinstall 1630208 Jan 20 23:04 1_249_792679299.dbf -rw-r----- 1 oracle oinstall 249344 Jan 20 23:09 1_250_792679299.dbf -rw-r----- 1 oracle oinstall 1024 Jan 20 23:09 1_251_792679299.dbf -rw-r----- 1 oracle oinstall 4608 Jan 20 23:09 1_252_792679299.dbf -rw-r----- 1 oracle oinstall 1024 Jan 20 23:09 1_253_792679299.dbf [oracle@xifenfei archivelog]$ mkdir bak [oracle@xifenfei archivelog]$ mv *.dbf bak [oracle@xifenfei archivelog]$ ll total 4 drwxr-xr-x 2 oracle oinstall 4096 Jan 20 23:11 bak
为了重现,当我们使用blockrecover恢复的时候,如果缺少归档会怎么样
尝试blockrecover
RMAN> BLOCKRECOVER DATAFILE 4 block 28; Starting blockrecover at 20-JAN-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=135 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=126 devtype=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: sid=125 devtype=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: sid=124 devtype=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00004 channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/xff_4.rman channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=/u01/oracle/oradata/xff_4.rman tag=TAG20130120T222333 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of blockrecover command at 01/20/2013 23:11:41 RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of log thread 1 seq 253 lowscn 12286830510311 found to restore RMAN-06025: no backup of log thread 1 seq 252 lowscn 12286830510307 found to restore RMAN-06025: no backup of log thread 1 seq 251 lowscn 12286830510305 found to restore RMAN-06025: no backup of log thread 1 seq 250 lowscn 12286830509979 found to restore RMAN-06025: no backup of log thread 1 seq 249 lowscn 12286830489543 found to restore RMAN-06025: no backup of log thread 1 seq 248 lowscn 12286830489541 found to restore RMAN-06025: no backup of log thread 1 seq 247 lowscn 12286830489279 found to restore
alert日志记录
Sun Jan 20 23:11:38 2013 alter database recover datafile list clear Sun Jan 20 23:11:38 2013 Completed: alter database recover datafile list clear Sun Jan 20 23:11:38 2013 Starting block media recovery Sun Jan 20 23:11:39 2013 Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_235_792679299.dbf Sun Jan 20 23:11:41 2013 alter database recover cancel Sun Jan 20 23:11:41 2013 Media Recovery Canceled Completed: alter database recover cancel
blockrecover恢复途中或者异常终止,dbv检测
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/XFF/users01.dbf DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:18:29 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf Page 28 is marked corrupt Corrupt block relative dba: 0x0100001c (file 4, block 28) Bad check value found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0100001c last change scn: 0x0b2c.c02b0248 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x02480601 check value in block header: 0x13fc computed block checksum: 0x663b DBVERIFY - Verification complete Total Pages Examined : 1280 Total Pages Processed (Data) : 903 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 38 Total Pages Failing (Index): 0 Total Pages Processed (Other): 47 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 291 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Highest block SCN : 3224022228 (2860.3224022228)
在blockrecover处理过程,或者处理失败的后,block依然是坏块,如果遇到这类情况,没有事务可以直接指定seq/scn/time的方法来恢复,如果有事务,需要指定恢复时间点过该事务的时间点,让smon能够正常回滚,从而使得smon进程正常工作
还原归档后继续测试
[oracle@xifenfei archivelog]$ mv bak/* ./ RMAN> BLOCKRECOVER DATAFILE 4 block 28; starting media recovery archive log thread 1 sequence 247 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_247_792679299.dbf ………… media recovery complete, elapsed time: 00:00:01 Finished blockrecover at 20-JAN-13 [oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/XFF/users01.dbf DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:15:43 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf DBVERIFY - Verification complete Total Pages Examined : 1280 Total Pages Processed (Data) : 904 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 38 Total Pages Failing (Index): 0 Total Pages Processed (Other): 47 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 291 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Highest block SCN : 3224023169 (2860.3224023169)
>
blockrecover处理alert日志
Sun Jan 20 23:15:01 2013 Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_250_792679299.dbf Sun Jan 20 23:15:01 2013 Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_251_792679299.dbf Sun Jan 20 23:15:01 2013 Recovery of Online Redo Log: Thread 1 Group 3 Seq 252 Reading mem 0 Mem# 0: /u01/oracle/oradata/XFF/redo03.log Sun Jan 20 23:15:01 2013 Recovery of Online Redo Log: Thread 1 Group 1 Seq 253 Reading mem 0 Mem# 0: /u01/oracle/oradata/XFF/redo01.log Sun Jan 20 23:15:01 2013 Recovery of Online Redo Log: Thread 1 Group 2 Seq 254 Reading mem 0 Mem# 0: /u01/oracle/oradata/XFF/redo02.log Sun Jan 20 23:15:02 2013 Completed block media recovery
补充说明
1.在9i中使用blockrecover会在Archive destination中生产block的备份文件,类似469_519791_3063_2442393528.bkd(file 469 block 519791),需要注意Archive destination目录结尾需要”/”,不然可能出现直接写入和该目录并列的Archive destination+blockrecover产生文件;10g中不生成该文件;如果该block在9i中未备份,也不会在生产相关文件,而是利用归档恢复.
2.blockrecover需要还原从该block从备份之后的所有归档(如果被备份起来需要还原出来),如果缺少归档可能导致恢复失败,包括基于scn/seq/time的等
3.blockrecover在执行过程中或者执行异常终止,该block依然是坏块,不会对其他block产生影响.在使用blockrecover出现异常终止后的block修复,在后续blog中提供解决方法
4.对于不含事务的坏块,如果数据允许丢失可以通过设置event跳过坏块(特殊block除外),然后重建对象;对于含event的block 坏块,建议使用blockrecover处理或者直接恢复数据文件,如果没有备份,考试使用event跳过事务回滚,然后屏蔽坏块处理