Home > Oracle > Check Database for Block Corruptions

Check Database for Block Corruptions

To verify the full db please do:

1) Set your $ORACLE_SID and $ORACLE_HOME appropriately if not already set.

2) Start RMAN in nocatalog mode and connect to your database:
From the operating system prompt issue:
$ rman target / nocatalog
or
$ rman target sys/<sys_password> nocatalog

3) From the RMAN> prompt issue the validate command with the “check logical”
clause:

The following example shows how to validate all datafiles:


run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}

4)Execute the script

create table segment_corrupted (owner varchar2(10), segment_name varchar2(50), segment_type varchar(20));
truncate table segment_corrupted;

set serveroutput on

declare
nrows number;
badsegs number;
begin
badsegs:=0;
nrows:=0;
for i in (select file#,block# from v$database_block_corruption) loop
begin
insert into segment_corrupted select owner, segment_name, segment_type from dba_extents where file_id=i.file# and i.block# between block_id and block_id + blocks -1;
end;
nrows:=nrows+1;
end loop;
commit;
if nrows>0 then
dbms_output.put_line('List of segments affected by corruption');
end if;
for i in (select distinct owner, segment_name, segment_type from segment_corrupted) loop
dbms_output.put_line(i.segment_type||' '||i.owner||'.'||i.segment_name);
badsegs:=badsegs+1;
end loop;
dbms_output.put_line('Total blocks corrupted: '||to_char(nrows)||'. Total segments affected: '||to_char(badsegs));
end;
/

5) run the sql


 select * from v$database_block_corruption;

All these steps are valid only if the database is in ARCHIVELOG mode

Advertisements
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: