Results 1 to 13 of 13

Thread: Corrupt blocks When Backing up (urgent)

  1. #1
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    36

    Corrupt blocks When Backing up (urgent)

    Hi Friends,

    I get the following error messgae when I scheduled my backups thsi weekend

    ORA-19566 exceeded limit of string corrupt blocks for file string

    Could you please tell me what I need to do ? Please get back as soon as possible

    Rukmini
    rmadduluri@stantec.com

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Identify what tablespace the file belongs to, then run export of this tablespace. If you can export fine, then there may not be any problem. Otherwise you may have to rebuild the tablespace.

  3. #3
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    36
    I identified the tablespace and also the table that is corrupt by running dbverify. there is one block that is corrupt

    I tried to do create xxx as select * from the corrupted table and it fails on the block. I'm trying to run the Dbms_repair package and it fails right in the first procedure when I try to run Admin_tables procedure to create repair_table .

    BEGIN
    DBMS_REPAIR.ADMIN_TABLES (
    TABLE_NAME => 'REPAIR_TABLE',
    TABLE_TYPE => dbms_repair.repair_table,
    ACTION => dbms_repair.create_action,
    TABLESPACE => 'USERS');
    END;

    I run thsi script and get an error saying that dbms_repair.repair_table should be decalred. I'm trying to skip the corrupt blocks and copy the data. It is a huge tablespace and I do not want to export and reimport.

    What did youmean by rebuilding the tablespace

  4. #4
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    36
    I was too stupid while running the DBMS_REPAIR. I was able to run the fix_corrupt_blocks procedure and now I'm trying to export the tablespace. I'll have to see if it will be sucessful and figure out if we have lost any data.

  5. #5
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    36
    skhanal,

    while resolving this error on one database , the other database also had a corrupt block in a cluster. Should I drop the cluster and recreate it , what will happen to the tables in that cluster if I drop the cluster.

    Thanks
    Rukmini

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Dropping cluster drops tables inside it.

  7. #7
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    36
    Thanks I did try that and it worked. When I do an RMAN Restore , isn't it supposed to recover the System01.dbf too. B'se my database is in NoArchiveLog mode, I have to Restore the entire database in Mount state and I did that this morning but at the end I tried to open the database and it complained that "system01.dbf" needs medai recovery. What does that indicate?

    Also one of our database is extremely slow since yesterday, there was a corrupt block in one of the table. I dropped the table and recraeted it . Everything seems to be fine but is extremely slow even to run simple sql queries. Should I do an analyze ?

    Thanks for all you help.

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Did you restore the whole database using RMAN?. With RMAN you can restore database, tablespace or individual files.

    After you recreated the table did you recreate all the indexes.

  9. #9
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    36
    Yes I did restore the whole database using RMAN . I had no other option b'se it is in NOARCHIVELOG mode. I did a full backup and a full restore. I thought RMAN will restore all objects inmcluding indexes, I did not recreate indexes.

  10. #10
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    I dropped the table and recraeted it
    I thought you recreated it yourself not by RMAN. If you used RMAN to restore the whole db then everything should be in.

    Try analyzing the table.

  11. #11
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    36
    You are right, we are talking about two databases taht had corrupt blocks.

    1) on One database I had to do complete restore RMAN

    2) on the other I was able to get away with dropping the table and recreating it .

    The first one gave me system01.dbf needs media recovery

    The second was extremely slow after I reacreated the table. After your reply to my post I went and checked my indexes and noticed that the index on that table was missing. I have now recreated the index and also doing the analyze . It should be okay now. Our developers are testing it and I should know the results in a few minutes.

    I appreciate your help and is there any advise from your side on some backup and restore strategies. Should I keep using RMAN to do my backup's or should I do manually backup's . Any suggestions are welcome.

    Thanks
    Rukmini

  12. #12
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    I prefer RMAN as you don't have to worry about putting tablespaces in backup mode for hot backup. As a side benefit you get consistency check on database because RMAN does not just copy files it backups only data. This also reduces the backup size.

  13. #13
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    36
    Thanks Shialesh , I hope I have got your first name right

Posting Permissions

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