- How to bulk detach / attach databases in SQL Server - February 2, 2024
- How to enforce password policy in an Oracle database - 4 January 2024
- How do we change the name of the user / schema in an Oracle database - 1 December 2023
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