Home > Oracle > check if oracle database has crashed

check if oracle database has crashed

SELECT
a.thread#, b.open_mode, a.status,
CASE
WHEN ((b.open_mode=’MOUNTED’) AND (a.status=’OPEN’)) THEN ‘Crash Recovery req.’
WHEN ((b.open_mode=’MOUNTED’) AND (a.status=’CLOSED’)) THEN ‘No Crash Rec. req.’
WHEN ((b.open_mode=’READ WRITE’) AND (a.status=’OPEN’)) THEN ‘Inst. already open’
ELSE ‘huh?’
END STATUS
FROM v$thread a,
v$database b,
v$instance c
WHERE a.thread# = c.thread#;
/

SELECT
a.name,
a.checkpoint_change#,
b.checkpoint_change#,
CASE
WHEN ((a.checkpoint_change# – b.checkpoint_change#) = 0) THEN ‘Startup Normal’
WHEN ((a.checkpoint_change# – b.checkpoint_change#) > 0) THEN ‘Media Recovery’
WHEN ((a.checkpoint_change# – b.checkpoint_change#) < 0) THEN 'Old Control File'
ELSE 'what the ?'
END STATUS
FROM v$datafile a, — control file SCN for datafile
v$datafile_header b — datafile header SCN
WHERE a.file# = b.file#;
/

select file#, status, error,recover from v$datafile_header
/

Advertisements
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: