Forum Stats

  • 3,781,097 Users
  • 2,254,480 Discussions
  • 7,879,564 Comments

Discussions

Oracle 9i System file get corrupted. How to remove the corruption.

534533
534533 Member Posts: 95
edited Oct 14, 2008 12:09AM in General Database Discussions
Hello,
I am facing problem during backup of oracle database (Oracle 9i, 32 bit windows). Pls see the following error messages.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sep 16 10:48:41 mnmccbr.mci.sc: * mnmcfs.mci.sc:E:\SC\SC12.1\Databases\Oracleconfig\oraBackupNM45 ORA-19566:
exceeded limit of 0 corrupt blocks for file E:\SC\SC12.1\DATABASES\ORACLECONFIG\SYS1NM45.ORA
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Output of dbverify.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBVERIFY: Release 9.2.0.6.0 - Production on Sat Oct 11 09:40:42 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = E:\SC\SC12.1\DATABASES\ORACLECONFIG\SYS1NM45.ORA
Page 28093 is marked corrupt ***
Corrupt block relative dba: 0x00406dbd (file 1, block 28093)
Bad check value found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0x00406dbd
last change scn: 0x0000.09abd22e seq: 0x2 flg: 0x06
consistency value in tail: 0xd22e0602
check value in block header: 0x8b91, computed block checksum: 0x800
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
DBVERIFY - Verification complete
Total Pages Examined : 31616
Total Pages Processed (Data) : 21138
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 7246
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1451
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1780
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 224578617 (0.224578617)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The Backup is enabled in our machine but we are not able to take backup since we we get the error (6 Sept). I am not sure whether archive log are available since 6th Sept. Even oracle is working fine but backup is not possible.

Pls note that this is customer testbed. Any idea to remove the corruption from the system file.


Thanks
With Regards
Hemant Joshi
Tagged:

Answers

  • orawarebyte
    orawarebyte Member Posts: 3,838 Silver Badge
    edited Oct 13, 2008 6:56AM
    Corrupt block relative dba: 0x00406dbd (file 1, block 28093)

    yours block 28093 is corrupted either you need to recover the block or restore the file 1 and then recover for detail see this link.

    https://metalink2.oracle.com/metalink/plsql/f?p=130:14:6831911279782618352::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,373595.1,1,1,1,helvetica
    Yours backup will always perfom checksum and will stuck with this corrupted block,nevertheless since you never encounter to query or make any DML within block records yet thats why you are not facing any issue during daily activity.

    Khurram
  • Tinku
    Tinku Member Posts: 302
    Hi

    Try to find out segment on the basis of file 1, block 28093.you will get information about how important segment's block is corrupted.


    Tinku
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    Hemant,
    Unfortunately, you have a data block corrupted in the system datafile. As pointed out already, the only way to recover it (actually if you want to recover) would be get that backup which is containing the data for this file from the good times. If you have that backup with you than using RMAN's BlockRecover , you can do the recovery of the corrupted block.
    HTH
    Aman....
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    Aman, how about going for "DBMS_REPAIR" in this case.. !!

    - Pavan Kumar N
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    Pavan,
    IMO dbms_repair is the most misleading kind of package. Though the name says "repair" but AFAIK ,there is no actual recovery done by it. All it does is "marking" of the block as corrupted. This is not recovery. The reason I didn't suggest OP for going for this option was that he is referring to System datafile and it may be possible that the block is a part of some system object. If it would be a normal file, probably I would have suggested. The only option which is there for "actual recovery" is to get a good backup and recovery either the file or the block.
    Cheers
    Aman....
  • 534533
    534533 Member Posts: 95
    Hello All,
    I tried the resize procedure but the corruption doesn't cleared.
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL> ALTER DATABASE DATAFILE 'E:\SC\SC12.1\DATABASES\ORACLECONFIG\SYS1NM45.ORA' RESIZE 246 M;
    Database altered.
    SQL> ALTER DATABASE DATAFILE 'E:\SC\SC12.1\DATABASES\ORACLECONFIG\SYS1NM45.ORA' RESIZE 245 M;
    Database altered.
    SQL> ALTER DATABASE DATAFILE 'E:\SC\SC12.1\DATABASES\ORACLECONFIG\SYS1NM45.ORA' RESIZE 244 M;
    ALTER DATABASE DATAFILE 'E:\SC\SC12.1\DATABASES\ORACLECONFIG\SYS1NM45.ORA' RESIZE 244 M
    *
    ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value

    SQL> ALTER DATABASE DATAFILE 'E:\SC\SC12.1\DATABAES\ORACLECONFIG\SYS1NM45.ORA' RESIZE 400 M;
    Database altered.
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------
    Can you please let me know how to recover corrupted Block or how to recover the corrupted file only.




    Thanks
    With Regards
    Hemant Joshi.
  • Enrique Orbegozo
    Enrique Orbegozo Member Posts: 594 Silver Badge
    Try
    RMAN> run {blockrecover datafile 1 block 28093;}
    But block recover only works with Enterprise Edition

    Enrique
  • Chris Slattery
    Chris Slattery Member Posts: 1,532 Blue Ribbon
    Figure out what object = segment is corrupted. it is likely this is an internal Oracle object; you need to raise it with them as a Metalink call to see if there is a way around it beyond a full export/import/rebuild cycle - e.g. by rerunning catproc or something.


    Have you found the root cause for the corruption ?
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    I tried the resize procedure but the corruption doesn't cleared.
    But who suggested this ? Did you read the replies where all mentioned to collect a good backup and perform recovery.I gave you a link for the command, Blockrecover . Did you see it?
    Cheers
    Aman....
  • Anand...
    Anand... Member Posts: 3,590
    edited Oct 14, 2008 12:09AM
    Hi..

    As your SYSTEM datafile is corrupted, blockrecover using RMAN utility is a good option as suggested by AMAN. Otherwise, create a new DB and go for full export-import activity,aithough it would be a time consuming activity.

    Anand
This discussion has been closed.