Incomplete Recovery   «Prev  Next»

Lesson 4Incomplete database recovery steps
ObjectiveDescribe the processes used in incomplete recovery.

Incomplete Database Recovery Steps with Archiving

The following commands are used to perform incomplete recovery:
TYPES OF INCOMPLETE RECOVERY COMMAND SYNTAX
Time-based recovery RECOVER [AUTOMATIC] DATABASE UNTIL TIME 'YYYY-MM-DD:HH:MI:SS';
Cancel-based recovery RECOVER [AUTOMATIC] DATABASE UNTIL CANCEL;
Change-based recovery RECOVER DATABASE UNTIL CHANGE <INTEGER>;
Recovery using a backup control file RECOVER [AUTOMATIC] DATABASE UNTIL TIME 'YYYY-MM-DD:HH:MI:SS' |CANCEL|CHANGE USING BACKUP CONTROLFILE;

Alternatives to use when you perform Incomplete Recovery

There are some alternatives to use when you perform incomplete recovery. If you use Enterprise Manager media recovery options, you may add ALTER DATABASE in front of the RECOVER command. To make Oracle automatically apply redo log files during recovery, there are two methods:
  1. Enter AUTO at the recovery prompt
  2. Use the command RECOVERY AUTOMATIC

Recovery steps

Incomplete recovery is a difficult operation. Experience shows that most problems that occur in the process are caused by DBA errors. Thus, it is essential to carefully follow the recovery steps demonstrated in the View SlideShow below. You may also preview all the steps. As a DBA, you should also make sure the objects are recovered before you make the database available to your users. You may, for example, query the user's schema to check that the supposedly recovered objects exist. If they do not, you have to repeat the recovery process again.

Incomplete Database Recovery steps in Oracle

  1. Shut down the database and perform a whole closed database backup.
  2. Restore all datafiles.
  3. Open the database in mount mode.
  4. Recover the datafiles to the specified time, redo log file, or change number.
  5. Open the database with the RESETLOGS option.
  6. Perform a whole database backup.

It is again important to perform a whole database backup after a successful incomplete recovery. Only this backup is valid if a recovery is required at a later time.


PITR Scenarios

There are occasions where you want to roll the entire database back to a point in time before a logical corruption occurred. If the flashback retention is not sufficient to rewind back as far as you would like, then you have to resort to restoring the entire database and applying incremental backups and archived redo logs to a point in time right before the logical corruption occurred (for example, dropping several large tables or updating hundreds of tables with the wrong date). Therefore, point-in-time recovery (PITR) is a good solution for a PDB tablespace or the entire PDB. As you might expect, all other PDBs and the CDB are unaffected when performing PITR for a PDB. As with a non-CDB PITR, when you perform an incomplete recovery, you have to open the PDB with RESETLOGS. For a tablespace within the PDB, the PDB remains open for the duration of the tablespace PITR.

PDB Tablespace Logging

The logging_clause of the CREATE PLUGGABLE DATABASE statement specifies the logging attribute of the PDB. The logging attribute controls whether certain DML operations are logged in the redo log file (LOGGING) or not (NOLOGGING).
Note: This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).
You can use this clause to specify one of the following attributes:
  1. LOGGING, the default, indicates that any future tablespaces created within the PDB will be created with the LOGGING attribute by default.
  2. NOLOGGING indicates that any future tablespaces created within the PDB will be created with the NOLOGGING attribute by default.
You can override the default logging attribute by specifying either LOGGING or NOLOGGING at the schema object level.
For example, in a CREATE TABLE statement. The specified attribute is used to establish the logging attribute of tablespaces created within the PDB if the logging_clause is not specified in the CREATE TABLESPACE statement.

The following six diagrams describe the steps for incomplete recovery.
Step 1: Shut down the database and perform a whole closed database backup
1) Step1: Shut down the database and perform a whole closed database backup (including control files and redo logs) before you start the incomplete recovery. There are two reasons for this: 1) If your incomplete recovery fails (for example, you recover past the desired point of recovery), you will not be able to apply the redo logs and control files that you have already used for the next recovery. You will need to use the backup of these files. 2) It saves you time if your first attempt fails. If this happens, you can restore the datafiles from the new backup, rather than from a previous backup that needs to have archived logs applied. If you cannot perform a whole backup for some reason, you should at least archive the current redo logs (by issuing the ALTER SYSTEM ARCHIVE LOG CURRENT statement) and back up the control file.

