1

Primary DB: 11.2.0.3 Enterprise
Standby DB: 12.1.0.2 Enterprise

I've found several articles on making changes on a Physical Standby DB, but I've been having difficulty locating how to enable user testing on a Transient Logical Standby DB (then flashing back their changes and re-enabling log transport and SQL Apply after the testing has been completed).

Based on what I know of Logical Standby Databases, I wrote up the following procedures. However, this is untested since if I botch up the Standby DB, I can't simply re-duplicate the DB through RMAN since the database versions are different. I'll essentially have to re-clone the Primary DB, reconfigure the Standby for dataguard, upgrade the Standby DB version, etc, etc... basically, a lot of work that I would prefer to avoid, if possible...

If someone who has had experience with this can look over my below procedures and let me know if i'm forgetting anything, or what potential issues/caveats I should watch out for or be aware of, I would greatly appreciate it:

*note: the primary => standby log transport is specified in log_archive_dest_3

PRIMARY
alter system set log_archive_dest_state_3=defer scope=both;

STANDBY
alter database stop logical standby apply;
alter database guard none;
alter database flashback on;
create restore point before_testing guarantee flashback database;

*AFTER TESTING IS DONE*

STANDBY
flashback database to restore point 'before_testing';
drop restore point before_testing;
alter database flashback off;
alter database guard all;
alter database start logical standby apply immediate;

PRIMARY
alter system set log_archive_dest_state_3=enable scope=both;
1

My procedures seem valid... I was able to test the logical DB, then bring it back into an exact copy Logical Standby and re-enable SQL Apply without issues.

However, there was a minor change to my procedures: flashback database requires that the DB is in mount state and that alter database open requires resetlogs or noresetlogs.

Therefore, the amended procedures are as follows:

PRIMARY
alter system set log_archive_dest_state_3=defer scope=both;

STANDBY
alter database stop logical standby apply;
alter database guard none;
alter database flashback on;
create restore point before_testing guarantee flashback database;

*AFTER TESTING IS DONE*

STANDBY
shutdown immediate;
startup mount;
flashback database to restore point 'before_testing';
alter database open resetlogs;
alter database guard all;
alter database start logical standby apply immediate;
drop restore point before_testing;
alter database flashback off;

PRIMARY
alter system set log_archive_dest_state_3=enable scope=both;
0

You're talking about Snapshot Standby here. Have a look at:

http://www.oracle.com/technetwork/testcontent/usingsnapshot-088081.html

  • Snapshot Standby is applicable to Physical Standby Databases, not Logical Standby databases. – Kris Johnston Dec 3 '15 at 16:46
  • The subject is "Testing on a Transient Logical Standby Database"? – Phil Sumner Dec 4 '15 at 9:16
  • Yes, a Transient Logical Standby Database is a subset of a Logical Standby Database (which isn't the same thing as a Snapshot Standby)...I've eliminated the "Transient" word in the subject since it isn't really applicable to the question. A Logical Standby is a completely separate beast from a Physical Standby, as the database is already open in read/write and uses logminer and SQL Apply instead of applying the Redo/archive directly. I mostly wasn't sure if the logminer and SQL apply would have issues after a flashback database, but it appears that my fears were unfounded. :) – Kris Johnston Dec 4 '15 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.