dbawiki

Original at edstevensdba.wordpress.com

• The listener itself doesn’t give a flying fig about what is in tnsnames.ora. That file (tnsnames.ora) is used ONLY by client processes. In the case of dynamic registration, the database IS the client process.
• The listener is quite capable of starting with no listener.ora file at all. In this case it will start with all default values, including the default name of LISTENER and default port of 1521. For most people, most of the time, this is sufficient.
• If the local_listener parameter is not set (null) the database will send the registration request to port 1521. Notice that 1521 is also the default port of of the listener.
• If you choose to set local_listener, you can either use a full connect string ¹⁾) or simply reference an entry in tnsnames.ora, to resolve to the address (server name and port) of the listener.
• The SID_LIST section of listener.ora has nothing to do with dynamic registration. Quite the opposite. The SID_LIST section is how you implement static registration.

Turn on tracing
There are some settings that can help…

Setting a value greater than 0 ensures that connections are not left open indefinitely, due to, for example, an abnormal client termination.

  -  sqlnet.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/sqlnet.ora
  -  Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

ADR_BASE = /oracle/product

DIAG_ADR_ENABLED=OFF

sqlnet.expire_time=10
DEFAULT_SDU_SIZE=11280

The clause for LISTENER_FEUK11UD is a reference for the local_listener parameter.

  -  tnsnames.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/tnsnames.ora
  -  Generated by Oracle configuration tools.

LISTENER_FEUK11UD  ======
  (ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))


FEUK11UD  ======
  (DESCRIPTION  ======
    (ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))
    (CONNECT_DATA  ======
      (SERVER = DEDICATED)
      (SERVICE_NAME = FEUK11UD)
    )
  )

Setting the local_listener in the database to the connection string instead of the connect identifier can help the debugging process…

alter system set local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))" scope=both;

Setting STARTUP_WAIT_TIME_LISTENER Setting CONNECT_TIMEOUT_LISTENER

  -  listener.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/listener.ora
  -  Generated by Oracle configuration tools.

STARTUP_WAIT_TIME_LISTENER_FEUK11UD = 0
CONNECT_TIMEOUT_LISTENER_FEUK11UD = 30
LISTENER_FEUK11UD  ======
  (DESCRIPTION_LIST  ======
    (DESCRIPTION  ======
      (ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER_FEUK11UD = /oracle/product

Check what the global_name parameter is in the database

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Mar 21 14:00:48 2019
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> select * from global_name;

GLOBAL_NAME
-------------------------
UPG.WORLD

listener.ora specifying global_dbname without the domain

LSNR481_184 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hn481.crelan.be)(PORT = 3531))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC3531))
    )
  )

SID_LIST_LSNR481_184 =
(SID_LIST =
  (SID_DESC =
     (GLOBAL_DBNAME = upg)
     (SID_NAME = upg)
     (ORACLE_HOME = /crelan/tst/ora_bin1/app/oracle/product/18.4/dbhome_1)
   )
)

What services are recognised?

$ORACLE_HOME/bin/lsnrctl services lsnr481_184

LSNRCTL for IBM/AIX RISC System/6000: Version 18.0.0.0.0 - Production on 21-MAR-2019 21:28:24

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hn481.crelan.be)(PORT=3531)))
Services Summary...
Service "upg" has 1 instance(s).
  Instance "upg", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:5 refused:0
         LOCAL SERVER
The command completed successfully

listener.ora specifying global_dbname with domain

LSNR481_184 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hn481.crelan.be)(PORT = 3531))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC3531))
    )
  )

SID_LIST_LSNR481_184 =
(SID_LIST =
  (SID_DESC =
     (GLOBAL_DBNAME = upg.world)
     (SID_NAME = upg)
     (ORACLE_HOME = /crelan/tst/ora_bin1/app/oracle/product/18.4/dbhome_1)
   )
)

What services are recognised now?

$ORACLE_HOME/bin/lsnrctl services lsnr481_184

LSNRCTL for IBM/AIX RISC System/6000: Version 18.0.0.0.0 - Production on 21-MAR-2019 21:28:24

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hn481.crelan.be)(PORT=3531)))
Services Summary...
Service "upg.world" has 1 instance(s).
  Instance "upg", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:5 refused:0
         LOCAL SERVER
The command completed successfully