Database Reference
In-Depth Information
SHUTDOWN STARTUP MINS_DOWN DAYS_UP DAYS_STILL_UP
----------------- ----------------- ---------- ---------- -------------
28-jun-2013 16:04
28-jun-2013 17:02 28-jun-2013 17:02 .03 .04
29-jun-2013 06:00 01-jul-2013 09:42 3102.53 .54
02-jul-2013 14:59 02-jul-2013 14:59 .03 1.22
02-jul-2013 15:00 02-jul-2013 15:00 .03 0
02-jul-2013 15:10 02-jul-2013 15:10 .03 .01
02-jul-2013 17:01 02-jul-2013 17:02 1.55 .08
18-jul-2013 02:00 18-jul-2013 11:31 571.37 15.37
05-aug-2013 09:00 06-aug-2013 09:06 1445.62 17.9
14-aug-2013 09:09 14-aug-2013 09:58 49.42 8
31-aug-2013 14:08 02-sep-2013 10:51 2683.15 17.17
02-sep-2013 14:32 02-sep-2013 14:51 18.93 .15
02-sep-2013 15:13 02-sep-2013 15:13 .03 .02
02-sep-2013 15:15 02-sep-2013 15:15 .05 0
02-sep-2013 16:53 02-sep-2013 16:54 .03 .07
02-sep-2013 16:56 02-sep-2013 16:56 .03 0 .07
16 rows selected.
I won't go into the nuances of the SQL query here, but the innermost query from lines 18 through 21 collects the
“Starting” and date lines (remember, when using a
LIKE
clause,
_
matches precisely one character—at least one and
at most one). That query also numbers the lines using
rownum
. Then, the next level of query uses the built-in
LAG()
analytic function to reach back one and two rows for each row, and slide that data up so the third row of this query has
the data from rows 1, 2, and 3. Row 4 has the data from rows 2, 3, and 4, and so on. We end up keeping just the rows
that were like
Starting ORACLE instance %
, which now have the two preceding timestamps associated with them.
From there, computing downtime is easy: we just subtract the two dates. Computing the uptime is not much harder
(now that you've seen the
LAG()
function): we just reach back to the prior row, get its startup time, and subtract that
from this line's shutdown time.
My Oracle 12c database came into existence on 28-Jun-2013 and it has been shut down numerous times (and as
of this writing it has been up for .07 days in a row).
If you are interested in seeing another example of mining the alert log for useful information, go to
http://tinyurl.com/y8wkhjt
. This page shows a demonstration of how to compute the average time it took to
archive a given online redo log file. Once you understand what is in the alert log, generating these queries on your
own becomes easy.
In addition to using an external table to query the alert log in 12
c
, you can easily view the alert log using the ADRCI
tool. That tool lets you find, edit (review), and monitor (interactively display new records as they appear in the alert
log). Also, the alert log in 11
g
and above is available in two versions—the old version we just used and an XML version:
EODA@ORA12CR1> column value new_val V
EODA@ORA12CR1> select value from v$diag_info where name = 'Diag Alert';
VALUE
-------------------------------------------------------------------------------
/home/ora12cr1/app/ora12cr1/diag/rdbms/ora12cr1/ora12cr1/alert
EODA@ORA12CR1> !ls &V/log.xml
/home/ora12cr1/app/ora12cr1/diag/rdbms/ora12cr1/ora12cr1/alert/log.xml
EODA@ORA12CR1> !head &V/log.xml
<msg time='2013-06-28T16:04:25.378-04:00' org_id='oracle' comp_id='rdbms'
