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.
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
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
SQL> alter database flashback on;
SQL> alter database open;
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
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');
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
blog comments powered by Disqus