Three ways to tail the alert log in Oracle 11g

The Oracle 11g database offers us several choices in reading the alert log of the database.

1. You can read the last 200 lines from sqlplus using the folowing sql.

select * from (
select record_ID,message_text from X$DBGALERTEXT
order by record_ID desc
)
where rownum <=200
order by record_ID asc

It's something like tail -n 200 alert_SID.log


2. Oracle 11g introduces the adrci, a diagnostic utility so that you can see the alerts from different logs. It also can be customized very easely. The simpelest way to read the last 200 lines from the alert log with the follow option is:


[oracle@oracle11gdb ~]$ adrci


ADRCI: Release 11.1.0.6.0 - Beta on Wed Mar 30 14:59:02 2011


Copyright (c) 1982, 2007, Oracle.  All rights reserved.


ADR base = "/db/app/oracle"
adrci> show log -tail 200 -f



3. The clasic way, using the tail command, but now the file is in a differen location:

$ORACLE_BASE/diag/rdbms/DBNAME/ORACLE_SID/trace/alert_SID.log

so, the command might be:

tail -fn 200  $ORACLE_BASE/diag/rdbms/db11g/db11g/trace/alert_db11g.log

how to deal with block corruption

The block corruption is a tricky issue to deal with. You can usually see the corrupted blocks with the following query:


select * from v$database_block_corruption


If there are logical corruptions they can be fixed with restore-recover from RMAN or other many ways.

If there are physical corruptions, first fix the storage, and after this you can clear the corrupted blocks.




I found a SQL that determines which objects are affected by the corrupted blocks on this post:

http://toddlerdba.blogspot.com/2010/09/block-corrouption.html

It creates “segment_corrupted” table and inserts into it the objects affected by the corrupted blocks returned by the query above.


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

-----------------------------------------------------------------------------------------------------------------------------
truncate table segment_corrupted;

----------------------------------------------------------------------------------------------------------

declare
begin
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;
commit;
end;
end loop;
commit;
end;
/

The segment_name is the Object Name and the segment_type is the Object Type.  After this script ends the Segment_corrupted table will have the objects affected by the corrupted blocks.

Sometimes a corrupted block may not affect any object.



There are many ways of dealing with corrupted blocks.


If there are indexes affected the simplest way is to drop and recreate the index.

If there are other types of objects, and you have a valid backup, you can drop them and recover from that backup.

 For tables, sometimes works rebuilding them using Toad (in fact toad renames the table, creates another one with the same data and recreates indexes, constraints, triggers, and other objects, but this doesn’t always work).

If the corrupted block does not affect any object, another solution besides recovering the datafile from backup, is to fill the tablespace and empty it (create a huge table with random data and drop it after) but this is a time consuming solution.


Do not forget to take a full database backup after you have cleared the corrupted blocks.