oracle索引数据块损坏,数据块损坏恢复总结

最新推荐文章于 2024-07-16 16:21:50 发布
最新推荐文章于 2024-07-16 16:21:50 发布
阅读量630 收藏
点赞数
文章标签： oracle索引数据块损坏

数据块恢复总结

一、说明

1.1、坏块故障现象

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 7, block # 19846)

ORA-01110: data file 7: '+DATA/orcl/datafile/jxc1.270.1010446343’

1.2、坏块类型数据坏块

索引坏块

其它损坏

1.3、检验坏块的方式

$dbv file=/opt/oracle/oradata/mycdb/system01.dbf

RMAN> validate database|datefile xx;

1.4、坏块处理

若有有效的rman备份则恢复语句如下：

recover datafile 7 block 19846;

recover corruption list; validate 检测后可用该语句进行恢复

若仅有数据泵备份，则从数据泵恢复该表数据到备份的状态。

impdp  directory=dump dumpfile=bfapp20.dump SCHEMAS=BFAPP20 include=table:"\=\'T_OBJ\'"

impdp  directory=dump dumpfile=bfapp20.dump tables=BFAPP20.T_OBJ

无备份，屏蔽掉坏块，拯救部分数据。

启用10231内部事件      alter system set events='10231 trace name context forever,level 10’;

关闭10231内部事件      alter system set events='10231 trace name context off’;

二、实战演练

2.1、构造数据损坏坏块

1、创建表插入数据

create table t as select object_id,object_name from dba_objects;

insert into t select * from t;

create index t_obj_id on t (object_id);

SQL> exec dbms_stats.gather_table_stats('ZX1','T',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

2.2、查询表占用块相关信息

SQL> select owner,segment_name,header_file,header_block,blocks from dba_segments where owner='ZX1' and segment_name='T';

OWNER      SEGMENT_NAME     HEADER_FILE HEADER_BLOCK  BLOCKS

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

ZX1             T         54       130     1024

SQL> select rowid,

dbms_rowid.rowid_relative_fno(rowid) rel_fno,

dbms_rowid.rowid_block_number(rowid) blockno,

dbms_rowid.rowid_row_number(rowid) rowno

from T WHERE ROWNUM<=10;

ROWID      REL_FNO BLOCKNO      ROWNO

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

AAAR88AA2AAAACDAAA   54     131  0

AAAR88AA2AAAACDAAB   54     131  1

AAAR88AA2AAAACDAAC   54     131  2

AAAR88AA2AAAACDAAD   54     131  3

AAAR88AA2AAAACDAAE   54     131  4

AAAR88AA2AAAACDAAF   54     131  5

AAAR88AA2AAAACDAAG   54     131  6

AAAR88AA2AAAACDAAH   54     131  7

AAAR88AA2AAAACDAAI   54     131  8

AAAR88AA2AAAACDAAJ   54     131  9

10 rows selected.

2.3、查询索引所在的块

SQL> select owner,segment_name,header_file,header_block,blocks from dba_segments where owner='ZX1' and segment_name='T_OBJ_ID';

OWNER        SEGMENT_NAME            HEADER_FILE  HEADER_BLOCK     BLOCKS

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

ZX1           T_OBJ_ID             54       1154          384

2.4、创建数据坏块

[oracle@oracle18c1 ~]$ rman target sys/Pa44w0rd@192.168.5.40:1521/mypdb1

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Jun 5 14:19:25 2020

Version 18.3.0.0.0

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

connected to target database: MYCDB:MYPDB1 (DBID=2302914177)

RMAN> blockrecover datafile 54 block 131 clear;

Starting recover at 05-JUN-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=209 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=204 device type=DISK

Finished recover at 05-JUN-20

2.5、创建索引坏块

RMAN> blockrecover datafile 54 block 1159 clear;

Starting recover at 05-JUN-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=147 device type=DISK

Finished recover at 05-JUN-20

2.6、查询触发数据坏块

SQL> select count(*) from zx1.t;

select count(*) from zx1.t

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 54, block # 131)

ORA-01110: data file 54: '/opt/oracle/oradata/mycdb/mypdb1/jxc.dbf'

2.7、查询触发索引坏块

SQL> select count(*) from zx1.t where object_id<=10000;

select count(*) from zx1.t where object_id<=10000

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 54, block # 1159)

ORA-01110: data file 54: '/opt/oracle/oradata/mycdb/mypdb1/jxc.dbf'

2.8、使用dbv工具检验坏块信息

[oracle@oracle18c1 ~]$ dbv file=/opt/oracle/oradata/mycdb/mypdb1/jxc.dbf

DBVERIFY: Release 18.0.0.0.0 - Production on Fri Jun 5 15:48:49 2020

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

DBVERIFY - Verification starting : FILE = /opt/oracle/oradata/mycdb/mypdb1/jxc.dbf

Page 131 is marked corrupt

Corrupt block relative dba: 0x0d800083 (file 54, block 131)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x0d800083

last change scn: 0x0000.0000.003a64ed seq: 0x2 flg: 0x04

spare3: 0x0

consistency value in tail: 0x64ed0602

check value in block header: 0xf584

computed block checksum: 0x6100

Page 1159 is marked corrupt

Corrupt block relative dba: 0x0d800487 (file 54, block 1159)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x0d800487

last change scn: 0x4700.0000.003a651a seq: 0x2 flg: 0x04

spare3: 0x0

consistency value in tail: 0x651a0602

check value in block header: 0x2fe7

computed block checksum: 0x4700

DBVERIFY - Verification complete

Total Pages Examined         : 128000

Total Pages Processed (Data) : 937

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 322

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 165

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 126574

Total Pages Marked Corrupt   : 2

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 3827002 (0.3827002)

2.9 validate 命令验证

[oracle@oracle18c1 ~]$ rman target sys/Pa44w0rd@192.168.5.40/mypdb1

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Jun 5 15:50:21 2020

Version 18.3.0.0.0

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

connected to target database: MYCDB:MYPDB1 (DBID=2302914177)

RMAN> validate datafile 54;

Starting validate at 05-JUN-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=213 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=10 device type=DISK

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00054 name=/opt/oracle/oradata/mycdb/mypdb1/jxc.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:15

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

54   FAILED 0              126574       128000          3827002

File Name: /opt/oracle/oradata/mycdb/mypdb1/jxc.dbf

Block Type Blocks Failing Blocks Processed

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

Data        1               938

Index       1               323

Other       0               165

validate found one or more corrupt blocks

See trace file /opt/oracle/diag/rdbms/mycdb/mycdb/trace/mycdb_ora_18140.trc for details

Finished validate at 05-JUN-20

注意：该视图的更新需要执行validate命令。

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE#     BLOCK#  BLOCKS CORRUPTION_CHANGE# CORRUPTIO  CON_ID

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

54      131       1    3826925          CHECKSUM       3

54     1159       1   5.1161E+18         CHECKSUM       3

2.10、索引坏块修复

SQL> alter index ZX1.T_OBJ_ID rebuild online;

Index altered.

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE#     BLOCK#  BLOCKS CORRUPTION_CHANGE#    CORRUPTIO   CON_ID

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

54       131       1     3826925      CHECKSUM     3

54       1159       1      5.1161E+18    CHECKSUM     3

SQL> select count(*) from ZX1.T where object_id<=20000;

COUNT(*)

----------

39534

2.11、数据坏块修复

1、无备份时处理

SQL> select count(*) from ZX1.T;

select count(*) from ZX1.T

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 54, block # 131)

ORA-01110: data file 54: '/opt/oracle/oradata/mycdb/mypdb1/jxc.dbf'

SQL> alter system set events='10231 trace name context forever,level 10';

System altered.

SQL> select count(*) from ZX1.T;

COUNT(*)

----------

145362

2、有rman备份时处理

[oracle@oracle18c1 ~]$ rman target sys/Pa44w0rd@192.168.5.40/mypdb1

Recovery Manager: Release 18.0.0.0.0 - Production on Fri Jun 5 16:19:30 2020

Version 18.3.0.0.0

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

connected to target database: MYCDB:MYPDB1 (DBID=2302914177)

RMAN> recover datafile 54 block 131;

Starting recover at 05-JUN-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=84 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=152 device type=DISK

channel ORA_DISK_1: restoring block(s)

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

restoring blocks of datafile 00054

channel ORA_DISK_1: reading from backup piece /backup/fullbk.1qv1vvtp_1_1

channel ORA_DISK_1: piece handle=/backup/fullbk.1qv1vvtp_1_1 tag=TAG20200605T112752

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

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

Finished recover at 05-JUN-20

RMAN> select count(*) from ZX1.T;

COUNT(*)

----------

145700

三、只有数据泵备份时处理方式

方式1

[oracle@oracle ~]$ impdp  directory=dump dumpfile=bfapp20.dump SCHEMAS=BFAPP20 include=table:"\=\'T_OBJ\'"

Import: Release 11.2.0.3.0 - Production on Wed Mar 25 19:47:03 2020

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_SCHEMA_01":  /******** AS SYSDBA directory=dump dumpfile=bfapp20.dump SCHEMAS=BFAPP20 include=table:\=\'T_OBJ\'

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "BFAPP20"."T_OBJ"                           2.385 MB   74914 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 19:47:10

方式2

[oracle@oracle ~]$ impdp  directory=dump dumpfile=bfapp20.dump tables=BFAPP20.T_OBJ

Import: Release 11.2.0.3.0 - Production on Wed Mar 25 19:47:35 2020

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA directory=dump dumpfile=bfapp20.dump tables=BFAPP20.T_OBJ

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "BFAPP20"."T_OBJ"                           2.385 MB   74914 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 19:47:42

头像收藏家
关注
ORA-01578 ORACLE data block corrupted
bruce
11-25 4125
用户连接到数据库执行存储过程时报坏块ORA-01578ORACLE data block corrupted错误
ora-01578: oracle 数据块损坏,Oracle ORA-01578坏块解决方法
weixin_36478445的博客
04-03 2110
本帖最后由 paulyi 于 2014-1-19 13:15 编辑一. 出问题时的情景我在自己测试数据库上给大表 tb_table导完数据后，建立索引时，出现以下错误ORA-01578: ORACLE data block corrupted (file # 7, block # 28969)ORA-01110: data file 7: 'D:\ORACLE\ORADATA\PAUL\GAM...
ORA-01578: ORACLE data block corrupted
Goolen的专栏
12-26 2960
今天在测试库上创建表的时候，报了个错误 SQL> create table abc as select * from dba_objects; create table abc as select * from dba_objects                                   * ERROR at line 1: ORA-01578: ORACLE data b
Oracle常见数据块损坏处理方式
最新发布
大龄IT民工
07-16 562
数据库根据块中存储的所有字节数计算出的数字，在dbwr写脏数据的时候，同时也将此数字写入数据块的头部，之后再读取该块时，会重新计算校验和，并与数据块头的值进行比较。db_block_checking的默认值是FALSE，如果设置为TRUE表示将会对所有的数据块进行检查；数据文件为二进制文件，通过vi命令修改其中的数据，注意在修改数据文件内容时，不要修改头部信息，精良修改中间部分。由于移动了基表，改变了原有的rowid的值，会导致索引失效。查看tbs02表空间对应的数据文件，进行相关的修改。
oracle索引文件坏块处理,Oracle 索引出现坏块处理
weixin_39980841的博客
04-04 257
SQL> create table test as select * from dba_objects where rownum<1001;Table created.SQL> create index idx_test on test(object_id);Index created.SQL> select file_id, block_id, blocks from d...
oracle索引逻辑损坏,ORACLE检查找出损坏索引(Corrupt Indexes)的方法详解
weixin_39590635的博客
04-03 283
索引索引与表一样，也属于段(segment)的一种。里面存放了用户的数据，跟表一样需要占用磁盘空间。索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针(地址)。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个...
oracle block corrupted,ORA-01578: ORACLE data block corrupted
weixin_33218578的博客
04-14 234
#### <> <> <000000> ORA-01110: data file 9: '/home/oracle/OraBase/oradata/QAWEB/datafile/o1_mf_wbxobj_l_5tk3oxx2_.dbf'java.lang.Throwable: ORA-01578: ORACLE data block corrupted...
ORA-01578 ORACLE 数据块损坏
qq_44906603的博客
04-28 1876
ORA-01578 ORACLE 数据块损坏
ORA-01578:Oracle data block corrupted
weixin_34413802的博客
01-29 266
ORA-01578:Oracledatablockcorrupted(file#num,block#num) 数据坏块数据仓库的解决方法分析产生这种问题的原因1、 十之八九这个Oracle的数据库server打开了异步I/O(async io)或增加了写进程。2、 硬件的I/O出现了错误。3、 操作系统的I/O或缓存出现我问题，比如操作系统对于异步I/O...
ORA-01578: ORACLE data block corrupted (file # 9, block # 45729)
weixin_34296641的博客
09-13 201
今天在实验的机子的oracle上的一张表中批量插入5000000条记录，在插入过程中途发生了表空间不够的情况，导致插入终止，在表空间上添加一个数据文件，对表中的数据进行查询，发生如下的错误： SQL> select count(*) from book; select count(*) from book ORA-01578: ORACLE data ...
Oracle数据块损坏：10231事件下的不完全恢复策略
总结来说，本文是关于在Oracle环境下处理数据块损坏的实战教程，特别关注10231内部事件引发的不完全恢复策略，对于数据库管理员和开发人员来说，提供了实用的指导和案例研究，以便在面对类似问题时能够更好地应对和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值