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  |
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 #574516 is a reply to message #574511] |
Fri, 11 January 2013 09:21  |
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 #574907 is a reply to message #574510] |
Wed, 16 January 2013 13:14 |
 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 06:44:01 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
