This discussion is archived
8 Replies Latest reply: Oct 2, 2013 12:52 AM by Fran RSS

Issue after DBMS_REPAIR

smee Newbie
Currently Being Moderated

Hi all,

 

I have encountered ORA-01578: ORACLE data block corrupted issue with one of tables in my database. Database version is Oracle 10.2.0.4 on Windows Server 2003. After I solved this problem by using document Using DBMS_REPAIR to Repair Data Block Corruption, I found that I'm unable to insert new data into table. Select and update work fine, but insert does not. When I try to insert new record I get ORA-01578 error again.

 

Do someone have idea how this happened?

 

Tnx,

Smee

  • 1. Re: Issue after DBMS_REPAIR
    Fran Guru
    Currently Being Moderated

    could you execute?:

    SQL> select * from V$DATABASE_BLOCK_CORRUPTION;


    rman> list failure:

    rman> advise failure <failure_id>;

    rman> repair failure;

     

    and when finish, execute again :

    SQL> select * from V$DATABASE_BLOCK_CORRUPTION;


    to check if block corruption disappeared.

  • 2. Re: Issue after DBMS_REPAIR
    smee Newbie
    Currently Being Moderated

    Hi Fran,

     

    tank you for your response.

     

    Yes, when I execute first query i get:

    SYS@dmsora SQL>  select * from V$DATABASE_BLOCK_CORRUPTION;
         FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
    ---------- ---------- ---------- ------------------ ---------
            17        133          1                  0 CHECKSUM

     

    After that I god:

     

    RMAN> list failure;
    List of Database Failures
    =========================
    Failure ID Priority Status    Time Detected Summary
    ---------- -------- --------- ------------- -------
    522        HIGH     OPEN      01-OCT-13     Datafile 17: 'D:\ORADATA\TEST_CORRUP
    T\DBF_CORRUPT01.DBF' contains one or more corrupt blocks
    RMAN> advise failure 522;
    List of Database Failures
    =========================
    Failure ID Priority Status    Time Detected Summary
    ---------- -------- --------- ------------- -------
    522        HIGH     OPEN      01-OCT-13     Datafile 17: 'D:\ORADATA\TEST_CORRUP
    T\DBF_CORRUPT01.DBF' contains one or more corrupt blocks
    analyzing automatic repair options; this may take some time
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=133 device type=DISK
    analyzing automatic repair options complete
    Mandatory Manual Actions
    ========================.
    No backup of block 133 in file 17 was found. Drop and re-create the associated
    object (if possible), or use the DBMS_REPAIR package to repair the block corrution
    2. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair
    Optional Manual Actions
    =======================
    no manual actions available
    Automated Repair Options
    ========================
    no automatic repair options available
    RMAN> repair failure;
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of repair command at 10/01/2013 10:30:39
    RMAN-06953: no automatic repairs were listed by ADVISE FAILURE

     

    BR,

    Smee

  • 3. Re: Issue after DBMS_REPAIR
    RaghunathDhandapani Newbie
    Currently Being Moderated

    Hi,

     

    Please use the below query to determine, which object is corrupted.

     

    select tablespace_name, segment_type, owner, segment_name 

    FROM dba_extents

    WHERE file_id = 17

    AND block_id < 133

    AND block_id + blocks >= 133;

     

    Post the results.

     

    If it is an index, drop it and re-create.

     

    If it is a table object, create table <table_name.duplicate> as select * from <table_name>

     

    drop <table_name>

     

    rename table <table_name.duplicate> to <table_name>.

     

     

    Also, It seems you do not have a backup , if you have a good backup, you could use the blockrecover in RMAN. Hope this helps.

     

    Thanks

    Raghu

  • 4. Re: Issue after DBMS_REPAIR
    smee Newbie
    Currently Being Moderated

    Hi RaghunathDhandapani,

     

    The corrupted object is table t1.

     

    TABLESPACE_NAME SEGMENT_TYPE       OWNER   SEGMENT_NAME

    ------------------------------ ------------------ ------------------------------- ----------------------------

    TS_CORRUPT          TABLE         TS_CORRUPT           T1

     

    Recreating table is one possibility, but I'm wonder why after dbms_repair I am not able to insert new record, I'm also not able to drop columns. I tried to recreate index but the problem remain.

    Here is my test case:

    - created tablespace, user, table, index

    - inserted data in table

    - altered tablespace offline

    - edited dbf file and change one record value (corrupted block) I don't know, maybe this is issue - I do not got logical corruption. Maybe I have to, somehow, initiate logical corruption.

    - altered tablespace online

    - repaired with dbms_repair

    - select works fine, but cannot insert new record.

     

    BR,

    Smee

  • 5. Re: Issue after DBMS_REPAIR
    RaghunathDhandapani Newbie
    Currently Being Moderated

    I hope you have performed the below steps in DBMS_REPAIR

    1)Create two administration tables to hold a list of corrupt blocks(Repair_table) and index keys (orphan_table)pointing to those blocks.

     

    2)Detect corrupt blocks using DBMS_REPAIR.check_object.

     

    3)Mark the blocks as corrupt using FIX_CORRUPT_BLOCKS .

     

    4)All indexes must be checked to see if any of their key entries point to a corrupt block using DBMS_REPAIR.dump_orphan_keys

    If the orphan key count is greater than 0 the index should be rebuilt.

     

    5)freelists must be rebuilt using the DBMS_REPAIR.rebuild_freelists.

     

    6) And then use DBMS_REPAIR.skip_corrupt_blocks to all DML statements ignore the data blocks marked as corrupt.

     

    Am i correct?

  • 6. Re: Issue after DBMS_REPAIR
    smee Newbie
    Currently Being Moderated

    Yes, you are.

  • 7. Re: Issue after DBMS_REPAIR
    RaghunathDhandapani Newbie
    Currently Being Moderated

    hmm, it seems your table is still corrupted, you have to work with DBMS_REPAIR again, follow the steps in the previous post,except creating the repair and orphan tables or you can re-create the table.

  • 8. Re: Issue after DBMS_REPAIR
    Fran Guru
    Currently Being Moderated

    smee escribió:

    No backup of block 133 in file 17 was found. Drop and re-create the associated 

    object (if possible), or use the DBMS_REPAIR package to repair the block corrution

    you should have backups, if you haven't, you can't use RMAN here.

     

    try this:

    1) export tablespace

    2) recreate table with alter table move to change to a different segment.

    3) import

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points