crash or media recovery needed

--crash recovery needed
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#;

--Media recovery needed
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 ?'
FROM v$datafile a, -- control file SCN for datafile
v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#;

Leave a Reply

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

You are commenting using your 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: