Oracle Database Discussions

I inherited the care and maintenance of two 10.2.0.2 EE databases (non-RAC) running on a single Windows server. The first thing I noticed upon opening the listener log was many TNS-01184 and TNS-01185 errors repeating every few minutes, going back forever. Nobody seems to be complaining, but the presence of these errors bothers me and I don't understand why they are arising, and I haven't encountered this phenomenon on any other database I manage. Please note the following details and help me understand what is going on.

Here is one cycle of events from the listener log, that repeats continually:

04-SEP-2008 09:40:34 * service_update * mydb1 * 0
04-SEP-2008 09:40:40 * service_update * mydb1 * 0
04-SEP-2008 09:40:43 * service_register * mydb2 * 1184
TNS-01184: Listener rejected registration or update of service handler "DEDICATED"
 TNS-01185: Registration attempted from a remote node
04-SEP-2008 09:40:43 * service_died * mydb2 * 12537
04-SEP-2008 09:40:46 * service_update * mydb1 * 0
04-SEP-2008 09:40:47 * service_register * mydb1 * 1184
TNS-01184: Listener rejected registration or update of service handler "DEDICATED"
 TNS-01185: Registration attempted from a remote node
04-SEP-2008 09:40:47 * service_died * mydb1 * 12537
04-SEP-2008 09:40:49 * service_update * mydb1 * 0
04-SEP-2008 09:40:55 * service_update * mydb1 * 0

Here is my interpretation of what the log is saying:

1. MYDB1's pmon successfully pings the listener a few times

2. MYDB2 attempts to register with the listener, but fails, because the listener thinks it is "remote". MYDB2 loses contact with the listener.

3. MYDB1 then tries to register with the listener, fails and loses contact with the listener.

4. 2 seconds later, MYDB1 is back making updates with the listener.

Meanwhile, nobody is complaining about dropped connections or being unable to reach either database. I can find no real-world impact of these errors. But maybe, hidden danger lurks.

Here are the canonical error descriptions:

bq. TNS-01184: Listener rejected registration or update of service handler string+
bq. Action: Not normally visible to the user. For further details, turn on tracing and restart the instance. If error persists, contact Oracle Customer Support.
(Note: this seems to be a misprint. I think I would gain more knowledge by turning on Listener tracing and bouncing the listener, not the instance.)

bq. TNS-01185: Registration attempted from a remote node+
bq. Cause: It was determined that the registering instance is not located on the same node as the listener. It is required that the instance be colocated with the listener for this type of service registration.
bq. Action: Check the configuration of the registering instance. Ensure that local_listener parameter specifies only local listeners. Use remote_listener parameter to specify remote listeners.

Thing that really baffles me is why the error message refers to remote listeners. Both instances are local to the listener.

Here are the values of the LOCAL_LISTENER init param on each database, which the error message refers to. REMOTE_LISTENER is not set, and should not need to be set.

local_listener=LISTENER_MYDB1
local_listener=LISTENER_MYDB2

Here is the listener configuration. Pretty standard stuff.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = mydb1)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (SID_NAME = mydb1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = mydb2)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (SID_NAME = mydb2)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
    )
  )

I guess my logical next step would be to turn on listener tracing, but meanwhile I thought I'd bring this up here; maybe someone has some wisdom to offer about this phenomenon.

Thanks.