Database Recovery
Database recovery is a very complex topic, by practicing recovery techinques regularly will help when the real thing happens everything should fall into place.
Oracle performs automatic recovery when an instance has crashed or been aborted (using shutdown abort). Recovery involves a two step process
The recovery begins at a point in the redo logs know as thread checkpoint redo byte address. This is the time when the last checkpoint was done before the crash. You can help in reducing the time recovery happens by decreasing the time the checkpoint occurs thus reducing the amount of data that has to be recovered. By setting the parameter fast_start_mttr_target (maximum is 3600 seconds - 1 hour) you can specify how long a recovery should take, beware that this is only a target and oracle will try its best to meet this target, also decreasing this threshold will probably impact the performance of the server. There are two other system parameters that can be used to control when checkpointing occurs log_checkpoint_timeout and fast_start_io_target.
set MTTR | alter database set fast_start_mttr_target = 600; Note: the value is in seconds |
Display current MTTR | select recovery_estimated_ios, estimated_mttr, target_mttr from v$instance_recovery; |
Other useful system parameters |
|
LOG_CHECKPOINT_TIMEOUT | log buffers are written to disk within this target can have big impact on performance if to low (default is zero and maximum is 3600 secs – 1 hour) alter system set fast_start_mttr_target=60 scope=both; (no restart required) |
FAST_START_IO_TARGET | maximum number of seconds that any new or modified block in the buffer cache waits until it is written to disk (overrides fast_start_mttr_target). |
There are two types of recovery possible complete and incomplete recovery
Block Media Recovery
If only a few blocks within a datafile are corrupted, you should consider block media recovery, this can be performed via RMAN.
Database Recovery
I have broken down recovery techniques into different key area's
There is a table that can be used to identify files needing media recovery
V$RECOVERY_FILE | reports missing or corrupted files, the table will identify the file and error. |
Complete Database Recovery
Recovering a database can be done in three ways RMAN, traditional user-managed recovery (Netbackup, ufsrestore) or Enterprise Manager (OEM).
RMAN complete DB | ## Double check that backup sets exists and we can recover rman> sql "shutdown immediate"; Note: you must be in mount mode, the redo logs will roll forward the database during recovery |
RMAN tablespace recovery | ## Double check that backup exists and we can recover ## Now take the tablespace offline and recover the file ## You need to take the tablespace offline when recovering a whole tablespace |
RMAN datafile recovery | ## Double check that backup exists and we can recover ## Restore the datafile then recover it, ## It is possible to not take the tablespace offline as long as no one uses it, the dba_data_files (online_status) states that the file needs recovering |
User-Managed | 1. Open in mount mode |
OEM | see oracle manual |
Recovering non-critical files
see recovering non-critical files for more details
Recovering critical files
see recovering critical files for more details
Incomplete Database Recovery
see incomplete database recovery for more details
Recovering from User Errors
see recovering from user errors for more details
Flashback
see flashback for more details