Oracle Flashback Database

Hi,

I will explain what is the Flashback database in Oracle in this article.

Read previous article before this.

Oracle Flashback Architecture

Flashback Database

You can return your Oracle Database to a past time or system change number (SCN) as long as you want if the Retention is enough with Flashback database. Of course this operation is very critical operation because it is at the entire Database level, please do not decide without thinking 3 times.

Some limitations exist for  Flashback database like following.

• If You have an old backup, then you can restore datafiles dropped after flashback database via RMAN Advised failure.
• If Undo datafile is shrinked, we cannot use flashback database.
• If the control file is restored from the backup, we can return with flashback to the point where the control file was restored.

Flashback database is usually used after Application Deployment in test databases, such as a test or any task is failed, and you want to return database or SCN before this task, then you can use flashback database features.

What is the SCN ?

SCN (System Change Number): One of the most critical things in terms of our database is the SCN because it is a like Oracle’s clock and marks a consistent point in time in the database. Each time a transaction is run in an Oracle database, the Oracle Database will increment the SCN and give the related transaction a SCN number, and the new SCN of the database is the SCN of the last committed transaction.

To find out the current scn value in database, execute the following command

Select current_scn from v$database;

Execute the following command to find which date this SCN corresponds to.

Select scn_to_timestamp(481572) from dual;

Execute the following command to find which SCN Specific date corresponds to.

Select timestamp_to_scn(to_timestamp('01-03-19 04.26.36','dd.mm.yy hh24:mi:ss'))

SCN is unique for every database. You can’t access very old SCN’s information. Because it disappears after a while.

Now let’s go to make an example to understand it,  a user called DEVECI and his TEST table will be dropped in the database. We will return database before this DROP operation, thus we have recovered table and prevented data loss.

Firstly, Find out current SCN before this operation.

Select current_scn from v$database;

382240

Then drop table and also user.

DROP TABLE TEST CASCADE CONSTRAINTS ;

Drop user DEVECI;

Now DEVECI schema has dropped and TEST table is not available, you can  use the following steps to return the database to an SCN before drop with flashback.

Shutdown immediate;

Startup mount;

Flashback database to scn 382240;

Alter database open resetlogs;

At the end of this operation we can see that the lost data is available back.

You can  use the following command to return the database to Specific Timestamy before drop with flashback.

Flashback database to timestamp to_timestamp('29-07-16 04.26.36','dd-mm-yy hh24.mi.ss');

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/