Archive for the ‘Flashback’ Category

Steps to use Guaranteed Restore Points for backouts of database changes in a dataguard broker environment

Failover

Create the restore point on the failover databaase first, if there is addtional standby’s do the same on each standby

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>create restore point AT_test GUARANTEE FLASHBACK DATABASE;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;

Primary

Create the restore point on the primary database

SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>create restore point AT_test GUARANTEE FLASHBACK DATABASE;

Back out steps – Flashback

In the event of having to backout us the following steps to flashback to the restore points

Disabling the configuration in dataguard broker 

Disable the configuration in dataguard broker

DGMGRL> show configuration – check no errors before starting this
DGMGRL> disable FAST_START FAILOVER; – Only needed if fast start failover is enabled
DGMGRL> DISABLE CONFIGURATION;

Primary

Flashback the primary database to the restore point

SQL>shu immediate
SQL>startup mount
SQL>FLASHBACK DATABASE TO RESTORE POINT AT_test;
SQL>alter database open resetlogs;

Standby

Flashback the primary database to the restore point

SQL>shu immediate
SQL>startup mount
SQL>FLASHBACK DATABASE TO RESTORE POINT AT_test;

Enabling the configuration in dataguard broker 

Enable the configuration in dataguard broker

DGMGRL> show configuration – check no errors before starting this
DGMGRL> enable CONFIGURATION;
DGMGRL> enable FAST_START FAILOVER; – Only needed if fast start failover is enabled

Post Flashback Checks

Check the standby alert log to make sure the logs are being applied, you should see output like below

Media Recovery Waiting for thread 1 sequence 8 (in transit)
Recovery of Online Redo Log: Thread 1 Group 13 Seq 8 Reading mem 0
Mem# 0: /wload/test2/app/oracle/u02/oradata/testdb1/srl13a.rdo
Mem# 1: /wload/test2/app/oracle/u03/oradata/testdb1/srl13b.rdo
RFS[3]: Selected log 14 for thread 1 sequence 9 dbid 528024012 branch 780852273
Tue Apr 17 15:27:39 2012
Archived Log entry 997 added for thread 1 sequence 8 ID 0x1fc46837 dest 1:
Media Recovery Waiting for thread 1 sequence 9 (in transit)
Recovery of Online Redo Log: Thread 1 Group 14 Seq 9 Reading mem 0
Mem# 0: /wload/test2/app/oracle/u02/oradata/testdb1/srl14a.rdo
Mem# 1: /wload/test2/app/oracle/u03/oradata/testdb1/srl14b.rdo

Flashback Dataguard Database

Posted: April 12, 2012 in Flashback

Steps to flashback the primary and stanby database to a point in time.

Disable dataguard configuration

dgmgrl
show configuration – check no errors before starting this
disable FAST_START FAILOVER;
DISABLE CONFIGURATION;
Primary

srvctl stop database -d PTEST1
srvctl start database -d PTEST1 -o mount
SQL>flashback database to timestamp to_timestamp(‘2011-02-17 14:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) or restore point
SQL>alter database open resetlogs;
SQL>select resetlogs_change# from v$database;
srvctl start service -s TEST -d PTEST1

Standby

srvctl stop database -d FTEST1
srvctl start database -d FTEST1 -o mount
flashback database to scn <above-2>
Enable dataguard configuration

dgmgrl
ENABLE CONFIGURATION;
ENABLE FAST_START FAILOVER;

show configuration – check no errors