mysql跳过坏块_跳过rman坏块恢复 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - 惜分飞...

在有些情况下,我们仅有一份rman备份,而这个时候rman 备份有出现坏块,使得我们的还原/恢复工作无法继续下去,导致数据大量丢失.我们可以通过设置event 19548/19549来跳过坏块,最大程度抢救数据

rman备份数据文件

C:\Users\XIFENFEI>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 6 20:31:19 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: XIFENFEI (DBID=1422012639)

RMAN> backup tablespace users format 'f:/users_bak.rman';

Starting backup at 06-JUN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=197 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 06-JUN-13

channel ORA_DISK_1: finished piece 1 at 06-JUN-13

piece handle=F:\USERS_BAK.RMAN tag=TAG20130606T203154 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 06-JUN-13

切换归档日志

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination E:\oracle\product\11.2.0\dbhome_1\RDBMS

Oldest online log sequence 95

Next log sequence to archive 97

Current log sequence 97

重命名数据文件

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

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

e:\oracle\oradata\XIFENFEI>move USERS01.DBF USERS01_bak.DBF

移动了 1 个文件。

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

SQL> startup

ORACLE instance started.

Total System Global Area 418484224 bytes

Fixed Size 1385052 bytes

Variable Size 327159204 bytes

Database Buffers 83886080 bytes

Redo Buffers 6053888 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF'

破坏备份集

破坏前

破坏后

这里很明显,我通过ue把rman备份集中的T修改为了A,肯定破坏了文件,使之出现坏块

rman还原数据文件

C:\Users\XIFENFEI>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 6 21:02:41 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: XIFENFEI (DBID=1422012639, not open)

RMAN> restore datafile 4;

Starting restore at 06-JUN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to E:\ORACLE\ORADATA\XIFENFEI\USERS

01.DBF

channel ORA_DISK_1: reading from backup piece F:\USERS_BAK.RMAN

channel ORA_DISK_1: ORA-19870: error while restoring backup piece F:\USERS_BAK.R

MAN

ORA-19612: datafile 4 not restored due to missing or corrupt data

failover to previous backup

creating datafile file number=4 name=E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF

Finished restore at 06-JUN-13

这里可以清晰的看到rman报ORA-19612错误，restore 失败,alert日志为:

Thu Jun 06 21:02:31 2013

ALTER DATABASE OPEN

Errors in file E:\ORACLE\diag\rdbms\xifenfei\xff\trace\xff_dbw0_7400.trc:

ORA-01157: ????/?????? 4 - ??? DBWR ????

ORA-01110: ???? 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF'

ORA-27041: ??????

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

Errors in file E:\ORACLE\diag\rdbms\xifenfei\xff\trace\xff_ora_4272.trc:

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF'

ORA-1157 signalled during: ALTER DATABASE OPEN...

Thu Jun 06 21:02:33 2013

Checker run found 1 new persistent data failures

Thu Jun 06 21:03:23 2013

Corrupt block 101 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=3

Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data

Continuing reading piece F:\USERS_BAK.RMAN, no other copies available.

rman备份集有坏块，导致rman还原无法正常进行下去,还原后的数据文件大小

观察已经正常还原出来数据文件情况

SQL> select CHECKPOINT_CHANGE#,file# from v$datafile_header;

CHECKPOINT_CHANGE# FILE#

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

1571582 1

1571582 2

1571582 3

18379 4

1571582 5

1571582 6

1571582 7

SQL> recover database datafile 4 ;

ORA-00274: illegal recovery option DATAFILE

SQL> recover datafile 4;

ORA-00279: change 18379 generated at 01/20/2013 17:13:56 needed for thread 1

ORA-00289: suggestion :

E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000001_0805223583.0001

ORA-00280: change 18379 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

rman只是还原了很小的一部分文件,做恢复提示需要从归档日志seq 1开始(某些情况可能需要其他归档,总之不是正常情况),证明rman还原异常

设置event事件还原

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup pfile='e:/pfile.txt' mount;

ORACLE instance started.

