Restoration point in Oracle 10 GB flash back database

Do you still remember the concept of saving points in SQL? In a transaction, you can create a save point, make some modifications, create another save point, and so on. If these changes are not what you want, all you need to do is roll them back to a specific save point.

Now we will introduce a new feature introduced in Oracle Database 10 GB-Flashback database. With this function, you can reverse the database back to the previous time point. In this case, is it good to have a function similar to the Save point (that is, to be able to return to a named point, not just a time point?

In Oracle Database 10g 2nd, you can use a new function named recovery point to perform this operation. The following is how it works. Suppose there is a long-term processing (involving multiple batch processing programs that must be run in order ).

The event sequence is as follows:

Create recovery point rp1
Run batch job 1
Create recovery point rp2
Run batch job 2
And so on. Batch Job 2 fails during execution. You need to restore the database to a consistent state. You do not have to restore it to the initial stage of running. Because recovery point rp2 was created before the execution of the batch processing job, you only need to flash the database back to the recovery point.

Use the following code to create a recovery point
Create restore point before_monthend_200503;

BEFORE_MONTHEND_200503 is created based on the current database time and SCN. To ensure that the database can be flashed back to a specific recovery point, you can specify guarantee by creating a guaranteed recovery point as follows:
Create restore point before_monthend_200503 guarantee flashback database;

You can run SELECT from the dynamic performance view V $ RESTORE_POINT to check whether the recovery point exists:
SQL> select * from v $ restore_point; SCN DATABASE_INCARNATION # GUA STORAGE_SIZE TIME NAME
---------------------------------------------------
1429811 1 YES 8192000 27-MAR-05 05.18.39.000000000 PM BEFORE_MONTHEND_200503

When you flash back to the recovery point, you only need to issue the following:
Flashback database to restore point before_monthend_200503;

If you check the alarm log, it displays a line similar to the following:
Media Recovery Applied until change 1429814

Recovery points (especially guaranteed recovery points) are useful in many database-related tasks. The QA database is a typical example. In this database, you may need to create a recovery point, run some tests, and flash back to the recovery point, so that the database looks as if nothing had happened. You can then perform another test and restore it to the recovery point again.