Help - Search - Members - Calendar
Full Version: v$ views
Oracle DBA Forums > Oracle > Oracle Forum
nickdba
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..
HAL9000
"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

SQL
select 'Hostname : ' || host_name
,'Instance Name : ' || instance_name
,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from sys.v_$instance;


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

SQL
SELECT to_char(logon_time,'DD/MM/YYYY HH24:MI:SS')
FROM v$session WHERE sid=1
nickdba
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..

QUOTE (HAL9000 @ Jul 14 2008, 04:05 PM) *
"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

SQL
select 'Hostname : ' || host_name
,'Instance Name : ' || instance_name
,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from sys.v_$instance;


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

SQL
SELECT to_char(logon_time,'DD/MM/YYYY HH24:MI:SS')
FROM v$session WHERE sid=1
HAL9000
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'`
nickdba
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'` >>'



QUOTE (HAL9000 @ Jul 15 2008, 01:49 PM) *
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'`
burleson
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
nickdba
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..

QUOTE (burleson @ Jul 16 2008, 10:16 AM) *
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.