Example 2: Guaranteed Restore Point - Flashback Logging Enabled - Oracle

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 10.2.0.2.0 - 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 10.2.0.2.0 - 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;

COUNT(*)

----------

50

SQL> select * from testuser.logging;

TIMESTAMP LOGINFO

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

27-MAY-08 Begin procedure

27-MAY-08 End procedure