Step 2: Restore all datafiles (only datafiles)
2) Step 2: Restore all datafiles (only datafiles). In order to take your database back in time, you need to restore all the datafiles (not from the backup made in the previous step). In an Oracle database, transaction activities can only be rolled forward to the desired time, not back to that time. If you fail to restore all the datafiles, the database will not be synchronized and cannot be opened.

Step 3: Open the database in mount mode
3) Step 3: Open the database in mount mode.

Step 4: Recover the datafiles to the specified time, redo log file, or change number.
4) Step 4: Recover the datafiles to the specified time, redo log file, or change number. In this example, the datafiles are only recovered until ARC_70.ora is applied and then the recovery process is terminated.

Step 5: Open the database with the RESETLOGS option
5) Step 5: Open the database with the RESETLOGS option. After incomplete recovery, the datafiles have different sequence numbers with the control files and online redo log files. You have to open the database with the RESETLOGS option to synchronize all datafiles. During this process, if a redo log file is missing, it is automatically recreated.

Perform a whole database backup after successful incomplete recovery
6) Step 6: Perform a whole database backup after successful incomplete recovery. After you open the database with the RESETLOGS option, all the files are synchronized to the same number. Notice that the RESETLOGS option reset all the files to 1. It is important to perform a whole database backup after a successful incomplete recovery because this is the only valid backup if a recovery is required at a later time.


The next lesson shows how to monitor the recovery progress using the alert log.

Incomplete Database Recovery Steps

Click the link below to review your understanding of incomplete database recovery steps.
Incomplete Database Recovery Steps

Incomplete Database Recovery Steps

The correct order is as follows:
  1. Perform a whole closed database backup.
  2. Restore all datafiles.
  3. Mount the database.
  4. Recover the datafiles to a specified point-in-time before the failure.
  5. Open the database with RESETLOGS command.
  6. Shut down and perform a whole database backup.

Background

Incomplete database recovery consists of two steps: 1) restore and 2) recovery. The restore step will re-create data files, and the recover step will apply redo up to the specified point in time. The restore process can be initiated from RMAN in several ways:
  1. restore database until
  2. restore tablespace until
  3. flashback database
For the majority of incomplete database recovery circumstances, you’ll use the restore database until command to instruct RMAN to retrieve data files from the RMAN backup files. This type of incomplete database recovery is the main focus of this chapter. The until portion of the restore database command instructs RMAN to retrieve data files from a point in the past based on one of the following methods:
  1. Time
  2. Change (sometimes called system change number [SCN])
  3. Log sequence number
  4. Restore point

The RMAN restore database until command will retrieve all data files from the most recent backup set or image copy. RMAN will automatically determine from the until clause which backup set contains the required data files. If you omit the until clause of the restore database command, RMAN will retrieve data files from the latest available backup set or image copy. In some situations, that may be the behavior you desire. We recommend you use the until clause to ensure that RMAN restores from the correct backup set or image copy. When you issue the restore database until command, RMAN will determine how to extract the data files from any of the following:
  1. Full database backup
  2. Incremental level 0 backup
  3. Image copy backup generated by the backup as copy command
You cannot perform an incomplete database recovery on a subset of your database's online data files. When performing incomplete database recovery, all of the checkpoint SCNs for all online data files must be synchronized before you can open your database with the alter database open resetlogs command. You can view the data file header SCNs and the status of each data file via this SQL query:

select
file#
,status
,fuzzy
,error
,checkpoint_change#,
to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss') as checkpoint_time
from v$datafile_header;

The FUZZY column of V$DATAFILE_HEADER refers to a data file that contains one or more blocks that have an SCN greater than or equal to the checkpoint SCN in the data file header (meaning there have been writes to the data file since the last checkpoint). During regular database operating conditions, the FUZZY column will normally be YES. However, after you restore a data file, if a data file has a FUZZY value of YES, you cannot open the database normally yet; in this situation more redo needs to be applied before the database can be opened. Table 12-1 summarizes these scenarios.

SEMrush Software