oracle-script-lib/db_corrupt.sql at master · eratolekov/oracle-script-lib · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

-- db_corrupt.sql
-- jkstill@gmail.com - 2013
-- show rows in v$database_block_corruption
-- and look up which objects they are
--

col file# format 99999
col block# format 9999999999
col blocks format 999999
col corruption_change# format 999999
col corruption_type format a20 head 'CORRUPTION TYPE'


prompt
prompt Corruption types
prompt
prompt ALL ZERO - Block header on disk contained only zeros.
prompt The block may be valid if it was never filled and if it is in an Oracle7 file.
prompt The buffer will be reformatted to the Oracle8 standard for an empty block.
prompt
prompt FRACTURED - Block header looks reasonable, but the front and back of the block are different versions.
prompt
prompt CHECKSUM - optional check value shows that the block is not self-consistent.
prompt It is impossible to determine exactly why the check value fails, but it probably fails
prompt because sectors in the middle of the block are from different versions.
prompt
prompt CORRUPT - Block is wrongly identified or is not a data block (for example, the data block address is missing)
prompt
prompt LOGICAL - Block is logically corrupt
prompt
prompt NOLOGGING - Block does not have redo log entries
prompt (for example, NOLOGGING operations on primary database can introduce this type of corruption on a physical standby)
prompt

select *
from v$database_block_corruption
order by  corruption_change#
/


prompt
prompt Finding Objects of Corrupt Blocks
prompt

set serveroutput on size 1000000

begin

	for crec in (
		select file#, block#, blocks
		from v$database_block_corruption
		order by  corruption_change#
	)
	loop
		for brec in (
			select e.owner, e.segment_name, e.segment_type, e.partition_name, e.tablespace_name
			from dba_extents e
			where e.file_id = crec.file# and crec.block# between e.block_id and e.block_id + e.blocks - 1
		)
		loop
			dbms_output.put_line('==================================================');
			dbms_output.put_line('OWNER	  : ' || brec.owner);
			dbms_output.put_line('SEGMENT   : ' || brec.segment_name);
			dbms_output.put_line('TYPE		  : ' || brec.segment_type);
			dbms_output.put_line('PARTITION : ' || brec.partition_name);
			dbms_output.put_line('TABLESPACE: ' || brec.tablespace_name);

		end loop;
	end loop;
end;
/