Is there a view that display data block corruption?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Is there a view that display data block corruption?

  1. #1
    Join Date
    Apr 2007
    Posts
    13

    Is there a view that display data block corruption?

    Hi,

    Is there a view that show data block corruption? For example, The V$DATABASE_BLOCK_CORRUPTION view display the information about the data block corruptions that marked corrupt since the last backup, correct?
    But, If I don't have scheduling backup every day, How Do I discover the data block corruptions whitout the alert log file?

    I'm sorry to ask this, but I searched and I didn't find it.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Some of the other ways to detect are to use one of the following:
    DBMS_REPAIR package
    DB_VERIFY utility
    ANALYZE TABLE ... VALIDATE STRUCTURE
    enable DB_BLOCK_CHECKING at the instance level.
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184
    Please follow the steps in the Metalink Note ID Below:

    Metalink Note: 28814.1

  4. #4
    Join Date
    Apr 2007
    Posts
    13
    sambavan
    I am trying to use the DBMS_REPAIR.CHECK_OBJECT package, but I am not understanding what it is happen.
    Let me see how I am doing.
    SET SERVEROUTPUT ON
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
    DECLARE num_corrupt INT;
    VSCHEMA_NAME VARCHAR2(20):= 'WEB';
    VOBJECT_NAME VARCHAR2(90):= 'ATNET_PERMISSAO_SISTEMA';
    VDTIME DATE;
    BEGIN
    num_corrupt := 0;

    SELECT SYSDATE INTO VDTIME FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('--- START TIME: ' || VDTIME || ' ---');

    DBMS_REPAIR.CHECK_OBJECT (
    SCHEMA_NAME => VSCHEMA_NAME,
    OBJECT_NAME => VOBJECT_NAME,
    REPAIR_TABLE_NAME => 'REPAIR_TABLE',
    CORRUPT_COUNT => num_corrupt);

    DBMS_OUTPUT.PUT_LINE('NUMBER CORRUPT IN ' || VOBJECT_NAME || ': ' || TO_CHAR (num_corrupt));

    SELECT SYSDATE INTO VDHORA FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('--- END TIME: ' || VDTIME || ' ---');
    END;
    /


    and when I call by the sql prompt, a errour occurs:
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 6, block # 193)
    ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/web01.dbf'
    ORA-06512: at "SYS.DBMS_REPAIR", line 293
    ORA-06512: at line 11

    I generated a block corruption on this datafile, web01.dbf, for to simulate and to use this package.
    Did you understand what I want to do?
    Did I make anything wrong?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width