Forum Stats

  • 3,827,258 Users
  • 2,260,753 Discussions
  • 7,897,193 Comments

Discussions

Issue after DBMS_REPAIR

smee
smee Member Posts: 42
edited Oct 2, 2013 3:52AM in General Database Discussions

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

Tagged:

Answers

  • Fran
    Fran Member Posts: 3,211

    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.

  • smee
    smee Member Posts: 42

    Hi Fran,

    tank you for your response.

    Yes, when I execute first query i get:

    [email protected] 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

  • Raghunath Dhandapani
    Raghunath Dhandapani Member Posts: 10
    edited Oct 1, 2013 8:57AM

    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

  • smee
    smee Member Posts: 42

    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

  • 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?

  • smee
    smee Member Posts: 42
  • 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.

  • Fran
    Fran Member Posts: 3,211
    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

This discussion has been closed.