By William Sescu
When I don’t need I feature, I don’t turn it on, or do not use it because it reduces the possibility to run into issues. Most of the times this is true, however, during the preparation for an RMAN workshop, the RMAN list failure command showed me the following dictionary issue.
RMAN> list failure; using target database control file instead of recovery catalog Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- -------------------- ------- 2 CRITICAL OPEN 13-FEB-2017 10:12:26 SQL dictionary health check: seg$.type# 31 on object SEG$ failed
I thought first, that it might be related to some incorrect errors shown by the health check (DBMS_HM), because there used to be some issues with that tool. But even after applying the following patch, nothing changed and the error still appears.
19543595: INCORRECT HEALTHCHECK ERRORS FROM DBMS_HM – FALSE ERRORS ON TS$ , FILE$ OR USER
So I started a manual health check again to get some more details.
SQL> BEGIN 2 DBMS_HM.RUN_CHECK (check_name => 'Dictionary Integrity Check', 3 run_name => 'WilliamsDICTrun002', 4 input_params => 'CHECK_MASK=ALL'); 5 END; 6 / PL/SQL procedure successfully completed. SQL> SELECT DBMS_HM.GET_RUN_REPORT('WilliamsDICTrun002') from dual; DBMS_HM.GET_RUN_REPORT('WILLIAMSDICTRUN002') --------------------------------------------------------------------- Basic Run Information Run Name : WilliamsDICTrun002 Run Id : 61 Check Name : Dictionary Integrity Check Mode : MANUAL Status : COMPLETED Start Time : 2017-02-13 10:56:58.250100 +01:00 End Time : 2017-02-13 10:56:58.689301 +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 : 62 Type : FAILURE Status : OPEN Priority : CRITICAL Message : SQL dictionary health check: seg$.type# 31 on object SEG$ failed Message : Damaged rowid is AAAAAIAABAAAK+RAAc - description: Ts# 1 File# 2 Block# 28032 is referenced
Now I do have the ROWID, the file number and the block number of the affecting object. Let’s see what it is.
SQL> select FILE#, BLOCK#, TYPE#, TS#, BLOCKS from seg$ where rowid='AAAAAIAABAAAK+RAAc'; FILE# BLOCK# TYPE# TS# BLOCKS ---------- ---------- ---------- ---------- ---------- 2 28032 11 1 1024 SQL> SELECT segment_name, segment_type, block_id, blocks 2 FROM dba_extents 3 WHERE 4 file_id = 2 5 AND 6 ( 28032 BETWEEN block_id AND ( block_id + blocks ) ); SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BLOCKS -------------------------- ------------------------ ---------- ---------- HEATMAP SYSTEM STATISTICS 28032 1024
Really strange. It is related to the HEATMAP segment, but I am not using the heat map feature, or used it in the past.
SQL> show parameter heat NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ heat_map string OFF SQL> select name, DETECTED_USAGES from DBA_FEATURE_USAGE_STATISTICS where name like 'Heat%'; NAME DETECTED_USAGES ------------------------ --------------- Heat Map 0
But how can I get this fixed now? You could either ignore this issue, create a SR at Oracle, or you can drop the statistics segment, in case you are not using the heatmap feature.
In my case, I decided to the drop the statistics segment by issuing the following command. Dropping the statistics segment works by setting the underscore parameter “_drop_stat_segment” to 1.
SQL> select SEGMENT_NAME, SEGMENT_TYPE from dba_extents where SEGMENT_TYPE = 'SYSTEM STATISTICS'; SEGMENT_NAME SEGMENT_TYPE -------------------------- ------------------------ HEATMAP SYSTEM STATISTICS SQL> ALTER SYSTEM SET "_drop_stat_segment"=1 scope=memory; System altered. SQL> select SEGMENT_NAME, SEGMENT_TYPE from dba_extents where SEGMENT_TYPE = 'SYSTEM STATISTICS'; no rows selected
The heat map table is gone now. Let’s run the dictionary check again.
SQL> BEGIN 2 DBMS_HM.RUN_CHECK (check_name => 'Dictionary Integrity Check', 3 run_name => 'WilliamsDICTrun003', 4 input_params => 'CHECK_MASK=ALL'); 5 END; 6 / PL/SQL procedure successfully completed. SQL> SELECT DBMS_HM.GET_RUN_REPORT('WilliamsDICTrun003') from dual; DBMS_HM.GET_RUN_REPORT('WILLIAMSDICTRUN003') --------------------------------------------------------------------- Basic Run Information Run Name : WilliamsDICTrun003 Run Id : 81 Check Name : Dictionary Integrity Check Mode : MANUAL Status : COMPLETED Start Time : 2017-02-13 11:17:15.190873 +01:00 End Time : 2017-02-13 11:17:15.642501 +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 RMAN> list failure; using target database control file instead of recovery catalog Database Role: PRIMARY no failures found that match specification
Looks much better now.
Conclusion
Even if you are not using some features, you can still have trouble with them. 🙂