Oracle Flashback Database

Hi,

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

 

Read previous article before this.

 

 

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');

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *