How to check the last 10 startup times of the database along with timezone in Oracle

December 11, 2020

The last 10 startup times of the database along with timezone in Oracle

Sometimes we have to find when the database is started with which timezone especially when the database is getting rebooted unknowingly in Oracle.

The below query gives the output of the last 10 start times of the database along with the timezone in Oracle, Click here for sample output.

SET SPACE 1 LINESIZE 80 PAGES 1000
SELECT * FROM (
select inst_id,to_char(ORIGINATING_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS TZH:TZM')
from V$DIAG_ALERT_EXT
WHERE trim(COMPONENT_ID)='rdbms'
and MESSAGE_TEXT like ('PMON started with%')
order by inst_id,originating_timestamp desc )
WHERE rownum < 11;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

Key points to remember:
1. V$DIAG_ALERT_EXT fetches data from XML-based alert log in the ADR (Automatic Diagnostic Repository) i.e from the Oracle database alert log.

2. In the RAC database execute in each instance according to your requirements.

We always encourage the technical person to visit the section SCRIPTS to get more daily usage SQL commands.

Hope so you like this script!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.

DATABASE, ORACLE, ORACLE 19C, SCRIPTS