10g Admin Workshop II - 5 - Database Recovery

There are two methods for performing recovery:
• User-managed recovery
– Files must be maintained and moved into place manually.
– Use SQL*Plus commands.
• RMAN recovery
– Files are managed automatically.
– Use RMAN functionality including all repository maintenance and reporting capabilities.
– This can be done by using Enterprise Manager.
– Oracle Corporation recommends using this method.

User-Managed Recovery: RECOVER Command
The first step in performing user-managed media recovery is to manually restore the data files by copying them from a backup. If you do not restore a data file to its original location, you must update the control file with the new location by using an ALTER DATABASE RENAME FILE command. You must also restore any archived logs files needed to recover the restored data files. For RMAN restorations, you would use the SET NEWNAME command to specify the new location for that file.
You can use one of the following commands to recover the database or data file:
• RECOVER [AUTOMATIC] DATABASE
This command can be used only for a closed database recovery.
• RECOVER [AUTOMATIC] TABLESPACE <NUMBER> | <NAME>
This command can be used only for an open database recovery.
• RECOVER [AUTOMATIC] DATAFILE <'filename'> | <NAME>
This command can be used for both an open and a closed database recovery.
The AUTOMATIC keyword instructs the Oracle database to automatically generate the name of the next archived redo log file needed to continue the recovery operation. Otherwise, you are prompted for these names.

Complete Recovery
These are the steps that take place during complete recovery:
1. Damaged or missing files are restored from a backup.
2. Changes from incremental backups, archived redo log files, and online redo log files are applied as necessary. The redo log changes are applied to the data files until the current online log is reached and the most recent transactions have been reentered. Undo blocks are generated during this entire process. This is referred to as rolling forward or cache recovery.
3. The restored data files may now contain committed and uncommitted changes.
4. The database is opened before undo is applied. This is to provide higher availability.
5. The undo blocks are used to roll back any uncommitted changes. This is sometimes referred to as transaction recovery.
6. The data files are now in a recovered state and are consistent with the other data files in the database.

Incomplete Recovery
Incomplete recovery, or database point-in-time recovery, uses a backup to produce a noncurrent version of the database. That is, you do not apply all of the redo records generated after the most recent backup. Perform this type of recovery only when absolutely necessary.
To perform incomplete recovery, you need:
• A valid offline or online backup of all the data files made before the recovery point
• All archived logs from the time of the backup until the specified time of recovery

The progression taken to perform an incomplete recovery is listed below:
1. Restore data files from backup. The backup that is used may not be the most recent one, if your restore point destination is to be not very recent.
2. Apply redo from the archived redo log files, including as many as necessary to reach the restore point destination.
3. Now the data files contain some committed and some uncommitted transactions because the redo can contain uncommitted data.
4. The database is opened before undo is applied. This is to provide higher availability.
5. While the redo was being applied, redo supporting the undo data files was also applied. So, the undo is available to be applied to the data files in order to undo any uncommitted transactions. That is done next.
6. The data files are now recovered to the point in time that you chose.

Situations Requiring Incomplete Recovery
• Complete recovery fails because of a missing archived log file.
• One or more unarchived redo log files and a data file are lost.
• A backup of the control file is used to open or recover the database.

Types of Incomplete Recovery
There are four types of incomplete recovery:
• Time-based recovery
• Cancel-based recovery
• Change-based recovery
• Log sequence recovery

Performing User-Managed Incomplete Recovery
The following command is used to perform incomplete recovery:
RECOVER [AUTOMATIC] DATABASE option
Here:
• AUTOMATIC: Automatically applies archived and redo log files
• option: UNTIL TIME 'YYYY-MM-DD:HH24:MI:SS'
UNTIL CANCEL
UNTIL CHANGE <integer>
USING BACKUP CONTROLFILE

Performing User-Managed Incomplete Recovery
To perform user-managed incomplete recovery, follow these steps:
1. Shut down the database.
2. Restore data files. If there is enough space available, restore to the LOG_ARCHIVE_DEST location or use the ALTER SYSTEM ARCHIVE LOG START TO <LOCATION> command or the SET LOGSOURCE <LOCATION> command to change the location.
3. Mount the database.
4. Recover the database.
5. Open the database with the RESETLOGS option. To synchronize data files with control files and redo logs.

User-Managed Time-Based Recovery:
SQL> SHUTDOWN IMMEDIATE
$ cp /BACKUP/* /u01/db01/ORADATA
SQL> STARTUP MOUNT
SQL> RECOVER DATABASE UNTIL TIME '2005-11-28:11:44:00';
SQL> ALTER DATABASE OPEN RESETLOGS;

User-Managed Cancel-Based Recovery:
Recover the database as follows:
1. Shut down the database.
2. Restore all data files from the most recent backup.
3. You already have a valid backup, so mount the database.
4. Execute RECOVER DATABASE UNTIL CANCEL.
5. Execute ALTER DATABASE OPEN RESETLOGS to open the database.
When the RECOVER DATABASE UNTIL CANCEL command is executed, it recovers the database until it cannot find a log file.

Performing Incomplete Recovery by Using RMAN
1. Mount the database.
2. Allocate multiple channels for parallelization.
3. Restore all data files.
4. Recover the database by using UNTIL TIME, UNTIL SEQUENCE, or UNTIL SCN.
5. Open the database by using RESETLOGS.

Time-Based Recovery Using RMAN
1. If the target database is open, perform a clean shutdown.
2. Mount the target database. Do not back up the database during the recovery.
3. Ensure that NLS_LANG and NLS_DATE_FORMAT environment variables are set appropriately:
    $ NLS_LANG=american_america.we8iso8859p15
$ NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'
4. Start Recovery Manager and connect to the target database.
 $ rman target rman/rman@ORCL
5. You can allocate multiple channels to improve the performance:
RMAN> run {allocate channel c1 type DISK;
2> allocate channel c2 type DISK;
6. RMAN> RUN {
2> SET UNTIL TIME = '2005-11-28:11:44:00';
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> ALTER DATABASE OPEN RESETLOGS; }

Log Sequence Recovery Using RMAN
RMAN> RUN {
2> SET UNTIL SEQUENCE 120 THREAD 1;
3> ALTER DATABASE MOUNT;
4> RESTORE DATABASE;
5> RECOVER DATABASE; # recovers through log 119
6> ALTER DATABASE OPEN RESETLOGS;
7> }

Incomplete Recovery and the Alert Log
During recovery, progress information is stored in the alert log. This file should always be checked before and after recovery.

Restore point:
• Serves as an alias for an SCN or a specific point in time
• Is stored in the control file
• Can be used with:
– RECOVER DATABASE
– FLASHBACK DATABASE
– FLASHBACK TABLE

Incomplete Recovery: Best Practices
• It is important to follow all recovery steps carefully because most incomplete recovery
problems are caused by a DBA error during the recovery process.
• Before starting incomplete recovery, perform a whole closed database backup
If a whole backup is not performed, at least archive the current redo log:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT
and back up the control file:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u01/data/backup.ctl';

RESTORE CONTROLFILE FROM AUTOBACKUP command:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

The instance must be in the NOMOUNT state

Restore spfile from the autobackup
must first set the DBID for your database, and then use command:
RESTORE SPFILE FROM AUTOBACKUP;

After you have started the instance with the restored server parameter file, RMAN can restore the control file from the autobackup.

After restoring the control files of your database from backup, you must perform complete media recovery and then open your database with the RESETLOGS option.

-- EOF --