Remember the concept of savepoints in SQL? In a transaction, you can create a savepoint, make some modifications, create another savepoint, and so on. If the changes are not what you expected, all you have to do is roll them back to a specific savepoint.
Now pan over to a new functionality introduced in Oracle Database 10g, Flashback Database, which allows you to rewind the database to a previous point in time. Wouldn't it be nice to have functionality similar to savepoint in this situation—that is, to be able to rewind to a specific named point, not just a point in time?
In Oracle Database 10g Release 2, you can do that using a new functionality called restore points. Here's how it works. Suppose you have a long month-end processing involving several batch programs you have to run sequentially. Here is the sequence of events:
1. Create a restore point rp1
2. Run batch job 1
3. Create a restore point rp2
4. Run batch job 2
and so on. The batch job 2 fails in the middle of execution, and you need to take the database to a consistent state. You don't have to take it all the way to the beginning of the run. Because the restore point rp2 was created before the execution of the batch job, you can simply flashback the database to that restore point.
You create a restore point with
create restore point before_monthend_200503;
Restore point BEFORE_MONTHEND_200503 is now created based on the current database time and SCN. If you want to ensure that the database can be flashed back to a particular restore point, you can specify a guarantee by creating guaranteed restore points as shown below:
create restore point before_monthend_200503
guarantee flashback database;
You can confirm the existence of this restore point by SELECTing from a dynamic performance view V$RESTORE_POINT:
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
Later when you want to flashback the database to that restore point, you could simply issue:
flashback database to restore point before_monthend_200503;
If you examine the alert log, it will show a line similar to:
Media Recovery Applied UNTIL CHANGE 1429814
Restore points—especially guaranteed restore points—are quite useful in many database-related tasks. A good example is QA databases, where you may want to establish a restore point, run some tests, and flashback to the restore point to make the database look as if nothing happened. Then you can perform another round of testing and again restore it to the restore point.
Peek into the Flash Recovery Area
You may have configured Flash Recovery Area to back up different types of files. But how do you know what types of backups are available there?
A new view, V$FLASH_RECOVERY_AREA_USAGE, shows what's available in the flashback area.
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG .02 .02 1
BACKUPPIECE 68.98 1.02 10
IMAGECOPY 0 0 0
FLASHBACKLOG .95 0 3
Using this view you can immediately see what kind of files are available in the Flash Recovery Area. It only shows a percentage however, so how do you determine actual values? Simply query the view $RECOVERY_FILE_DEST.
SQL> select * from V$RECOVERY_FILE_DEST;
NAME
----------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/home/oracle
2147483648 1502122496 22201856 14
This query shows that the total size of the recovery area is 16384000. Flashback logs occupy 0.95% of the column SPACE_LIMIT as shown in the previous query, so you can calculate the actual size of the space occupied. It also shows you how much space can be reclaimed from the different types of backups in the Flash Recovery Area. For instance, you can reclaim 1.02% of the space occupied by backup pieces, perhaps due to obsolete backups. Using this view you can make intelligent predictions about Flash Recovery Area usage and sizing.
Source: Oracle 10g R2 Top Features for DBAs
No comments:
Post a Comment