暂无图片
暂无图片
6
暂无图片
暂无图片
暂无图片

关于Oracle数据库损坏所有相关问题及处理方法

原创 _ 云和恩墨 2022-12-02
1771

一、损坏分类

1.1、物理坏块
  • 块断裂
    主要指数据块头部和尾部的SCN不一致导致
    块头SCNBase 和seq --bas_kcbh偏移量8,seq_kcbh偏移量14
    块尾SCNBase和seq --tailchk 偏移量8188 由SCNBase的低位两个字节加上块类型加上SCN序列号组成,报错信息如下:

    Corrupt block relative dba: 0x0380e573 (file 14, block 58739)
    Fractured block found during buffer read   --检测到块断裂
    Data in bad block -
    type: 6 format: 2 rdba: 0x0380e573
    last change scn: 0x0288.8e5a2f78 seq: 0x1 flg: 0x04
    consistency value in tail: 0x00780601      --块尾计算值
    check value in block header: 0x8739, computed block checksum: 0x2f00  --块头0x8739,校验错误
    spare1: 0x0, spare2: 0x0, spare3: 0x0
    ***
    Reread of rdba: 0x0380e573 (file 14, block 58739) found same corrupted data
    
  • 错误校验不一致
    块校验用于确定最后一次由Oracle写入之后,块是否由Oracle外部的东西更改。校验在将块写入磁盘之前由DBWR或direct load计算,并存储在块头中。
    每次读取块时,如果参数db_block_checksum参数不为false,Oracle都会计算一个校验和,并将其与存储在块头中的校验和进行比较。常见报错:

Corrupt block relative dba: 0x0380a58f (file 14, block 42383)
Bad check value found during buffer read                              --校验错误
Data in bad block -
type: 6 format: 2 rdba: 0x0380a58f
last change scn: 0x0288.7784c5ee seq: 0x1 flg: 0x06
consistency value in tail: 0xc5ee0601
check value in block header: 0x68a7, computed block checksum: 0x2f00  --校验值不一致
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380a58f (file 14, block 42383) found same corrupted data
  • 块错位
    当 Oracle 检测到正在读取的块的内容属于不同的块并且校验有效时,实例错误如下
Corrupt block relative dba: 0x0d805a89 (file 54, block 23177)
Bad header found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0d805b08 ---->  与头部0x0d805a89不一致
last change scn: 0x0692.86dc08e3 seq: 0x1 flg: 0x04
consistency value in tail: 0x08e30601
check value in block header: 0x2a6e, computed block checksum: 0x0 --但是校验值一直
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

物理损坏的块也称为媒体损坏块。媒体损坏块不是软损坏块

1.2、逻辑块损坏

当包含校验有效校验,但是块开头下方的结构已损坏(块内容已损坏)时。它可能会导致不同的 ORA-600 错误。逻辑损坏的详细损坏描述通常不会打印在 alert.log 中。DBVerify 检查会报告块中逻辑损坏的内容。常见逻辑损坏如下:

  • 行被不存在的事务锁定 - ORA-600 [4512] 等

  • 使用的空间量不等于块大小

  • 坏块

    当启用 db_block_checking 时,它可能会产生内部错误 ORA-600 [kddummy_blkchk] 或 ORA-600 [kdBlkCheckError]。如果启用了 db_block_checking 并且该块在磁盘上已经逻辑损坏,则下一次块更新会将块标记为,并且以后对该块的读取将产生错误 ORA-1578。在这种情况下,DBVerify 会报告此损坏并显示如下错误

    DBV-200: Block, dba <rdba>, already marked corrupted
    
1.3、软损坏块
  • 什么是软损坏
    • 由逻辑损坏标记为损坏
    • 试图在缓冲区缓存中恢复但无法恢复的块。下一条消息指示将块标记为内存中的软损坏,因为自动块恢复无法恢复该块:
"Block recovery logically corrupted file .. block ..."
  • 标记软损坏

    • 启用db_block_checking后,Oracle在修改已损坏的块时会对其进行软损坏(映像之前的块已损坏,这意味着该块可能已在DISK上损坏)。

    • 当块修改期间进程失败后自动块恢复无法恢复块时Block recovery logically corrupted file … block出现在alert日志中。

    • dbms_repair.FIX_CORRUPT_BLOCKS 也可用于将逻辑损坏块标记为软损坏

    • 如果块已经物理损坏,则介质恢复也可以软损坏块

随后的块读取会产生 ORA-1578 而不是内部错误

  • 为什么一个块被标记为软损坏

    • 块被标记为软损坏以防止其他内部错误并防止数据库中的进一步损坏。当一个块在逻辑上损坏时,它可能会导致 ORA-600 / ORA-7445 错误,并且当损坏的块被修改时,可能会对 Oracle 缓冲区缓存内存中的其他块产生进一步的损坏。将块标记为软损坏可防止 DML SQL 语句修改块。当块被标记为软损坏时,SQL 语句在读取块时会失败并出现错误 ORA-1578。

    • 块也被标记为软损坏时,可以使用事件 10231 或使用过程 dbms_repair.SKIP_CORRUPT_BLOCKS可以跳过它

    • 使用 TDE(透明数据加密 - 表空间加密)时,如果使用了不正确或无效的钱包,块可能会被标记为软损坏。请注意,对于这种情况,只要使用了无效的钱包,该块在内存中就只是软损坏(dbverify 不会产生错误,因为该块在磁盘上不是软损坏)。

  • 标记为软损坏的行为

    • 当一个块被标记为软损坏时,除非使用dbms_repair.SKIP_CORRUPT_BLOCKS或事件10231/10233,否则SQL语句在读取该块时会失败并出现错误RA-1578。

    • RMAN 备份不会因软损坏块而失败。RMAN 在备份期间不设置 MAXCORRUPT 子句将忽略“软损坏”块。

    • 介质恢复(前滚)忽略软损坏块。一般来说,介质恢复会忽略物理损坏块和软损坏块。当块在逻辑上损坏时,介质恢复可能会因意外错误而失败。当块是物理损坏时,介质恢复将块标记为软损坏,恢复继续进行,没有错误(跳过损坏的块)。

    • RMAN 验证不报告跟踪文件中的软损坏块,而是在v$database_block_corruption中报告软损坏块。

    SQL> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
          1      59744          1                  0 CORRUPT
    
    • dbv验证时报错DBV-200错误
    DBV-00200: Block, dba <rdba>, already marked corrupted
    

二、如何识别数据库中的损坏

2.1、识别损坏的数据块
backup validate check logical database;

RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;
- CHECK LOGICAL 选项检查 PHYSICAL 和 LOGICAL 块损坏。
- 当发现逻辑损坏时,警报日志会更新为 Error backing up file <file#>, block <block#>: logical corruption
- 当发现物理损坏时,警报日志也会更新损坏描述:
RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}

backup validate check logical datafile 1, 2
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
      FILE#          BLOCK#          BLOCKS CORRUPTION_CHANGE# CORRUPTIO
          6              10               1      8183236781662 LOGICAL
          6              42               1                  0 FRACTURED
          6              34               2                  0 CHECKSUM
          6              50               1      8183236781952 LOGICAL
          6              26               4                  0 FRACTURED
5 rows selected.
Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found during validation
Data in bad block:
 type: 16 format: 2 rdba: 0x01000009
 last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x000010ff
 check value in block header: 0xb4e0
 computed block checksum: 0xa800 -------->这个地方不是0x0,物理坏块 校验不通过
Reread of blocknum=9, file=/<path>/<datafilename>.dbf found same corrupt data

Block Checking: DBA = 25165834, Block Type = KTB-managed data block
data header at 0x2b2deb49e07c
kdbchk: fsbo(144) wrong, (hsz 78)
Error backing up file 6, block 10: logical corruption           -->逻辑坏块 
Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found during validation                                -->发现
Data in bad block:
 type: 16 format: 2 rdba: 0x01000009
 last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x000010ff
 check value in block header: 0xb4e0
 computed block checksum: 0xa800                                       --》校验不对
Reread of blocknum=9, file=/<path>/<datafilename>.dbf found same corrupt data

监视校验进度

select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and totalwork != 0
and sofar <> totalwork;
- 对于10g及以下版本的NOARCHIVELOG模式数据库,数据库必须处于MOUNT状态;否则会产生错误 ORA-19602。如果无法关闭数据库,请改用 dbverify。这个限制在11g 中取消了。
- 从 11g 开始:单个数据文件的验证可以通过使用 section 子句并行进行。RMAN 将文件分成多个部分并并行处理每个文件部分
  backup validate check logical datafile 5 SECTION SIZE 1024M;
- 从 11g 开始,可以使用 BLOCK TO 子句在数据文件中检查一系列块。下一个命令检查数据文件 1 的第 5 到 20 个块:
  validate check logical datafile 1 BLOCK 5 TO 20;
- V$DATABASE_BLOCK_CORRUPTION 中报告的损坏会随着每次 RMAN 备份验证运行而更新
2.2、识别损坏的数据段

The query can be run to map each block to a segment in the database. It will map each block from v$database_block_corruption to either a segment or if the block is free.

set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
     , greatest(e.block_id, c.block#) corr_start_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
       - greatest(e.block_id, c.block#) + 1 blocks_corrupted
     , corruption_type description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
     , header_block corr_start_block#
     , header_block corr_end_block#
     , 1 blocks_corrupted
     , corruption_type||' Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
     , greatest(f.block_id, c.block#) corr_start_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
       - greatest(f.block_id, c.block#) + 1 blocks_corrupted
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_ FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----- ------------------ ------------ ---------- ----- ----------------- --------------- ---------------- -------------
SCOTT TABLE EMP 6 10 10 1
SCOTT TABLE PARTITION ORDER ORDER_JAN 6 26 28 3
6 29 29 1 Free Block
SCOTT TABLE BONUS 6 34 34 1
6 35 35 1 Free Block
SCOTT TABLE DEPT 6 42 42 1 Segment Header
SCOTT TABLE INVOICE 6 50 50 1
  • 如果损坏的块在字典管理的表空间中并且段头块损坏,则上述查询可能会显示同一块两次。
  • 如果 ASSM 表空间中的段头块损坏,上述查询会显示段头块,但可能不会显示同一对象的后续损坏块。
2.3、识别 12c 中损坏的 NOLOGGING 块

由 rman validate 标识的 NOLOGGING 块位于新视图 v$nonlogged_block 中:

set echo on
select systimestamp from dual;
select FILE#, BLOCK#, BLOCKS, to_char(NONLOGGED_START_CHANGE#, '999999999999999') NONLOGGED_START_CHANGE#
from v$nonlogged_block;

set pagesize 2000
set linesize 250
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)

- greatest(e.block_id, c.block#) + 1 blocks_corrupted
  , null description
  FROM dba_extents e, v$nonlogged_block c
  WHERE e.file_id = c.file#
  AND e.block_id <= c.block# + c.blocks - 1
  AND e.block_id + e.blocks - 1 >= c.block#
  UNION
  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
  , greatest(f.block_id, c.block#) corr_start_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
  , 'Free Block' description
  FROM dba_free_space f, v$nonlogged_block c
  WHERE f.file_id = c.file#
  AND f.block_id <= c.block# + c.blocks - 1
  AND f.block_id + f.blocks - 1 >= c.block#
  order by file#, corr_start_block#;

三、如何格式化不属于任何段的损坏块

不属于任何段的坏块损坏表现如下

1.Rman备份失败,出现ORA-19566错误,并且报告损坏的块不属于任何对象
2.Dbverify将块显示为已损坏
3.损坏的块不属于任何对象

RMAN和DBV仍然会报告损坏的块,直到它被重新使用和重新格式化。

Step 1 - 识别损坏的数据文件
RMAN-03009: failure of backup command on <channel_name> channel at 04/29/2005 09:44:41

ORA-19566: exceeded limit of 0 corrupt blocks for file E:\xxxx\<datafilename>.ORA.
Step 2 对受影响的数据文件运行DBV/Rman验证,并检查损坏的数据块
dbv userid={system/<password>} file={full path filename} logfile={output filename}
DBVERIFY - Verification starting : FILE = E:\xxxx\<datafilename>.ORA

样本输出

Page 48740 is marked corrupt    ***

 Corrupt block relative dba: 0x01c0be64 (file 7, block 48740)
 Bad check value found during dbv:
  Data in bad block -
  type: 0 format: 2 rdba: 0x0000be64
  last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
  consistency value in tail: 0x00000001
  check value in block header: 0xb964, computed block checksum: 0x2a5a
  spare1: 0x0, spare2: 0x0, spare3: 0x0
***
 DBVERIFY - Verification complete
  Total Pages Examined         : 64000
  Total Pages Processed (Data) : 0
  Total Pages Failing   (Data) : 0
  Total Pages Processed (Index): 1751
  Total Pages Failing   (Index): 0
  Total Pages Processed (Other): 45
  Total Pages Processed (Seg)  : 0
  Total Pages Failing   (Seg)  : 0
  Total Pages Empty            : 62203
  Total Pages Marked Corrupt   : 1

rman检查

Rman> backup validate check logical database ;

For specific datafile

Rman> backup validate check logical datafile <fileno> ;

Once done query

SQL>Select * from v$database_block_corruption ;
Step 3 -检查块是否是任何对象的一部分-对于少量损坏的块
检查是否属于任何对象
SQL> select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupted block number> between block_id
and block_id + blocks -1;

检查是否不属于任何对象
 Select * from dba_free_space where file_id= <Absolute file number> 
and <corrupted block number> between block_id and block_id + blocks -1;  
Step 4 -检查块是否是任何对象的一部分-对于大量损坏的块
$ rman target / nocatalog
or
$ rman target sys/ nocatalog
run {
allocate channel d1 type disk;

allocate channel d2 type disk;
------------------------------------------------------------------------

-- multiple channels may be allocated for parallelizing purposes
-- depends: RMAN - Min ( MAXOPENFILES , FILESPERSET )

-- Defaults: MAXOPENFILES =8, FILESPERSET =64
------------------------------------------------------------------------

allocate channel dn type disk;
backup check logical validate database;
}

select * from v$database_block_corruption;

检查该块是被使用还是空闲区

set lines 200 pages 10000
col segment_name format a30

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)

- greatest(e.block_id, c.block#) + 1 blocks_corrupted
  , null description
  FROM dba_extents e, v$database_block_corruption c
  WHERE e.file_id = c.file#
  AND e.block_id <= c.block# + c.blocks - 1
  AND e.block_id + e.blocks - 1 >= c.block#
  UNION
  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
  , header_block corr_start_block#
  , header_block corr_end_block#
  , 1 blocks_corrupted
  , 'Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
  WHERE s.header_file = c.file#
  AND s.header_block between c.block# and c.block# + c.blocks - 1
  UNION
  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
  , greatest(f.block_id, c.block#) corr_start_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
  , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
  WHERE f.file_id = c.file#
  AND f.block_id <= c.block# + c.blocks - 1
  AND f.block_id + f.blocks - 1 >= c.block#
  ORDER BY file#, corr_start_block#;
Step 5 -以SYS和SYSTEM之外的用户身份创建一个虚拟表
SQL> create table s (
n number,
c varchar2(4000)
) nologging tablespace <tablespace name having the corrupt block> pctfree 99;

SQL> select segment_name,tablespace_name from user_segments where segment_name='S' ;
Step 6 -在虚拟表上创建触发器,一旦损坏的块被重用,该触发器将抛出异常
CREATE OR REPLACE TRIGGER corrupt_trigger
AFTER INSERT ON <username>.s
REFERENCING OLD AS p_old NEW AS new_p
FOR EACH ROW
DECLARE
corrupt EXCEPTION;
BEGIN
IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
RAISE corrupt;
END IF;
EXCEPTION
WHEN corrupt THEN
RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
END;
/

当提示输入块号时,输入报告损坏的块。
当提示输入文件号时,输入损坏数据文件的相对文件号(v$datafile中的rfile#值)。
Step 7-从受影响的数据文件中为表分配空间
  • 如果这是一个ASSM表空间,您可能需要重复此步骤几次。也就是说,创建多个表并分配多个区。并定期查看dba_extents以确保空闲空间现在被分配给了一个虚拟表。这是因为ASSM将自动确定下一个扩展区的大小

  • 建议确保关闭数据文件的自动扩展,以防止其增长

首先,通过查询dba_free_space找到extent大小

SQL> Select BYTES from dba_free_space where file_id=<file no> and <corrupt block no> between block_id and block_id + blocks -1;

BYTES
---------------- ---------- ---------- ---------- ---------- ------------
 65536
 在本例中是64K,因此按如下方式分配扩展区:
 SQL> alter table <username>.s
allocate extent (DATAFILE 'E:\xxxx\<datafilename>.ORA' SIZE 64K);
如果该数据文件中有多个64K的空闲区段,您可能需要使用以下循环
 BEGIN
for i in 1..1000000 loop
EXECUTE IMMEDIATE 'alter table <username>.s allocate extent (DATAFILE '||'''E:\xxxx\<datafilename>.ORA''' ||'SIZE 64K) ';
end loop;
end ;
/
继续分配,直到损坏的块成为<用户名>的一部分。使用以下查询进行确认:
 SQL> select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupt block number> between block_id
and block_id + blocks -1 ;
Step 8 -将数据插入虚拟表以格式化块
--Sample code (depending on the size of the tablespace it may vary):

BEGIN
FOR i IN 1..1000000000 LOOP
INSERT /*+ APPEND */ INTO <username>.s select i, lpad('REFORMAT',3092, 'R') from dual;
commit ;
END LOOP;
END;

Or

BEGIN
FOR i IN 1..1000000000 LOOP
INSERT INTO <username>.s VALUES(i,'x');
END LOOP;
END;
/
Or use the below code which includes 2 loops:

Begin
FOR i IN 1..1000000000 loop
for j IN 1..1000 loop
Insert into <username>.s VALUES(i,'x');
end loop;
commit;
END LOOP;
END;

对于插入到表中的每一行都将触发触发器,并且一旦将第一行插入到损坏的块中,就会产生ORA-20000异常。

Step 9 -确认块现在没有损坏

再次对损坏的数据文件(或整个数据库)运行dbverify或RMAN验证。它不会将块显示为已损坏。

确保为要写入磁盘的内存中的信息进行了几次手动日志切换或检查点操作。

RMAN备份不会报告该块上的任何错误。

Rman> Backup validate check logical datafile <fileno> ;

 Or

Rman> validate datafile <fileno> block <blockno reported corrupt>, <blockno reported corrupt> ;

SQL>Select * from v$database_block_corruption ;
Step 10:-删除在步骤4中创建的虚拟表
SQL> DROP TABLE <username>.s ;
Step 11:-执行手动日志切换和检查点

执行日志切换和检查点操作,以便将内存中格式化的块写入磁盘,并且dbverify不再报告错误

SQL>Alter system switch logfile ;
alter system checkpoint;
Step 12:删除在步骤6中创建的触发器
SQL> DROP trigger corrupt_trigger ;

四、控制文件损坏

4.1、报错
 - Error:   ORA 227
   Text:    corrupt block detected in controlfile: (block %s, # blocks %s)

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

Cause:   A block header corruption or checksum error was detected on reading
         the controlfile.
Action:  Use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUP
         CONTROLFILE command.

控制文件已损坏。这可能是由 Oracle 外部问题引起的,例如硬件/操作系统问题或 Oracle 缺陷。

4.2、确定是否由Bug 20324049导致错误
set numwidth 15
select max(FHCSQ)
, case trunc(max(FHCSQ)/4294967295,1)
  when 0.9 then 'WARNING: Reference Bug 20324049'
             else 'NO Warning' end "Bug 20324049"
from x$kcvfh;
4.3、处理
step 1、备份控制文件的当前状态

使用常规副本 (cp) 或任何其他机制备份现有的控制文件。这可用于将来的诊断,以防需要它们进行额外的恢复。

step 2、确定哪个控制文件出现故障

ORA-00227 通常伴随 ORA-202,它打印受影响的控制文件名。查看警报日志以获取更多详细信息。

step 3、对所有控制文件执行DBVERIFY

对所有控制文件执行 DBVERIFY

dbfsize /<path>/<controlfilename>.ctl

Database file: /<path>/<controlfilename>.ctl
Database file type: file system
Database file size: 614 16384 byte blocks

dbv file=/<path>/<controlfilename>.ctl blocksize=16384
4.4 解决办法
  • 方法1、复制另一个
  • 方法2、重建控制文件
  • 方法3、恢复控制文件,并介质恢复。
alter database backup controlfile to trace;
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/opt/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_17822.trc
SQL> startup nomount;
SQL>@control.sql

https://www.modb.pro/db/392807 

五、undo坏块

5.1、错误
属于撤消段的块损坏ORA-01578
ORA-00600 [4193],ORA-00600 [4194],ORA-00600 [4037]等...
这些错误通常与以下错误一起出现:
Error <Error code> encountered while recovering transaction (<undo_segment>,<slot>) on object <object_id>.
5.2、三种场景
  • 场景1:没有待定事务处理。
  • 场景2:数据库运行时有挂起的事务
  • 场景3:存在数据库崩溃的挂起事务
5.3、检查是否有未决交易
Select u.inst#            instid    ,
       u.name             useg      ,
       u.status$          status    ,
       x.ktuxeusn         usn       ,
       x.ktuxeslt         slt       ,
       x.ktuxesqn         wrp       ,
       x.ktuxesiz         undoblocks
From   x$ktuxe            x,
       undo$              u
Where  x.ktuxeusn = u.us#
And    x.ktuxesta = 'ACTIVE'
And    x.ktuxecfl like 'DEAD%' ;
5.4、解决方案将取决于我们的备份情况,以及是否有挂起的事务。

解决方案将取决于我们的备份情况,以及是否有挂起的事务

5.4.1、块损坏
rman恢复
Recover tablespace testing dba 29360329 ;
5.4.2、没有分布式事务

删除undo表空间的前提是没有活动事务

show parameter undo_tablespace
select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name='UNDOTBS1';
create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\UNDOTBS02.DBF' size 5000M;
alter system set undo_tablespace = undotbs2 scope=both;
select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
5.4.3、有未决事务

报错伴随

Error <Error code> encountered while recovering transaction (<undo_segment>,<slot>) on object <object_id>.
There are different cases:

Error 376 encountered while recovering transaction (A, B) on object YYYY.
(A, B) shows the UNDO segment id and slot number (USN , SLOT)
YYYY shows the object id (NOT the data object id)
Error 600 encountered while recovering transaction (A, B) on object YYYY.
(A, B) shows the UNDO segment id and slot number (USN , SLOT)
YYYY shows the object id (NOT the data object id)
This error can raise without reference to an object_id, but affecting directly to the UNDO segment.

Error XXXX encountered while recovering transaction (A, B).
Note this error has no "on object ...." clause in the error.
This is reported to the alert log when error XXXX is encountered on a UNDO SEGMENT Block.
XXXX is the ORA-XXXX error encountered
(A, B) shows the rollback segment id and slot number (USN , SLOT) of the transaction being recovered.

在这种情况下,最佳选择是恢复-恢复或RMAN数据块恢复

如果数据库关闭且无法启动

startup mount;
alter system set "_smu_debug_mode" = 1024
这是一个临时操作,因此在完成尝试修复问题的过程后必须取消设置
alter database opne;
如果alert.log中报告了任何object_id,请尽可能将其重新创建
如果没有报告任何对象,请尝试重新创建还原表空间(如果有待定事务,ORACLE将不允许重新创建它)
alter system set "_smu_debug_mode" = ''
4.4、ORA-00600 [4194]/[4193]
Short Description of ORA-00600[4194]

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

A mismatch has been detected between Redo records and rollback (Undo)
records.

ARGUMENTS:
Arg [a] Maximum Undo record number in Undo block
Arg [b] Undo record number from Redo block
方法一、删除undo表空间

Step 1

SQL> Startup nomount ; --> using spfile
SQL> Create pfile='/temp/<corrupt_pfile>.ora' from spfile ;
SQL> Shutdown immediate;

Step 2

修改参数文件set Undo_management=Manual
SQL> Startup mount pfile='/temp/<corrupt_pfile>.ora'

SQL> Show parameter undo

SQL> Alter database open ;

SQL> Create rollback segment r01 ;

SQL> Alter rollback segment r01 online ;

 Create undo tablespace undotbs_new datafile '<>' size <> M ;

请注意:-您可以延迟删除旧的还原表空间,这只是为了允许对死事务进行块清除。
因此,在数据库启动并使用新的还原表空间运行几个小时后,可以执行下面的步骤。
另请注意,如果您的数据库已使用任何不支持的方法被强制打开(数据文件不同步,归档日志丢失),请不要放弃旧的撤消。
 
SQL> Drop tablespace <undo tablespace name> including contents and datafiles

Step 3

-------

SQL> Shutdown immediate;

SQL> Startup nomount ; ---> Using spfile

SQL>Alter system set undo_tablespace=<new Undo tablespace created> scope=spfile;

SQL> Shutdown immediate ;

SQL> Startup

Check if error is 

对于Rac实例(如果一个实例关闭,另一个启动并运行)

如果一个节点启动并运行,而另一个节点出现ORA-00600[4194]/[4193]故障,则从启动并运行的实例创建一个新的还原表空间,
并将其作为因错误而关闭的另一个实例的默认表空间。使用以下命令启动失败的实例新的还原表空间。

从启动并运行的实例

Create undo tablespace undo_new datafile '<filename>' size <> m ;
Alter system set undo_tablespace=<New undo tablespace name> sid=<instance which has corrupt undo tablespace and is down> scope=spfile ;

SQL>Startup mount

SQL>Show parameter undo

SQL>Alter database open ;

SQL>Drop tablespace <Old undo tablespace of the failing instance> including contents and datafiles

五、数据字典不一致

hcheck.sql - Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c and Above (Doc ID 136697.1)
它指的是数据字典表之间的不一致。这些表归用户SYS所有,存储在SYSTEM表空间中,用于跟踪数据库中创建的用户、表、索引等。

提供一个匿名PL/SQL块来检查常见的数据字典问题。
该脚本检查所选字典关系的一致性,并寻找某些已知问题——一些报告的“问题”将是正常的和预期的。
该脚本可用于Oracle版及更高版本。hcheck8i.sql包含8.1。
这是一个轻量级的“只读”脚本,没有任何影响。
该脚本主要在Oracle技术支持的指导下使用。

该脚本报告了各种与字典相关的问题,这些问题可能是问题,也可能不是问题。任何报告的问题都应由Oracle支持分析师审查,因为一些报告的“问题”可能是正常的和预期的。
HCKE错误被认为是一个潜在的问题。
HCKW错误被视为警告。

SQL> spool hcheck.log
SQL> @hcheck
SQL> spool off

$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on ...

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter username: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> spool hcheck.log
SQL> @hcheck

HCheck Version 07MAY18 on .....
----------------------------------------------

Catalog Version 12.2.0.1.0 (1202000100)
db_name:
Is CDB?: YES CON_ID: 3 Container: CDB1_PDB1
                                        Catalog Fixed
Procedure Name Version Vs Release Timestamp Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- LobNotInObj ... 1202000100 <= *All Rel* ..... 09:49:01 PASS
.- MissingOIDOnObjCol ... 1202000100 <= *All Rel* ..... 09:49:01 PASS
...

.- OrphanedTable ... 1202000100 <= *All Rel* ..... 09:49:02 FAIL
HCKE-0019: Orphaned TAB$ (no SEG$) (Doc ID 1360889.1)
ORPHAN TAB$: OBJ#=105785 DOBJ#=105785 TS=7 RFILE/BLOCK=7/113 TABLE=SCOTT.SALES_REP BOBJ#=

.- MaxControlfSeq ... 1202000100 <= *All Rel* ..... 09:49:02 PASS

.- SegNotInDeferredStg ... 1202000100 > 1102000000 ..... 09:49:02 PASS
---------------------------------------

..................... Elapsed: 1 secs
---------------------------------------

Found 1 potential problem(s) and 0 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
PL/SQL procedure successfully completed.

Statement processed.

Complete output is in trace file:
/<path>/diag/rdbms/<db_name>/<oracle_sid>/trace/<oracle_sid>_<ora>_<pid>_HCHECK.trc


SQL> spool off

六、redo损坏

ORA-354 ORA-353 and ORA-312 on Redo Log Group members (Doc ID 332672.1)

6.1、报错
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 1892904 change 878787 time 12/05/2014 13:17:10
ORA-00312: online log 3 thread 1: '/oracle/dbs/log3_A.dbf'
ORA-00312: online log 3 thread 1: '/oracle/dbs/log3_B.dbf'

如果所有重做成员确实损坏,并且数据库处于archivelog模式,则预警日志也可能包含下一条消息:

ARCn: All Archive destinations made inactive due to error 354
....
CORRUPTION DETECTED: thread %d sequence %d log %d at block %d. Arch found corrupt blocks
Example:
ARC1: All Archive destinations made inactive due to error 354
....
CORRUPTION DETECTED: thread 1 sequence 28 log 3 at block 1892904. Arch found corrupt blocks
6.2、原因

重做日志组中的在线重做日志文件成员可能已损坏。

如果Oracle在归档日志成员时检测到损坏,它会尝试从该组的第二个成员读取相同的重做块。如果在重做日志组的所有成员中发现该块已损坏,则归档不会继续进行。

原因通常是由于操作系统故障或硬件故障造成的覆盖/遗漏。

6.3、解决

验证哪些重做日志文件成员已损坏

alter session set tracefile_identifier='VALIDATEREDO';

alter system dump logfile '&name' VALIDATE;

oradebug setmypid
oradebug tracefile_name

SQL> alter system dump logfile '/oracle/dbs/log3_A.dbf' validate;
alter system dump logfile '/oracle/dbs/log3_A.dbf' validate
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 1892904 change 869569 time 12/05/2014 13:17:10
ORA-00334: archived log: '/oracle/dbs/log3_A.dbf'

对每个redo验证

select member
from v$logfile
where group# = &group_number;

在应用以下任何解决方案之前,请备份损坏的重做日志文件,以备进一步分析根本原因时使用。
解决方法一、
如果重做日志成员之一未损坏,则执行下一个命令几次,直到相应的重做日志组号在视图v$log中处于status=CURRENT状态。

alter system switch logfile;

select status
from v$log
where group#=&log_group_number;

可以选择等待,直到重做日志被重用,并且块将被修复。

解决方案2
如果所有重做日志成员都已损坏

ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

清除重做日志组后,预警日志可能会更新为下一条消息:

WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE [Timestamp] (CHANGE [scn]) CANNOT BE USED FOR RECOVERY.

请确保在清除重做日志组后进行新的备份。

还要注意,如果重做日志组的单个成员编号不能被清除;产生错误ORA-1514:

SQL> alter database clear unarchived logfile '/oracle/dbs/log3_A.dbf';
alter database clear unarchived logfile '/oracle/dbs/log3_A.dbf'
*
ERROR at line 1:
ORA-01514: error in log specification: no such log
ORA-01517: log member: '/oracle/dbs/log3_A.dbf'

这是正常现象

七、行和列损坏

这意味着列不包含与其声明相对应的有效值;示例:列被声明为日期,但存储的日期无效。如果块结构没问题;例如,带有check logical选项的dbverify和rman都
没有检测到任何问题,则这不是块损坏,但为了完整起见,此处包含了这一问题。

日期/时间戳列的验证步骤由以下步骤组成,如果所讨论的表有多个日期/时间戳列,可以多次重复验证步骤:
1)创建一个存储可疑行的表
2)验证日期/时间戳列
3)检查找到的行
4)手动更正日期/时间戳值

7.1、创建一个存储可疑行的表(假设在表所有者模式中):
SQL>
connect &user/&password
undefine table_name

set escape \
create table &&table_name\_invalid\_date
(row_id rowid, col_name varchar2(31), col_value varchar2(50));
7.2、验证时间戳列
SQL>
connect &user/&password
undefine table_name

set escape \
create table &&table_name\_invalid\_date
(row_id rowid, col_name varchar2(31), col_value varchar2(50));

set escape \
undefine table_name
undefine column_name

set serveroutput on

declare
cursor c_cur is select rowid,substr(dump(&&column_name),instr(dump(&&column_name),':')+2) dump_col from &&table_name where &&column_name is not null; -- no need to check null values

i integer;
j integer;
k integer:=0;
bc boolean; -- a date can be BC or AD, dump values of century/year will/might be negative in case of BC
bc_ad varchar2(2);

century varchar(3);
year varchar(3);
month number(2);
day number(2);
hour number(2);
minute number(2);
second number(2);

chck_date date;

begin
for c in c_cur loop

j:=instr(c.dump_col,',');
century:=substr(c.dump_col,1,j-1)-100;
if century < 0 /* if century is negative then date is BC */
then
bc:=true;
century:=abs(century);
end if;
if length(century)=1
then century:='0'||century;
end if;

i:=j+1;
j:=instr(c.dump_col,',',1,2);
year:=substr(c.dump_col,i,j-i)-100;
if year < 0 /* if year is negative then date is BC */
then
bc:=true;
year:=abs(year);
end if;
if length(year)=1
then year:='0'||year;
end if;

i:=j+1;
j:=instr(c.dump_col,',',1,3);
month:=substr(c.dump_col,i,j-i);

i:=j+1;
j:=instr(c.dump_col,',',1,4);
day:=substr(c.dump_col,i,j-i);

i:=j+1;
j:=instr(c.dump_col,',',1,5);
hour:=substr(c.dump_col,i,j-i)-1;

i:=j+1;
j:=instr(c.dump_col,',',1,6);
minute:=substr(c.dump_col,i,j-i)-1;

/* timestamp might not contain a full format and end direct after seconds */
i:=j+1;
j:=instr(c.dump_col,',',1,7);
if j=0
then j:=length(c.dump_col)+1;
end if;
second:=substr(c.dump_col,i,j-i)-1;

/* checking/setting date BC or AD */
if bc
then
bc_ad:='bc';
else
bc_ad:='ad';
end if;

/* usage of a separate PL/SQL block to trap the exception locally in order to continue with for in ... loop */
begin

/* checking if the timestamp value can be recomposed to a date */
chck_date:=to_date(day||'-'||month||'-'||century||year||' '||hour||':'||minute||':'||second||' '||bc_ad,'dd-mm-yyyy hh24:mi:ss ad');

exception
when others then
k:=k+1;
insert into &&table_name\_invalid\_date values (c.rowid,'&&column_name',c.dump_col);
end;

end loop;
commit;

/* showing how many columns with invalid date were found */
dbms_output.put_line('No of incorrect date values as found: '||k);

end;
/
7.3、检查找到的行
set escape \
undefine table_name
column col_name format a20;
column col_value format a40;
set pagesize 9999
select * from &&table_name\_invalid\_date;

ROW_ID COL_NAME COL_VALUE

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

AAAdtXAAEAAAKIqAAR column1 255,100,0,114,0,0,0,1,16,211,192
AAAdtXAAEAAAKIqAAS column1 255,100,0,114,0,0,0,1,16,211,192
...
AAAdtXAAEAAAKIrAAT column1 18,11,45,192,0,2,0
AAAdtXAAEAAAKIrAAU column1 18,11,45,192,0,2,0
...
AAAdtXAAEAAAKIrAAK column2 255,100,0,114,0,0,0,1,16,211,192
AAAdtXAAEAAAKIrAAT column2 18,11,41,80,0,2,0
AAAdtXAAEAAAKIrAAU column2 18,11,41,80,0,2,0
AAAdtXAAEAAAKIrAAa column2 18,11,35,112,0,0,0,1,16,211,192
AAAdtXAAEAAAKIrAAb column2 18,11,35,112,0,0,0,1,16,211,192
AAAdtXAAEAAAKIrAAe column2 5,74,56,236,18,205,0,17,16,209,248

A correct column value would look like the following:
create table test (t timestamp);
insert into test values (sysdate);
select dump(t) from test;

DUMP(T)
--------------------------------------------------------------------------------

Typ=180 Len=7: 120,109,12,11,10,58,49

120 - 100 = 20 = century -1 (20 implies 21st century)
109 - 100 = 09 = year
12 = month
11 = day
10 - 1 = 9 hour
58 -1 = 57 = min
49 - 1 = 48 = second

检查找到的每一行,以确认该列的值不正确:
SQL> select <column_name> from <table_name> where rowid='&rowid';
如果列值正确,您可以将其从<表名> _无效日期中删除,这样您就可以使用<表名> _无效日期的内容来更正该表。
7.4、手动更正日期/时间戳值
SQL>
set escape 
undefine table_name
undefine column_name

update &&table_name set &&column_name=...
where rowid in (select row_id from &&table_name_invalid_date where col_name='&&column_name');

如果您能够从其他列值中导出日期/时间戳列的正确值,您可能需要在单个基础上更正行。

八、表/索引不一致

表/索引不一致是指表中的条目在索引中不存在,反之亦然。常见的错误有ORA-8102,ORA-600 [kdsgrp1],ORA-1499

有几种类型的损坏,如:

数据文件块损坏-物理/逻辑
表/索引不匹配
范围不一致
数据字典不一致
8.1、数据文件块损坏-块内损坏
它是指可能导致不同错误(如ORA-1578、ORA-8103、ORA-1410、ORA-600等)的块内损坏。
RMAN -识别数据文件块损坏
backup check logical validate database;
backup check logical database
检查视图V$DATABASE _ BLOCK _ CORRUPTION以确定RMAN检测到的块损坏。
DBVerify -识别数据文件块损坏
dbv file=<datafile name> blocksize=<datafile Block size>

RMAN Vs DBVerify -数据文件块内损坏

在识别块内损坏时,使用什么工具是一个两难的问题。以下是RMAN和DBV的一些比较:

  • 当RMAN使用逻辑选项时,它对块内损坏进行与DBV完全相同的检查。
  • RMAN可以使用多个通道并行运行,这使得它比DBV更快,后者不能在单个命令中并行运行。
  • DBV检查空块。在10g中,当使用本地管理的表空间时,RMAN可能不会检查空闲区中的块。在11g中,RMAN会检查空闲区和已用区。
  • DBV和RMAN (11g)都可以检查一系列数据块。RMAN: VALIDATE DATAFILE 1 BLOCK 10 to 100;. DBV: start=10 end=100
  • RMAN将损坏信息保存在控制文件中(v$database _ block _ corruption,v$backup _ corruption)。DBV没有。
  • RMAN可能不会像报告为逻辑损坏块的块中究竟损坏了什么那样报告损坏细节。DBV在屏幕或日志文件中报告损坏的详细信息。
  • DBV可以扫描SCN高于给定SCN的块(HIGH_SCN子句)。
  • DBV不需要连接到数据库。
  • RMAN不会检测Doc ID7517208.8中描述的逻辑损坏(DBV有)。11.2之前bug
8.2、识别表/索引不匹配
表/索引不一致是指表中的条目在索引中不存在,反之亦然。常见的错误有ORA-8102,ORA-600 [kdsgrp1],ORA-1499 by "analyze validate structure cascade
-analyze发现不一致命令
analyze table <table name> validate structure cascade <ONLINE>;
ORA-1499. Table/Index row count mismatch (Doc ID 563070.1)
8.3、确定本地管理的表空间中的范围不一致

它指的是本地管理的表空间(LMT)用来保存已用区和空闲区信息的部分中的不一致。

- 重叠范围(两个段可能错误地使用同一个块)。常见的错误是ORA-8103、ORA-1410、ORA-600 [kdddgb2]
- 报告为已用的空闲区
- dba_tablespaces.SEGMENT_SPACE_MANAGEMENT='MANUAL'

alter session set tracefile_identifier='TABLESPACE_VERIFY';
execute dbms_space_admin.tablespace_verify('&tablespace_name')
oradebug setmypid
oradebug tracefile_name
--实例输出
Extent Map Entry Overlaps with Another Extent Map Entry
SegDBA: 0x0fc3e832 : ExtNo 3 UetDBA 0x2ec00091: ExtNbk: 32
SegDBA: 0x1000c8d0 : ExtNo 212 UetDBA 0x2ec00011: ExtNbk: 1280


dba_tablespaces.SEGMENT_SPACE_MANAGEMENT='AUTO'

alter session set tracefile_identifier='ASSM_TABLESPACE_VERIFY';
execute dbms_space_admin.assm_tablespace_verify('&tablespace_name',dbms_space_admin.TS_VERIFY_BITMAPS)
oradebug setmypid
oradebug tracefile_name

如果存在不一致,dbms_space_admin会生成一个跟踪文件。请注意,如果检测到不一致,运行dbms_space_admin的会话可能不会在屏幕中报告。

8.4、识别由写入丢失导致的损坏

当I/O子系统确认数据块写入完成时,发生数据块丢失写入,而事实上写入并未发生在永久存储器中。结果是数据库中的块是陈旧/旧的拷贝,它不是逻辑或物理损坏的;块内部结构是正确的
当与类似ORA-600 kdsgrp1、ORA-8103(旧对象id)等的另一个上下文比较时,具有丢失的改变的块可能产生几个错误;或者介质恢复期间的下一个错误(像在物理备用中):ORA-600 [3020],ORA-752(如果启用了db_lost_write_protect)。

  • DBV/RMAN打算识别由丢失写入引起的不一致:
    由于dbverify/rman会运行块内检查(块不会与另一个上下文进行比较),因此通过丢失IO来识别损坏扩展并不简单。块本身是健康的,因为结构是有效的(不是垃圾)。但是,在极少数情况下,数据块可能会间接暴露于逻辑损坏,尤其是在空间管理区域,如果存在写入丢失。例如,数据块在元数据中被标记为已满,但更改已丢失。后续插入可能会在逻辑上损坏该块。

  • 介质恢复/物理待机
    最佳选择是像备用数据库一样进行介质恢复,或者在另一个系统中还原/恢复数据库。媒体恢复执行检查以识别块内容是否是预期的内容,因为重做结构跟踪块先前版本(预期的scn ),并将其与当前块scn进行比较。如果存在不匹配,则生成ORA-600 [3020]或ORA-752。

如果配置了物理备用数据库,那么现在在主数据库和备用数据库上将DB_LOST_WRITE_PROTECT设置为TYPICAL。通过这样做,主数据库将为读取创建重做条目(select语句),这有助于更快地检测备用数据库中丢失的写入。

有一个运行诊断试验恢复的选项,这将扫描重做以查找问题,但实际上不会对恢复的数据库进行任何更改。试用恢复会在alert.log中报告任何其他损坏。恢复…测试语句可用于调用试验恢复。涉及文件编号283262.1有关试用恢复的更多详细信息。

  • 重叠范围

由于元数据中的空间管理不一致,丢失的写入可能会导致致命问题,如数据段相互覆盖;重叠范围。

  • analyze

运行“analyze table validate structure cascade ”,在将索引内容与相应的表内容进行比较时,可能会检测到由丢失的写入导致的不一致。如果不匹配,则报告ORA-1499。它还可以仅在旧块版本具有不同的数据对象id时识别旧块版本,这可能导致ORA-8103、ORA-1410等。使用cascade选项分析整个数据库中的所有表可能需要很长时间。

九、ASM磁盘组损坏信息收集

1、首先,如果受影响的磁盘组已装载且数据库已打开,则从与受影响的磁盘组相关联的所有数据库运行完整数据库备份(例如,使用RMAN ),并验证备份状态良好。然后继续下面的步骤。
2、详细描述腐败产生时正在进行的工作,或者自上次工作以来发生了什么变化(请非常详细)?
3、对受影响的磁盘组运行下一次运行状况检查,如下所示:
SQL > alter disk group < disk group name > check all no repair;
4、提供ASM alert.log(它将报告上一个命令的结果)。 如果这是一个ASM集群配置,则从所有节点提供ASM alert.log。
5、从受影响的磁盘组获取AMDU转储,如下所示(以网格操作系统用户身份执行):
<ASM Oracle Home>/bin/amdu -diskstring '<ASM disks location>/*' -dump '<diskgroup>'
$> <ASM Oracle Home>/bin/amdu -diskstring '/dev/rhdisk*' -dump 'DATA'

$> <ASM Oracle Home>/bin/amdu -diskstring '/dev/oracleasm/disks/*' -dump 'DATA'

$> <ASM Oracle Home>/bin/amdu -diskstring '/dev/rdsk/*' -dump 'DATA'
6、此外,为了确认或丢弃物理磁盘上的任何磁盘I/O问题,收集并提供所有节点的操作系统日志(至少包含3个月前到现在的条目),如以下文档所述:
7、另外,请提供每个受影响和损坏的磁盘的第一个50MB转储,如下所示:
$> dd if=<full path affected disk name> of=/tmp/<affected disk name>.dump bs=1048576 count=50
dd if=/dev/oracleasm/disks/DB0054 of=/tmp/DB0054.dump bs=1048576 count=50

十、表/索引行数不匹配

10.1、检测
SQL> analyze table test validate structure cascade;
analyze table test validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
Table/Index row count mismatch
table 6559 : index 10000, 0
Index root = tsn: 6 rdba: 0x01400091

这意味着:表扫描返回6559行,索引扫描返回10000行。
rdba: 0x01400091是索引段头相对数据块地址。它是十进制20971665,即Rfile#=5(相对文件号)Block#=145:

prompt Enter tsn:
accept tsn
prompt Enter rdba (in hex, no '0x' prefix):
accept hex_rdba

set verify off

select file#,
dbms_utility.data_block_address_block(
to_number('&hex_rdba','XXXXXXXXX')) block#
from sys.v$datafile
where ts# = &tsn
and rfile# = dbms_utility.data_block_address_file(
to_number('&hex_rdba','XXXXXXXXX'));

FILE# BLOCK#
---------- ----------
7          145 

从dba_segments运行下一个查询可以识别相关的索引(它需要header_file的绝对文件号):

QUERY 1:

SQL> select owner, segment_name, segment_type
2 from dba_segments
3 where header_file = 7
4 and header_block = 145;

OWNER SEGMENT_NAME SEGMENT_TYPE
-------- --------------- ------------------
SCOTT I_TEST INDEX

10g+中的ORA-600 [kdsgrp1]或较低版本中的ORA-600 [12700]也可能表现出这种逻辑不一致。

10.2原因

表及其索引之间存在逻辑不一致。这种逻辑不一致通常是由于表中的高水位线(HWM)问题造成的,在这种情况下,全表扫描返回的行数可能比索引扫描少。

不一致可能是由Oracle缺陷或操作系统/硬件问题导致的,这些问题会导致IO丢失。

10.3、解决方案

通过运行以下查询,可以识别通过索引检索的行,这些行不是通过全表扫描检索的:

select /*+ INDEX_FFS(<tablename> <indexname identified in 2.1>) */ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from <tablename>
where <indexed column> is not null
minus
select /*+ FULL(<tablename>)*/ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from <tablename>;

Example:

select /*+ INDEX_FFS(TEST I_TEST) */ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from test
where c2 is not null
minus
select /*+ FULL(TEST)*/ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from test;

全表扫描中丢失的值可以使用下一个plsql(脚本1)的索引存储在另一个表中:

drop table test_copy;

create table test_copy as select * from test where 1=2;

declare
cursor missing_rows is
select /*+ INDEX_FFS(TEST I_TEST) */ rowid rid
from test
where c2 is not null
minus
select /*+ FULL(TEST)*/ rowid rid
from test;
begin
for i in missing_rows loop
insert into TEST_COPY
select /*+ ROWID(TEST) */ * from TEST where rowid = i.rid;
end loop;
end;
/ 
10.4解决办法

-当索引的行数少于表的行数时,重新创建索引可能会解决这个问题。
对于索引的行数多于表中的行数,并且表中确实不存在该行的情况,使用本节中描述的第一个查询中索引返回的rowid,通过“select * from table where rowid = & rowid”进行确认,则解决方案可以是重新创建索引,注意索引可能具有预期的数据,并且可以通过访问索引列值来保存数据,如下所示:

select /*+ INDEX_FFS(<tablename> <indexname identified in 2.1>) */ rowid, <indexed column 1>, <indexed column 2>...
from <tablename>
where <indexed column> is not null
minus
select /*+ FULL(<tablename>)*/ rowid, <indexed column 1>, <indexed column 2>...
from <tablename>;

十一、提取索引定义

select 'select dbms_metadata.get_ddl ("INDEX", "'||index_name||'", "'||owner||'") from dual;' from dba_indexes where tablespace_name = 'EXAMPLE' and index_name like 'E%';

十二、检测lob损坏

12.1、尝试导出包含lob的表时,会出现以下错误:
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-31693: Table data object "OWNER"."TABLEABC" failed to load/unload and is being
skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old、

这些错误的原因可能是LOB损坏。维护lob将解决Exp/Expdp的问题。

12.2、检查lob损坏
set serverout on
exec dbms_output.enable(100000);
declare
page number;
len number;
c varchar2(10);
charpp number := 8132/2;

begin
for r in (select rowid rid, dbms_lob.getlength (<your_clob_column>) len
from <your_table_with_clcob_column>) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (<your_clob_column>, 1, 1+ (page * charpp))
into c
from <your_table_with_clcob_column>
where rowid = r.rid;
       
exception
when others then
dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
dbms_output.put_line (sqlerrm);
end;
end loop;
end if;
end loop;
end;
/

如果没有错误,LOB是OK的。

否则,如果发生错误(即ORA-1403、ORA-1578、ORA-1555、ORA-22922 ),则意味着LOB已损坏。

12.3、解决

-使用物理备份还原和恢复LOB段。
-清空lob

2.如果连续两次运行显示相同的行,那么这些LOB记录就有问题。最简单的方法是删除带有上述ROWIDs的lob,并重新构建它们

12.4、如何清空lob

1.使用物理备份还原和恢复LOB段。
2.使用UPDATE语句清空受影响的lob、
3.执行导出,排除损坏的rowids

注意:如果没有损坏的lob数据,并且问题仍然存在,则重建整个表以解决问题。

1. Create a new temporary table for storing all rowids of the corrupted LOBs. Let's call it "corrupt_lobs"

SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);

2. Make a desc on the table containing the LOB column:
DESC <TABLE_NAME>
Name Null? Type
----------   --------- ------------
<COL1>       NOT NULL  NUMBER
<LOB_COLUMN>           BLOB

-- Run the following PLSQL block:

declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
for cursor_lob in (select rowid r, <LOB_COLUMN> from <TABLE_NAME>) loop
begin
n:=dbms_lob.instr(cursor_lob.<LOB_COLUMN>,hextoraw('889911'));
exception
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/

-- In the end all rowids of the corrupted LOBs will be inserted into the corrupt_lobs newly created table.

-- A possible solution would then be to empty the affected LOBs using a statement like:
SQL> update <TABLE_NAME> set <LOB_COLUMN> = empty_blob() where rowid in (select corrupt_rowid from corrupt_lobs);
(注意:对于BLOB和BFILE列,请使用EMPTY _ BLOB对于CLOB和NCLOB列,使用EMPTY_CLOB )

-- Or export the table without the corrupted row, like:
% expdp system/<PASSWORD> DIRECTORY=my_dir DUMPFILE=<dump_name>.dmp LOGFILE=<logfile_name>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> QUERY=\"WHERE rowid NOT IN \(\'<corrupt_rowid>\'\)\"

十三、索引组织表

读取表时跳过ORA-8103 ORA-1410 ORA-1578 ORA-600[kdsgrp 1]的脚本

13.1、跳过ORA-1578 ORA-8103 ORA-1410
REM Create a new table based on the table that is producing errors with no rows: --基于产生错误且没有行的表创建一个新表:

create table <new table name>
as
select *
from <original table name>
where 1=2;

REM Create the table to keep track of ROWIDs pointing to affected rows: --创建表来跟踪指向受影响行的ROWIDs:

create table bad_rows (row_id rowid, oracle_error_code number);

set serveroutput on

DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

CURSOR c1 IS select /*+ index_ffs(tab1 <index name>) parallel(tab1) */ rowid
from <original table name> tab1
where <indexed column> is NOT NULL
order by rowid;

r RowIDTab;
rows NATURAL := 20000;
bad_rows number := 0 ;
errors number;
error_code number;
myrowid rowid;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO r LIMIT rows;
EXIT WHEN r.count=0;
BEGIN
FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
insert into <new table name>
select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)>
from <original table name> A where rowid = r(i);
EXCEPTION
when OTHERS then
BEGIN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR err1 IN 1..errors LOOP
error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
if error_code in (1410, 8103, 1578) then
myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
bad_rows := bad_rows + 1;
insert into bad_rows values(myrowid, error_code);
else
raise;
end if;
END LOOP;
END;
END;
commit;
END LOOP;
commit;
CLOSE c1;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/

如果“坏行总数:”显示0,并且确定磁盘上存在导致ORA-8103的不正确块,则意味着该块是空的(没有行),并且没有数据丢失。

13.2、跳过表中的ORA-600

当ORA-600由不存在的链接行(无效的nrid)生成时,如ORA-600 [kdsgrp1]和事件10231不起作用时,这很有用。

create table bad_rows (row_id ROWID
,oracle_error_code number);

rem Create the new empty table:

create table &&new_table
as select *
from &&affected_table
where 1=2;


set serveroutput on
declare
n number:=0;
bad_rows number := 0;
error_code number;
ora600 EXCEPTION;
PRAGMA EXCEPTION_INIT(ora600, -600);
begin
for i in (select rowid rid from &&affected_table) loop
begin
insert into &&new_table
select *
from &&affected_table
where rowid=i.rid;
n:=n+1;
exception
when ora600 then
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,600);
commit;
when others then
error_code:=SQLCODE;
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,error_code);
commit;
end;
end loop;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
dbms_output.put_line('Total Good rows: '||n);
end;
/

十四、临时段损坏

Example 1: Alert Log may show ORA-1578 followed by the corrupt object information with "SEGMENT TYPE = Temporary Segment":

Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_8086.trc  (incident=2446468):
ORA-01578: ORACLE data block corrupted (file # 1707, block # 233066810)
ORA-01110: data file 1707: '/oracle/dbs/tools.dbf'
Wed Sep 24 16:33:09 2014
Corrupt Block Found
         TSN = 73, TSNAME = TOOLS
         RFN = 1024, BLK = 2646152, RDBA = 2646152
         OBJN = 0, OBJD = 124839000, OBJECT = TOOLS, SUBOBJECT =
         SEGMENT OWNER = , SEGMENT TYPE = Temporary Segment

in this example tablespace TOOLS is a PERMANENT tablespace (dba_tablespaces.contents='PERMANENT').

Example 2: Alert Log may not show any errors but message:

Corrupt Block Found
         TSN = 23, TSNAME = TEMP
         RFN = 1, BLK = 4608, RDBA = 4198912
         OBJN = 298432, OBJD = 4198912, OBJECT = , SUBOBJECT =
         SEGMENT OWNER = , SEGMENT TYPE =
select segment_name, segment_type
from dba_segments
where tablespace_name = '&TSNAME'
  and header_file = &FILE_NUMBER
  and header_block = &BLK;
In the Example 1 above it is:

select segment_name, segment_type
  from dba_segments
where tablespace_name = 'TOOLS'
  and header_file = 1707
  and header_block = 233066810;

SEGMENT_NAME         SEGMENT_TYPE

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

1707.233066810       TEMPORARY
If the above query returns a row then fix the corruption by using dbms_space_admin:

exec dbms_space_admin.segment_corrupt('&TSNAME', &RFN, &BLK)
exec dbms_space_admin.segment_drop_corrupt('&TSNAME', &RFN, &BLK)
exec dbms_space_admin.tablespace_rebuild_bitmaps('&TSNAME')
In our example it is:

select relative_fno
from   dba_data_files
where  tablespace_name = 'TOOLS'
 and   file_id = 1707;

RELATIVE_FNO
------------
        1024 

Then remove the temporary Segment and rebuild the tablespace bitmap:  
exec dbms_space_admin.segment_corrupt('TOOLS', 1024, 233066810)
exec dbms_space_admin.segment_drop_corrupt('TOOLS', 1024, 233066810)
exec dbms_space_admin.tablespace_rebuild_bitmaps('TOOLS')

十五 、处理Oracle块损坏

Handling Oracle Block Corruptions (Doc ID 28814.1)

十六、常见报错

ORA-1578

ORA-1578 The data block indicated was corrupt. This was a physical corruption, also called a media corruption. The cause is unknown but is most likely external to the database. If ORA-26040 is also signaled, the corruption is due to NOLOGGING or UNRECOVERABLE operations.
ORA-1410

This error is raised when an operation refers to a ROWID in a table for which there is no such row.
The reference to a ROWID may be implicit from a WHERE CURRENT OF clause or directly from a WHERE ROWID=... clause.
ORA-1410 indicates the ROWID is for a BLOCK that is not part of this table.
ORA-8103

The object has been deleted by another user since the operation began; example: another session truncated or dropped the segment while the SQL statement was still active.
If the error is reproducible, following may be the reasons:
a.) The header block has an invalid block type.
b.) The data_object_id (seg/obj) stored in the block is different than the data_object_id stored in the segment header. See dba_objects.data_object_id and compare it to the decimal value stored in the block (field seg/obj).
ORA-8102

An ORA-08102 indicates that there is a mismatch between the key(s) stored in the index and the values stored in the table. What typically happens is the index is built and at some future time, some type of corruption occurs, either in the table or index, to cause the mismatch.
ORA-1498 

Generally this is a result of an ANALYZE ... VALIDATE ... command.
This error generally manifests itself when there is inconsistency in the data/Index block. Some of the block check errors that may be found:-
a.) Row locked by a non-existent transaction
b.) The amount of space used is not equal to block size
c.) Transaction header lock count mismatch.
While support are processing the tracefile it may be worth the re-running the ANALYZE after restarting the database to help show if the corruption is consistent or if it 'moves'.
Send the tracefile to support for analysis.
If the ANALYZE was against an index you should check the whole object. Eg: Find the tablename and execute:
ANALYZE TABLE xxx VALIDATE STRUCTURE CASCADE;
ORA-1499

An error occurred when validating an index or a table using the ANALYZE command.
One or more entries does not point to the appropriate cross-reference.
ORA-752 or ORA-600 [3020]

Media recovery detected a lost write of a data block. A data block write to storage was lost during normal database operation on the primary database.

This is reporting a lost write during media recovery.

Reference the next article:

Note 1265884.1 : Resolving ORA-00752 or ORA-600 [3020] During Standby Recovery

ORA-26040

Trying to access data in block that was loaded without redo generation using the NOLOGGING/UNRECOVERABLE option.
This Error raises always together with ORA-1578
ORA-600 [12700]

Oracle is trying to access a row using its ROWID, which has been obtained from an index.
A mismatch was found between the index rowid and the data block it is pointing to. The rowid points to a non-existent row in the data block. The corruption can be in data and/or index blocks.
ORA-600 [12700] can also be reported due to a consistent read (CR) problem.
ORA-600 [3020]

This is called a 'STUCK RECOVERY'.
There is an inconsistency between the information stored in the redo and the information stored in a database block being recovered.

This error indicates a lost write or a lost change in the database

ORA-600 [4194]

A mismatch has been detected between Redo records and rollback (Undo) records.
Oracle is validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block.
This error is reported when the validation fails.
ORA-600 [4193]

A mismatch has been detected between Redo records and Rollback (Undo) records.
Oracle is validating the Undo block sequence number in the undo block against the Redo block sequence number relating to the change being applied.
This error is reported when this validation fails.
ORA-600 [4137]

While backing out an undo record (i.e. at the time of rollback) Oracle found a transaction id mismatch indicating either a corruption in the rollback segment or corruption in an object which the rollback segment is trying to apply undo records on.
This would indicate a corrupted rollback segment.
ORA-600 [6101]

Not enough free space was found when inserting a row into an index leaf block during the application of undo.
ORA-600 [2103]

Oracle is attempting to read or update a generic entry in the control file.
If the entry number is invalid, ORA-600 [2130] is logged.
ORA-600 [4512]

Oracle is checking the status of transaction locks within a block.
If the lock number is greater than the number of lock entries, ORA-600 [4512] is reported followed by a stack trace, process state and block dump.
This error possibly indicates a block corruption.
ORA-600 [2662]

A data block SCN is ahead of the current SCN.
The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN identified by the process that is normally close to the database scn.
If the SCN is less than the dependent SCN then ORA-600 [2662] is signaled.
ORA-600 [4097]

Oracle is accessing a rollback segment header to review if a transaction has been committed.
However, the xid given is in the future of the transaction table.
This could be due to a rollback segment corruption issue.
ORA-600 [4000]

It means that Oracle has tried to find an undo segment number in the data dictionary and this undo segment number was not found.
ORA-600 [6006]

Oracle is undoing an index leaf key operation. If the key is not found, ORA-00600 [6006] is logged.
ORA-600[6006] is usually caused by a media corruption problem related to either a lost write to disk or a corruption on disk.
ORA-600 [4552]

This assertion is raised because Oracle is trying to unlock the rows in a block, but receive an incorrect block type.
The second argument is the block type received.
ORA-600[6856]

Oracle is checking that the row slot that is about to be freed is not already on the free list.
This internal error is raised when this check fails.
ORA-600[13011]

During a delete operation Oracle is deleting from a view via an instead-of trigger or an Index organized table and have exceeded a 5000 pass count
ORA-600[13013]

During the execution of an UPDATE statement, after several attempts (Arg [a] passcount) Oracle is unable to get a stable set of rows that conform to the WHERE clause.

Note 816784.1 : How to resolve ORA-00600 [13013], [5001]

ORA-600[13030]

 
ORA-600[25012]

Oracle is trying to generate the absolute file number given a tablespace number and relative file number and cannot find a matching file number or the file number is zero.
ORA-600[25026]

Looking up/checking a tablespace invalid tablespace ID and/or rdba found
ORA-600[25027]

Invalid tsn and/or relative file number found
ORA-600 [kcbz_check_objd_typ_3]

An object block buffer in memory is checked and is found to have the wrong object id. This is most likely due to corruption.
ORA-600 [kdsgrp1]

Error may be caused by:
Case 1. A row referenced in an index that does not exist in the table

ORA-1499 may be produced by analyze:

analyze table <table name> validate structure cascade online;


Case 2. An non-existent rowid pointed to by a chained row

Run an export (exp) or Full Table Scan to identify if there is a permanent invalid chained row.

ORA-600[kddummy_blkchk] ORA-600[kdblkcheckerror]

ORA-600 [kdbBlkCheckError]
ORA-600 [ktfBlkCheckError]
ORA-600 [ktfBlkCheckError]
ORA-600 [ktsBlkChekError]
ORA-600 [ktspBlkCheckError]
ORA-600 [ktfbnBlkCheckError]
ORA-600 [ktuBlkCheckError]
ORA-600 [kdliBlkCheckError]
ORA-600 [kdxdBlkCheckError]
ORA-600 [kdiBlkCheckError]

ORA-600 [kddummy_blkchk] is for 10g and ORA-600[kdblkcheckerror] for 11g onward.

These errors report a Logical Block Corruption

If the error is raised in a data guard physical standby database, follow the next article:

Note 2821699.1 : Resolving Logical Block Corruption Errors in a Physical Standby Database

ORA-600[ktadrprc-1]

Orphan segment or invalid rdba in Index,Table,Partition etc. Example: An entry in sys.ind$ does not exist in sys.seg$

Note 136697.1 : "hcheck.sql" Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c

ORA-600[ktsircinfo_num1]

This exception occurs when there are problems obtaining the row cache information correctly from sys.seg$. In most cases there is no information in sys.seg$.

Note 136697.1 : "hcheck.sql" Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c

ORA-600[qertbfetchbyrowid]

This error might be that a row was not found in an Index. Perform the check in section "Identify TABLE / INDEX Mismatch" in:

Note 836658.1 : Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes

ORA-600[ktbdchk1-bad dscn]

This exception is raised when Oracle is performing a sanity check on the dependent SCN and fail.
The dependent scn is greater than the current scn.

十七、参考文档

Primary Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1)
Note 840978.1 : Physical and Logical block corruption
Note 472231.1 : How to identify all the Corrupted Objects in the Database reported by RMAN
Note 819533.1 : How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY
Note 48808.1 : OERR: ORA-00227 corrupt block detected in controlfile: (block %s, # blocks %s) Primary Note / Troubleshooting, Diagnostic and Solution
Note 1950230.1 : Solving UNDO Corruption
Note 281429.1 : Basic Steps to be Followed While Solving ORA-00600 [4194]/[4193] Errors Without Using Unsupported parameter
Note 39283.1 : ORA-600 [4194] "Undo Record Number Mismatch While Adding Undo Record"
Note 431652.1 : How to Change the Existing Undo Tablespace to a New Undo Tablespace
Note 136697.1 : "hcheck.sql" Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c
Note 332672.1 : ORA-354 ORA-353 and ORA-312 on Redo Log Group members
Note 1031381.6 : How to Dump Redo Log File Information.
Note 428526.1 : Baddata Script To Check Database For Corrupt column data
Note 976591.1 : How To validate a date/timestamp column
Note 869305.1 : How To identify a 'corrupt' row when error is raised but no row information provided
Note 136620.1 : Sanity Check of Oracle NUMBERS, How to Find and Patch
Note 836658.1 : Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes
Note 438143.1 : Use of dbms_metadata.get_ddl() to extract Index DDL
Note 394143.1 : How Could I Format The Output From Dbms_metadata.Get_ddl Utility?
Note 28814.1 : Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g
Note 452341.1 : How to detect Lob Corruption
Note 293515.1 : ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors
Note 1527738.1 : SCRIPT to skip ORA-8103 ORA-1410 ORA-1578 ORA-600 [kdsgrp1] when reading a TABLE
Note 1332088.1 : How to clear a block corruption in a TEMPORARY segment
Note 422039.1 : Steps to drop a temporary segment while SMON is not able
Note 556733.1 : DBMS_REPAIR script
Note 68013.1: DBMS_REPAIR example
最后修改时间:2022-12-05 09:32:11
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论