Saturday, 5 November 2016

CleanUp Temporary Segments Occupying Permanent Tablespace


>  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: