Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

Monthly Archives: November 2015

12c Dictionary Integrity Check SEG$ critical failure

Database version 12.1.0.2.0

Problem

After execution of Dictionary Integrity Check of Database Health Monitor CRITICAL FAILURE related to seg$ table reported.

BEGIN
  DBMS_HM.RUN_CHECK (
   check_name   => 'Dictionary Integrity Check',
   run_name     => 'DictCheck001');
END;
/

Report for this run:

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.GET_RUN_REPORT('DictCheck001') FROM DUAL;

Basic Run Information
 Run Name		      : DictCheck001
 Run Id 		      : 656
 Check Name		      : Dictionary Integrity Check
 Mode			      : MANUAL
 Status 		      : COMPLETED
 Start Time		      : 2015-11-06 11:52:28.483834 +01:00
 End Time		      : 2015-11-06 11:52:29.315382 +01:00
 Error Encountered	      : 0
 Source Incident Id	      : 0
 Number of Incidents Created  : 0

Input Paramters for the Run
 TABLE_NAME=ALL_CORE_TABLES
 CHECK_MASK=ALL

Run Findings And Recommendations
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 657
 Type	       : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: seg$.type# 31 on object SEG$
	       failed
 Message       : Damaged rowid is AAAAAIAABAAAEP/AAQ - description: Ts# 1
	       File# 2 Block# 30464 is referenced

Cause

The cause of this issue is actually bug in Health Monitor related to new feature introduced in 12c, which called HEATMAP. It is when oracle will track access to data segments and blocks. It collects this statistics in memory and periodically purges to disk.

SQL> select file#, block#, type#, ts#, blocks from seg$ where rowid='AAAAAIAABAAAEP/AAQ';

     FILE#     BLOCK#	   TYPE#	TS#	BLOCKS
---------- ---------- ---------- ---------- ----------
	 2	30464	      11	  1	  1024

Check what is located in this segment:

SQL> col segment_name format a30
SQL> select segment_name, SEGMENT_TYPE, SEGMENT_TYPE_ID from sys_dba_segs where tablespace_id=1 and header_file= 2 and header_block=30464;

SEGMENT_NAME		       SEGMENT_TYPE	  SEGMENT_TYPE_ID
------------------------------ ------------------ ---------------
HEATMAP 		       SYSTEM STATISTICS	       11

Segment type 11 is related to new SYSTEM STATISTICS segment type, which is new in 12c, there was no such segment type in pre-12c databases.

Health Monitor as part of Dictionary Integrity check executes next query internally:

SQL> select 31, rowid, 'seg$.type#' from SEG$ where type# not between 1 and 10;

	31 ROWID	      'SEG$.TYPE
---------- ------------------ ----------
	31 AAAAAIAABAAAEP/AAQ seg$.type#

It assumes that in SEG$ should not be any segments with type more than 10, which is true for pre-12c databases, for 12c this code should be modified to include also segment_type 11.

Consclusion

This FAILURE can be safely ignored until the bug will be fixed.

Workaround

After Health Monitor will detect this issue RMAN will also list it as the CRITICAL failure in failures list.

RMAN> list failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
597        CRITICAL OPEN      05-NOV-15     SQL dictionary health check: seg$.type# 31 on object SEG$ failed

You should CLOSE it, use CHANGE FAILURE rman command:

RMAN> change failure 597 closed;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
597        CRITICAL OPEN      05-NOV-15     SQL dictionary health check: seg$.type# 31 on object SEG$ failed

Do you really want to change the above failures (enter YES or NO)? YES
closed 1 failures

Now it will not appear any more in failures list and the status will switch to CLOSED in Health Monitor rpoerts.

SQL> SELECT DBMS_HM.GET_RUN_REPORT('DictCheck001') FROM DUAL;
Basic Run Information
 Run Name		      : DictCheck001
 Run Id 		      : 656
 Check Name		      : Dictionary Integrity Check
 Mode			      : MANUAL
 Status 		      : COMPLETED
 Start Time		      : 2015-11-06 11:52:28.483834 +01:00
 End Time		      : 2015-11-06 11:52:29.315382 +01:00
 Error Encountered	      : 0
 Source Incident Id	      : 0
 Number of Incidents Created  : 0

Input Paramters for the Run
 TABLE_NAME=ALL_CORE_TABLES
 CHECK_MASK=ALL

Run Findings And Recommendations
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 657
 Type	       : FAILURE
 Status        : CLOSED
 Priority      : CRITICAL
 Message       : SQL dictionary health check: seg$.type# 31 on object SEG$
	       failed
 Message       : Damaged rowid is AAAAAIAABAAAEP/AAQ - description: Ts# 1
	       File# 2 Block# 30464 is referenced

 

 


(c) Aychin Gasimov, 11/2015, Munich, Germany