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

Example 1: Normal Restore Point - Flashback Logging Disabled - 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



In this simple example, we'll use a normal restore point to flash back a table to our testing starting point after it has been modified during the testing of a new procedure. For simplicity's sake, the procedure will only delete rows from our table, but it could just as easily have been performing complicated business logic. Because we have flashback logging disabled, it isn't possible for us to perform a flashback database (we'll receive a 'ORA-38726: Flashback database logging is not on.' if we attempt it).

Because we'll be using flashback table, we need to enable row movement for the 'testuser.test' table involved, in effect giving Oracle permission to modify the ROWIDs for the rows in our table when it moved things around during the flashback table.

SQL> alter table testuser.test enable row movement;

Table altered.

Create the 'process_data' procedure, representative of a new batch process we're testing:

SQL> create or replace procedure testuser.process_data is

2 begin

3 delete from testuser.test where id > 50;

4 commit;

5 end;

6 /

Procedure created.

Next, we'll create a normal restore point 'before_test1' to mark our starting point for testing. This will allow us to return to this point in time to make changes and retest our new batch process without having to note the SCN or guess at the timestamp:

SQL> create restore point before_test1;

Restore point created.

Next, we'll execute the "process_data" procedure. After the procedure has completed, we see that the data has been processed correctly, and only 50 rows remain in the testuser.test table:

SQL> exec testuser.process_data;

PL/SQL procedure successfully completed.

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




We've completed testing for the first iteration of our batch process. To allow testing of the procedure to run against the testuser.test table once again, we'll use our restore point to flash back the table to our starting point, and begin the subsequent rounds of testing:

SQL> flashback table testuser.test to restore point before_test1;

Flashback complete.

Once we've completed the testing and our restore point is no longer needed, we're free to drop the restore point from the database. Note that this step isn't actually required, as the restore points will automatically age out of the database, but it's probably a good idea to keep things as tidy as possible. We'll drop the restore point to minimize confusion:

SQL> drop restore point before_test1;

Restore point dropped.

>>> 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: