ORA-600 [kdsgrp1] / ORA-1555 / ORA-600 [ktbdchk1: bad ds_sqlora

This is commonly seen in a Data Guard Physical Standby Database configuration after a Switchover or Failover but may also occur in all kind of databases (with no dataguard configuration).

SYMPTOMS

Different errors may occur related to dependent scn such as:

ORA-1555
ORA-600 [2663]
ORA-600 [kdsgrp1]
ORA-600 [ktbdchk1: bad dscn]

DBVERIFY reports the next error when the fix of Bug 7517208 is present; reference Note 7517208.8 for interim patches:

itl[] has higher commit scn(aaa.bbb) than block scn (xx.yy)
Page failed with check code 6056

WORKAROUND

Install Patch 22241601 to prevent this issue from being introduced.

After installing Patch 22241601 Oracle tries to repair existent invalid ITL commit scn (healing). There is not need to set any parameter for it. _ktb_debug_flags=8 is now set by default thus the healing is enabled by default when Patch 22241601 is installed.

For already existent invalid SCNs on disk, the SCN is repaired when the Index block is cleaned out (example: in a block update). While blocks are not touched, dbverify still reports the 6056 errors.

Blocks are repaired when they are cleaned out. Trace file shows a message like:

Healing Corrupt DLC ITL objd:%d
objn:%d tsn:%d rdba: itl:%d

 option:%d xid:
cmtscn: curscn:

The above "Healing Corrupt.." tracing can be avoided by installing the fix of Bug 22756771 and adding the value 256 to the parameter _ktb_debug_flags; then _ktb_debug_flags=264 (8+256) will try to heal already affected blocks without adding tracing.

Sometimes the fix may not repair the block for an already existent invalid SCN on disk; then repair this issue with:

1. Recreate the affected Index in the Primary Database. Use Note 819533.1 to identify the INDEX reported by DBVerify.
or
2. If the issue is in the standby database: properly refresh the affected file from primary to standby. If the error is in the primary database, properly refresh the file from the standby to the primary database.

Additional Notes / Summary

Identify the affected indexes by running DBVerify with the fix of Bug 7517208 in place; reference Note 7517208.8 for versions including this fix and how to get interim patches.

DBVERIFY with the fix of Bug 7517208 is enhanced to identify the affected blocks. RMAN does not detect it; v$database_block_corruption would not have information about these blocks.

This fix is not disabled if _ktb_debug_flags is set to 0; it will only disable the healing to repair an already invalid SCN in the INDEX Itl but the fix still prevents this problem to be introduced as it does not depend of any parameter.

PATCHES

Install Patch 22241601

How to install the patch in a Dataguard configuration?

In a Dataguard environment the patch must be applied to both primary and standby databases. Because the fix cannot be made as DG rolling patch installable, the procedure to install an one off patch in Dataguard is:

Shutdown primary database (SHUTDOWN NORMAL or IMMEDIATE)
Make sure all standby databases have applied redo generated by the primary
Shutdown the standby database
Apply the patch on standby databases and bring to either the mount or open read only state
Restart managed recover on the standby database
Apply the fix on primary and open the primary in read / write mode.

HISTORY

11-APR-2014 - Converted this note to an alert document

17-SEP-2014 - Clarified more that proactively setting "_ktb_debug_flags" prevents this issue from happening.

01-DEC-2014 - Typo corrected

28-APR-2015 - Included RDBMS version 12.1.0.2 in the list of Products as also affected

02-JUN-2015 - For versions where the fix of Bug 8895202 is not included like 11.1.0.7.0 and 11.2.0.1.0, install an one off patch for bug 13513004which also contains this fix and is enabled in the same way.

22-JUN-2015 - Added workaround of "_smu_debug_mode" for ORA-600 [ktbgcl1_KTUCLOMINSCN_1]

08-OCT-2015 - Included specific versions instead of referencing 11.2.0.2 or later, clarified what versions are exposed to ORA-600 [ktbgcl1_KTUCLOMINSCN_1] and added section "Instructions for each version"

09-OCT-2015 - In Patches section: changed Patch 8895202 by Patch 13513004

19-FEB-2016 - Removed the reference of "_smu_debug_mode" for ORA-600 [ktbgcl1_KTUCLOMINSCN_1] as it may cause other errors. The recommendation in versions affected by Bug 13513004 (only 11.2.0.2 and 11.2.0.3) is to install patch 13513004

01-APR-2016 - Added reference to bug 22756771 which add the value 256 to _ktb_debug_flags to avoid tracing; then _ktb_debug_flags=264 (8+256) will be a workaround for this issue without adding tracing.

16-APR-2016 - The true root cause of this issue has been identified in bug 22241601 which replaces bug 8895202 and bug 13513004. There is not need to set _ktb_debug_flags to enable this fix as bug 22241601 is a formal fix (not a workaround).

REFERENCES

BUG:8895202 - ITL HAS HIGHER COMMIT SCN THAN BLOCK SCN
NOTE:819533.1 - How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY
BUG:13513004 - ORA-600 [KTBGCL1_KTUCLOMINSCN_1] ON BLOCKS FIXED UP BY 8895202
BUG:22241601 - ORA-600 [KDSGRP1] IN ADG AFTER FAILOVER

How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY (文档 ID 819533.1)

https://support.oracle.com/epmos/ui/images/quicklink_smlarrowdown_enabled.gif转到底部

https://support.oracle.com/epmos/adf/images/t.gif[kdsgrp1] / ORA-1555 / ORA-600 [ktbdchk1: bad ds" />

In this Document

Goal

Solution

Identify the Absolute File Number (AFN) and Block Number (BL)

Identify the Corrupt Object

Fixing Corruptions

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.5.0 to 12.1.0.2 [Release 8.1.5 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 21-Oct-2013***

GOAL

The purpose of this note is to provide the instructions to identify the corrupt Object reported by the error ORA-1578 or tools like RMAN / DBVERIFY

SOLUTION

Identify the Absolute File Number (AFN) and Block Number (BL)

The absolute and relative file numbers (RFN) are often the same but can differ (especially if the database has been migrated from Oracle7 or if Transportable/Plugged Tablespaces are used). It is important to get the correct numbers for the AFN and RFN or you may end up salvaging the wrong object.

Getting the AFN from ORA-1578

The AFN is provided by the error ORA-1110 produced right after the ORA-1578. In the next example the AFN is 5 and BL is 34.

SQL> select * from scott.dept_view;

select * from scott.dept_view

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 11, block #
34)

ORA-01110: data file 5:
'/home/oracle/oradata/users.dbf'

Getting the AFN from DBVERIFY output

A corrupt block might be reported by dbverify in different ways. DBVERIFY normally provides the RDBA associated to the affected block. Then the RFN is used to get the AFN in the query from dba_data_files below. Here are some examples:

RFN=11 BL=34:

Page 34 is marked corrupt

Corrupt block relative dba: 0x02c00022 (file 11, block
34)

Bad check value found during dbv:

Data in bad block:

   type: 6 format: 2 rdba:
0x02c00022

   last change scn:
0x0771.4eebe71c seq: 0x2 flg: 0x04

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

   consistency value in tail:
0xe71c0602

   check value in block header:
0xd3ce

   computed block checksum:
0x2

Dbverify always reports the relative data block address (rdba/dba) in its output. In the above case the Relative dba is the hexadecimal value0x02c00022 taken from Message "Corrupt block relative dba: 0x02c00022 (file 11, block 34)". The rdba/dba provides the RFN. The RFN is then 11. Use the query from dba_data_files below to get the AFN.

Another example from dbverify is:

RFN=11 BL=35:

Dbv output:

DBV-200: Block, dba 46137379,
already marked corrupted"

To get the RFN and Block# use the next query:

select dbms_utility.data_block_address_file(&&rdba)
RFN,

dbms_utility.data_block_address_block(&&rdba) BL

from dual;

Example:

SQL> select dbms_utility.data_block_address_file(&&rdba)
RFN,

   2
dbms_utility.data_block_address_block(&&rdba) BL

   3 from dual;

Enter value for
rdba: 46137379

RFN       
BL

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

11        
35

Get the AFN from the RFN using dba_data_files:

select file_id AFN, relative_fno, tablespace_name

from dba_data_files

where relative_fno=&RFN;

Example:

SQL> select file_id AFN, relative_fno, tablespace_name

   2 from dba_data_files

   3 where
relative_fno=&RFN;

Enter value for rfn: 11

AFN       
RELATIVE_FNO TABLESPACE_NAME

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

5         
11          
USERS

The AFN is 5

Getting the AFN from RMAN

RMAN report corruptions in the v$database_block_corruption view.

The column FILE# in that view is the AFN. Column BLOCK# is BL.

Identify the Corrupt Object

Once that the AFN is identified, run the next query to identify the corrupt Object:

select * 

from dba_extents

where file_id = &AFN

and &BL between block_id AND block_id + blocks -
1;

Example:

SQL> select *

2 from dba_extents

3 where file_id = &AFN

4 and &BL between block_id AND block_id + blocks - 1;

Enter value for afn: 5

Enter value for bl: 34

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES      BLOCKS RELATIVE_FNO

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

SCOTT DEPT                        TABLE        USERS           0         5       33       65536      8      11

If the above query does not return rows, it can also be that the corrupted block is a segment header in a Locally Managed Tablespace (LMT). When the corrupted block is a segment header block in a LMT, the above query produces a corruption message in the alert.log but the query does not not fail. In that case run this query:

select owner, segment_name, segment_type,
partition_name 

from   dba_segments

where  header_file = &AFN

  and  header_block =
&BL;

If the block belongs to a free EXTENT (not associated to an object) or if the block is in a TEMPFILE the above queries will return no data. For TEMPFILES the "Segment Type" will be "TEMPORARY".

If the block belongs to a free extent it should appear in DBA_FREE_SPACE:

select * 

from  dba_free_space

where file_id = &AFN

  and &BL between block_id AND block_id +
blocks - 1;

Note that in Oracle 10g and above when an ORA-1578 is produced, the alert log is also updated with the information of the corrupt object. Example:

Corrupt Block Found

TSN = 5, TSNAME = USERS

RFN = 11, BLK = 34, RDBA = 46137378

OBJN = 46107, OBJD = 36440, OBJECT = DEPT, SUBOBJECT =

SEGMENT OWNER = SCOTT, SEGMENT TYPE = Table Segment

Fixing Corruptions

Once that the Corrupt Object has been identified solve the corruption by following the next articles:

If getting corruption error ORA-1578 follow the instructions in Doc ID 1578.1 to fix the corruption.

To fix other corruptions follow Doc ID 28814.1

REFERENCES

NOTE:472231.1 - How to identify all the Corrupted Objects in the Database with RMAN
NOTE:28814.1 - Handling Oracle Block Corruptions
NOTE:836658.1 - Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes
NOTE:1578.1 - OERR: ORA-1578 "ORACLE data block corrupted (file # %s, block # %s)" Master Note

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：重建索引的进度查看

后一篇：VBS监控CPU的使用率

新浪BLOG意见反馈留言板　欢迎批评指正