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

Example 2: Guaranteed Restore Point - Flashback Logging Enabled - 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



To illustrate the use of a guaranteed restore point, we'll use an example where we have a new batch process we're testing that will be moving to production in the near future, and would like to test multiple algorithms to determine the most efficient method for processing the data. We'll create a restore point before running the tests, enabling us to in effect "reset" the database back to the exact same point in time before each test.

This will make our testing much easier, ensuring that we're doing an apples-to-apples comparison of the algorithms while minimizing both the amount of work the DBA must do to restore the old data as well as the amount of time spent to do this restore.

Because we'd like to have the ability to utilize the full spectrum of functionality provided by restore points and flashback database, we'll enable flashback database now. Ensure the prerequisites have been met (both Restore Point Prerequisites and Flashback Database Prerequisites above), and use the following steps to enable flashback logging on your database:

oracle@dbatest:~> sqlplus / as sysdba

SQL*Plus: Release - Production on Tue May 27 10:18:42 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release - Production

With the Partitioning, OLAP and Data Mining options

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> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

After we've enabled flashback database, we'll create a second test procedure to "process" our data for this example. As in example 1, our procedure will make some basic manipulations on our data, which will represent a more complex business process. This time, however, our procedure will affect more than one table, allowing us to illustrate the effect of the more global flashback database (vs. the flashback table from example 1).

SQL> create or replace procedure testuser.process_data is

2 begin

3 insert into testuser.logging values (sysdate, 'Begin procedure');

4 delete from testuser.test where id > 50;

5 insert into testuser.logging values (sysdate, 'End procedure');

6 commit;

7 end;

8 /

Procedure created.

With our batch processing procedure compiled, we're ready to create a guaranteed restore point to "label" the current starting point for use later in flashing back the database:

SQL> create restore point before_test guarantee flashback database;

Restore point created.

As we've now tagged our current point in time, we'll execute the batch processing procedure, and verify that we have only 50 rows remaining in the testuser.test table, and that we've generated some logging in our testuser.logging table:

SQL> exec testuser.process_data(5);

PL/SQL procedure successfully completed.

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




SQL> select * from testuser.logging;


--------- ---------------

27-MAY-08 Begin procedure

27-MAY-08 End procedure

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