LOAD_BALANCE = OFF remote_listener 可以不设置，也可以设置成一样做 TAF

This was originally logged as Unpublished Bug:9104473 but it is not a bug, but a configuration error, due to the fact that the primary and standby databases were cross-registered with each others listeners. The bug explains this, "Since the databases have the same SID (due to it being dataguard) and are both registered with each listener, when they connect to the standby in EM, the connection is actually getting routed to the primary. Hence, they are actually looking at the primary database info when they connect to the standby".

The following information shows this:-

at sqlplus:-

show parameters listener (on both the primary and the standby)

Primary Database
NAME TYPE VALUE
------------------------------------ -----------

local_listener string orcl_p.<domain>
remote_listener string orcl.<domain>



Standby Database
NAME TYPE VALUE
------------------------------------ -----------

local_listener string orcl_s.<domain>
remote_listener string orcl.<domain>


In this case the remote_listener orcl.<domain>was actually a failover tns alias in the tnsnames.ora file
so it pointed first to the primary host, and then to the standby.

eg.


orcl.<domain>=
(DESCRIPTION =
(FAILOVER = ON)
(LOAD_BALANCE = OFF)
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL = TCP)
(HOST = host1.domain.<domain>)
(PORT = 25520)
)
(ADDRESS=
(PROTOCOL = TCP)
(HOST = host2.domain.<domain>)
(PORT = 25520)
)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 3600)
(DELAY = 1)
)
)
)

The output of lsnrctl status on both listeners, showed that both the standby and primary database were registered with both listeners.

SOLUTION

Remove the remote_listener parameter settings in the database, and restart the listeners.