How to roll back an Oracle Database using a restore point in a Data Guard environment

• 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 to roll back an Oracle Database using a restore point in a Data Guard environment - 28 April 2025
• How can we increase performance on Oracle GoldenGate Replicat target with parallelism? - 19 March 2025
• How to create users in databases that belong to a SQL Server Always On Availability Group - 10 February 2025

In previous article we had seen how we can roll back an Oracle Database in time through flashback database to guaranteed restore pointIn this article we will see what procedure we follow in case we have primary and standby databases in an environment. Data Guard.

The footsteps

The steps we need to follow are different if we have version 19c or newer, so we will follow different steps depending on the version and its state.

Steps with Oracle Database 19c or later in case the standby database is in mount state

Create a Guaranteed Restore Point

In primary school

First, we create the guarantee restore point on the primary:

sqlplus / as sysdba

create restore point rpoint guarantee flashback database;

exit;

We check that the Restore Point has been created

In Primary and Standby

We run the following query on all sides to make sure that the restore point has been created:

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

We check the open mode on the standby databases

To see what steps we will follow for the reset, we will have to make sure the standby mode is open, in these steps it should be in mount state otherwise we will have to follow the steps mentioned below in another category:

select open_mode from gv$database;

Disabling fast-start failover

If we need to restore the database and we have fast-start failover enabled in Data Guard, we must disable it (it doesn't matter where we connect from):

dgmgrl /

disable fast_start failover force;

exit;

Flashback to Guaranteed Restore Point

At this point we are ready to restore the databases to the restore point, but first stop the primary and return it to a mount state.

In primary school

We run the following on the primary to restore and open the database:

sqlplus / as sysdba

shutdown immediate
startup mount
flashback database to restore point rpoint;
alter database open resetlogs;

exit;

We enable fast-start failover

After the restore is complete, we connect to the Data Guard broker (it doesn't matter from where) and activate the fast-start failover (if it was enabled):

dgmgrl /

show configuration
enable fast_start failover; 

exit;

Deleting Restore points

Finally we connect to primary base and delete the restore point (it will also delete it from the standby):

sqlplus / as sysdba

select name,space_used/1024/1024/1024 from v$recovery_file_dest;
select time,name,storage_size/1024/1024/1024 from v$restore_point;
--drop restore point rpoint;

exit;

Steps for versions prior to Oracle Database 19c or if the standby database is in read only with apply state

Create a Guaranteed Restore Point

In primary school

As a first step, we create the guarantee restore point as before:

sqlplus / as sysdba

create restore point rpoint guarantee flashback database;

exit;

We check that the Restore Point has been created

In Primary and Standby

We run the following query on all sides to make sure that the restore point has been created and to keep its name as it will have a different name on the standby:

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

Disabling fast-start failover

If we need to restore the database and we have fast-start failover enabled in Data Guard, we must disable it (it doesn't matter where we connect from):

dgmgrl /

disable fast_start failover force;

exit;

Flashback to Guaranteed Restore Point

At this point we are ready to restore the databases to the restore point, but first stop them and start them in mount state.

We stop replication

First we stop the transfer and application of files on the standby:

dgmgrl /

edit database oradev set state = 'TRANSPORT-OFF';
edit database oradev_dr set state = 'APPLY-OFF';

exit;

We flashback to primary.

Then we flashback the primary after first restarting it in mount state:

sqlplus / as sysdba

shutdown immediate;
startup mount;
flashback database to restore point rpoint; 
alter database open resetlogs;    

exit;

We flashback to standby

Then we do the same process on the standby, changing the name of the restore point:

sqlplus / as sysdba

shutdown immediate;
startup mount;
flashback database to restore point rpoint_PRIMARY;

exit;

We activate Data Guard

After the restore is complete, we connect to the Data Guard broker (it doesn't matter from where), activate the Data Guard configuration and fast-start failover (if it was enabled):

dgmgrl /

edit database oradev set state = 'TRANSPORT-ON';
edit database oradev_dr set state = 'APPLY-ON';
enable fast_start failover;

exit;

Deleting Restore points

Finally we connect to primary base and delete the restore point (it will also delete it from the standby):

sqlplus / as sysdba

select name,space_used/1024/1024/1024 from v$recovery_file_dest;
select time,name,storage_size/1024/1024/1024 from v$restore_point;
--drop restore point rpoint;

exit;

Sources:

• How to create, use and drop a Guaranteed restore point in a Data Guard configuration (Doc ID 2338328.1)
• Managing Physical and Snapshot Standby Databases