I have a 12c Dataguard configuration between
apple (primary) and
orange (physical standby). The following is my entry in ldap:
apple= (DESCRIPTION_LIST= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=apple_home)(PORT=15940)) (ADDRESS=(PROTOCOL=TCP)(HOST=orange_home)(PORT=15940))) (CONNECT_DATA=(SERVICE_NAME=virtual_apple)(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic))) ) (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=apple_home)(PORT=15940)) (CONNECT_DATA=(SERVICE_NAME=apple)(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic))) ) )
My goal with this is to allow clients to
sqlplus user@apple regardless of which database is the current primary. This is why you see
virtual_apple in the connect string, it is a service created that only runs on current primary. I added the second
DESCRIPTION so that in the event of a switchover where
apple is the standby, Broker can connect from
orange parameter below). It will otherwise fail to connect because it will be looking for the
virtual_apple service on the standby which is undesirable due to the fact that users would then be redirected to the standby when running
The problem that I have been encountering is that redo shipping and applying seems "spotty". One minute there will no problems and the next I will get
ORA-16857: standby disconnected from redo source for longer than specified threshold and redo logs stop applying. I then set
TransportDisconnectedThreshold='120' as I have found in articles. However, the problem persists.
I suspect this is due to my network service name description. Perhaps Broker takes too long trying to reach the wrong service name which throws
ORA-16857 but then hits the right one but then loses it again. Is my connection string wrong/problematic? Is there a parameter I could add to help solve this issue? Is my issue elsewhere?
log_archive_dest_2='service="apple"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="apple" net_timeout=30','valid_for=(online_logfile,all_roles)'