DBMS_SPACE_ADMIN.ASM_TABLESPACE_VERIFY

In this Document   Purpose Scope Details DBMS_SPACE_ADMIN Tablespace Verify Symptoms of Extent Inconsistency How to execute Tablespace Verify to Identify Extent Inconsistency? dbms_space_admin.assm_tablespace_verify dbms_space_admin.tablespace_verify How long does it take to run Tablespace Verify to Identify Extent Inconsistency? Locks acquired.  Can Tablespace Verify be run while there are concurrent DDL or DML?   10g ONLY: Tablespace Verify May Report FALSE Inconsistency Description of Inconsistencies Reported by Tablespace Verify   Extent Map Entry Corrupt Extent Map Entry Marked Partially Free in BitMap BitMap entry partially used with no Extent Map entry Extent Map Entry Overlaps with Another Extent Map Entry References   Applies to: Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform.   Purpose The purpose of this document is to describe the different corruptions / inconsistencies reported by package DBMS_SPACE_ADMIN in procedures ASM_TABLESPACE_VERIFY with the ts_option=>dbms_space_admin.TS_VERIFY_BITMAPS and TABLESPACE_VERIFY. For now this document does not detail the resolution of such inconsistencies. This document also describes the lock acquired in versions 10g or greater.  Scope   Details DBMS_SPACE_ADMIN Tablespace Verify Tablespace Verify is intended to identify EXTENT inconsistencies in Locally Managed Tablespaces (LMT). The documentation describes it as: "This detects bits that are marked used or free wrongly and detects multiple allocation of extents." An EXTENT is defined by: "Multiple contiguous data blocks allocated for storing a specific type of information. A segment is made up of one or more extents." Locally Managed Tablespaces tracks the EXTENT information in each datafile using bitmaps instead of Data Dictionary Tables.   Symptoms of Extent Inconsistency Symptoms of such inconsistencies can be manifested with several errors, here are the most common examples: ORA-8103 ORA-1410 ORA-600 [5464] ORA-600 [25027] ORA-600 [ktfbbset-2] ORA-600 [ktfbbssearch-9] ORA-600 [kcbz_check_objd_typ_3] ORA-600 [ktsbvmap1] ORA-600 [ktspgfblk3:kcbz_objdchk] ORA-600 ktrget2:kcbz_objdchk ORA-600 [ktspgfb-1] ORA-600 [ktsircinfo_num1] ORA-600 [ktssdrp1] ORA-600 [kddummy_blkchk] [file#] [block#] [18018] ORA-600 [kdBlkCheckError] [file#] [block#] [18018] etc   How to execute Tablespace Verify to Identify Extent Inconsistency? The syntax is: dbms_space_admin.assm_tablespace_verify('&tablespace_name',dbms_space_admin.TS_VERIFY_BITMAPS) dbms_space_admin.assm_tablespace_verify('&tablespace_name',dbms_space_admin.TS_VERIFY_BITMAPS) For each tablespace to be validated, it is recommended to execute tablespace verify in a separate session and/or define the tracefile_identifier before each run. Note that the session running dbms_space_admin does not report in the screen if an inconsistency is detected. dbms_space_admin will write in a trace file instead. Check if the trace file provided by "oradebug tracefile_name" has inconsistency messages: dbms_space_admin.assm_tablespace_verify For ASSM Tablespaces (DBA_TABLESPACES.SEGMENT_SPACE_MANAGEMENT=AUTO) run: alter session set tracefile_identifier='&tablespace_name'; execute dbms_space_admin.assm_tablespace_verify('&tablespace_name',dbms_space_admin.TS_VERIFY_BITMAPS) oradebug setmypid oradebug tracefile_name dbms_space_admin.tablespace_verify For MSSM (DBA_TABLESPACES.SEGMENT_SPACE_MANAGEMENT=MANUAL) run: alter session set tracefile_identifier='&tablespace_name'; execute dbms_space_admin.tablespace_verify('&tablespace_name') oradebug setmypid oradebug tracefile_name   How long does it take to run Tablespace Verify to Identify Extent Inconsistency? In internal tests with one cpu it took around two seconds to complete a tablespace verify for a tablespace size of 16GB with 16 datafiles holding 20502 tables.  It may take minutes in very large tablespaces.    Locks acquired.  Can Tablespace Verify be run while there are concurrent DDL or DML? In 11g and greater, tablespace verify takes a TT lock ID2=16 which is internally used for synchronizing the allocation and deallocation of extents; that avoids segments obtaining or releasing extents.   Once the lock is acquired then Tablespace Verify will not allow extent management in that tablespace; processes trying to allocate space in that tablespace will wait until verify is done.  The wait is shown as "enq: TT - contention"; v$session_wait.p3 for the waiting session has the tablespace number that can be identified in v$tablespace. This a lock example in 12c for a session running tablespace verify from v$lock: select * from v$lock where sid=193; ADDR             KADDR            SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID ---------------- ---------------- --- -- --- --- ----- ------- ----- ----- ------- 0000000077AC0D50 0000000077AC0DD0 193 TT 5   0   6     0       2     1     0 0000000077AC10B0 0000000077AC1130 193 TT 5   16  3     0       2     0     0 This is a session performing a create table in tablespace number 5 waiting for the lock: select event, p1,p2,p3 from v$session_wait where sid=18; EVENT                P1          P2 P3 -------------------- ----------  -- -- enq: TT - contention 1414791172  5  0   For 10g: 10g ONLY: Tablespace Verify May Report FALSE Inconsistency Oracle version 10g allows DDL and DML managing extents while tablespace verify is run.  This may then cause reporting False inconsistencies.  This does not happen in 11g.  As 10g is an unsupported version, the solution is to run tablespace verify while there are not processes working on the tablespace being verified.  Description of Inconsistencies Reported by Tablespace Verify These are the different inconsistencies that tablespace verify may identify; the details of the affected extents are described in the trace file generated: Extent Map Entry Corrupt "Extent Map Entry Corrupt" is an invalid extent map entry in the segment.  One example is when a table or index has invalid values in the extent map section. Example from the trace file by tablespace verify: Extent Map Entry Corrupt: SegDBA: 0x47800009 : ExtNo 1 UetDBA 0x00000000: ExtNbk: 0   Example of a corruption in extent map with invalid values of 0x00000000 extent rdba (this is not included in the trace file of tablespace verify): Extent Map ----------------------------------------------------------------- 0x00000000 length: 0 0x00000000 length: 0 0x00000000 length: 2560    Extent Map Entry Marked Partially Free in BitMap "Extent Map Entry Marked Partially Free in BitMap" refers to an extent map entry that is not marked used even though it is used by a segment. Example from the trace file by tablespace verify: Extent Map Entry Marked Partially Free in BitMap: SegDBA: 0xb5c0b87b : ExtNo 38 UetDBA 0x02c65985: ExtNbk: 64 BitMap entry partially used with no Extent Map entry "BitMap entry partially used with no Extent Map entry" refers to Extent Bitmap entry marked used in the bitmap, but there is no extent map entry. This can be produced if there is an ORPHAN Table (TAB$ with no SEG$ entry). Example from the trace file by tablespace verify: BitMap entry partially used with no Extent Map entry Range RelFno 11: BeginBlock: 410629 EndBlock: 4194303 hcheck.sql reports ORPHAN objects with errors: HCKE-0016 ORPHAN IND$ HCKE-0017 ORPHAN INDPART$ HCKE-0019 ORPHAN TAB$ HCKE-0020 ORPHAN TABPART$    Extent Map Entry Overlaps with Another Extent Map Entry "Extent Map Entry Overlaps with Another Extent Map Entry" refers to Overlap between two extent map entries. This may cause that two or more segments incorrectly use the same blocks overwriting each other. Example from the trace file by tablespace verify: Extent Map Entry Overlaps with Another Extent Map Entry SegDBA: 0x87af4d40 : ExtNo 1978 UetDBA 0x8988faf5: ExtNbk: 8 SegDBA: 0x8988faf8 : ExtNo 0 UetDBA 0x8988faf5: ExtNbk: 8 dbms_space_admin.tablespace_rebuild_bitmap reports ORA-600 [kddummy_blkchk]/ORA-600 [kdBlkCheckError] code error [18018] if there are overlapped extents. Between the two segments affected by overlapped extents, data of one segment is good, while the other segment had an extent overwritten. After dropping the object which data was lost, manually mark the extent at tablespace bitmap as used by running dbms_space_admin.tablespace_fix_bitmaps.   Until the bitmaps of the tablespace is rebuilt, overlapped extent will continue to be introduced as extents are allocated in the tablespace.       Identify the Segment  Follow Note 887263.1 to identify the segment involved in the inconsistency.   References NOTE:887263.1 - How to identify the SEGMENT reported by dbms_space_admin.TABLESPACE_VERIFY