0

I want to see log for how often my oracle Database goes down ,so is there a SQL query can give me this info..

1
  • 1
    You need to start with defining downtime. When the database wasn't running or when it wasn't accessible from somewhere specific? This is most likely not possible.
    – Jonny
    Commented Apr 2, 2019 at 6:24

2 Answers 2

2

Example for Oracle 11.2

Prepare

sqlplus / as sysdba 
CREATE VIEW X$_DBGALERTEXT AS SELECT * FROM X$DBGALERTEXT; 
GRANT SELECT ON X$_DBGALERTEXT TO <USERNAME>; 
CREATE SYNONYM <USERNAME>.X$DBGALERTEXT FOR SYS.X$_DBGALERTEXT;

select startup and shutdown time database.

select  to_char(originating_timestamp, 'YYYY-MM-DD HH24:MI' ), message_text 
from X$DBGALERTEXT
where  message_text like '%Starting ORACLE instance%' or
       message_text like '%Instance shutdown%'

Example output

2018-07-31 10:45    Starting ORACLE instance (normal)

2018-07-31 11:12    Instance shutdown complete

2018-07-31 11:13    Starting ORACLE instance (normal)

2019-01-25 10:16    Instance shutdown complete

2019-01-25 14:15    Starting ORACLE instance (normal)
1
  • That's a good start but if someone pulled the plug on the server the database wouldn't record the shutdown and you'd have to guess how long it was down. Without a system like OEM, constantly monitoring the database, I'm not sure how accurately this question can be answered.
    – Jon Heller
    Commented Apr 3, 2019 at 2:09
1

There is no such info. To get those information you could create a shutdown / startup trigger and log the information in a new table.

For triggers see: https://docs.oracle.com/database/121/LNPLS/create_trigger.htm#LNPLS01374

Database event: AFTER STARTUP / BEFORE SHUTDOWN

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.