Loading Consent Dialog

Incomplete Recovery   «Prev  Next»

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

Performing Time-Based Recovery

Scenario

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 simulation 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 in this simulation. You may also preview all the steps.

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.
  1. The statements in a single transaction can reference any number of local or remote tables.
  2. 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.
  3. 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:
  1. A connection is established using the path described by one or more database links.
  2. A distributed SQL statement is executed.
  3. 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:
  1. A connection occurs using the path described by one or more database links
  2. A distributed SQL statement executes
  3. 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
The next lesson shows how to perform a cancel-based recovery.

Oracle Backup Recovery