Hi,

To my knowing we always have v$instance or gv$instance to query the status of the instance. Is there any v$views that gives us the complete history regarding the startup and shutdown of databases. Is there any PLSQL builtins provided by oracle.Please let me how to find..

Thank you in advance and appreciate your time for sharing knowledge..

"Is there any v$views that gives us the complete history regarding the startup and shutdown of databases. "

Startup is in v$instance.startup_time, but shutdown data is problematic.

During a normal shutdown, Oracle writes message to the alert log, but under an emergency abort (i.e. un-plug the server), nothing appears in the logs, nor within Oracle. Oracle stops abruptly, and there is no way to see it, except periodlic polling for a PMAON or SMON process.

Startup and shutdown are written to the alert log, externally, but you could define the alert log as an external table and run a query to see startup time.

http://www.dba-oracle.com/t_oracle_alert_l...rnal_tables.htm

Or, try this query to see uptime, from v$instance



If you assume that PMON startup time is the same as the database startup time, you can get the uptime here:

How do I use cat to scan the alert log to give me the details of the database Instance down time for the past 6 months.

cat alert_log.log | grep instance -- shows the instance shutting down and starting up.. How do I grep with the time at which it occured..

Nick,

The problem is that the shutdown and startup time is not on the same line.

You would have to write a program to parse it.

Here is an example:

http://www.orafaq.com/maillist/oracle-l/2003/07/09/1282.htm

tail -20 $ALERT_DEST/alert_$ORACLE_SID.log |/usr/xpg4/bin/grep -f $DIR/paternfile"` echo $ORACLE_SID: Alert Log last Checked at `date '+%m/%d/%y %H:%M'`

Hi HAL9000

I modified the script as below to parse the alert log and grep the instance shutdown and instance startup messages but I get the errors. Could you please let me know if I am missing something..

$1=$ORACLE_SID
export ORACLE_SID=$1;
export DIR=/home/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0 export ALERT_DEST=/u01/app/oracle/admin/$ORACLE_SID/bdump
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
COLLECTOR=`cat $ALERT_DEST/alert_$ORACLE_SID.log ` echo $ORACLE_SID: Alert Log last Checked at `date '+%m/%d/%y %H:%M'` > $DIR/lastran_$ORACLE_SID.log
for i in $COLLECTOR
do
ORAERR=`echo $i | grep instance`
if [ $ORAERR ]
then
i=`echo $i | awk '{print $1}'`
y=`cat $DIR/tmpalert$ORACLE_SID.log | egrep -ch $i`
if [ $y -ge 2 ]
then
continue
else
echo $i Paged at `date '+%m/%d/%y %H:%M'` >>
$DIR/tmpalert$ORACLE_SID.log
echo "Oracle errors in alert log on $ORACLE_SID"
fi;
fi;


$ sh check_alert.sh
check_alert.sh: line 1: =PROD2: command not found
cat: /u01/app/oracle/admin//bdump/alert_.log: No such file or directory
check_alert.sh: line 18: syntax error near unexpected token `newline'
check_alert.sh: line 18: ` echo $i Paged at `date '+%m/%d/%y %H:%M'` >>'

Hi,

>> I modified the script as below to parse the alert log

You know, it might be easier to define the alert log as an external table:

http://www.dba-oracle.com/t_oracle_alert_l...rnal_tables.htm

That way, you can parse with normal SQL, using regular expressions, very powerful:

http://www.dba-oracle.com/oow_getting_regu...expressions.pdf

Thank You Mr.Burleson,

Its working great, I need to have the timestamp of each msg , is it possible .. there by I can calculate the total downtime of the database..