Home » RDBMS Server » Server Administration » Data Block Corruption even after repaired (Oracle11G,AIX 6.1)
Data Block Corruption even after repaired [message #574510] Fri, 11 January 2013 07:48 Go to next message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
Hi Gurus,
An error diplayed in the application log as
"Database error text........: "ORA-01578: ORACLE data block corrupted (file #
239, block # 333600)#ORA-01110: data file 239:
'/oracle/BT5/sapdata3/sr3_202/sr3.data202'"
Internal call code.........: "[RSQL/DELE/RSDDTREXNEWDIMID ]"
Please check the entries in the system log (Transaction SM21).

Checked the alert_SID.log file and found the following
Hex dump of (file 239, block 333584) in trace file /oracle/BT5/saptrace/diag/rdbms/bt5/BT5/incident/incdir_372648/BT5_m000_12386396_i372648_a.trc
Corrupt block relative dba: 0x3bc51710 (file 239, block 333584)
Bad header found during validation
Data in bad block:
type: 1 format: 2 rdba: 0x00003a00
last change scn: 0x8000.0000018c seq: 0x93 flg: 0x45
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x47390065
check value in block header: 0x0
computed block checksum: 0x0
Reread of blocknum=333584, file=/oracle/BT5/sapdata3/sr3_202/sr3.data202. found same corrupt data
Reread of blocknum=333584, file=/oracle/BT5/sapdata3/sr3_202/sr3.data202. found same corrupt data
Reread of blocknum=333584, file=/oracle/BT5/sapdata3/sr3_202/sr3.data202. found same corrupt data
Reread of blocknum=333584, file=/oracle/BT5/sapdata3/sr3_202/sr3.data202. found same corrupt data
Reread of blocknum=333584, file=/oracle/BT5/sapdata3/sr3_202/sr3.data202. found same corrupt data
Fri Jan 04 10:24:15 2013
Dumping diagnostic data in directory=[cdmp_20130104102415], requested by (instance=1, osid=62062644), summary=[incident=372648].
Fri Jan 04 10:29:05 2013
Corrupt Block Found
TSN = 4, TSNAME = PSAPSR3
RFN = 239, BLK = 340395, RDBA = 1002779051
OBJN = 1137609585, OBJD = 1137609585, OBJECT = /BIC/ADMK_90FR00, SUBOBJECT =
SEGMENT OWNER = SAPSR3, SEGMENT TYPE = Table Segment
Fri Jan 04 10:34:05 2013
Corrupt Block Found
TSN = 4, TSNAME = PSAPSR3
RFN = 239, BLK = 340396, RDBA = 1002779052
OBJN = 1137609585, OBJD = 1137609585, OBJECT = /BIC/ADMK_90FR00_OLD, SUBOBJECT =
SEGMENT OWNER = SAPSR3, SEGMENT TYPE = Table Segment"

As action to this I did the following

SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES
(
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => DBMS_REPAIR.REPAIR_TABLE,
ACTION => DBMS_REPAIR.CREATE_ACTION,
TABLESPACE => 'SYSTEM'
)
2 3 4 5 6 7 8 9 ;
10 end;
11 /

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES
(
TABLE_NAME => 'ORPHAN_KEY_TABLE',
TABLE_TYPE => DBMS_REPAIR.ORPHAN_TABLE,
ACTION => DBMS_REPAIR.CREATE_ACTION,
TABLESPACE => 'SYSTEM'
);
END;
/ 2 3 4 5 6 7 8 9 10

PL/SQL procedure successfully completed.

SQL> desc repair_table
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE

SQL> desc ORPHAN_KEY_TABLE
Name Null? Type
----------------------------------------- -------- ----------------------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 vCorruptBlocks INT := 0;
BEGIN
3 4 DBMS_REPAIR.CHECK_OBJECT
5 (
SCHEMA_NAME => 'SAPSR3',
6 7 OBJECT_NAME => '/BIC/ADMK_90FR00',
8 REPAIR_TABLE_NAME => 'REPAIR_TABLE',
9 CORRUPT_COUNT => vCorruptBlocks
);
10 11 DBMS_OUTPUT.PUT_LINE('Number of blocks corrupted: ' || TO_CHAR (vCorruptBlocks));
12 END;
13 /
Number of blocks corrupted: 800

PL/SQL procedure successfully completed.

SQL>


Size of the corrupted table is 42GB.

SQL> select sum(bytes)/1024/1024/1024 from dba_extents where segment_name='/BIC/ADMK_90FR00';

SUM(BYTES)/1024/1024/1024
-------------------------
46.3515625

SQL> select count(*) from "/BIC/ADMK_90FR00";

COUNT(*)
----------
56631818


SQL> BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
(
SCHEMA_NAME => 'SAPSR3',
OBJECT_NAME => '/BIC/ADMK_90FR00',
OBJECT_TYPE => DBMS_REPAIR.TABLE_OBJECT,
FLAGS => DBMS_REPAIR.SKIP_FLAG
);
END;
/ 2 3 4 5 6 7 8 9 10

PL/SQL procedure successfully completed.

SQL> select count(*) from sapsr3."/BIC/ADMK_90FR00";

COUNT(*)
----------
56631818



Even after the above method continous block corruption reported in alert file so I created a copy of table /BIC/ADMK_90FR00 and renamed the corrupt one as /BIC/ADMK_90FR00_OLD and also renamed the copied table to the original name. After doing this I got block corruption against table /BIC/ADMK_90FR00_OLD so therafter I dropped the table using drop table purge command .now the situation is I am still getting block corruption error in the alert file as following

Corrupt Block Found
TSN = 4, TSNAME = PSAPSR3
RFN = 239, BLK = 339952, RDBA = 1002778608
OBJN = 1137609585, OBJD = 1137609585, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
Fri Jan 11 13:40:55 2013
Corrupt Block Found
TSN = 4, TSNAME = PSAPSR3
RFN = 239, BLK = 339952, RDBA = 1002778608
OBJN = 1137609585, OBJD = 1137609585, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =



Please note the OBJD which is 1137609585 which is the same objd earlier that refers to /BIC/ADMK_90FR00_OLD table which is already been dropped and also does not exists in the recycle bin .

There is no such object in the database whose id is 1137609585 which earlier existed as the object id for the table /BIC/ADMK_90FR00_OLD

Please note we are not having RMAN configured for this DB.

Please help me resolve this ASAP .Is this due to any Bug of 11G.

Any early help in this is highly appreciated.

Thanks
DBCOP

[Updated on: Fri, 11 January 2013 07:51]

Report message to a moderator

Re: Data Block Corruption even after repaired [message #574511 is a reply to message #574510] Fri, 11 January 2013 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think it is a right corruption alert but a false object.
The procedure returns the object id as it found it in the block that is as it was before you delete the object.
When you delete (and purge) the object the block is not overwritten, the data (and Oracle ones) are still in the block.
You can check if any corrupted block belongs to an actual object using dba_extents.
If not, you can ignore the corruption, it will be erased when the block will be reused.
The proof of this is that the procedure does not return any object as it does not found any object that the block belongs to (all values are NULL).

Regards
Michel

Re: Data Block Corruption even after repaired [message #574516 is a reply to message #574511] Fri, 11 January 2013 09:21 Go to previous messageGo to next message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
Thanks for the reply . I checked the blocks using dba_extents but no rows returned . So I believ these errors are coming from the blocks which are free then can I shrink the corresponding datafiles to return these free blocks to the OS and thus get rid of such unnecessary errors in alert file.
Re: Data Block Corruption even after repaired [message #574517 is a reply to message #574516] Fri, 11 January 2013 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 22517
Registered: January 2009
Senior Member
<then can I shrink the corresponding datafiles to return these free blocks to the OS and thus get rid of such unnecessary errors in alert file.
I give up.
Can you?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Data Block Corruption even after repaired [message #574521 is a reply to message #574516] Fri, 11 January 2013 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do that or you can create a dummy table and extend it (alter table allocate extent) until it covers all the corrupted blocks then you have just to drop/purge the table.

Regards
Michel
Re: Data Block Corruption even after repaired [message #574907 is a reply to message #574510] Wed, 16 January 2013 13:14 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I find it easier to move objects to another tablespace, resize the datafile, then move the objects back. I use the followig script to see what is at the end of the datafile that I am trying to resize.
ECSCDAD3 > @mapperi 8

OWNER          OBJECT          FILE_ID   BLOCK_ID     BLOCKS
-------------- --------------- ------- ---------- ----------
ECSCDAD3 TABLE ALAN.                 8          8          8
free space                           8         16        112

2 rows selected.

ECSCDAD3 > list
  1  select             'free space' owner      /*"owner" of free space*/
  2  ,          '         ' object              /*blank object name*/
  3  ,          file_id                         /*file id for the extent header*/
  4  ,          block_id                        /*block id for the extent header*/
  5  ,          blocks                          /*length of the extent, in blocks*/
  6  from               dba_free_space
  7  where              file_id=&1
  8  union
  9  select      substr(owner,1,20)||' '||substr(segment_type,1,9) /*owner name (first 20 chars)*/
 10  ,          substr(segment_name,1,32)||'.'||partition_name  /*segment name*/
 11  ,          file_id                         /*file id for the extent header*/
 12  ,          block_id                        /*block id for the extent header*/
 13  ,          blocks                          /*length of the extent, in blocks*/
 14  from               dba_extents
 15  where              file_id=&1
 16* order by   3,4
Re: Data Block Corruption even after repaired [message #574916 is a reply to message #574907] Wed, 16 January 2013 13:51 Go to previous message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I find it easier to move objects to another tablespace,


If you the space to move them...

Regards
Michel
Previous Topic: fresh installation with old oradata
Next Topic: SQL for generating roles
Goto Forum:
  


Current Time: Mon Jul 28 18:41:44 CDT 2014

Total time taken to generate the page: 0.05747 seconds