26 May Oracle 12cR1 : ORA-16775 with Cascading Standby Databases
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 🙂
Sorry, the comment form is closed at this time.