get automatic notification of database corruption
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
A solution using DBMS_SYSTEM calls
One issue confronted by DBAs is that some exception conditions are not written to the alert log. So if a DBA is looking for a single place to find alerts and status information, even with OEM Grid Control, it does not exist. One example of such unreported information is that stored in the dynamic performance corruption views.

This demo is based on production code and is uses the undocumented built-in package DBMS_SYSTEM to write a timestamp to the alert log "KSDDDT" followed by a message using "KSDWRT." It has, to demonstrate the technique, been extended using a query based on the dba_extents view to identify the type of owner, object_type, object_name, and partition of corrupt blocks. Similar information with respect to corrupt backups and corrupt backup copies can also be written in a similar manner.

The following stored procedure is all that is required to make this work. The procedure should be run using DBMS_SCHEDULER at least once during each 24 hour period. Unless corruption is detected the overhead is extremely low and it should finish in one second or less.
CREATE OR REPLACE PROCEDURE corruption_alert IS
 i INTEGER;
 j INTEGER;
 k INTEGER;

 TYPE cbc_type IS TABLE OF dba_extents%ROWTYPE;
 cbc_tab cbc_type;

 CURSOR cbc_cur IS
 WITH q AS
 (SELECT file#, block# FROM gv$database_block_corruption)
 SELECT ds.owner, ds.segment_name, ds.partition_name, ds.segment_type, NULL TSNAME,
 NULL EXTID, NULL FILEID, e.ktfbuebno BLOCK_ID, NULL BYTES, NULL BLOCKS, NULL RFNO
 FROM sys.sys_dba_segs ds, sys.gv$ktfbue e, sys.file$ f, q
 WHERE e.ktfbuesegfno = ds.relative_fno
 AND e.ktfbuesegbno = ds.header_block
 AND e.ktfbuesegtsn = ds.tablespace_id
 AND ds.tablespace_id = f.ts#
 AND e.ktfbuefno = f.relfile#
 AND bitand(NVL(ds.segment_flags, 0), 1) = 1
 AND bitand(NVL(ds.segment_flags,0), 65536) = 0
 AND f.file# = q.file#
 AND e.ktfbuebno = q.block#;
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM gv$backup_corruption;

  IF i <> 0 THEN
    dbms_system.ksdfls;
    dbms_system.ksdddt;
    dbms_system.ksdwrt(2,'ORA-20341: GV$BACKUP_CORRUPTION contains '|| TO_CHAR(i)||' corruption records');
  END IF;

  SELECT COUNT(*)
  INTO j
  FROM gv$copy_corruption;

  IF j <> 0 THEN
    dbms_system.ksdfls;
    dbms_system.ksdddt;
    dbms_system.ksdwrt(2,'ORA-20342: GV$COPY_CORRUPTION contains ' || TO_CHAR(j)||' corruption records');
  END IF;

  SELECT COUNT(*)
  INTO k
  FROM gv$database_block_corruption;

  IF k <> 0 THEN
    dbms_system.ksdfls;
    dbms_system.ksdddt;
    dbms_system.ksdwrt(2,'ORA-20343: GV$DATABASE_BLOCK_CORRUPTION contains '||TO_CHAR(k)||' corruption records');

    OPEN cbc_cur;
    FETCH cbc_cur BULK COLLECT INTO cbc_tab;
    CLOSE cbc_cur;
    FOR l IN 1..cbc_tab.COUNT LOOP
      dbms_system.ksdwrt(2,'ORA-20343: block=' || cbc_tab(l).block_id ||
      ' owner=' || cbc_tab(l).owner ||
      ' segment_type=' || cbc_tab(l).segment_type ||
      ' segment_name=' || cbc_tab(l).segment_name ||
      ' partition_name=' || cbc_tab(l).partition_name
      );
    END LOOP;
  END IF;

  IF GREATEST(i,j,k) = 0 THEN
    dbms_system.ksdfls;
    dbms_system.ksdddt;
    dbms_system.ksdwrt(2,'ORA-00000: No Corruption Records Detected');
  END IF;
END corruption_alert;
/
In order to support testing, and further extending the code, I have created the following environment in which a schema named alerttest is the owner of the procedure, is granted all necessary permissions, and as block corruption hopefully does not exist in your system creates a local table with the name gv$database_block_corruption that you can load, as I have done below, with sample records.

To create realistic sample corruption records it is necessary to query dba_extents and choose file# and block# values corresponding to real objects in your database.
conn / as sysdba

CREATE USER alerttest
IDENTIFIED BY alerttest
DEFAULT TABLESPACE system
TEMPORARY TABLESPACE temp
QUOTA 1M ON SYSTEM;

CREATE OR REPLACE VIEW gv$ktfbue AS
SELECT * FROM x$ktfbue;

GRANT create session, create table, create procedure, create synonym TO alerttest;
GRANT execute ON dbms_system TO alerttest;
GRANT select ON dba_extents TO alerttest;
GRANT select ON gv_$backup_corruption TO alerttest;
GRANT select ON gv_$copy_corruption TO alerttest;
GRANT select ON gv_$database_block_corruption TO alerttest;
GRANT select ON file$ TO alerttest;
GRANT select ON sys_dba_segs TO alerttest;
GRANT select ON gv$ktfbue TO alerttest;

conn alerttest/alerttest

CREATE SYNONYM dbms_system FOR sys.dbms_system;

CREATE TABLE gv$database_block_corruption AS
SELECT * FROM sys.gv_$database_block_corruption
WHERE 1=2;

INSERT INTO gv$database_block_corruption
(inst_id, file#, block#, blocks, corruption_change#, corruption_type)
VALUES
(1, 22, 95778, 1, 10594075626107, 'CORRUPT');

INSERT INTO gv$database_block_corruption
(inst_id, file#, block#, blocks, corruption_change#, corruption_type)
VALUES
(1, 22, 99721, 1, 10594075637770, 'CORRUPT');

INSERT INTO gv$database_block_corruption
(inst_id, file#, block#, blocks, corruption_change#, corruption_type)
VALUES
(1, 22, 67147, 1, 10594075638237, 'CORRUPT');

INSERT INTO gv$database_block_corruption
(inst_id, file#, block#, blocks, corruption_change#, corruption_type)
VALUES
(1, 22, 73961, 2, 10594075788453, 'CORRUPT');

INSERT INTO gv$database_block_corruption
(inst_id, file#, block#, blocks, corruption_change#, corruption_type)
VALUES
(1, 22, 162833, 7, 10594075667134, 'CORRUPT');

col corruption_change# format 999999999999999

SELECT * FROM gv$database_block_corruption;

-- build the corruption_alert procedure here.
-- after running the procedure review the alert log.
After running the procedure, using test data similar to mine, you should see entries in your alert log similar to this:

Tue Feb 02 10:43:04 2010
ORA-20343: GV$DATABASE_BLOCK_CORRUPTION contains 5 corruption records
Tue Feb 02 10:43:42 2010
ORA-20343: block=3448 owner=UWCLASS segment_type=INDEX segment_name=PK_AIRPLANES partition_name=
ORA-20343: block=3504 owner=UWCLASS segment_type=INDEX segment_name=PK_AIRPLANES partition_name=
ORA-20343: block=3552 owner=UWCLASS segment_type=INDEX segment_name=PK_AIRPLANES partition_name=
ORA-20343: block=3560 owner=UWCLASS segment_type=INDEX segment_name=PK_AIRPLANES partition_name=
ORA-20343: block=3600 owner=UWCLASS segment_type=INDEX segment_name=PK_AIRPLANES partition_name=
 
Related Topics
DBMS_ROWID
DBMS_SYSTEM
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved