Finally I’m back at my English blog, I had been posting at Pythian’s blog and in my Spanish blog , but I had this one a little forgotten, but I promise to make a comeback at it.

Recently in my Spanish blog, I had written a blog post in where I configured a cascading Standby (Spanish), but while playing with it, I stumbled into an error when I had tried to do a switchover from the first Standby to the second Standby which I will try to explain in this blog post.

To give a bit of a background , I had a setup of 1 Primary Database and 2 Physical Standby’s with 2 farsync instances

DGMGRL> show configuration

Configuration - DGCONFIG

Protection Mode: MaxPerformance
Databases:
orcl - Primary database
farsync - Far Sync
orclstby - Physical standby database
orclrep - Physical standby database (receiving current redo)
farsync2 - Far Sync (inactive)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

These are the RedoRoutes for all 3 DBs and the 2 FarSync instances

DGMGRL> show database orcl 'RedoRoutes'
RedoRoutes = '( LOCAL : farsync SYNC)'
DGMGRL> show database orclstby 'RedoRoutes'
RedoRoutes = '(orcl : orclrep ASYNC)(orclrep : orcl ASYNC)(LOCAL : farsync2 SYNC)'
DGMGRL> show database orclrep 'RedoRoutes'
RedoRoutes = '(LOCAL : orclstby SYNC)'
DGMGRL> show instance farsync 'RedoRoutes'
RedoRoutes = '(orcl : orclstby ASYNC )'
DGMGRL> show instance farsync2 'RedoRoutes'
RedoRoutes = '(orclstby : orcl ASYNC, orclrep )'

So I have no problem doing a switchover from orcl to orclstby or orclrep, or as well when I had orclstby as a primary and do switchover to orcl

DGMGRL> switchover to orclstby
Performing switchover NOW, please wait...
...
Switchover succeeded, new primary is "orclstby"
DGMGRL> switchover to orcl
Performing switchover NOW, please wait...
...
Switchover succeeded, new primary is "orcl"
DGMGRL> switchover to orclrep
Performing switchover NOW, please wait...
...
Switchover succeeded, new primary is "orclrep"
DGMGRL> switchover to orclstby
Performing switchover NOW, please wait...
...
Switchover succeeded, new primary is "orclstby"

But the problem came when I tried to do a switchover from orclstby to orclrep, the error ORA-16775: ORA-16775 : target standby database in broker operation has potential data loss popped up.

While doing a revision of what logs had been applied to orclstby and orclrep, I saw now lag nor a redo log missing. Though I did see that there were a couple of logs pending on being shipped and applied which was weird.

--On orclstby
----------

(SELECTNA CURRENT_PRIMARY_SEQ MAX_STBY To be applied To be Shipped
--------- ------------------- ---------- ------------- -------------
ORCL 177 174 2 3

--On orclrep
-------------
LOGS TIME SEQUENCE#
---------------- ------------------ ----------
Last Applied : 27-MAY-14:00:38:11 177
Last Received : 27-MAY-14:00:38:11 177

If you remember the RedoRoutes for orclstby, I had the following:

DGMGRL> show database orclstby 'RedoRoutes'
RedoRoutes = '(orcl : orclrep ASYNC)(orclrep : orcl ASYNC)(LOCAL : farsync2 SYNC)'
DGMGRL> show instance farsync2 'RedoRoutes'
RedoRoutes = '(orclstby : orcl ASYNC, orclrep )'

Meaning that the local database (orclstby) should only be sending the logs to the farsync2 instance, and the farsync2 will be sending the redo to the orclrep database. Which would be this topology

DGMGRL> show configuration

Configuration - DGCONFIG

Protection Mode: MaxPerformance
Databases:
orclstby - Primary database
farsync2 - Far Sync
orcl - Physical standby database
orclrep - Physical standby database
farsync - Far Sync (inactive)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

So the problem that I saw and the way I solved it, was that revising the log_archive_dest_2 of  orclstby which is the one defined for the orclrep DB, I saw that it was enabled as well as the log_archive_dest_3 which is the one for the farsync2 instance

SQL> show parameter log

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
...
log_archive_config string dg_config=(orclstby,orcl,farsy
nc,farsync2,orclrep)
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST
log_archive_dest_2 string service="orclrep", ASYNC NOAFF
IRM delay=0 optional compressi
on=disable max_failure=0 max_c
onnections=1 reopen=300 db_uni
que_name="orclrep" net_timeout
=30, valid_for=(standby_logfil
e,all_roles)
...
log_archive_dest_3 string service="farsync2", SYNC AFFIR
M delay=0 optional compression
=disable max_failure=0 max_con
nections=1 reopen=300 db_uniqu
e_name="farsync2" net_timeout=
30, valid_for=(online_logfile,
all_roles)
...
log_archive_dest_state_2 string ENABLE
...
log_archive_dest_state_3 string ENABLE

So what I did, was DEFER the log_archive_dest_state_2 in orclstby

SQL> alter system set log_archive_dest_state_2='defer' scope=both;

System altered.

And now I was able to do the switchover from orclstby to orclrep without a problem

DGMGRL> switchover to orclrep
Performing switchover NOW, please wait...
Operation requires a connection to instance "orclrep" on database "orclrep"
Connecting to instance "orclrep"...
Connected as SYSDBA.
New primary database "orclrep" is opening...
Operation requires startup of instance "orclstby" on database "orclstby"
Starting instance "orclstby"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "orclrep"
DGMGRL> show configuration

Configuration - DGCONFIG

Protection Mode: MaxPerformance
Databases:
orclrep - Primary database
orclstby - Physical standby database
orcl - Physical standby database (receiving current redo)
farsync - Far Sync (inactive)
farsync2 - Far Sync (inactive)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Conclusion
Right now I am assuming this is a bug for cascading databases with Dataguard in 12cR1, but I need to do a bit more research if it’s true or not, but in the meantime I hope this helps 🙂