> Alert Log
Hex dump of (file 4, block 1310651) in trace file /u01/app/oracle/admin/sarathi/bdump/sarathi_smon_3309.trc
Corrupt block relative dba: 0x0113ffbb (file 4, block 1310651)
Bad header found during buffer read
Data in bad block:
type: 40 format: 2 rdba: 0x01038a9c
last change scn: 0x0000.00077dc3 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x7f68
computed block checksum: 0x0
Reread of rdba: 0x0113ffbb (file 4, block 1310651) found same corrupted data
Corrupt Block Found
TSN = 4, TSNAME = USERS
RFN = 4, BLK = 1310635, RDBA = 18087851
OBJN = -1, OBJD = 52382, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
Hex dump of (file 4, block 1310635) in trace file /u01/app/oracle/admin/sarathi/bdump/sarathi_smon_3309.trc
Corrupt block relative dba: 0x0113ffab (file 4, block 1310635)
Bad header found during buffer read
Data in bad block:
type: 40 format: 2 rdba: 0x01038a9c
last change scn: 0x0000.00077dc3 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x7f68
computed block checksum: 0x0
Reread of rdba: 0x0113ffab (file 4, block 1310635) found same corrupted data
Hex dump of (file 4, block 1310643) in trace file /u01/app/oracle/admin/sarathi/bdump/sarathi_smon_3309.trc
Corrupt block relative dba: 0x0113ffb3 (file 4, block 1310643)
Bad header found during buffer read
Data in bad block:
type: 40 format: 2 rdba: 0x01038a9c
last change scn: 0x0000.00077dc3 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x7f68
computed block checksum: 0x0
Reread of rdba: 0x0113ffb3 (file 4, block 1310643) found same corrupted data
Hex dump of (file 4, block 1310651) in trace file /u01/app/oracle/admin/sarathi/bdump/sarathi_smon_3309.trc
Corrupt block relative dba: 0x0113ffbb (file 4, block 1310651)
Bad header found during buffer read
Data in bad block:
type: 40 format: 2 rdba: 0x01038a9c
last change scn: 0x0000.00077dc3 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x7f68
computed block checksum: 0x0
Reread of rdba: 0x0113ffbb (file 4, block 1310651) found same corrupted data
Corrupt Block Found
TSN = 4, TSNAME = USERS
RFN = 4, BLK = 1310643, RDBA = 18087859
OBJN = -1, OBJD = 52383, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
> Some work around but not success
SQL> select *
from dba_extents
where file_id = &DATA_FILE_ID
and &CORRUPTED_BLOCK_ID between block_id AND block_id + blocks - 1; 2 3 4
Enter value for data_file_id: 4
old 3: where file_id = &DATA_FILE_ID
new 3: where file_id = 4
Enter value for corrupted_block_id: 1310643
old 4: and &CORRUPTED_BLOCK_ID between block_id AND block_id + blocks - 1
new 4: and 1310643 between block_id AND block_id + blocks - 1
no rows selected
SQL> select *
from dba_extents
where file_id = &DATA_FILE_ID
and &CORRUPTED_BLOCK_ID between block_id AND block_id + blocks - 1; 2 3 4
Enter value for data_file_id: 4
old 3: where file_id = &DATA_FILE_ID
new 3: where file_id = 4
Enter value for corrupted_block_id: 1310635
old 4: and &CORRUPTED_BLOCK_ID between block_id AND block_id + blocks - 1
new 4: and 1310635 between block_id AND block_id + blocks - 1
no rows selected
SQL> select *
from dba_extents
where file_id = &DATA_FILE_ID
and &CORRUPTED_BLOCK_ID between block_id AND block_id + blocks - 1; 2 3 4
Enter value for data_file_id: 4
old 3: where file_id = &DATA_FILE_ID
new 3: where file_id = 4
Enter value for corrupted_block_id: 1310651
old 4: and &CORRUPTED_BLOCK_ID between block_id AND block_id + blocks - 1
new 4: and 1310651 between block_id AND block_id + blocks - 1
no rows selected
select header_file, header_block,segment_name from dba_segments where header_file=4;
HEADER_FILE HEADER_BLOCK SEGMENT_NAME
----------- ---------------------- ----------------------------------------
4 1310635 4.1310635
4 1310643 4.1310643
4 1310651 4.1310651
select owner,segment_name,segment_type,header_file,header_block,extents,(bytes/1024/1024)MB
from dba_segments
where segment_type = 'TEMPORARY' and tablespace_name = 'USERS'
SOLUTION:
==============================
USE PACKAGE DBMS_SPACE_ADMIN
==============================
select header_file, header_block,segment_name from dba_segments where header_file=4;
select tablespace_name, owner, segment_name, sum(bytes/1024/1024) from dba_segments
where segment_type = 'TEMPORARY' group by tablespace_name, owner;
--------------------------------------------------------------------
execute DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT ('USERS',4,1310651);
exec DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT ('USERS',4,1310643);
execute DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT ('USERS',4,1310635);
--------------------------------------------------------------------
ERROR at line 1:
ORA-03211: The segment does not exist or is not in a valid state
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 46
ORA-06512: at line 1
---------------------------------------------------------------------
execute dbms_space_admin.segment_corrupt('USERS',4,1310651);
execute dbms_space_admin.segment_corrupt('USERS',4,1310643);
execute dbms_space_admin.segment_corrupt('USERS',4,1310635);
--------------------------------------------------------------------
execute DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT ('USERS',4,1310651);
exec DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT ('USERS',4,1310643);
execute DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT ('USERS',4,1310635);
--------------------------------------------------------------------
select owner,segment_name,segment_type,header_file,header_block,extents,(bytes/1024/1024)MB
from dba_segments
where segment_type = 'TEMPORARY' and tablespace_name = 'USERS'
no row selected
select header_file, header_block,segment_name from dba_segments where header_file=4;
no row selected
Reference :
Why Coming this issue and segment name is convert into numeric value below the rerence link ?
No comments:
Post a Comment