Oracle Database
--
Questions
--
Followers
Top Experts
ora-01578: oracle data block corrupted (file # 3, block # 15031)#
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
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
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
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
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
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.
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.
Drop the index an rebuild it.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
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.
"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.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
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.
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.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Thanks johnstone. This solution worked for me...!!
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Oracle Database
--
Questions
--
Followers
Top Experts
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.