Database File time-based recovery
- Suppose you already performed a backup of the current database and restored all the datafiles.
Now you need to check the status of all these database files. Type
DIR at the prompt and press Enter.
As the DBA, you restore all datafiles to ensure that they are older than the point-in-time to which the recovery is to be made. These files will be rolled-forward since it is impossible to start with a file that is newer than the time we want to roll forward to. All the
datafiles MUST be older than the control file AND before the point-in-time we are interested in recovering to. When you look at the code, note that the control file is newer than the point-in-time we wish to roll forward to. The status of all these files seems correct. Now you are ready to start the recovery process. To access Server Manager, type
SVRMGR30 at the prompt and press Enter.
- To connect to the database as sysdba, type
CONNECT INTERNAL at the prompt and press Enter. Here the default password ORACLE is entered for you.
- If you try to start the database at this moment, your startup command will fail because the older datafiles are not synchronized with the newer control file. To try to see this effect, type
STARTUP at the prompt and press Enter.
- The database has failed to open. Notice here that only the first file needing recovery is reported. To start the time-based recovery and to recover files to 18 January 2000 at 12:00:00 hours, type
RECOVER DATABASE UNTIL TIME '2000-01-18:12:00:00'; at the prompt and press Enter.
- Now Oracle prompts you with the suggestion of which archived log file needs to be applied. To put the recovery process in the automatic mode, type
AUTO in the blank line and press Enter.
- The recovery process is quite time-consuming so this simulation only displays the beginning and the end of the process. Click anywhere in the simulation window to move to the next screen.
- Media recovery is automatically halted at the correct point in time. Now if you try to open the database, your command will fail because the redo logs are not synchronized with the datafiles. To observe this effect, type
ALTER DATABASE OPEN; at the prompt and press Enter.
- To synchronize the datafiles with redo logs, type
ALTER DATABASE OPEN RESET LOGS; at the prompt and press Enter.
- The correct command is issued and the recovery is completed. It cannot be resumed past this point-in-time! OPEN RESETLOGS has changed the current log sequence number to 1. If you wish to roll further forward, the entire process must be repeated. To check the current redo log sequence number, type
ARCHIVE LOG LIST at the prompt and press Enter.
- The current log sequence number is 1. You have now successfully recovered the database using time-based recovery. You need to remember to perform a whole database backup after the incomplete recovery is finished. This is the end of the Simulation. Click the Exit button.
What Is an Incomplete Recovery?
Incomplete recoveries (also known as point-in-time recoveries, or PITRs) impact the entire database; in other words, you cannot perform an incomplete recovery on just one part of the database because it would result in that part of the database having a different System Change Number (SCN, or point in time if you prefer) than the remainder of the
database. Incomplete recoveries come in four different flavors:
- Point-in-time recoveries
- SCN-based recoveries
- Change-based recoveries
- Point-in-time recoveries based on a restore point
Before we proceed, we want to review the important impact incomplete recovery has on the entire database. Oracle demands that a database be in a consistent state at startup, and if it is not consistent, Oracle will complain bitterly. To illustrate this point, consider an example in which a user who has his own tablespace has just mistakenly truncated a table
in that tablespace for which he has no backup. He calls a junior DBA in a panic and asks her to recover just that tablespace to the point in time before he issued the truncate operation.