Locating the alert log (Oracle 10g - 11g)

I’ve been working on a 10g to 11g migration project, and as part of that I was tasked with migrating the scripts and crontab settings.

There were scripts for…..

• Archiving the alert log and compressing it.
• Searching the alert log for errors etc.
• Also tidying the trace files etc.

I found that several scripts did things with the alert log and that the file paths to the alert log were hard coded, as were those for the trace files. As you should know by now, the background_dump_dest and user_dump_dest system parameters have been superseded by the diagnostic_dest parameter. Where it was simple 10g and before, its now complex at 11g+.

10g- - {background_dump_dest}/.....

11g+ - {diagnostic_diag}/diag/rdbms/{oracle Sid}/{oracle Sid}/trace/.....

And it doesn't end there because the first oracle Sid seems to be the db_unique_name in lowercase. 

What I needed was a generic script that could locate the files with either setting, and this is what I came up with. This version returns the alert log file name and file path, but variations to just return the full path can be achieved simply.

$ cat locate_alert_log.sh

#-----------------------------------------------------

sqlplus -s "/ as sysdba" <<EOF  > /tmp/locate_alert_log.log

set lines 200

set head off

select

decode((select value from v\$parameter where name = 'diagnostic_dest'),null,

   (select value from v\$parameter where name = 'background_dump_dest')

   || '/alert_' ||

   (select value from v\$parameter where name = 'db_name')

   || '.log'

,

   (select value from v\$parameter where name = 'diagnostic_dest')

   || '/diag/rdbms/' ||

   (select lower(value) from v\$parameter where name = 'db_unique_name')

   || '/' ||

   (select value from v\$parameter where name = 'db_name')

   || '/trace/alert_' ||

   (select value from v\$parameter where name = 'db_name')

   || '.log')

from dual

/

EOF

tail -2 /tmp/locate_alert_log.log | head -1

#--------------------------------------------

$ more `./locate_alert_log.sh`

Happyjohn