How to Flashback an Oracle Database (Flashback Database)

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

In previous article we had analyzed how to do flashback database in guarantee restore point in an Oracle database. In this article we will see how it is done through technology flashback database without using restore point to let's go back in time the database.

What does flashback database mean?

The flashback database it belongs to the category of data protection and provides the ability to quickly return to the image that the base had at a point in time.

But why would we want to flashback a database?

With its operation flashback database we can restore the database in a very short time in case something goes wrong without having to restore.

How we activated the flashback database function

To begin with, we should define the size and the location we want to have flash recovery area. From its activation flashback database and then they will be created recovery files. As changes are made to the database, the size of these files will increase according to the size of the data that has changed.

To set the size in gigabytes:

alter system set DB_RECOVERY_FILE_DEST_SIZE ='megethos'G scope=both;

To define where the recovery files will be saved:

alter system set db_recovery_file_dest='path';

We can choose up to how many minutes back we want to be able to turn the base in time. The default is one day behind (1440 minutes):

alter system set DB_flashback_Retention_target = 'MINUTES' scope=both; 

At this point we have to check that the base works in archived log mode, that is, that the logs are kept as archived otherwise we will not be able to activate the flashback:

select log_mode,flashback_on from v$database;

To enable flashback:

alter database flashback on;

How to restore the base to a past point in time

In case of single instance

shutdown immediate;
startup mount;
flashback database to timestamp TO_TIMESTAMP( '2020-05-29 08:00:00','YYYY-MM-DD HH24:MI:SS');
alter database open resetlogs;

In case of RAC (Real Application Cluster)

In case we have multiple instances for database (RAC) the base should go down and only go up an instance her. Accordingly, after the flashback, the instance should be downloaded and the database with all its instances should be uploaded:

srvctl stop database -db db1 -stopoption immediate
srvctl start instance -db db1 -instance instance1 -startoption mount
flashback database to timestamp TO_TIMESTAMP( '2020-05-29 08:00:00','YYYY-MM-DD HH24:MI:SS');
alter database open resetlogs;
srvctl stop database -db db1 -stopoption immediate
srvctl start database -db db1

If we have many services they should be uploaded:

srvctl start service -d db1 -s service_tade

Sources:

• Using Flashback Database and Restore Points