Flashback in Oracle

We are going to have look at another part of the Oracle database. Flashback is going to be the topic of our discussion of today. Let’s start with its introduction after that with its example.

Being DBA we have to make us familiar with Flashback technology. It is considered as a good feature of Oracle Database.

What is flashback technology? 

Let’s have a technical definition of flashback technology.

Oracle Flashback Technology is a group of Oracle Database features that that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

We can perform multiple tasks with the help of flashback technology some of them are mention below:-

• It performs queries that return past data
• We can perform queries that return metadata that shows a detailed history of changes to the database.
• Recover tables or rows to a previous point in time is the most beautiful feature of flashback technology.
• We can see that it automatically track and archive transactional data changes.
• It rolls back a transaction and its dependent transactions while the database remains online.

Before use flashback technology we have to set some location and parameters so Oracle Database can give us better solutions.

RVWR Background Process

A new RVWR background process is started when Flashback Database is enabled. It is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.

Enabling Flashback Database:

Make sure the database is in archive mode and FLASHBACK_ON Yes

SQL>SELECT flashback_on, log_mode FROM v$database;

Configure the recovery area(if necessary) by setting the two parameters:

• db_recovery_file_dest
• db_recovery_file_dest_size

Open the database in MONT mode and turn on the flashback feture:

SQL> STARTUP MOUNT;
SQL>ALTER DATABASE ARCHIVELOG; [If not in archive mode]
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

Test Case

SQL> create table test_flashback(name varchar(30));
SQL> insert into test_flashback values('TEST BEFORE');
SQL> commit;
SQL> select to_char(sysdate,'dd-mm-yy hh24:mi:ss') from dual;
SQL> SELECT current_scn FROM v$database;
SQL> insert into test_flashback values('TEST AFTER');
SQL> commit;
SQL> select * from test_flashback;
SQL> drop table test_flashback;
SQL> shutdown immediate;
SQL> startup mount;
SQL> FLASHBACK DATABASE to timestamp to_timestamp('16-05-2018 13:59:45', 'DD-MM-YYYY HH24:MI:SS');
OR
SQL> FLASHBACK DATABASE TO SCN 3726625;

SELECT current_scn FROM v$database;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> SELECT * FROM test_flashback;

Another Example:

SQL> conn Oraclehelp/Oraclehelp
SQL> create table test_flash(id number);
SQL> commit;
SQL> drop table test_flash;
SQL> flashback table test_flash to before drop;
SQL> select * from test_flash;

Thanks for giving valuable time to add new gems to Oracle’s treasure.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp