In this post I’ll share a few Tips and Tricks when managing Backup and Recovery for an Oracle Database.

When an instance abnormally terminates, the thread status (v$thread) remains OPEN because Oracle didn’t get a chance to update the status to CLOSED. On start-up, when Oracle detects that an instance thread was abnormally left open, the system monitor process will automatically perform crash recovery. This is how SMON will determine what to do:-

SELECT
a.thread#, b.open_mode, a.status,
CASE
WHEN ((b.open_mode='MOUNTED') AND (a.status='OPEN')) THEN 'Crash Recovery Required.'
WHEN ((b.open_mode='MOUNTED') AND (a.status='CLOSED')) THEN 'No Crash Recovery Required.'
WHEN ((b.open_mode='READ WRITE') AND (a.status='OPEN')) THEN 'Instance is already open'
ELSE 'Oh God, Please give me strength to deal with it !!'
END STATUS
FROM v$thread a,
v$database b,
v$instance c
WHERE a.thread# = c.thread#;

You might have always heard/read about how SMON Process compares the SCN information in the controlfile and in the corresponding datafiles, theoretically its perfect but how about taking a peek into how SMON does that, from where and then what decision it makes, to demonstrate this:-

SELECT
a.name,
a.checkpoint_change#,
b.checkpoint_change#,
CASE
WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup the database normally - Check successful for this datafile'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Recovery is required'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File is in use, restore a newer controlfile'
ELSE 'Oh God, Please give me strength to deal with it !!'
END STATUS
FROM v$datafile a, -- control file SCN for datafile
v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#;

Now the time for some tips, if you are not aware of the options preview, validate and test:-

Ever thought about PREVIEWING which backup will be required for the restore operation, here it is the preview clause.

• RMAN> restore database preview;                                             —– full listing
• RMAN> restore database preview summary;                           —– summarize the full listing

You can use preview with any restore command, all you have to do is add preview clause at the end of restore command, it will not restore it will just give you the backup files which will be required for the restore by querying the repository ONLY it does not check that these files are physically accessible. TRY it you’ll like it.

Several times when a scenario comes where someone asks you whether this recovery is possible, at that time you would be sure about the recovery process but still I strongly suggest to validate that the backup pieces are available and structurally sound before you actually restore any datafiles, cause its really embarrassing when after committing its possible, you figure out that the backup pieces are corrupted , so for those times, here it is the validate clause.

• RMAN> restore database validate;                                            —– Checks for Physical Corruption.
• RMAN> restore database validate check logical;                    —– Checks for Physical & Logical Corruption.

You can use the validate (check logical) clause with any restore command, all you have to do is to add validate (check logical)  clause at the end of restore command, it will not restore it will just validate the backupsets/archived redo log against any physical or logical corruption, It is recommended to regularly check your backups via validate clause to check against any physical or logical corruption.

Now comes another scenario wherein you need to perform a database recovery, but you suspect one of your archived redo log files is bad. You want to perform a test to see whether all of the redo is available and can be applied. for this, here it is the test clause.

• RMAN> connect target /
• RMAN> startup mount;
• RMAN> restore database;
• RMAN> recover database test;

You can use the test clause with any recover command, all you have to do it to add test clause at the end of recover command. The test clause instructs Oracle to apply the redo necessary to perform recovery but does not make the changes permanent in the datafiles. When you recover in test mode, Oracle applies the required redo but rolls back the changes at the end of the process.

 

Enough clauses for today, I was about to add some more but due to certain reasons (“URGENT BEER PARTY”) I have to go. Hope you like it, would continue sharing some more clauses in subsequent posts.

PLEASE COMMENT IF YOU FACE ANY ERRORS WHEN PERFORMING THIS RECOVERY SO THAT WE CAN WORK ON THOSE AND ENHANCE THIS REPOSITORY FOR OTHER READERS.