[20150811]模拟坏块处理.txt

[20150811]模拟坏块处理.txt

--如果存在备份,修复坏块还是相对简单的.在11g下:

select * from V$DATABASE_BLOCK_CORRUPTION;

--在rman下执行:
blockrecover corruption list;

--如果数据块没有使用,没有分配data_object_id而出现坏块,如何恢复呢?一般采用的方法建立新对象的方法,格式化这个数据块.
--具体测试如下:

1.建立测试环境:

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

CREATE TABLESPACE MSSM DATAFILE
  '/mnt/ramdisk/test/mssm01.dbf' SIZE 64M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

SCOTT@test> create table t tablespace mssm as select rownum id ,cast('testtesttesttest' as varchar2(20)) name from xmltable('1 to 400000');
Table created.

--这样建立文件大小12M。

--建立备份:
backup database format '/home/oracle/backup/full_%u';
backup datafile 6 format '/home/oracle/backup/DATAFILE6_%u' ;
backup as copy datafile 6  format '/home/oracle/backup/mssm01.dbf_copy' ;

$  cd /home/oracle/backup
$  mv mssm01.dbf_copy mssm01.dbf_copy_org
--delete force copy of datafile 6;

--修改block=3000的信息:
--3000*8192/1024/1024=23.4375,这样没有信息写在该块。

2.关闭数据库,破坏数据块:

SCOTT@test> @bbvi 6 3000
BVI_COMMAND
------------------------------------------------------
bvi -b 24576000 -s 8192 /mnt/ramdisk/test/mssm01.dbf

SCOTT@test> @convrdba.sql 6 3000
RDBA16                 RDBA
-------------- ------------
       1800bb8     25168824

$ bvi -b 24576000 -s 8192 /mnt/ramdisk/test/mssm01.dbf
01770000  00 A2 00 00 B8 0B 00 00 00 00 00 00 00 00 01 05 B8 AC 0
--将这些信息清零。
--我使用bvi,使用dd也可以.注意要加conv=notrunc参数.方向不要搞错。
--dd if=/dev/zero of=/mnt/ramdisk/test/mssm01.dbf bs=8192 seek=3000 conv=notrunc count=1

$  dbv file=/mnt/ramdisk/test/mssm01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 11 08:25:08 2015
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/test/mssm01.dbf
Page 3000 is influx - most likely media corrupt
Corrupt block relative dba: 0x01800bb8 (file 6, block 3000)
Fractured block found during dbv:
Data in bad block:
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000001
check value in block header: 0x0
block checksum disabled

DBVERIFY - Verification complete
Total Pages Examined         : 8192
Total Pages Processed (Data) : 1492
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 9
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 6690
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 1
Highest block SCN            : 4147024117 (2.4147024117)
--可以发现Corrupt block relative dba: 0x01800bb8 (file 6, block 3000)信息,表示存在坏块。

SCOTT@test> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
--可以发现视图V$DATABASE_BLOCK_CORRUPTION没有记录。

RMAN> backup validate datafile 6;
Starting backup at 2015-08-11 08:26:44
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/mnt/ramdisk/test/mssm01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-08-11 08:26:45

SCOTT@test> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
--10g下对这种情况没有记录。使用blockrecover corruption list;应该没用。使用dbv检查依旧。

RMAN> blockrecover corruption list;
Starting blockrecover at 2015-08-11 08:27:55
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished blockrecover at 2015-08-11 08:27:55

--直接指定数据文件以及对应块。
RMAN> blockrecover datafile 6 block 3000 ;
Starting blockrecover at 2015-08-11 08:29:32
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/DATAFILE6_10qeakdl
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/home/oracle/backup/DATAFILE6_10qeakdl tag=TAG20150811T081133
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

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

Finished blockrecover at 2015-08-11 08:29:36

$  dbv file=/mnt/ramdisk/test/mssm01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 11 08:30:21 2015
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/test/mssm01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 8192
Total Pages Processed (Data) : 1492
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 10
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 6690
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 4147024117 (2.4147024117)

--可以发现在有备份的情况下恢复实际上还是很简单的,因为V$DATABASE_BLOCK_CORRUPTION没有记录,使用blockrecover corruption list;不行。
--但是直接执行blockrecover datafile 6 block 3000 ;,还是能修复问题的。

3.重复采用别的方式:
--采用rman的方式破坏。这种方式的破坏是往块里面写入1堆垃圾(通过bvi观察),注意意后面的参数clear:
RMAN> blockrecover datafile 6 block 3000 clear ;
Starting blockrecover at 2015-08-11 08:37:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
Finished blockrecover at 2015-08-11 08:37:51

$  dbv file=/mnt/ramdisk/test/mssm01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 11 08:39:02 2015
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/test/mssm01.dbf
Page 3000 is marked corrupt
Corrupt block relative dba: 0x01800bb8 (file 6, block 3000)
Bad check value found during dbv:
Data in bad block:
type: 58 format: 2 rdba: 0x01800bb8
last change scn: 0x0002.f72e9086 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x90863a01
check value in block header: 0x612e
computed block checksum: 0xef7c

-这种情况下,如果做如下操作会报错:
RMAN> backup as copy datafile 6  format '/home/oracle/backup/mssm01.dbf_copy' ;

Starting backup at 2015-08-11 08:40:27
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/mnt/ramdisk/test/mssm01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/11/2015 08:40:28
ORA-19566: exceeded limit of 0 corrupt blocks for file /mnt/ramdisk/test/mssm01.dbf

SCOTT@test> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected

--依旧没有记录。因为在备份数据文件时使用copy方式要检查每个块,而backup datafile 6 仅仅备份有信息的块,这样使用copy方式会
--报错。

SCOTT@test> create table tx tablespace mssm as select * from t where 1=2;
Table created.

SCOTT@test> alter table tx allocate extent  (size 20M);
Table altered.

$  dbv file=/mnt/ramdisk/test/mssm01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 11 08:48:25 2015
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/test/mssm01.dbf
Page 3000 is marked corrupt
Corrupt block relative dba: 0x01800bb8 (file 6, block 3000)
Bad check value found during dbv:
Data in bad block:
type: 58 format: 2 rdba: 0x01800bb8
last change scn: 0x0002.f72e9086 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x90863a01
check value in block header: 0x612e
computed block checksum: 0xef7c

RMAN> backup datafile 6 format '/home/oracle/backup/DATAFILE6_%u' ;
Starting backup at 2015-08-11 08:51:53
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/mnt/ramdisk/test/mssm01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-08-11 08:51:53
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/11/2015 08:51:54
ORA-19566: exceeded limit of 0 corrupt blocks for file /mnt/ramdisk/test/mssm01.dbf
--这次报错。因为该块已经被tx占用。

SCOTT@test> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
--可以发现这样视图V$DATABASE_BLOCK_CORRUPTION没有记录。

RMAN> backup validate datafile 6;
Starting backup at 2015-08-11 08:52:10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/mnt/ramdisk/test/mssm01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-08-11 08:52:11

SCOTT@test> select * from V$DATABASE_BLOCK_CORRUPTION;
       FILE#       BLOCK#       BLOCKS CORRUPTION_CHANGE# CORRUPTIO
------------ ------------ ------------ ------------------ ---------
           6         3000            1                  0 CHECKSUM

--这次有记录了。

4.选择生成新数据覆盖坏块:
SCOTT@test> insert into tx values (null,null);
1 row created.

SCOTT@test> insert into tx values (null,null);
1 row created.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> ALTER TABLE tx MINIMIZE RECORDS_PER_BLOCK;
Table altered.

--3000*8192/1024/1024=23.4375, 24M位置。前面已经占用12M。
--(24-12)*1024*1024/8192=1536.
--这样写1536*2=3072条记录基本就覆盖有问题的数据块。而且这样写日志相对较小。

SCOTT@test> insert into tx select null,null from dual connect by level<=3100;
3100 rows created.

SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint;
System altered.

$  dbv file=/mnt/ramdisk/test/mssm01.dbf
DBVERIFY: Release 10.2.0.4.0 - Production on Tue Aug 11 09:01:10 2015
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/test/mssm01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 8192
Total Pages Processed (Data) : 3045
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 10
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 5137
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 4147026312 (2.4147026312)

SCOTT@test> set null NULL
SCOTT@test> select rowid,tx.* from tx where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=3000;
ROWID                        ID NAME
------------------ ------------ --------------------
AAAQCjAAGAAAAu4AAA NULL         NULL
AAAQCjAAGAAAAu4AAB NULL         NULL

--可以发现已经修复。

时间: 2024-10-05 15:49:49

[20150811]模拟坏块处理.txt的相关文章

[20160816]11G dataguard坏块修复.txt

[20160816]11G dataguard坏块修复.txt --11GR2 不仅仅支持在备库在只读的情况下,日志应用(ACTIVE Data Guard),还提供主备库的坏块修复.自己以前也做过相关测试, --我记得上次测试的仅仅是主库数据块损坏,没有测试备库的数据块损坏.补充一些测试: 1.环境: SYS@test> @ ver1 PORT_STRING                    VERSION        BANNER ---------------------------

[20160722]对象C_OBJ#_INTCOL#有坏块.txt

[20160722]对象C_OBJ#_INTCOL#有坏块.txt --前几天看到的帖子,一直没时间测试,链接如下: http://www.itpub.net/thread-2063836-1-1.html --我以前按照eygle的链接http://www.eygle.com/archives/2012/05/event_38003_c_obj_intcol.html做过测试,测试在11.2.0.2下做的. --通过设置alter system set event='38003 trace n

[20150601]rman备份出现坏块.txt

[20150601]rman备份出现坏块.txt --昨天看链接: http://www.jydba.net/磁盘损坏造成RMAN备份文件有坏块的恢复案例/ --提到如果备份片存在坏块的恢复案例,他使用的参数,我自己从来没见过. alter system set event='19548 trace name context forever', '19549 trace name context forever' scope=spfile; -- oerr ora 19548,oerr ora

[20121016]坏块处理以及数据恢复.txt

[20121016]坏块处理以及数据恢复.txt 如果产生坏块,在没有备份的情况下,如何最大程度的恢复信息,减少损失. SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Produ

[20140424]oracle的逻辑坏块.txt

[20140424]oracle的逻辑坏块.txt 今天上午本来想做一个11GR2的Automatic block media repair,链接如下:http://blog.itpub.net/267265/viewspace-1148315/ 但是我遇到一个奇怪的问题,检查和的计算问题: SYS@test> @ver BANNER --------------------------------------------------------------------------------

ORA-01578 坏块原因模拟及解决方法

  原创转载起注明出处   我们可能有时候会遇到如下错误 一.错误展示   SQL> select * from test123;   select * from test123   ORA-01578: ORACLE data block corrupted (file # 1, block # 61658) ORA-01110: data file 1: '/oracle_ocfs/oradata/pprac/system01.dbf' 二.原因     这样的情况下可能是因为底层的硬件问题

ORACLE坏块(ORA-01578)处理方法

oracle ORACLE的坏块即ORA-01578错,同时还可能伴随ORA-01110错,这种错误对于初学者或是那些没有实践经验的dba来说无疑是很棘手的.我当初就深受其害,写下这篇文章则是希望对大家有所帮助. 一.出问题时的情景 1.  我的一个计费的入库的进程停掉,报的便是ORA-01578错,对应用相关的表tg_bill03做SQL>select from tg_cdr03 where rownum<10;这样是可以的,但做SQL>select count(*) from tg_

如何在Linux上检测硬盘上的坏道和坏块

让我们从坏道和坏块的定义开始说起,它们是一块磁盘或闪存上不再能够被读写的部分,一般是由于磁盘表面特定的物理损坏或闪存晶体管失效导致的. 随着坏道的继续积累,它们会对你的磁盘或闪存容量产生令人不快或破坏性的影响,甚至可能会导致硬件失效. 同时还需要注意的是坏块的存在警示你应该开始考虑买块新磁盘了,或者简单地将坏块标记为不可用. 因此,在这篇文章中,我们通过几个必要的步骤,使用特定的磁盘扫描工具让你能够判断 Linux 磁盘或闪存是否存在坏道. 以下就是步骤: 在 Linux 上使用坏块工具检查坏道

Oracle 12C的第一次异常恢复—文件头坏块

接到第一个使用Oracle 12C作为生产库的恢复救援.有两个业务数据文件报文件头损坏,其他数据文件全部是9月份的一次备份,在当前的条件下,希望我们能够帮他们恢复出来业务文件中的数据数据库版本信息  代码如下 复制代码 SQL> select * from v$version; BANNER                                                                               CON_ID-------------------