Home arrow Oracle arrow Page 4 - Implementing and Using Oracle`s Restore Point Functionality

Reset and Reopen - Oracle

With the release of Oracle 10gR2, Oracle introduced the concept of Restore Points, presenting to DBAs a more user-friendly approach to implementing point-in-time recovery. In this article, you'll learn what Restore Points are, their many advantages, and how to use them.

  1. Implementing and Using Oracle`s Restore Point Functionality
  2. Example 1: Normal Restore Point - Flashback Logging Disabled
  3. Example 2: Guaranteed Restore Point - Flashback Logging Enabled
  4. Reset and Reopen
By: Ray Terrill
Rating: starstarstarstarstar / 4
July 22, 2008

print this article



Now that we've executed the first test of our new batch process, we need to "reset" the data back to how it appeared before running the test. We'll shut down and remount the database, then use the ''flashback database" command to "reset" the database to our specified restore point:

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 159383552 bytes

Fixed Size 1259696 bytes

Variable Size 62916432 bytes

Database Buffers 92274688 bytes

Redo Buffers 2932736 bytes

Database mounted.

SQL> flashback database to restore point before_test;

Flashback complete.

We've now "reset" the database back to the restore point. We need to re-open the database, but because we're essentially doing a point-in-time recovery, we need to use the "resetlogs" command to recreate the redo logs and create a new instantiation of the database:

SQL> alter database open resetlogs;

Database altered.

We can verify that we once again see 200 rows in the "testuser.test" table, that our "testuser.logging" table is empty, and that we're ready to test another iteration of our batch processing algorithm:

SQL> select count(*) from testuser.test;




SQL> select count(*) from testuser.logging;




Once we've completed our several iterations of testing, we're ready to remove the guaranteed restore point and free up whatever space the database used to manage the restore point. We accomplish this with the following:

SQL> drop restore point before_test;

Restore point dropped.


Restore points provide a means for a DBA to label a particular point-in-time in the timeline of the database with a user-defined name, without digging into the details of SCNs or timestamps. The guaranteed restore point takes this a step further, providing the ability to guarantee a flashback database is possible until the DBA decides it is no longer needed, along with the ability to perform a flashback database without enabling flashback logging (with a reduced level of functionality). In conclusion, restore points make an invaluable addition to the DBA's arsenal of tools, useful any time a point-in-time recovery is needed.

NOTE: All code tested and known to be working on Oracle, running on 32-bit SLES9 (linux).

>>> More Oracle Articles          >>> More By Ray Terrill

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: