With the flashback database feature, the hours-long restore-recover process can be completed in a few minutes. You can roll back and open your database to a specific time. It is better to use Flashback database feature to restore user errors (truncate table v.s.) rather than physical problems.<\/p>\n
The Flashback database property is turned off by default. It is necessary to set the FLASHBACK_ON property to turn this feature on.<\/p>\n
It is also necessary to specify how far you will be back by using the DB_FLASHBACK_RETENTION_TARGET parameter.<\/p>\n
The default value of the DB_FLASHBACK_RETENTION_TARGET parameter is 1440 minutes (one day). You should first activate Flashback Recovery Area for your database.<\/p>\n
You need to set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters for the Flashback Recovery Area.<\/p>\n
<\/h3>\n
Flashback status of your database:<\/h3>\n
SQL>select flashback_on from v$database;<\/pre>\n
<\/p>\n
There are three different states for Flashback_on. \nyes:<\/strong> The flashback database feature is running. \nno:<\/strong> Flashback database feature is turned off. \nrestore point only:<\/strong> Only active for flashback guaranteed restore points.<\/p>\n
* Another important point is that the database should be running in archivelog mode.<\/p>\n
<\/p>\n
With the command below, you can see the archive mode of your database.<\/p>\n
Steps to activate the Flashback Database feature:<\/h3>\n
For 10g:<\/h3>\n
SQL> SHUTDOWN IMMEDIATE;\r\nSQL> STARTUP MOUNT;\r\nSQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH; # 3 g\u00fcn\r\nSQL> ALTER DATABASE FLASHBACK ON;\r\nSQL> ALTER DATABASE OPEN;<\/pre>\n
<\/p>\n
For 11g:<\/h3>\n
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH; # 3 g\u00fcn\r\nSQL> ALTER DATABASE FLASHBACK ON;<\/pre>\n
<\/p>\n
We can do the Flashback database process in 3 ways.<\/p>\n
1) SCN \n2) RESTORE POINT \n3) DATE<\/p>\n
You can access the current SCN information of your database with the help of the following script;<\/p>\n
SQL>select current_scn from v$database;<\/pre>\n
<\/p>\n
Create Restore Point:<\/h3>\n
SQL > CREATE RESTORE POINT MY_RP;<\/pre>\n
<\/p>\n
To access the created restore point information:<\/h3>\n
SQL >\u00a0 SELECT * FROM V$RESTORE_POINT<\/pre>\n
<\/p>\n
Example :<\/h3>\n
The following steps can be used to return our database back to a specific SCN.<\/p>\n
SQL> shutdown immediate\r\nSQL> startup mount\r\nSQL> flashback database to scn 2955736;\r\nSQL> alter database open resetlogs;<\/pre>\n
<\/p>\n
Flashback database commands should be run when your database is in mount mode.<\/p>\n
After the flashback database process is finished, you must open the database with the command<\/p>\n
alter database open resetlogs;<\/pre>\n
<\/p>\n
You can find the space required for the Flashback Database logs by the following query:<\/p>\n
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;<\/pre>\n
<\/p>\n
By default, flashback logs are created in all permanent tablespaces.<\/p>\n
If you want to close:<\/p>\n
SQL> ALTER TABLESPACE tbs_1 FLASHBACK OFF;<\/pre>\n
<\/p>\n
If you want to open again:<\/p>\n
SQL> ALTER TABLESPACE tbs_1 FLASHBACK ON;<\/pre>\n\n