Total System Global Area 418484224 bytes

Fixed Size 1385052 bytes

Variable Size 327159204 bytes

Database Buffers 83886080 bytes

Redo Buffers 6053888 bytes

Database mounted.

SQL> show parameter event;

NAME TYPE VALUE

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

event string 19548 trace name context forev

er, 19549 trace name context f

orever

Event 19548:This will attempt to restore content of the corrupted block if it is possible.

Event 19549:This will suppress erroring out during restore

rman还原数据文件

RMAN> restore datafile 4;

Starting restore at 06-JUN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to E:\ORACLE\ORADATA\XIFENFEI\USERS

01.DBF

channel ORA_DISK_1: reading from backup piece F:\USERS_BAK.RMAN

channel ORA_DISK_1: piece handle=F:\USERS_BAK.RMAN tag=TAG20130606T203154

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 06-JUN-13

这里证明数据库rman有坏块通过rman还原成功,alert日志提示如下

Thu Jun 06 21:29:53 2013

WARNING: The block that appears to be block number 100

in file 4 is corrupt in backup piece F:\USERS_BAK.RMAN.

Such blocks would usually be formatted as empty

in the restored file, but event 19548 has been

set to include the block as-is in the restored

file.

Corrupt block 102 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=-2

Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data

Continuing reading piece F:\USERS_BAK.RMAN, no other copies available.

…………

Corrupt block 258 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=-2

Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data

Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data

Continuing reading piece F:\USERS_BAK.RMAN, no other copies available.

WARNING: some data in the backup of file 4 was missing

or corrupt. Event 19549 has been set to allow

the file to be restored anyway.

backup header block count: 5369

backup actual block count: 5212

backup header checksum: -218250743

backup actual checksum: 1442665538

Full restore complete of datafile 4 E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF. Elapsed time: 0:00:25

checkpoint is 1570136

last deallocation scn is 1508457

这里rman还原依然遇到很多坏块,但是均跳过坏块,还是完整的恢复出来的数据文件(大小)

rman还原数据文件

RMAN> recover datafile 4;

Starting recover at 06-JUN-13

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 94 is already on disk as file E:\ORACLE\

PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000094_0805223583.0001

archived log for thread 1 with sequence 95 is already on disk as file E:\ORACLE\

PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000095_0805223583.0001

archived log for thread 1 with sequence 96 is already on disk as file E:\ORACLE\

PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000096_0805223583.0001

archived log file name=E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000094_080

5223583.0001 thread=1 sequence=94

media recovery complete, elapsed time: 00:00:00

Finished recover at 06-JUN-13

这里可以明显的看到在recover过程中数据库应用的是备份后的所有归档,数据文件是正常被还原出来(坏块除外)

查询对象

SQL> alter database open;

Database altered.

SQL> conn test/test

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

STB101 TABLE

SQL> select count(*) from stb101;

select count(*) from stb101

*

ERROR at line 1:

ORA-08103: object no longer exists

dbv检查坏块

e:\oracle\oradata\XIFENFEI>dbv file=USERS01.DBF

DBVERIFY: Release 11.2.0.3.0 - Production on Thu Jun 6 23:59:49 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

DBVERIFY - Verification starting : FILE = E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF

Page 100 is marked corrupt

Corrupt block relative dba: 0x01000064 (file 4, block 100)

Bad check value found during dbv:

Data in bad block:

type: 30 format: 2 rdba: 0x01000064

last change scn: 0x0000.00004890 seq: 0x1 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x48901e01

check value in block header: 0x8311

computed block checksum: 0x20

DBVERIFY - Verification complete

Total Pages Examined : 12320

Total Pages Processed (Data) : 4952

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 7069

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 298

证明设置了event之后，rman确实跳过了备份集中的坏块,而且是直接还原了坏块内容,证明了event 19548和19549作用

补充说明

在非特殊情况下强烈不建议设置相关event跳过rman中的坏块来还原/恢复数据库,这样将对数据的丢失,甚至数据库是否可以正常open不好评估,rman备份重要,确保rman备份可用也很重要.