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= |
|
