Recently encountered a block corruption problem where the type of corruption was “NO LOGGING” and luckily the blocks that were affected were “Free Blocks” . The solution for this scenario is detailed in MOS Note 336133.1 and as it mentions, the block needs to be reformatted in order to mark it as corruption-free.
The proposed solution for this involves creating an interim table, allocating the free blocks to this table, inserting data – which would lead to reformatting of the blocks . The rationale being that the reformatting of blocks occurs just before a DML operation makes modification/use of the block.
The problem we ran into while going through the solution was that we wern’t able to allocate one of the free corrupt blocks to my interim table. The allocation operation would encounter ORA-01653.
In case the datafile that has encountered corruption is the only datafile in the tablespace, then it is strongly recommended to add an additional datafile to your tablespace before you go through the solution described in the MOS Note 336133.1, in order to prevent the tablespace running out of space.
SQL> Select * from v$database_block_corruption ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 323264 8 6.7978E+12 NOLOGGING
set pagesize 2000
set lines 300
col segment_name for a30
col segment_type for a30
col owner for a30
col PARTITION_NAME for a20
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
ORDER BY file#, corr_start_block#;
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
------------------------------ ------------------ ------------------------------ ------------------------------ ---------- ----------------- --------------- ---------------- ------------------------
5 323264 323271 8 Free Block
After running through the initial steps we wereable to create an interim table AK.S and allocate all but one of the corrupt free blocks to it. The block that continued to remain unallocated after multiple allocation attempts was 323264.
06:06:53 SYS >alter table AK.S allocate extent (DATAFILE '/u01/oradata/test/test.dbf' SIZE 1M);
alter table AK.S allocate extent (DATAFILE '/u01/oradata/test/test.dbf' SIZE 1M)
*
ERROR at line 1:
ORA-01653: unable to extend table AK.S by 128 in tablespace TEST06:07:15 SYS @ >Select * from dba_free_space where file_id= 5 and 323376 between block_id and block_id + blocks -1;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
TEST 5 323264 1048576 128 5
The first thing that was attempted (and which didn’t help) was to reduce the next_extent for the table and then attempt to allocate a smaller chunk of contiguous space to the table. However it appears that the enough contiguous free space was not available & hence it couldn’t be allocated to our table.
06:11:55 SYS @ >select next_extent, pct_increase from dba_Tables where table_name='S' and owner ='AK';
NEXT_EXTENT PCT_INCREASE
----------- ------------
1048576
06:30:33 SYS @ >aLTER table AK.s STORAGE ( next 128k pctfree 10 );
Table altered.
Elapsed: 00:00:00.00
06:31:49 SYS @ >select next_extent, pct_increase from dba_Tables where table_name='S' and owner ='AK';
NEXT_EXTENT PCT_INCREASE
----------- ------------
131072
06:31:49 SYS @ >alter table AK.s allocate extent (DATAFILE '/u01/oradata/test/test.dbf' SIZE 1K);
alter table ak.s allocate extent (DATAFILE '/u01/oradata/test/test.dbf' SIZE 1K)
*
ERROR at line 1:
ORA-01653: unable to extend table AK.S by 128 in tablespace TESTSince this was not helping, the next thing that was attempted was to look at the tablespace block map and determine the segments occupying the adjoining blocks. If those segments could be moved, perhaps it would free up enough contiguous free space, which could then be allocated to the interim table for re-formatting .
A script to help you with this could be Script: To Create Tablespace Block Map (Doc ID 1019474.6)
You can use the output to identify segments in the adjoining blocks. In this case the adjoining objects were table EQUIP.ATTR & an index EQUIP.EXCHANGER_IDX
Tablespace File Block Id Size Segment
--------------- ---- ---------- -------- --------------------------------------
TEST 5 1 1
...
....
323,128 8 EQUIP.SITE_MAP
323,256 8 EQUIP.ATTR
323,264 128
323,392 16 EQUIP.EXCHANGER_IDX
323,408 8 EQUIP.SITE_MAP
...
....The next bit was to identify a suitable window to move the table and index to free up these blocks.
23:17:14 SYS @ >alter table EQUIP.ATTR move;
Table altered.
Elapsed: 00:00:00.00
23:18:10 SYS @ >alter index EQUIP.EXCHANGER_IDX rebuild;
Index altered.
23:20:12 SYS @ >alter tablespace TEST coalesce;
Tablespace altered.
Tablespace File Block Id Size Segment
--------------- ---- ---------- -------- --------------------------------------
TEST 5 1 1
...
....
323,248 8 EQUIP.SITE_MAP
323,256 152
323,408 8 EQUIP.SITE_MAP
...
....After moving these segments the contiguous space had grown and the subsequent attempt to allocate blocks to the table AK.S succeed. Finally the corrupt block was no longer “Free” and had been allocated to our interim Dummy table.
23:21:25 SYS @ >BEGIN
for i in 1..1000000 loop
EXECUTE IMMEDIATE 'alter table AK.s allocate extent (DATAFILE '||'''/u01/oradata/test/test.dbf''' ||'SIZE 1K) ';
end loop;
end ;
/
23:21:25 2 23:21:25 3 23:21:25 4 23:21:25 5 23:21:25 6 BEGIN
*
ERROR at line 1:
ORA-01653: unable to extend table AK.S by 128 in tablespace TEST
ORA-06512: at line 3
Now on rechecking we can see that the space has been allocated to the interim table. So now we could proceed with inserting data into this table to re-format the corrupt blocks.
OWNER SEGMENT_TYPE SEGMENT_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED
------- ------------ ------------ ----- ----------------- --------------- ----------------
AK TABLE S 5 323376 323383 8
We then follow the MOS note and insert data into the table to format all the corrupt blocks and finally run an RMAN validate for the datafile in question to verify that it was indeed now corruption free.
23:27:15 SYS @ >Begin
FOR i IN 1..1000000000 loop
for j IN 1..1000000 loop
Insert into ak.s VALUES(i,'x');
end loop;
commit;
END LOOP;
END;
/23:27:15 2 23:27:15 3 23:27:15 4 23:27:15 5 23:27:15 6 23:27:15 7 23:27:15 8 23:27:15 9
Begin
*
ERROR at line 1:
ORA-20000: Corrupt block has been formatted
ORA-06512: at "SYS.CORRUPT_TRIGGER", line 10
ORA-04088: error during execution of trigger 'SYS.CORRUPT_TRIGGER'
ORA-06512: at line 4
RMAN> Backup validate check logical datafile 5;
Starting backup at 20-DEC-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/oradata/test/test.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 22417 489728 6797783084585
File Name: /u01/oradata/test/test.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 185689
Index 0 272859
Other 0 8763
Finished backup at 20-DEC-20