首页 > 数据库 >Oracle常见数据块损坏处理方式

Oracle常见数据块损坏处理方式

时间：2024-07-16 17:26:14浏览次数：13  
标签：SYS 09 常见 损坏 orcl Oracle Total Pages block

1 前提：备份数据库

查看数据库的模式

SYS@orcl>select open_mode,log_mode from v$database;

OPEN_MODE	     LOG_MODE
-------------------- ------------
READ WRITE	     ARCHIVELOG

修改RMAN的备份参数

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> configure controlfile autobackup format for device type disk to '/tmp/backup/%F';

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/backup/cs_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/backup/%F';
new RMAN configuration parameters are successfully stored

创建测试表空间，并存放数据

SYS@orcl>create tablespace tbs02 datafile '/u01/app/oracle/oradata/orcl/tbs002.dbf' size 1m;

Tablespace created.

SYS@orcl>create table bruce.test01 tablespace tbs02 as select * from emp;

Table created.

SYS@orcl>select count(*) from bruce.test01;

  COUNT(*)
----------
	14

在RMAN下面备份数据库

RMAN> backup database format '/tmp/backup/%U' tag=bruce20221216;

Starting backup at 2022-12-16 11:13:48
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/tbs01_001.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00002 name=/u01/app/oracle/tbs003.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/tbs002.dbf
channel ORA_DISK_1: starting piece 1 at 2022-12-16 11:13:48
channel ORA_DISK_1: finished piece 1 at 2022-12-16 11:13:55
piece handle=/tmp/backup/141fh3vc_1_1 tag=BRUCE20221216 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2022-12-16 11:13:55

Starting Control File and SPFILE Autobackup at 2022-12-16 11:13:55
piece handle=/tmp/backup/c-1648706630-20221216-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2022-12-16 11:13:56

查看备份

RMAN> list backup of database;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
33      Full    1.23G      DISK        00:00:03     2022-12-16 11:13:51
        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: BRUCE20221216
        Piece Name: /tmp/backup/141fh3vc_1_1
  List of Datafiles in backup set 33
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1       Full 3900401    2022-12-16 11:13:48              NO    /u01/app/oracle/oradata/orcl/system01.dbf
  2       Full 3900401    2022-12-16 11:13:48              NO    /u01/app/oracle/tbs003.dbf
  3       Full 3900401    2022-12-16 11:13:48              NO    /u01/app/oracle/oradata/orcl/sysaux01.dbf
  4       Full 3900401    2022-12-16 11:13:48              NO    /u01/app/oracle/oradata/orcl/undotbs01.dbf
  5       Full 3900401    2022-12-16 11:13:48              NO    /u01/app/oracle/oradata/orcl/tbs01_001.dbf
  7       Full 3900401    2022-12-16 11:13:48              NO    /u01/app/oracle/oradata/orcl/users01.dbf
  8       Full 3900401    2022-12-16 11:13:48              NO    /u01/app/oracle/oradata/orcl/tbs002.dbf

RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
34      Full    10.19M     DISK        00:00:00     2022-12-16 11:13:55
        BP Key: 34   Status: AVAILABLE  Compressed: NO  Tag: TAG20221216T111355
        Piece Name: /tmp/backup/c-1648706630-20221216-03
  Control File Included: Ckp SCN: 3900420      Ckp time: 2022-12-16 11:13:55

RMAN> list backup of spfile;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
34      Full    10.19M     DISK        00:00:00     2022-12-16 11:13:55
        BP Key: 34   Status: AVAILABLE  Compressed: NO  Tag: TAG20221216T111355
        Piece Name: /tmp/backup/c-1648706630-20221216-03
  SPFILE Included: Modification time: 2022-12-16 09:47:46
  SPFILE db_unique_name: ORCL

--备份文件--

[root@ora-server backup]# pwd 
/tmp/backup
[root@ora-server backup]# ll
total 1299680
-rw-r----- 1 oracle oinstall 1320173568 Dec 16 11:13 141fh3vc_1_1
-rw-r----- 1 oracle oinstall   10698752 Dec 16 11:13 c-1648706630-20221216-03

2 故障恢复

2.1 制造故障

查看tbs02表空间对应的数据文件，进行相关的修改。

数据文件为二进制文件，通过vi命令修改其中的数据，注意在修改数据文件内容时，不要修改头部信息，精良修改中间部分。

2.2 dbverify命令

检查数据文件是否存在坏块：

[oracle@ora-server orcl]$ dbv file=/u01/app/oracle/oradata/orcl/tbs001.dbf blocksize=8192

DBVERIFY: Release 12.2.0.1.0 - Production on Fri Dec 30 10:28:11 2022

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/tbs001.dbf
Page 91 is marked corrupt
Corrupt block relative dba: 0x0240005b (file 9, block 91)
Bad check value found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x0240005b
 last change scn: 0x0000.0000.004688dd seq: 0x1 flg: 0x06
 spare3: 0x0
 consistency value in tail: 0x88dd0601
 check value in block header: 0x716f
 computed block checksum: 0x6000

DBVERIFY - Verification complete

Total Pages Examined         : 128
Total Pages Processed (Data) : 109
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 17
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 4622557 (0.4622557)

输出显示文件4的第45844个块出现坏块，dbv命令只能检测，不能修复。

2.3 blockrecover命令

在RMAN下使用blockrecover命令对坏块进行修复

RMAN> blockrecover datafile 9 block 91;

Starting recover at 30-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1163 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=8 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=396 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=783 device type=DISK
searching flashback logs for block images
finished flashback log search, restored 1 blocks

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

Finished recover at 30-DEC-22

退出RMAN后再检查

[oracle@ora-server ~]$ dbv file=/u01/app/oracle/oradata/orcl/tbs001.dbf blocksize=8192

DBVERIFY: Release 12.2.0.1.0 - Production on Fri Dec 30 11:05:45 2022

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/tbs001.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 128
Total Pages Processed (Data) : 110
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 17
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 4622557 (0.4622557)

2.4 analyze命令

通过analyze命令对表和索引的匹配情况进行逻辑检查，只报告错误信息，不标示坏块

  1. 创建测试表test01，并添加响应的索引信息
SYS@orcl>create table bruce.test01 tablespace users as select * from emp;

Table created.

SYS@orcl>create index bruce.ind_01 on bruce.test01(empno) tablespace users;

Index created.
  1. 使用analyze命令检测表和索引的匹配情况
SYS@orcl>analyze table bruce.test01 validate structure cascade;

Table analyzed.

SYS@orcl>analyze index bruce.ind_01 validate structure;

Index analyzed.
  1. 将测试表移动一下
SYS@orcl>alter table bruce.test01 move;
  1. 再次检测表和索引的匹配情况
09:14:01 SYS@orcl>analyze index bruce.ind_01 validate structure;  
analyze index bruce.ind_1 validate structure
*
ERROR at line 1:
ORA-01502: index 'BRUCE.IND_01' or partition of such index is in unusable state

09:16:13 SYS@orcl>analyze table bruce.test01 validate structure cascade;
analyze table bruce.test01 validate structure cascade
*
ERROR at line 1:
ORA-01502: index 'BRUCE.IND_01' or partition of such index is in unusable state

由于移动了基表，改变了原有的rowid的值，会导致索引失效

  1. 解决：将索引删除后重建，或者重新编译一下
09:24:13 SYS@orcl>alter index bruce.ind_01 rebuild;

Index altered.
  1. 再次检查
09:25:14 SYS@orcl>analyze table bruce.test01 validate structure cascade;

Table analyzed.

09:25:45 SYS@orcl>analyze index bruce.ind_01 validate structure;

Index analyzed.

2.5 数据库初始化参数

对于参数db_block_checking和db_block_checksum的设定

db_block_checking的默认值是FALSE，如果设置为TRUE表示将会对所有的数据块进行检查；数据库通过读取该块，来确认数据块的自我一致性；根据数据库的工作负载，一般会产生1%~10%的开销。
参数有以下可能的值：

  1. off，除system表空间之外，任何表空间中都不执行块检查
  2. low，在内存中的块的内容发生变化之后，进行基本的块头检查
  3. medium，执行所有的low检查，并对所有不是以索引组织的表执行块检查
  4. full，执行所有的low和medium检查，以及索引块的检查

默认的值：

09:26:04 SYS@orcl>show parameter db_block_checking

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_block_checking		     string	 FALSE

此参数可以使用alter session或者alter system的命令进行修改

db_block_checksum，校验和，默认值是TRUE。数据库根据块中存储的所有字节数计算出的数字，在dbwr写脏数据的时候，同时也将此数字写入数据块的头部，之后再读取该块时，会重新计算校验和，并与数据块头的值进行比较。一般会对数据库造成1%~2%的开销，Oracle建议开启此参数。

09:34:05 SYS@orcl>show parameter db_block_checksum;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum		     string	 TYPICAL

2.6 dbms_repair的使用

09:37:28 SYS@orcl>create tablespace block datafile '/u01/app/oracle/oradata/orcl/block.dbf' size 1m;

Tablespace created.

09:37:48 SYS@orcl>grant dba to bruce identified by 123456;

Grant succeeded.

09:38:09 SYS@orcl>conn bruce/123456
Connected.

09:38:37 BRUCE@orcl>create table test tablespace block as select * from bruce.test01;

Table created.

09:38:53 BRUCE@orcl>insert into test select * from test;

14 rows created.

09:39:14 BRUCE@orcl>/

28 rows created.

09:39:21 BRUCE@orcl>/

56 rows created.

09:39:22 BRUCE@orcl>/

112 rows created.

09:39:23 BRUCE@orcl>/

224 rows created.

09:39:25 BRUCE@orcl>/

448 rows created.

09:39:27 BRUCE@orcl>/

896 rows created.

09:39:35 BRUCE@orcl>/

1792 rows created.

09:39:36 BRUCE@orcl>/

3584 rows created.

09:39:38 BRUCE@orcl>/

7168 rows created.

09:39:39 BRUCE@orcl>/
insert into test select * from test
*
ERROR at line 1:
ORA-01653: unable to extend table BRUCE.TEST by 8 in tablespace BLOCK

09:39:42 BRUCE@orcl>commit;

Commit complete.

09:39:53 BRUCE@orcl>select count(*) from test;

  COUNT(*)
----------
     14336

09:40:04 BRUCE@orcl>create index i_test on test(ename);

Index created.

09:40:17 BRUCE@orcl>alter system checkpoint;

System altered.
  1. 模拟数据块损坏
09:40:33 BRUCE@orcl>conn / as sysdba
Connected.
09:40:40 SYS@orcl>shutdown immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
09:41:09 SYS@orcl>

--使用UltraEdit修改block.dbf后再启动数据库

10:06:21 SYS@orcl>startup
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size		    8621376 bytes
Variable Size		 1459618496 bytes
Database Buffers	  184549376 bytes
Redo Buffers		    8155136 bytes
Database mounted.
Database opened.
10:26:58 SYS@orcl>select count(*) from bruce.test;
select count(*) from bruce.test
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 91)		--提示有坏块
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/block.dbf'
  1. 通过dbv进行数据文件检查
[oracle@ora-server orcl]$ dbv file=/u01/app/oracle/oradata/orcl/block.dbf blocksize=8192

DBVERIFY: Release 12.2.0.1.0 - Production on Fri Dec 30 10:28:11 2022

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/block.dbf
Page 91 is marked corrupt
Corrupt block relative dba: 0x0240005b (file 9, block 91)
Bad check value found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x0240005b
 last change scn: 0x0000.0000.004688dd seq: 0x1 flg: 0x06
 spare3: 0x0
 consistency value in tail: 0x88dd0601
 check value in block header: 0x716f
 computed block checksum: 0x6000

DBVERIFY - Verification complete

Total Pages Examined         : 128
Total Pages Processed (Data) : 109
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 17
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 4622557 (0.4622557)
  1. 创建管理表，用于标识坏块信息
--表数据
10:27:20 SYS@orcl>exec DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE',1,1,'USERS');

PL/SQL procedure successfully completed.

--索引数据
10:36:30 SYS@orcl>exec DBMS_REPAIR.ADMIN_TABLES('ORPHAN_TABLE',2,1,'USERS');

PL/SQL procedure successfully completed.
  1. 检查坏块情况
10:37:24 SYS@orcl>set serveroutput on
10:43:15 SYS@orcl>declare
10:43:31   2  cc number;
10:43:35   3  begin
10:43:38   4  dbms_repair.check_object(schema_name => 'BRUCE',object_name => 'TEST',corrupt_count => cc);
10:44:06   5  dbms_output.put_line(a => to_char(cc));
10:44:27   6  end;
10:44:30   7  /
1

PL/SQL procedure successfully completed.
  1. 确认坏块标示情况
10:47:23 SYS@orcl>select object_name,relative_file_id,block_id,marked_corrupt,corrupt_description,repair_description,CHECK_TIMESTAMP from repair_table;

OBJECT_NAME															 RELATIVE_FILE_ID   BLOCK_ID MARKED_COR
-------------------------------------------------------------------------------------------------------------------------------- ---------------- ---------- ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CHECK_TIM
---------
TEST																        91 TRUE

mark block software corrupt
30-DEC-22
  1. 跳过坏块
10:50:45 SYS@orcl>exec dbms_repair.skip_corrupt_blocks(schema_name => 'BRUCE',object_name => 'TEST',flags => 1);

PL/SQL procedure successfully completed.

10:51:18 SYS@orcl>select count(*) from bruce.test;

  COUNT(*)
----------
     14166

--少了14336-14166=170行数据
  1. 处理index上的无效键值
10:57:39 SYS@orcl>declare
10:59:30   2  cc number;
10:59:34   3  begin
10:59:37   4  dbms_repair.dump_orphan_keys(schema_name => 'BRUCE',object_name => 'I_TEST',object_type => 2,
11:00:18   5  repair_table_name => 'REPAIR_TABLE',orphan_table_name => 'ORPHAN_TABLE',key_count => CC);
11:00:23   6  end;
11:00:26   7  /

PL/SQL procedure successfully completed.

11:01:13 SYS@orcl>select * from orphan_table;

...

170 rows selected.

标签：SYS,09,常见,损坏,orcl,Oracle,Total,Pages,block
From： https://blog.csdn.net/wuxiaobing1234/article/details/140469704

相关文章

  • 无人机区域常见名词
    融合空域是指有其他航空器同时运行的空域。隔离空域是指专门分配给无人机系统运行的空域，通过限制其他航空器的进入以规避碰撞风险。人口稠密区是指城镇、村庄、繁忙道路或大型露天集会场所等区域。重点地区是指军事重地、核电站和行政中心等关乎国家安全的区域及周边，或......
  • Visual Studio使用——vs使用过程中常见问题积累，技巧集锦等，持续更新中
    目录引出VisualStudio使用自定义代码片段vs显示所有文件总结Idea安装和使用0.Java下载和IDEA工具1.首次新建项目2.隐藏文件不必要显示文件3.目录层级设置4.Settings设置选择idea的场景提示代码不区分大小写取消git的代码作者显示引出VisualStudio使用——vs......
  • 存储系列DAS,SAN,NAS常见网络架构
    随着主机、磁盘、网络等技术的发展，对于承载大量数据存储的服务器来说，服务器内置存储空间，或者说内置磁盘往往不足以满足存储需要。因此，在内置存储之外，服务器需要采用外置存储的方式扩展存储空间，今天在这里我们分析一下当前主流的存储架构。一、DASDirectAttachedStorage，直接连......
  • Oracle数据库概述
    １oracle的数据模式是：用户建在数据库实例上，表建在用户上   不同数据库实例可以建相同的用户；  同一个数据库实例，用户名不能相同；  一个用户可以使用多个表空间；  一个表空间可以被多个用户所使用；  表的权限是和用户绑定的；  表空间不涉及任何权限控制问题； ......
  • 硬盘故障的常见原因是什么
    一、物理损坏机械故障：硬盘的机械部件，如磁头、盘片和马达等，可能出现故障。这些故障可能由磁头损坏、盘片划伤、马达故障等引起，导致硬盘无法正常读取或写入数据。这类故障通常由制造缺陷、老化或不当操作（如意外碰撞）引起，物理损坏往往意味着数据恢复变得非常困难。电路故障：硬盘的电......
  • oracle 查询锁 && 解锁
    目录oracle查询锁&&解锁查询数据库中的锁查询被锁的对象查询阻塞查询数据库正在等待锁的进程查询会话之间锁等待的关系查询锁等待事件解决方案：oracle查询锁&&解锁查询数据库中的锁select*fromv$lock;select*fromv$lockwhereblock=1;查询被锁的对象select*fr......
  • php函数入门学习（数组常见函数2 & 文件基础读写）
    //1、写一个函数，传入数组，返回数组中元素为数字且大于10的新结果数组functiongetNumArr($arr){  if(!$arr)return'请传入数组';  $arr2=array_filter($arr,function($v){    //echogettype($v)."<br>";    returngettype($v)==='inte......
  • Oracle 18c&19c physical dg切换总结
    这篇文章总结Oracle18c/19cPhysicalStandbyDG的主备切换的操作流程,主要参考官方文档18c&19cPhysicalStandbySwitchoverBestPracticesusingSQL*Plus(DocID2485237.1)[1].由于参考官方的最佳实践，所以有些步骤/过程略显繁琐。其实正常情况下，这里面的很多步骤都可以......
  • 常见的缓存问题
    常见缓存问题：1.缓存穿透2.缓存击穿3.缓存雪崩缓存穿透缓存穿透：是指客户端请求的数据在缓存中都不存在，这样缓存永远也不会生效，这些请求都会打到数据库。缓存穿透常见的解决方案有两种：缓存空对象优点：实现简单，维护方便缺点：额外的内存消耗，可能造成短期不一致。缓存穿透的......
  • 满满干活-wireshark进阶篇《Wireshark的TCP协议数据包常见报错提示》，练就你火眼金睛，助
    文章目录概要1.TCPPORTNUMBERSREUSED当四元组相同时会出现报错2.TCPWINDOWFULL与TCPZEROWINDOW之你怎么还不回我啊与我不行了。3.TCPWindowUpdate之我又行了能工作了小结概要本文介绍Wireshark的数据包常见报错提示，包括TCPPORTNUMBERSREUSED和TCPW......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99