Logical Corruption
Orlando Reyes, August 10, 2004 - 6:29 pm UTC
Tom,
If I have this information:
select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
11 12357 12 197184960 LOGICAL
and
select * from v$backup_corruption;
RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# MAR CO
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
1 533835361 533835140 3089 1 11 12357 12 197184960 NO LOGICAL
How can I get more details of what data resides on this blocks? and being 'Logical' can they be recoverd without loosing that data at all?
Any extra details would be appreciated.
Thanks,
Orlando
August 10, 2004 - 8:24 pm UTC
select * from dba_extents
where file_id = 11
and 12357 between block_id an block_id+blocks-1;
if it is something "rebuildable" -- like an index, drop and recreate might be the path of least resistance, else you would go back to your backups -- to before this was detected and restore that file/range of blocks (rman can do block level recovery)
How to simmulate block corruption
Nilanjan Ray, August 11, 2004 - 1:11 am UTC
Hi Tom,
Not sure how relevant the question would be, even then..How would I simmulate a block corruption to test it out with RMAN?
Almost all recovery scenarios can be simmulated, but "block corruption"...never encountered one...neither know how do simmulate one.
Thanks in advance for your reply.
Regards
Ray
August 11, 2004 - 9:51 am UTC
typically people write a C program or something to "muck up a block".
dd and other tools could be used as well.
Logical Corruption
Orlando Reyes, August 11, 2004 - 9:53 am UTC
Tom,
Excellent answer, just what I needed. By the way, can you give me some scenarios where most likely Logical Corruption can take place? Im afraid I dont quite understand how this type of corruption does occur.
Thanks so much.
Orlando
August 11, 2004 - 12:37 pm UTC
overwrite the byte field used by a varchar2 and turn it from "5" to "15" for example. the block is logically "corrupt". It has data that "looks reasonable" but is wrong.
My corrption not affect by direct export
A reader, August 11, 2004 - 11:25 am UTC
Hi Tom
I too had block corruption in a table with approx 15 million rows. I noticed it when performing an analyze on the table (using compute). However, a full export of the schema in which the tables resides (using DIRECT=Y) did not return an error.
With this in mind, I imported the table into another development schema, and it completed successfully !! So, we took an outage in production, and recreated the table from the export.
Why would this corruption not have been picked up by a direct export ?
Thanks
Neil
August 11, 2004 - 1:37 pm UTC
impossible to say as I don't know what error you actually got, what segment it actually was on (could have been the index for all i know).
could be it was an intermittent disk error as well -- that hit the analyze but not the export (someone jiggled the cables)
My corrption not affect by direct export
A reader, August 12, 2004 - 5:31 am UTC
Tom
trace file generated by analyze contained
table scan: segment: file# 55 block# 229385
skipping corrupt block file# 55 block# 251372
This is repeated every day (analyzed each morning)
but daily direct export / import succeeds.
SQL> select segment_type from dba_extents
where file_id=55
and 229385 between block_id and
(block_id +( blocks -1));
SEGMENT_TYPE
----------------------------------------
TABLE
$ dbv file=/u03/oradata/emu/emu_data_large02.dbf \
blocksize=8192 logfile=/dbv.log
DBVERIFY: Release 8.1.7.2.0 - Production on Mon Aug 10 10:10:13 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = /u03/oradata/emu/emu_data_large02.dbf
Block Checking: DBA = 230938092, Block Type = KTB-managed data block
Found block already marked corrupted
DBVERIFY - Verification complete
Total Pages Examined : 256000
Total Pages Processed (Data) : 253949
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 11
Total Pages Empty : 2040
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Any thoughts ?
Thanks
August 12, 2004 - 8:56 am UTC
does the trace say anything useful, it is hard to say without seeing the data (not that I want you to post an entire trace file)
Support would love to see it though (hint?)
A reader, August 19, 2004 - 3:58 pm UTC
Tom,
Can we fix corrupt lobsegment using dbms_repair or there is some other procedure.
Thanks.
August 19, 2004 - 8:02 pm UTC
you would contact support for guidance.
A reader, August 20, 2004 - 10:13 am UTC
Tom,
That was a thought that came across my mind while going through the thread, can you give some info on how would one go about.
August 21, 2004 - 10:22 am UTC
go about what? contacting support? you use your metalink account.
A reader, August 21, 2004 - 10:35 am UTC
i came across this error ..Please let me know how to go about ...
Exception in Transactions ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: 'D:\ORACLE\ORADATA\DEVFGE\TEMP01.DBF'
ORA-02063: preceding 2 lines from ALINK
is that my tempfile got corrupted? ... if so, i dont have archive logging ...how to go about?.. drop all transaction and create a new temp file? ... what if this happens to system or anyother permananet dbf files?
Thanks for your great service to oracle community
August 21, 2004 - 12:15 pm UTC
just drop it and create a TRUE temporary tablespace.
create temporary tablespace temp tempfile ......;
you dont' want to use datafiles for temp!
if you are worried about data loss, you would be running in archivelog mode. that is the answer to "what if this happens to system or any other..."
You would be following good backup and recovery procedures, you would have multiple backups (3 or 4 of the last backups), you would have the archived redo logs
otherwise -- you will lose at least some, if not all data at some point in time and will only be able to restore the last full backup -- losing everything that happened since the backup. that is as sure as death and taxes.
RMAN - corrupt blocks
AR, September 16, 2004 - 7:19 pm UTC
Tom,
I have posted a similar looking question to you today. However, this is not a duplicate thread / question.
I ran into a corruption issue (ORA-19566) while backing up a database using RMAN. Note : This was the first time that an RMAN backup was attempted on this database.
I then thought, marking these blocks corrupt using dbms_repair and setting them to be skipped, would in some sense "fix" the issue and allow me to backup the database using RMAN. However, that wasn't the case. Despite marking these blocks corrupt, RMAN continues to fail once it hits this block. (A "backup validate" doesn't detect this corruption..but thats another matter. That was infact my question in the previous thread/post)
Short of restoring/recovering the database from an old traditional backup - is there anything I can do to enable RMAN backups of this tablespace?
More background : A few blocks (2data+2index) were corrupt on a certain table and index. I ran dbms_repair.fix_corrupt_blocks procedure to "fix" it.
SQL> l
1 SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
2 CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
3* FROM REPAIR_TABLE
SQL> /
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
CASE_DOCKET_PARAMETERS 114774 6148 TRUE
mark block software corrupt
CASE_DOCKET_PARAMETERS 114775 6148 TRUE
mark block software corrupt
----
However RMAN backup continues to fail with
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch01 channel at 09/16/2004 15:28:09
ORA-19566: exceeded limit of 0 corrupt blocks for file /u09/oradata/CRMP/userCDPa.dbf
SQL> select file_id from dba_Data_files where file_name='/u09/oradata/CRMP/userCDPa.dbf';
FILE_ID
----------
57
Here is the dbv on the same file. See how it shows corruption on block 114775 (but NOT 114774 - why??).
DBVERIFY: Release 9.2.0.1.0 - Production on Thu Sep 16 11:11:22 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = /testora/userCDPa.dbf
Page 114775 is marked corrupt
***
Corrupt block relative dba: 0x0e41c057 (file 57, block 114775)
Bad header found during dbv:
Data in bad block -
type: 2 format: 6 rdba: 0x000e41c0
last change scn: 0xfd00.575115de seq: 0x0 flg: 0x02
consistency value in tail: 0x47defd06
check value in block header: 0x469, block checksum disabled
spare1: 0x2, spare2: 0x0, spare3: 0x9700
***
DBVERIFY - Verification complete
Total Pages Examined : 256000
Total Pages Processed (Data) : 255870
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 8
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 120
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
September 16, 2004 - 8:10 pm UTC
Once the validate process is complete you either check the alert log or a
view depending on the version of Oracle being used.
In Oracle 8i corruption found with the RMAN validate command is only reported
in the alert log. In versions 9i and beyond you can query the view name
v$database_block_corruption to determine what corruption, if any, was found by
RMAN.
Up your tolerance for "corrupt blocks"
dbms_repair and effects on v$database_block_corruption
AR, September 16, 2004 - 11:46 pm UTC
Tom,
Thank you for your response. A followup question though.
As per the RMAN user guide :
"If the backup validation discovers corrupt blocks, then RMAN updates the V$DATABASE_BLOCK_CORRUPTION view with rows describing the corruptions. After a corrupt block is repaired, the row identifying this block is deleted from the view."
I assume the above 2nd sentence means - if you've used "dbms_repair" to fix a corrupt block, the row will get deleted from the v$database_block_corruption view. But that's not happening in my case. (Or do they mean repair corrupted blocks using RMAN?! I don't even have a RMAN backup of this database..so that's not possible in my case).
See "FIX_TIMESTAMP" of some blocks in the repair_table. Why do these appear in the v$database_block_corruption?
The other strange thing is v$database_block_corruption continues to list blocks from datafiles that are no longer existant! (the tablespace has been dropped). Notice how file_ids 70,72 don't even exist..yet it continues to appear as a corruption. Datafiles 70,72 were part of a corrupted UNDO tablespace that I dropped and recreated.
SQL> select * from repair_table;
OBJECT_ID TABLESPACE_ID RELATIVE_FILE_ID BLOCK_ID CORRUPT_TYPE
---------- ------------- ---------------- ---------- ------------
SCHEMA_NAME OBJECT_NAME
------------------------------ ------------------------------
BASEOBJECT_NAME PARTITION_NAME
------------------------------ ------------------------------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
MARKED_COR CHECK_TIMESTAMP FIX_TIMESTAMP REFORMAT_TIMESTAMP
---------- -------------------- -------------------- --------------------
18935 30 57 114774 6148
COURT CASE_DOCKET_PARAMETERS
CDP_8
mark block software corrupt
TRUE 16-SEP-2004 11:53:19
18935 30 57 114775 6148
COURT CASE_DOCKET_PARAMETERS
CDP_8
mark block software corrupt
TRUE 16-SEP-2004 11:53:19
18935 30 57 114774 6148
COURT CASE_DOCKET_PARAMETERS
CDP_8
mark block software corrupt
TRUE 16-SEP-2004 17:29:09
18935 30 57 114775 6148
COURT CASE_DOCKET_PARAMETERS
CDP_8
mark block software corrupt
TRUE 16-SEP-2004 17:29:09
18935 30 57 114774 6148
COURT CASE_DOCKET_PARAMETERS
CDP_8
mark block software corrupt
TRUE 16-SEP-2004 20:02:16
18935 30 57 114775 6148
COURT CASE_DOCKET_PARAMETERS
CDP_8
mark block software corrupt
TRUE 16-SEP-2004 20:02:16
6 rows selected.
SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
57 114774 1 0 FRACTURED
57 114775 1 0 FRACTURED
67 147737 1 0 FRACTURED
67 147738 2 0 FRACTURED
70 217721 1 0 FRACTURED
70 217722 2 0 FRACTURED
72 105401 1 0 FRACTURED
72 105402 2 0 FRACTURED
84 83409 1 0 FRACTURED
84 83410 2 0 FRACTURED
10 rows selected.
SQL> select file_id,file_name from dba_data_files where file_id in (57,67,70,72,84);
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
84
/u05/oradata/CRMP/INDXCDPc.dbf
67
/u14/oradata/CRMP/userCDPf.dbf
57
/u09/oradata/CRMP/userCDPa.dbf
September 17, 2004 - 8:34 am UTC
believe they should have said "recovered"
I have to ask here -- what is causing this corruption? have you identified *why* things are going so wrong here?
fix_timestamp is nulls - sorry
AR, September 17, 2004 - 12:15 am UTC
Tom,
I made a mistake with the above previous post. I had a visual parallax error going on. I feel moronic.
The fix_timestamp is actually null. I was incorrectly looking at the check_timestamp. Obviously, the blocks have not been "fixed" then. No wonder they show up in v$database_block_corruption. The question on why blocks from non existant datafiles continue to show up on v$database_block_corruption remains though.
Can you tell me where I'm going wrong trying to 'fix' these blocks? See output of FIX_CORRUPT_BLOCKS - it doesn't seem to fix it. 'num fix : 0'. I don't have a lot of experience with dbms_repair and could use your help.
Many thanks.
SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'COURT',
OBJECT_NAME => 'CASE_DOCKET_PARAMETERS',
PARTITION_NAME => 'CDP_8',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
number corrupt: 2
PL/SQL procedure successfully completed.
----------
SET SERVEROUTPUT ON
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => 'COURT',
OBJECT_NAME => 'PK_CASE_DOCKET_PARAMETERS',
OBJECT_TYPE => dbms_repair.index_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
KEY_COUNT => num_orphans);
DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
END;
/
orphan key count: 177
PL/SQL procedure successfully completed.
------------
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'COURT',
OBJECT_NAME => 'CASE_DOCKET_PARAMETERS',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/
PL/SQL procedure successfully completed.
------
SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 2;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'COURT',
OBJECT_NAME=> 'CASE_DOCKET_PARAMETERS',
PARTITION_NAME => 'CDP_8',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/
num fix: 0
PL/SQL procedure successfully completed.
corruption
AR, September 17, 2004 - 9:44 am UTC
<quote>
I have to ask here -- what is causing this corruption? have you identified *why* things are going so wrong here?
</quote>
Yes - to an extent. I received this database on tape. Once I restored it, I found the corruption. I have duplicated it (identical block corruption) over and over again. Basically the corruption exists at source. It wasn't as if the corruption crept in on my side afterwards.
Thanks.
September 17, 2004 - 10:16 am UTC
so basically -- if you do not need these rbs's, drop and recreate them.
if you do need these rbs's (they are protecting active transactions), you'll need to get them to a state where you can drop and recreate them. dbms_repair is just going to make it so you can basically scrape data out of these files.
cut out the cancer.
(and you haven't identified what is causing the corruption, only that "you didn't do it, someone else did")
corruption - not in rbs
AR, September 17, 2004 - 10:27 am UTC
<quote>
so basically -- if you do not need these rbs's, drop and recreate them.
if you do need these rbs's (they are protecting active transactions), you'll need to get them to a state where you can drop and recreate them. dbms_repair is just going to make it so you can basically scrape data out of these files.
</quote>
Tom, the above listed corruption is not in the rbs. So, I'm not sure what you're referring to. Yes, I had some corruption in my undo, which I've already taken care of. - I have cut out the cancer in the UNDO. The above corruption that I'm still having problems with - is in table data and index segments.
Do you think my best bet is to go to support?
<quote>
(and you haven't identified what is causing the corruption, only that "you didn't do it, someone else did")
</quote>
True. But I'm not sure how to go about really "identifing" root cause of corruption? Can you elaborate. From what I know - software corruption can occur for any number of reasons anywhere along the way between database buffers, OS buffers, controller cache etc etc. Now how is one to go about identifying what exactly caused corruption? It'll be great if you could shed some light.
Thank you.
September 17, 2004 - 10:35 am UTC
there is a ton of text here, i scan.
Is the original source database you recieved from whomever "ok", was the corruption introduced on the copy to tape or is the source database "sick" too.
If the source is OK, you should just "get the source clean" again.
If the source is SICK, they should get in touch with support and figure out the best plan of action.
If you have massive widespread corruption -- which you appear to have, you need to try to isolate it. eg:
if ( tape copied datafiles are corrupt AND source database is not )
then
tape is probable culprit, test tapes, test tape devices
else
if ( source database is also corrupt )
then
check disk logs (OS logs, look for errors)
perform hardware tests on system (exercise everything)
enable init.ora parameters to perform checksumming
..... and so on
my corruption
AR, September 17, 2004 - 10:46 am UTC
Tom,
Source is fine/clean. Unfortunately, I can't simply copy a new "clean" source. Considerable work has been performed on this corrupt restored database. I would prefer to scrape out the 10blocks of corruption with dbms_repair and move on. But I'm having problems with dbms_repair.
I'll take it up support. Thank you for your time.
PS: Why do you say I have massive widespread corruption? Generally speaking, 14 * 8k on a 100G database is not monumental right?
September 17, 2004 - 11:51 am UTC
1 is big and bad
2 is huge
3 random blocks is beyond acceptable
if that one block were, oh say, a segment header....
Possible help in discovering the nature of your corruption.
Scott Martin, September 23, 2004 - 12:27 am UTC
AR;
I certainly cannot promise you any ultimate resolution to your corrupt block problem, however, in one of your earlier posts, your corrupt block query indicated the head and tail portions of your 8k block are incongruous. There can be numerous reasons for this, but the most troubling to Oracle is that I/O subsystems usually only guarantee atomicity at 2K boundaries. That is, either the entire 2K is written or none of it is. This guarantee does not generally extend to 8K blocks. However unlikely, a drive may write out the first, say, 4k, and then have the power cut out or something. This leaves a fractured block; exactly what your query from repair_table indicates. To detect this fracturing, Oracle encodes in the footer of a block a small piece of the header. In short, viewing your blocks directly will give you a better understanding of the nature of your problem and make you more informed when working with Oracle support. Our block viewer at </code>
http://www.tlingua.com <code>can help. For example, maybe your header and footer are only off by a little: This indicates one sort of problem. Maybe the first half of the bock and the second half of the block appear to come from two completely different Oracle blocks: This indicates something different. In either case, the information would be valuable to both you and Oracle support.
Good luck,
SCott.
To Scott Martin
AR, November 22, 2004 - 1:46 pm UTC
Scott,
Thank you for taking the time to respond. The block viewer product on your website does appear interesting.
Sorry for the late response but I hadn't visited this thread in a long time.
ORA-19566: exceeded limit of 0 corrupt blocks for file ...
ST, August 24, 2006 - 5:42 pm UTC
ORA-19566: exceeded limit of 0 corrupt blocks for file ...
I am getting "ORA-19566: exceeded limit of 0 corrupt blocks for file ..." error and found DOC: Note:336133.1. I have the corrupt datafile which is 32G. Running DBV I got the following :
DBVERIFY: Release 10.1.0.5.0 - Production on Thu Aug 24 10:14:07 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /opt/oracle/oradata/email/esbigtbl4.dbf
Page 2736605 is marked corrupt
Corrupt block relative dba: 0x06a9c1dd (file 26, block 2736605)
Bad header found during dbv:
Data in bad block:
type: 27 format: 2 rdba: 0x06a9c1dd
last change scn: 0x8e73.01cc8e72 seq: 0xcc flg: 0x01
spare1: 0x0 spare2: 0x0 spare3: 0x1cc
consistency value in tail: 0xc91e1b02
check value in block header: 0x8e74
block checksum disabled
DBVERIFY - Verification complete
Total Pages Examined : 3932160
Total Pages Processed (Data) : 49152
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 6189
Total Pages Failing (Index): 0
Total Pages Processed (Other): 3676319
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 200499
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 605825075 (0.605825075)
Query results to identify the segment are as follows:
SQL> select segment_name,segment_type,owner,block_id
2 from dba_extents where file_id=26 and 2736605 between block_id and block_id+blocks-1;
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE OWNER BLOCK_ID
------------------ ------------------------------ ----------
SYS_LOB0000035072C00002$$
LOBSEGMENT ES_MAIL 2736137
SQL> select * from v$DATABASE_BLOCK_CORRUPTION ;
no rows selected
SQL>
The Note suggested to create a table in a schema other than in SYS and SYSTEM and try to allocate extents.
I created the table as well allocated extents (Size in G) and a trigger. When I tried to insert the data, the system hangs and does nothing as i left running the procedure to insert into the dummy table for the whole day. Never seen an exception. But the problem occurs again when the backup starts again. Any ideas what can be done with that big datafile block corruption error.
Recovering corrupted blocks
Suraj Sharma, March 06, 2007 - 6:06 am UTC
Tom,
Thanks for letting me understand the block corruption!!
In one of our production database we are facing the problem of block corruption. While performing the upgradation there was a problem and somehow thousands of blocks had been marked corrupt. When we ran the dbv it showed 7000 blocks as corrupted initially , which has now come down to 253. The numbers of corrupted blocks are coming down, but its coming down very slowly. We verified that the corrupted blocks does not belong to any segments (table or indexes)
The output of dbv looks like ¿DBV-00200: Block, dba 12715732, already marked corrupted¿
My understanding about the corrupted block is that whenever Oracle writes into those blocks it gets formatted automatically.
Is there any way to directly repair those corrupted block?
March 06, 2007 - 11:07 am UTC
there is no need, they do not belong to any segments, they are not "corrupt", they are just unused.
ORA-01187: cannot read from file 526 because it failed verification tests
Hitesh, March 07, 2007 - 2:55 pm UTC
Hi Tom,
We got the above stated error while executing a big hash join even when we have a true temporary tablespace.
ORA-01110: data file 526: '/fs-b01-c/databases/admw/dba_temp-14.dbf'
Any thoughts on what are the possible reasons for it to happen?
Thanks
dwuser@ADMW>select dbms_metadata.get_ddl('TABLESPACE','DBA_TEMP') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','DBA_TEMP')
--------------------------------------------------------------------------------
CREATE TEMPORARY TABLESPACE "DBA_TEMP" TEMPFILE
'/fs-c01-a/databases/admw/dba_temp-05.dbf' SIZE 5368709120
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-c01-a/databases/admw/dba_temp-03.dbf' SIZE 13186891776
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-c01-a/databases/admw/dba_temp-07.dbf' SIZE 16190013440
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-c01-b/databases/admw/dba_temp-04.dbf' SIZE 5368709120
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-c01-b/databases/admw/dba_temp-06.dbf' SIZE 34358689792
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-c01-b/databases/admw/dba_temp-08.dbf' SIZE 16182673408
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-c01-a/databases/admw/dba_temp-01.dbf' SIZE 13190037504
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-c01-b/databases/admw/dba_temp-02.dbf' SIZE 20149436416
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-a01-a/databases/admw/dba_temp-09.dbf' SIZE 5368709120
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-a01-b/databases/admw/dba_temp-10.dbf' SIZE 5368709120
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-a01-c/databases/admw/dba_temp-11.dbf' SIZE 5368709120
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-b01-a/databases/admw/dba_temp-12.dbf' SIZE 5368709120
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-b01-b/databases/admw/dba_temp-13.dbf' SIZE 5368709120
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M,
'/fs-b01-c/databases/admw/dba_temp-14.dbf' SIZE 5368709120
AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
March 07, 2007 - 3:39 pm UTC
can we see the rest of the error stack please?
and tell us your hash-area-size settings and pga (workarea related) settings
parallel query or serial.
ORA-01578
steve, October 09, 2008 - 9:44 am UTC
Hi Tom,
We received ORA-01578 (during an RMAN backup) and were able
to identify it to a single block of a NOLOGGING Table.
So we did the following:
ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT
FOREVER, LEVEL 10';
create table temp_tbl as select * from
table_with_corruption (to skip the corrupt block)
truncate table table_with_corruption drop storage
insert into table_with_corruption select * from temp_tbl
I thought this would clear the corruption but it resurfaced
during the RMAN backup. Instead we did the same operation
but this time dropped and recreated the table.
Why was the 'truncate table drop storage' not sufficient
to clear the corruption? Or am I making a wrong conclusion?
Thanks
Steve
October 09, 2008 - 11:17 am UTC
depending on release, rman might back up all blocks - blocks that have been used at some point - even if they are not currently allocated to a segment.
So, here is what I guess
a) you truncated, that released the block to free space but did not touch the block at all, it is still corrupt looking.
b) you backed up, rman hit the block, it appears corrupt, rman said so.
c) you created a new table - this used the space where the corrupt block was, it actually formatted the block.
d) you backed up, rman hit the block and this time, since we just formatted it, it was OK.
Thanks
steve, October 09, 2008 - 11:29 am UTC
Thanks Tom - I was thinking along those lines but wasn't
sure.
Just for your information (in case you are interested):
Recovery Manager: Release 10.2.0.3.0 - Production on Thu Oct 9 16:10:00 2008
October 09, 2008 - 2:13 pm UTC
Correction on above URL
Stew Ashton, October 11, 2008 - 4:11 am UTC
http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/glossary.htm##BBRADV90298 Do not click on the above link. It will not take you to the right spot. You must copy and paste to the address bar in your browser.
AskTom is still eating "##BB" within URLs. I double the # and double the B manually to get it to display, then I turn off bold by hand.
This gets it to look right to humans, but the actual generated URL has a bold tag in the middle. As a result, if you click on the link you will go to the top of the page and not to the exact spot you want.
This is a bug. It hurts usability. It makes APEX look bad.
Index block corruption
John, August 01, 2012 - 8:01 pm UTC
Hello Tom,
I always appreciate your response
we had a strange situation
The SQL query simple select * from table where column(index) = some was returning data fine until yesterday
Today the same SQL was giving no results; no errors were
displayed
It was notified to DBA to investigate.
The DBA came back late afternoon and said that the INDEX was corrupted so It was recreated, works fine now.
I am surprised that if that index was corrupted the optimizer should have chosen the FTS and atleast given the results.
Is it possible to show no results if the index is corrupted? or DBA is misleading
Oracle Version = 11g
Index type = btree
I really appreciate your response and wanted to know your thoughts about index corruption.
Best Regards,
John
August 02, 2012 - 9:01 am UTC
I am surprised that if that index was corrupted the optimizer should have
chosen the FTS and atleast given the results.
if the index was corrupt, the optimizer wouldn't know - not unless it gave an error processing the data.
you would want to work this via support. The DBA should be able to restore this index and table in a test instance to right before they rebuilt the index to reproduce.
truncate table block corruption
A reader, September 24, 2012 - 11:01 am UTC
Hi Tom,
From your reply to thread "ORA-01578" above, I am trying to understand what happens to the corrupted block?
Would dropping/recreating the table eliminates the corrupted block? Irrespective of the case, if the corrupted block is freed up it could always be used by some session(when loading data) and again it could use the same block correct? What can be done to eliminate the corrupted block completely?
September 27, 2012 - 7:58 am UTC
the block will appear to be corrupt until it is reused by something. when you drop/truncate the segment, the block isn't touched - it is just put into dba free space. when something else reuses it, it'll be reinitialized.
dropping could actually keep it around a lot longer - as it would be associated with an object in the recycle bin.
I am facing different scenario on handling block corruption.
A reader, April 30, 2014 - 2:56 pm UTC
Hi Tom,
Oracle DB Version : Oracle Enterprise Eddition, 11.2.0.1.0
OS Version : Linux 2.6.18-308.el5 x86_64
Issue: Oracle Block Corruption showing as follow.
select file#,block#,blocks,to_char(Corruption_change#) "CORRUPTION_CHANGE#",CORRUPTION_TYPE from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ---------------------------------------- ---------
14 6234 1 105758559112 CORRUPT
14 4703527 1 105302404999 CORRUPT
Earlier This Corruption is related to a perticular object(6234 -- XX TABLE and 4703527 --- INDEX ON XX TABLE).
Now I have Dropped the table and recreated(with exp/imp) Only META DATA Exported and imported the same.
Exact corrupted blocks are :
select OWNER,segment_name,segment_type,tablespace_name,block_id from dba_extents where file_id = &file_di and &block_id between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME SEGMENT_TYPE BLOCK_ID
-------- ------------- ------------ ---------
xx_owner xx TABLE 6208
xx_owner xx INDEX 4703520
***Before dropping i could not able to fetch anything from the table not even(select * from xx;) error was :
ERROR at line 1:
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE
**** I do not have RMAN backup or any cold backup.
So did tried with dbms_verify utility(before table drop) got error while block checking with(DBMS_REPAIR.CHECK_OBJECT)
error is :
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE
So have dropped and recreated(imported) the xx table. now can able to fetch the data. But the block is still in corrupted state.
Tried with DBMS_REPARI but that block is not associated to any object now.
select OWNER,segment_name,segment_type,tablespace_name,block_id from dba_extents where file_id = 14 and 6208 between block_id and block_id+blocks-1
no rows selected
Kindly help me to resove this issue.
How to reallocated corrupted block to new object.
Krrish, May 06, 2014 - 1:37 pm UTC
Hi Tom,
As We can not recover the block whithout RMAN, i am clear on this.And we dont have cold back for datafile restore.
My question is :
How to allocate corrupted block to the newly created object. Since the object(table/index) which is associated to the corrupted block is recreated and the block is still in corrupted state and signaling on alertlog.