Oracle Database Discussions

Pravin	
 	
 	
Handle:	 Pravin  
Status Level:	 Newbie
Registered:	 Dec 28, 2010
Total Posts:	 95
Total Questions:	 33 (24 unresolved)
Name	 Mapa
Location	 Mumbai
Occupation	 System Administrator
Biography	 Now me working as System Admin. Trying for the full time DBA. I am OCP certified.

......................

How are you going through you alter logs now?

I use vi or grep to go search alter log.

Hi;

please review:
http://www.orafaq.com/wiki/Alert.log

@Fran
Man, You make me smile :)

Regard
Helios

@Helios-Gunes EROL
Always a pleasure

if anyone else shares my mood:
http://9gag.com/gag/5628807
http://9gag.com/gag/5628670

@ spur....me too use vi or cat for going through the alert logs. but do you know any tools to read the alert file, which can make my life easier. Going through the whole alert logs in vi editor is really very painfull.

@fran....keep ur smartness with yourself....

create directory BDUMP as '/u01/app/oracle/admin/mysid/bdump';

create table
alert_log ( msg varchar2(80) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alrt_mysid.log')
)
reject limit 1000;

Now we can easily extract important Oracle alert log information without leaving SQL*Plus, something like this SQL:

select msg from alert_log where msg like 'ORA-00600%';

http://www.dba-oracle.com/t_oracle_alert_log_sql_external_tables.htm

Regards
Girish Sharma

you can also mail it to yourself and use any text editor if you are not comformtable with vi.

cat filename| mail <email address>

Also, WinSCP tool lets you open file in notepad like texteditor.

Why you need tools ? its clear and sorted ...


But if you need tools for reading Alert log use One the below
-TOAD FOR ORACLE
-EnterPrise manager .

It doesn't have to be. Being moderately proficient in vi is just one of those basic skills you should have if you are going to be an Oracle DBA. You don't have to be proficient with every arcane editing command. But simple page up/page down, search up/search down, and use of grep for simple search/filter is no more difficult in vi than it is in notepad.

Edited by: EdStevens on Oct 18, 2012 11:44 AM

why do you have to "go through the alert log files" ?

a) if it is one time job for something specific, just use text editor viewer and search whatever you need.

b) if it is a daily routine job, when you have to search for some errors or anomalies - automate it. Write a script that searches whatever you need, cuts it and sends email report/alert. Schedule this script. done.

On Windows, better than notepad, use notepad++ (you can set it to autoscroll) or vim, install cygwin and use tail or less command. There are so many choices ... but yes, thse are essential tools as Ed mentions.

Edited by: rjamya on Oct 18, 2012 1:02 PM

Yeah reading a file since the time db got created is not going to be painful but also would be a waste of time. Why on earth you want to go through the alert log? What for? If rather than asking solution for a question which doesn't make much sense, you tell us the actual requirement of yours, we may be able to give you a proper reply.

Aman....

use something like logrotate .. rather than a single file.

Also if you have 11g database you can you ADRCI tools which is amazing tools will give you features like rotate , purge and reading alert log (Only Errors)

You can read the alert log through external table.

Oracle 10g
create or replace directory bdump as ‘/u01/app/oracle/admin/SID/bdump/’;
Oracle 11g
create or replace directory bdump as '/u01/app/oracle/diag/rdbms/db_name/instance_name/trace/’;

Of course, ‘/u01/app/oracle/admin/dipak/bdump/’ OR
‘/u01/app/oracle/diag/rdbms/dipak/dipak/trace/’
must point to the directory where the file actually resides.

In order to prevent a ORA-29913, someone must grant read and write on the directory to the user that uses the directory:
SQL> grant read, write on directory bdump to username;

Creating External table:
create table
alert_log ( msg varchar2(100) )
organization external (
type oracle_loader
default directory bdump
access parameters (
records delimited by newline
)
location('alert_DIPAK.log')
)
reject limit 1000;


SQL> select msg from alert_log where msg like 'ORA-00600%';


*11g New Feature - ADR (Automatic Diagnostic Repository) Viewing and Maintaining the alert log file*

The DIAGNOSTIC_DEST parameter identifies the location of the ADR and it defaults to the
$ORACLE_HOME/log or $ORACLE_BASE if this environment variable has been set.

Let us assign this location the environment variable $ADR_DUMP.
The alert log is stored in both XML as well as standard text format in the ADR. In this
example the database name is ‘dipak’.

The standard text alert log file (alert_DIPAK.log) is located
at $ADR_DUMP/diag/rdbms/dipak/dipak/trace and is maintained for backward
compatibility purposes.
The view V$DIAG_INFO also has information on the location of the various components of
the ADR.
SQL> col value format a50
SQL> SELECT NAME,VALUE FROM v$diag_info WHERE NAME LIKE '%Alert%';
NAME VALUE
-------------------- --------------------------------------------------
Diag Alert /u01/oracle/diag/rdbms/apex/apex/alert


Using the ADR CLI to view Alert Log Information:
The command ‘adrci’ will take us to the prompt.
adrci>

SHOW ALERT -TAIL (to view the last 10 entries)
SHOW ALERT -TAIL 50 (to view the last 50 entries)
SHOW ALERT -TAIL -F (keeps the alert log open to view as entries arrive until Control C is
pressed)
show alert -p “message_text like ‘%ORA-07445%’” (to show all occurrences of the
string ‘ORA-07445′ in the alert log.)