Restore to the Point

Use named points in time to roll your database back by using flashback technology

By Arup Nanda

November/December 2006

Jane, the lead DBA at Acme Bank, has three visitors today. The first, Paul, is the head of Quality Assurance. Paul's team creates a variety of test scenarios for the applications. For each scenario, they put together the test data, and after they run each test, they need to modify the data to bring back the pretest values.

The second visitor is Tom, the operations manager. Tom is responsible for processing batch financial transaction files from each branch of the bank. If the file from a branch produces an error, the entire process is aborted and must be started from the beginning.

The third visitor is Harry, a business manager of an application developed by a third-party vendor. Harry gets updates from the vendor to modify the database structure, modify data, and so on, as part of the application upgrade process. Most upgrades go smoothly; however, when an upgrade fails, things get messy. Harry's team spends considerable time devising ways to undo failed changes.

Paul, Tom, and Harry are asking Jane to help make their processes more efficient. Jane assures them that she has a solution. Fortunately, Acme is using Oracle Database 10g Release 2, and it is possible to "rewind" the database to a named point in time.

Jane sits her visitors down and reminds them all of how it's possible to turn back the hands of time and reinstate the database to a certain point in time using a simple command: flashback database. In Oracle Database 10g Release 2, Jane says, the functionality is now enhanced significantly by the ability to name a specific point in time, called a restore point . Using this, Paul, Tom, or Harry (or a DBA acting on their behalf) can mark and flash back the database to a logical point in time.

Jane starts a demonstration on a test database. She notes that the database must be running in archivelog mode and with flashback logging enabled. She first shuts down the database and then brings it up in mounted mode.

shutdown immediate;
startup mount;

Then she converts the database to run in archivelog mode.

alter database archivelog;

To enable flashback, Jane first configures two parameters in the database.

alter system set db_recovery_file_dest_size = 2G;
alter system set db_recovery_file_dest = '/u02/flashbackarea/acmeprd';

In flashback mode, the database creates flashback log files, which record the old images of the data after a change is made. These files are kept in the location specified by the db_recovery_file_dest parameter, up to the size specified by the db_recovery_file_dest_size parameter, which in this case is set to 2GB.

Jane then enables flashback logging:

alter database flashback on;

She opens the database:

alter database open;

She checks the status of the archive log mode and flashback:

select flashback_on, log_mode
from v$database;
FLASHBACK_ON  LOG_MODE
------------  ----------------
YES           ARCHIVELOG

This confirms that the database is indeed in flashback mode.

Jane proceeds to demonstrate how to use restore points, starting with an example of how Paul's QA team can benefit from this technique. Jane creates a restore point named qa_gold.

create restore point qa_gold;

This command, Jane reminds them, is new in Oracle Database 10g Release 2. It creates a named restore point, which is an alias to the system change number (SCN) of the database at that time.

Jane runs one of the QA team's tests, altering the test data. To flash back the database to the restore point she created, Jane shuts down the database, restarts it in mounted mode, and issues the flashback database command.

shutdown immediate;
startup mount;
flashback database to restore point qa_gold;

That's it; the database is now "rewound" to the restore point named qa_gold. There was no need for Jane to back up the database and perform a point-in-time recovery. Paul couldn't be happier.

For Tom, Jane demonstrates a slightly different approach. Since Tom runs the batch process on one file at a time, Jane suggests creating a restore point after processing each file with some predetermined naming convention, for example, after_branch_ n , where n is the BRANCH_ID.

To keep track of the files being processed, Tom has a table—PROC—with only one column—BRANCH_ID, which stores the id of the branch whose file has been processed. Jane runs through the following process as an example of a typical batch run using restore points:

1. She creates a restore point named start_batch to mark the start of the process.

create restore point start_batch;

2. She updates the PROC table to specify the branch being processed.

update proc set branch_id = 1;
commit;

3. She processes the file from branch 1.

4. After the branch 1 file is processed, she creates a new restore point.

create restore point after_branch_1;

The process is repeated until the files from all branches are processed.

Jane demonstrates the restore process to use if a file from branch 23 has an error. When the file from branch 23 is picked up for processing, the BRANCH_ID value in the PROC table will be 23.

SQL> select branch_id from proc;
BRANCH_ID
---------
       23

If the processing fails for the file from branch 23, Jane rolls back the changes to the after_branch_22 restore point.

shutdown immediate;
startup mount;
flashback database to restore point
after_branch_22;
alter database open;

To confirm that the flashback succeeded, she checks the PROC table again.

SQL> select branch_id from proc;
BRANCH_ID
---------
       22

The value of the column is 22, for the branch file one prior to the creation of the restore point. All changes made to the database after the creation of this restore point are undone.

Sometimes, the file from a branch fails but that is not known until much later. For instance, the branch 23 file processing may have failed, but that is not discovered until the processing of branch 29. Jane assures Tom that whether he's processing the branch 23 file, the branch 29 file, or any file in between, he can easily roll back to the after_branch_22 restore point.

In response to Harry's application update issue, Jane suggests a solution very similar to Paul's. Just prior to the database update, Harry or a DBA would create a restore point named pre_change. If the application update is not successful, all that Harry or the DBA needs to do is to flash back the database to that restore point using the flashback commands she demonstrated earlier.

Paul, Tom, and Harry leave Jane's office and go back to their respective departments to test their restore-point solutions.

A few hours later, Tom returns to Jane's office with an error message. When he tried to flash back to a restore point, he got this error:

ORA-38729: Not enough flashback
database log data to do FLASHBACK.

As the error shows, there are insufficient flashback logs to flash back the database to the restore point. Jane's explanation is simple—the flashback logs are kept up to the time specified by the db_flashback_retention_target database parameter.

Older logs are not automatically deleted; however, the maximum size of the flash recovery area is determined by the db_recovery_file_dest_size database parameter, which is 2GB in this case. When Tom's flash recovery area fills up to 2GB, Oracle Database must remove some logs older than 1,440 seconds to make room for the new ones. So, when Tom wanted to perform flashback, the logs needed for the selected restore point were aged out, and that caused the error.

Tom asks if— because the flashback logs will age out—there is any guarantee that he would be able to flash back to a particular restore point.

Jane assures him that he can guarantee that the logs he needs for a particular restore point are available by creating a guaranteed restore point. Jane shows Tom how to create a guaranteed restore point:

create restore point after_branch_22
guarantee flashback database;

This ensures that it is definitely possible to flash back the database to the after_branch_22 restore point. Oracle Database does not age out the old flashback logs required for this restore point.

After successfully addressing Tom's question, Jane calls in her DBAs to make sure they understand how to administer the restore points. First she shows them the query to find out how many restore points have been created:

select * from v$restore_point
order by scn;

The output is shown in Listing 1. Noting the output, Jane describes the columns. NAME is the name of the restore point; SCN and TIME are the SCN and the time stamp (in extended format) when the restore point was created, respectively; GUARANTEE_FLASHBACK_DATABASE (shown partially as "GUA") indicates whether it's a guaranteed restore point; STORAGE_SIZE indicates the storage used by this restore point (it is non-zero only in case of guaranteed restore point). Finally, DATABASE_INCARNATION# indicates the incarnation of the database when this restore point was created. If the database was flashed back and then opened with resetlogs, it creates a new incarnation of the database.

Code Listing 1: Output of V$RESTORE_POINT

SCN      DATABASE_INCARNATION# GUA  STORAGE_SIZE TIME                            NAME
-------- --------------------- ---- ------------ ------------------------------- ------------
14390197 6                     NO   0            01-AUG-06 01.12.27.000000000 PM QA_GOLD
24390219 6                     NO   0            01-AUG-06 02.13.16.000000000 PM AFTER_BRANCH_1
34390232 6                     NO   0            01-AUG-06 03.13.34.000000000 PM AFTER_BRANCH_2
44390243 6                     NO   0            01-AUG-06 04.13.49.000000000 PM AFTER_BRANCH_3
54394187 7                     YES  3981312      02-AUG-06 05.35.19.000000000 AM AFTER_BRANCH_4

When restore points are not needed anymore, Jane continues, you DBAs can delete them. She issues the following command to delete the after_branch_1 restore point:

drop restore point after_branch_1;

Using restore points, DBAs can mark a location in time, which can then be used to rewind and fast-forward the database to a specific location. Although restore points are very helpful in recovering the database quickly from user errors, they also have other excellent uses. Table 1 summarizes various scenarios and how to resolve them by using restore points.

Table 1: Opportunities for restore points

Next Steps

READ more about restore points.