Lesson 6	Performing time-based recovery
Objective	Demonstrate how to handle time-based or "until time" recovery.

Performing Time-Based Recovery Scenario in Oracle

Suppose the current time is 12:10 PM on 18-JAN-2000. A developer tells you that he accidentally dropped the CUSTOMERS table around 12:01 PM and he cannot afford to lose this table. As a DBA, you know that there are only a few database transactions involved because most staff members are currently at lunch. What should you do to help your developer get the table back?

Solution:

You immediately decide that time-based recovery should be used and you plan to recover the database up to 12:00 PM. The following section walks you though the steps to perform a time-based recovery. Since you are already familiar with the procedures of backing up and restoring a database, these steps are omitted. You may also preview all the steps.

Performing a time-based Recovery

If the database is open, shut it down with IMMEDIATE or the NORMAL option.
Perform a whole closed database backup.
Restore all the datafiles from the most recent backup (not the one you made in Step 2).
Open the database in mount mode.
If you need to restore the archived logs, restore them to the LOG_ARCHIVE_DEST location. If there is not enough disk space available at that location, you can use another location if you inform Oracle by issuing the
```
ALTER SYSTEM ARCHIVE LOG 
START TO <LOCATION>;
```
statement.
Recover the database by issuing the RECOVER DATABASE UNTIL TIME 'YYYY-MM-DD:HH:MI;SS'; statement and enter AUTO when you are prompted with the suggestion of which log you need to apply.
To synchronize datafiles with redo logs and control file, open the database using the RESETLOGS option by issuing the
```
ALTER DATABASE OPEN RESETLOGS;
```
statement.
To make sure the database has been recovered to the state you expected, you should check whether you got all the objects back.
Perform a whole closed database backup and notify the user that any data entered after the recovery time should be manually re-entered.

How time-based Works

Time-based restore and recovery is commonly used when you know approximately the date and time to which you want to recover your database. For example, you may know approximately the time you want to stop the recovery process but not a particular SCN.

Log sequence–based and cancel-based recovery work well in situations where you have missing or damaged log files. In such scenarios, you can recover only up to your last good archived redo log file.
SCN-based recovery works well if you can pinpoint the SCN at which you want to stop the recovery process. You can retrieve SCN information from views such as V$LOG and V$LOG_HISTORY.
You can also use tools such as LogMiner to retrieve the SCN of a particular SQL statement.
Restore point recoveries work only if you have established restore points. In these situations, you restore and recover up to the SCN associated with the specified restore point.
Tablespace point-in-time recovery is used in situations where you can restore and recover just a few tablespaces.
You can use RMAN to automate many of the tasks associated with this type of incomplete recovery.
Table point-in-time recovery may be appropriate if you accidentally dropped a table or erroneously deleted data.
Flashing back your database works only if you have enabled the flashback database feature. DBAs often use this feature in environments where the database needs to be rolled back to a baseline (like a test environment), or also in production environments to capture the state of the database right before an application upgrade takes place. This allows you to restore the database back to a point in time in the event the upgrade doesn’t go as planned.

SQL and COMMIT Transparency

The Oracle Database distributed database architecture provides query, update, and transaction transparency. For example, standard SQL statements such as SELECT, INSERT, UPDATE, and DELETE work just as they do in a non-distributed database environment. Additionally, applications control transactions using the standard SQL statements COMMIT, SAVEPOINT, and ROLLBACK. There is no requirement for complex programming or other special operations to provide distributed transaction control.

The statements in a single transaction can reference any number of local or remote tables.
The database guarantees that all nodes involved in a distributed transaction take the same action: they either all commit or all roll back the transaction.
If a network or system failure occurs during the commit of a distributed transaction, the transaction is automatically and transparently resolved globally. Specifically, when the network or system is restored, the nodes either all commit or all roll back the transaction.

Internal to the database, each committed transaction has an associated system change number (SCN) to uniquely identify the changes made by the statements within that transaction. In a distributed database, the SCNs of communicating nodes are coordinated when:

A connection is established using the path described by one or more database links.
A distributed SQL statement is executed.
A distributed transaction is committed.

Among other benefits, the coordination of SCNs among the nodes of a distributed database system allows global distributed read-consistency at both the statement and transaction level. If necessary, global distributed time-based recovery can also be completed.

Guaranteeing Global Database Consistency

Each committed transaction has an associated system change number (SCN) to uniquely identify the changes made by the SQL statements within that transaction. The SCN functions as an internal timestamp that uniquely identifies a committed version of the database. In a distributed system, the SCNs of communicating nodes are coordinated when all of the following actions occur:

A connection occurs using the path described by one or more database links
A distributed SQL statement executes
A distributed transaction commits

Among other benefits, the coordination of SCNs among the nodes of a distributed system ensures global read-consistency at both the statement and transaction level. If necessary, global time-based recovery can also be completed. During the prepare phase, the database determines the highest SCN at all nodes involved in the transaction. The transaction then commits with the high SCN at the commit point site. The commit SCN is then sent to all prepared nodes with the commit decision.

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.
The next lesson shows how to perform a cancel-based recovery.