Implementing/Using Oracle's Restore Point Functionality

Restore points allow DBAs to create a label for a particular moment in the timeline of the database, eliminating the need to delve into the details of SCNs or to attempt to calculate the exact time transactions are occurring. In addition, a specific type of restore point known as a Guaranteed Restore Point allows the DBA to ensure beyond any doubt that the database is able to flash back to a specific point in time (or even to any point in time between the guaranteed restore point and the current time with some additional configuration).

As we will see, restore points provide an invaluable tool that can be used to assist the DBA in application testing, guarding against failed batch updates, and anywhere else you'd consider using a conventional point-in-time recovery.

Configuration

Restore Point Prerequisites:

• COMPATIBLE initialization parameter must be > 10.2

Flashback Database Prerequisites:

• Database must be configured for and running in ARCHIVELOG mode

• Database must have configured and be using a FLASH_RECOVERY_AREA

Because restore points work hand-in-hand with the Flashback features introduced in Oracle 10g, many of the same prerequisites (above) apply to the configuration of restore points. I'll assume that the above parameters have been configured correctly, and move on to implementing and using both normal and guaranteed restore points.

To Enable Flashback Logging or Not

Because the restore point functionality works hand-in-hand with flashback database, it is advisable to enable flashback logging before beginning with the restore point functionality. Without this logging enabled, you will be unable to use flashback database to quickly return your database to the time specified by the restore point, except in the case of a guaranteed restore point (where you will lose the ability to restore the database to a point-in-time between the specified guaranteed restore point and the current time). You must also shut down and mount the database to create the first guaranteed restore point without flashback database enabled. To truly enjoy the functionality of restore points, as well as flashback database, it's vital to enable flashback logging (shown in Example 2).

Normal vs. Guaranteed Restore Point

There are two types of restore points that can be created, with differing levels of functionality. The Normal Restore Point simply allows the DBA to provide a name for the current state of the database, used instead of an SCN or timestamp for point-in-time recovery. A Guaranteed Restore Point also provides the ability to name the current state of the database, but allows for greater security in that the restore point will forever be available unless explicitly removed by the DBA, and also can provide the ability to flash back the database without first enabling flashback logging (albeit at a reduced level of functionality, discussed above in the "To Enable Flashback Logging or Not" section).

Examples: Using Restore Points

(Configuration for Examples)

To create the test user and test table used in the following examples, use the following SQL/PL SQL to create the user and insert 200 rows of sample data (code below assumes you have a USERS tablespace):

--Create user and grant necessary permissions

create user testuser identified by testuser;

grant create table to testuser;

grant create session to testuser;

grant create procedure to testuser;

grant execute on dbms_lock to testuser;

alter user testuser quota 5m on users;

--Connect as the newly created testuser and create the test table and logging table

conn testuser/testuser;

create table testuser.test (id int, name varchar2(5)) tablespace users;

create table testuser.logging (timestamp date, loginfo varchar2(15)) tablespace users;

--Insert sample data into the test table

declare

i number(5);

begin

for i in 1..200 loop

insert into testuser.test values (i, 'a'||i);

end loop;

commit;

end;

/