Mar 28 2017

Oracle 11g R2 Open Data Guard Read Write Mode With Flashback Technology And Revert Restore Point

Category: Administration,Backup And RecoveryFatih Acar @ 13:34

You can use flashback technology on standby side. If you want to make application test on database, you can use data guard as read write mode temporarily with flashback technology. You can create a restore point on standby side then you can do any transaction on standby side to test or any other purpose. After you complete your operations on the standby side, you can revert to restore point and again connect to primary side and apply logs from primary side.

This feature can be use for application test operations.

Perform Flashback Steps

Standby Side


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database flashback on;

Database altered.

Note : Check recovery destination and size. You have to have recovery dest and size.

SQL> show parameter recovery

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /oracle/fra
db_recovery_file_dest_size	     big integer 10G
recovery_parallelism		     integer	 0

SQL> create restore point before_test guarantee flashback database;

Restore point created.

Primary Side


SQL> alter system archive log current;

System altered.

Note : Archive dest for standby log sync parameter set defer.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.


Standby Side



SQL> alter database activate standby database;

Database altered.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  818401280 bytes
Fixed Size		    2232800 bytes
Variable Size		  587206176 bytes
Database Buffers	  226492416 bytes
Redo Buffers		    2469888 bytes
Database mounted.

SQL> alter database set standby database to maximize performance;

Database altered.

SQL> alter database open;

Database altered.

You can do test operation on standby side. After completed, you can revert standby database to restore point and apply logs from primary side.

Revert Operation

Standby Side



SQL> shutdown immediate;

SQL> startup mount;

SQL> flashback database to restore point before_test;

Flashback complete.

SQL> alter database convert to physical standby;

Database altered.

SQL> startup mount force;

SQL> alter database recover managed standby database disconnect;

If you want to use real time apply feature, you can open standby database read only mode and run below command.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Primary Side



SQL> alter system set log_archive_dest_state_2=enable;

System altered.

Drop Restore Point

Standby Side



SQL> alter database recover managed standby database cancel;

Database altered.

SQL> startup mount force;
ORACLE instance started.

Total System Global Area  818401280 bytes
Fixed Size		    2232800 bytes
Variable Size		  587206176 bytes
Database Buffers	  226492416 bytes
Redo Buffers		    2469888 bytes
Database mounted.
SQL> drop restore point before_test;

Restore point dropped.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

3,585 total views, 5 views today

Tags: Database Administration, Oracle Administration, Oracle Backup and Restore, Oracle Data Guard

facebook comments:

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.