Closure
Closure
Home » Databases » Oracle Database » How do we return an Oracle database to the previous state of a restore point (Flashback Database to guaranteed restore point)

How do we return an Oracle database to the previous state of a restore point (Flashback Database to guaranteed restore point)

How do we return an Oracle database to the previous state of a restore point (Flashback Database to guaranteed restore point)
, , , Leave a comment
How do we return an Oracle database to the previous state of a restore point (Flashback Database to guaranteed restore point)
Latest posts by Stratos Matzouranis (see all)

In the article we will analyze how we can make use of the function flashback database in guaranteed restore point in its database Oracle.

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

But why would we want to flashback a database?

With its operation flashback database we can keep one guarantee restore point before performing a major upgrade to the base. This action takes effect in case something goes wrong and is needed rollback not need to be done restore the entire base.

How do we create a guarantee restore point?

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

At this point we have to check that the database works in archived log mode, that is, that the logs are kept as archived.

select log_mode,flashback_on from v$database;

Enabling flashback mode is optional. If it is not activated, however, to restore the restore point, the base should be mounted and not open.

To activate the flashback
alter database flashback on;
To create the restore point
create restore point ONOMA guarantee flashback database;
To see where it is and how much space is still free in the flash recovery area
select name,space_used/1024/1024/1024 from v$recovery_file_dest;

How to restore the base to the restore point

We should look at the restore points we have and find its name.

select name,scn,time from v$restore_point;
In case of single instance
shutdown immediate;
startup mount;
flashback database to restore point ONOMA ;                 
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 restore point ONOMA;  
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

After completing the restore we can delete the restore point:

drop restore point ONOMA;

In case of Data Guard (with or without Fast Start Failover)

In case we have infrastructure Data Guard we should follow the below steps.

We check on all nodes that the restore point has been created, when creating it on the primary it also creates it on all secondary nodes:

select name,space_used/1024/1024/1024 from v$recovery_file_dest;
select name,scn,time from v$restore_point;

In case we have it enabled fast_start failover we should disable it:

dgmgrl /
disable fast_start failover force;

In the primary node we run the following to do flashback at the restore point while the database is in mount state:

sqlplus / as sysdba
shutdown immediate;
startup mount;
flashback database to restore point NAME;
alter database open resetlogs;

After completing the restore we can delete the restore point:

drop restore point NAME;

Finally we activate it again fast_start failover if we had it enabled before the actions:

dgmgrl /
enable fast_start failover;

Caution!!!

In case the standby database is open to read only mode a manual restart should be done:

sqlplus / as sysdba
shutdown immediate;
startup mount;

And after synchronizing in a second time, open again in read only mode:

alter database open read only;

In the next article we will see how we can flashback a database at a specific point in time without using a guarantee restore point.

Sources:

Share it

Leave a reply