Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
The Flashback database property is turned off by default. It is necessary to set the FLASHBACK_ON property to turn this feature on.
It is also necessary to specify how far you will be back by using the DB_FLASHBACK_RETENTION_TARGET parameter.
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.
You need to set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters for the Flashback Recovery Area.
Flashback status of your database:
1 | SQL>select flashback_on from v$database; |
There are three different states for Flashback_on.
yes: The flashback database feature is running.
no: Flashback database feature is turned off.
restore point only: Only active for flashback guaranteed restore points.
* Another important point is that the database should be running in archivelog mode.
With the command below, you can see the archive mode of your database.
1 2 3 4 5 6 7 | SQL> ARCHIVE LOG LIST Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 11 Next log sequence to archive 13 Current log sequence 13 |
Steps to activate the Flashback Database feature:
For 10g:
1 2 3 4 5 | SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH; # 3 gün SQL> ALTER DATABASE FLASHBACK ON; SQL> ALTER DATABASE OPEN; |
For 11g:
1 2 | SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH; # 3 gün SQL> ALTER DATABASE FLASHBACK ON; |
We can do the Flashback database process in 3 ways.
1) SCN
2) RESTORE POINT
3) DATE
You can access the current SCN information of your database with the help of the following script;
1 | SQL>select current_scn from v$database; |
Create Restore Point:
1 | SQL > CREATE RESTORE POINT MY_RP; |
To access the created restore point information:
1 | SQL > SELECT * FROM V$RESTORE_POINT |
Example :
The following steps can be used to return our database back to a specific SCN.
1 2 3 4 | SQL> shutdown immediate SQL> startup mount SQL> flashback database to scn 2955736; SQL> alter database open resetlogs; |
Flashback database commands should be run when your database is in mount mode.
After the flashback database process is finished, you must open the database with the command
1 | alter database open resetlogs; |
You can find the space required for the Flashback Database logs by the following query:
1 | SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG; |
By default, flashback logs are created in all permanent tablespaces.
If you want to close:
1 | SQL> ALTER TABLESPACE tbs_1 FLASHBACK OFF; |
If you want to open again:
1 | SQL> ALTER TABLESPACE tbs_1 FLASHBACK ON; |
