We are getting following error on our oracle(Release 8.1.5.0.0) based application. Could somebody advise, how we can recover from this.
ora-01578: oracle data block corrupted (file # 3, block # 15031)
ORA-01110: data file 3: 'E:\ORACLE\ORADATA\OASYSEG
DBVERIFY O/P :
C:\>dbv file = e:\oracle\oradata\oasysegc
DBVERIFY: Release 8.1.5.0.0 - Production on Thu Sep 6 18:49:07 2007
(c) Copyright 1999 Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = e:\oracle\oradata\oasysegc
Page 15031 is influx - most likely media corrupt
***
Corrupt block relative dba: 0x00c03ab7 file=0. blocknum=15031.
Fractured block found during dbv:
Data in bad block - type:6. format:2. rdba:0x00c03ab7
last change scn:0x0000.008809fd seq:0x2 flg:0x02
consistancy value in tail 0x0a8e0601
check value in block header: 0x0, check value not calculated
spare1:0x0, spare2:0x0, spare2:0x0
DBVERIFY - Verification complete
Total Pages Examined : 38400
Total Pages Processed (Data) : 10720
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 9383
Total Pages Failing (Index): 0
Total Pages Processed (Other): 314
Total Pages Empty : 17982
Total Pages Marked Corrupt : 1
Total Pages Influx : 1
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I got this result after running this query :
select owner, segment_name, segment_type
from dba_extents
where file_id = 3 and
15031 between block_id and block_id + blocks - 1;
OWNER ADMIN
SEGMENT_NAME PK_TIME_CARD
PARTITION_NAME
SEGMENT_TYPE INDEX
TABLESPACE_NAME USERS
EXTENT_ID 0
FILE_ID 3
BLOCK_ID 14482
BYTES 41943040
BLOCKS 5120
RELATIVE_FNO 3
This is an index file, how can i recover this.
Thanks Jonesone, I am novice in Oracle. I tried to delete the index using PL/SQL Developer but it showed me following error.
"You cannot delete primary/unique key index in oracle"
This is our production server, don't want to take risk. Appreciate if you could provide me steps to drop and rebuild the index.
This is where it can get tricky.
Do you know the table and columns involved? Do you have the original create statements? If you do can you post them?
I am not connected right now, so I do not recall one of the queries I want you to run, but what we need to determine is if the primary key constraint is enforced using a non-unique index. I believe this query will accomplish it:
select uniqueness from dba_indexes where index_name = 'PK_TIME_CARD';
To get the table the index is on:
select table_name from dba_indexes where index_name = 'PK_TIME_CARD';
To get the columns:
select column_name from dba_indexes where index_name = 'PK_TIME_CARD' order by column_position;
Those should give enough information to create the commands to rebuild the index.
Following is the information you have asked for:
select * from dba_indexes where index_name = 'PK_TIME_CARD';
OWNER ADMIN
INDEX_NAME PK_TIME_CARD
INDEX_TYPE NORMAL
TABLE_OWNER ADMIN
TABLE_NAME TIME_CARDS
TABLE_TYPE TABLE
UNIQUENESS UNIQUE
COMPRESSION DISABLED
PREFIX_LENGTH
TABLESPACE_NAME USERS
INI_TRANS 2
MAX_TRANS 255
INITIAL_EXTENT 41943040
NEXT_EXTENT 10485760
MIN_EXTENTS 2
MAX_EXTENTS 255
PCT_INCREASE 0
PCT_THRESHOLD
INCLUDE_COLUMN
FREELISTS 1
FREELIST_GROUPS 1
PCT_FREE 10
LOGGING YES
BLEVEL
LEAF_BLOCKS
DISTINCT_KEYS
AVG_LEAF_BLOCKS_PER_KEY
AVG_DATA_BLOCKS_PER_KEY
CLUSTERING_FACTOR
STATUS VALID
NUM_ROWS
SAMPLE_SIZE
LAST_ANALYZED
DEGREE 1
INSTANCES 1
PARTITIONED NO
TEMPORARY N
GENERATED N
SECONDARY N
BUFFER_POOL DEFAULT
USER_STATS NO
DURATION
PCT_DIRECT_ACCESS
ITYP_OWNER
ITYP_NAME
PARAMETERS
GLOBAL_STATS NO
DOMIDX_STATUS
DOMIDX_OPSTATUS
FUNCIDX_STATUS
I am using PL/SQL developer. I have all the inforamtion to rebuild the index. The issue is whenever i am trying to drop the original index, I am getting an error message :
"You cannot delete primary/unique key index in oracle"
I can't even alter this index. Please help.
Business Accounts
Answer for Membership
by: johnsonePosted on 2007-09-06 at 11:00:12ID: 19842329
First thing I would do is determine if the block is in use.
select owner, segment_name, segment_type
from dba_extents
where file_id = 3 and
15031 between block_id and block_id + blocks - 1;
If nothing is found then verify the block is free.
select *
from dba_free_space
where file_id = 3 and
15031 between block_id and block_id + blocks - 1;
If the block is free, then I believe no action is necessary. When the block is used it will be rewritten and the problem corrected.
If the block is used, you can definitely recover the data in the other blocks. It is not easy, but possible. Some of the data in the corrupt block may be lost.
Your other option would be to restore the file from backup, then roll it forward. This may avoid the corruption, but it may recreate the corruption.