Implementing and Using Oracle`s Restore Point Functionality

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.

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;

/


{mospagebreak title=Example 1: Normal Restore Point – Flashback Logging Disabled}

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;


COUNT(*)

———-

50


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.


{mospagebreak title=Example 2: Guaranteed Restore Point – Flashback Logging Enabled}

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

{mospagebreak title=Reset and Reopen}

Now that we’ve executed the first test of our new batch process, we need to "reset" the data back to how it appeared before running the test. We’ll shut down and remount the database, then use the ”flashback database" command to "reset" the database to our specified restore point:


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> flashback database to restore point before_test;


Flashback complete.


We’ve now "reset" the database back to the restore point. We need to re-open the database, but because we’re essentially doing a point-in-time recovery, we need to use the "resetlogs" command to recreate the redo logs and create a new instantiation of the database:


SQL> alter database open resetlogs;


Database altered.


We can verify that we once again see 200 rows in the "testuser.test" table, that our "testuser.logging" table is empty, and that we’re ready to test another iteration of our batch processing algorithm:


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


COUNT(*)

———-

200


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


COUNT(*)

———-

0


Once we’ve completed our several iterations of testing, we’re ready to remove the guaranteed restore point and free up whatever space the database used to manage the restore point. We accomplish this with the following:


SQL> drop restore point before_test;


Restore point dropped.


Conclusion

Restore points provide a means for a DBA to label a particular point-in-time in the timeline of the database with a user-defined name, without digging into the details of SCNs or timestamps. The guaranteed restore point takes this a step further, providing the ability to guarantee a flashback database is possible until the DBA decides it is no longer needed, along with the ability to perform a flashback database without enabling flashback logging (with a reduced level of functionality). In conclusion, restore points make an invaluable addition to the DBA’s arsenal of tools, useful any time a point-in-time recovery is needed.

NOTE: All code tested and known to be working on Oracle 10.2.0.2, running on 32-bit SLES9 (linux).

Google+ Comments

Google+ Comments