What does the UR = A in tnsnames.ora mean?

I recently had to connect remotely as sysdba to remote instances when I created Physical Standby Databases using RMAN.

There were some some connection problems along the way, and this was solved by setting the (UR=A) attribute in the tnsnames.ora entry for the database.

What exactly is the UR=A attribute and what does it do?

This is explained very well in a blog post from Ed Chen Logic so rather than explaining the same points all over again, continue reading there.

But in short, an instance (memory, background processes, but no data files) will inform the listener to block all connections to its corresponding service by default.

To bypass this restriction, you can add UR=A to the connect descriptor. From the primary server, I will then be able to connect to the standby instance running on prodserver2.oric.no.

Let's demonstrate it:

Start the standby database in nomount:

sqlplus / as sysdba
startup nomount
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size                  8635096 bytes
Variable Size            2986347816 bytes
Database Buffers         9865003008 bytes
Redo Buffers               24915968 bytes

Start a default listener on the standby server:

lsnrctl start

Make sure the default service is loaded: wait for a minute, or execute

alter system register;

Listener status is now:

Service "proddb01_stby.oric.no" has 1 instance(s).
  Instance "proddb01", status BLOCKED, has 1 handler(s) for this service...

Create a tnsnames.ora entry for connecting to the standby database from the primary server:

proddb01_stby.oric.no =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = proddb02.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb01_stby.oric.no)
    )
  )

Test it:

sqlplus sys/password@proddb01_stby.oric.no as sysdba

Result:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Add the UR=A to the connect_data section:

proddb01_stby.oric.no =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = proddb01.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb01_stby.oric.no)
      (UR = A)
    )
  )

And try again:

sqlplus sys/password@proddb01_stby.oric.no as sysdba

Result:

SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 9 16:43:09 2022

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select host_name from v$instance;

HOST_NAME
-----------------
proddb02.oric.no

SQL> show parameter db_unique

NAME             TYPE      VALUE
---------------- --------- --------------
db_unique_name   string    proddb01_stby

Listener output showing multiple instances when only one exists

In one of my multitenant setups, I saw the following output from the listener:

lsnrctl status cdb

Service "sales" has 2 instance(s).
  Instance "cdb", status READY, has 2 handler(s) for this service...
  Instance "sales", status UNKNOWN, has 1 handler(s) for this service...

There is only one instance in my setup, the container database called "cdb".
What's causing the listener to view the pdb "sales" as a separate instance, then?

See this post where Uwe Hesse explains:

It is nothing to worry about when you see the same instance showing up twice, once with status READY and once with status UNKNOWN. READY means listener knows this because PMON of that instance told him so (dynamic registration) UNKNOWN means listener knows this because of SID_LIST entry in the listener.ora (static registration)

Quite correctly, I had a static entry in my listener.ora file:

SID_LIST_cdb =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/orasoft/product/19c)
      (SID_NAME = sales)
    )
  )

If I remove the following part

(SID_NAME = sales)

and restart the listener, the extra instance will be removed from the listener output:

lsnrctl stop cdb
lsnrctl start cdb

Output:

Service "sales" has 1 instance(s).
  Instance "cdb", status READY, has 2 handler(s) for this service...

What exactly is the LOCAL_LISTENER parameter, and what does it do?

What is the local_listener parameter?

It is a parameter that points to the listener running on the local server.

What is the purpose of the local_listener parameter?

It is used for dynamic listener registration, which is the process of the database contacting the listener and registrering the services it offers automatically.

How is the database registering its services with the listener?

From Oracle 12c and onwards, this registration process is handled by the LREG process.
The lreg process is easy to spot from the operating system:

ps -fu oracle |grep lreg
oracle     22023       1  0 Sep19 ?        00:00:30 ora_lreg_cdb

How is dynamic listener registration implemented?

Start the listener process with or without a parameter file. If you do not have a listener.ora parameter file, the listener will run using default values.
In either case, simply start the listener process by typing

lsnrctl start

After a little while (usually within a minute) the database has registered its services with the listener.
You you do not want to wait, you can manually force a registration by logging onto the database as a dba and issue:

alter system register;

How will the database find the listener process?

This is where the local_listener parameter comes in. It tells the database where to find it, and which port to use.
The value of my local_listener parameter is:

SYS@cdb>SQL>show parameter local

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_CDB

My $TNS_ADMIN/tnsnames.ora file has an entry that matches the value LISTENER_CDB:

LISTENER_CDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = testserver1.oric.no)(PORT = 1521))

This makes it possible for the database to register its services with the listener.

Even if the local_listener is not set at all, dynamic registeration would still work if your listener runs with the default values, which is

(ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host.

How does the database know which services that should be registered with the listener?

It will start to listen for services listed in the v$active_services view.

How does the local_listener parameter work under the multitenant architecture?

The local_listener works identically in both non-CDB and a multitenant database.
However, in a multitenant setup, remember that each pdb will have its own corresponding service.
This service you cannot stop unless you unplugg or shutdown your pdb.

If you attempt to stop the service of a running pdb you will receive

ORA-44793: cannot stop internal services

Any other services created after this point can be stopped and started at will, and the listener will follow suit.


Let's see how it works:

First, list the services currently supported by the listener:

Services Summary...
Service "cdb.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "cdbXDB.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "sales.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
The command completed successfully

The listener supports the services for the root container ("cdb") and the pdb ("sales").

Let's try to create and start a new service in my pdb called "sales":

SQL> alter session set container=sales;

Session altered.

SQL> exec dbms_service.create_service(service_name=>'online_users', network_name=>'online_users');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('online_users');

PL/SQL procedure successfully completed.

List the services supported by the listener now:

Services Summary...
Service "cdb.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "cdbXDB.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "online_users.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...
Service "sales.oric.no" has 1 instance(s).
  Instance "cdb", status READY, has 1 handler(s) for this service...

For automatic restart of Pluggable databases and their services, please see this post.

Documentation:

local_listener

service_names

dbms_service

A great blog post by Mr. Ed Stevens on the same topic

How to confirm that the parameter INBOUND_CONNECT_TIMEOUT_listener_name is working

From the documentation:

Purpuose: To specify the time, in seconds, for the client to complete its connect request to the listener after the network connection had been established.

and

If the listener does not receive the client request in the time specified, then it terminates the connection. In addition, the listener logs the IP address of the client and an ORA-12525:TNS: listener has not received client's request in time allowed error message to the listener.log file.

My listener.ora file:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

inbound_connect_timeout_listener=3

Verify that the parameter is set:

LSNRCTL> show inbound_connect_timeout
LISTENER parameter "inbound_connect_timeout" set to 3
LSNRCTL>

Verify that logging is set and the location of the logfile:

LSNRCTL> show log_file
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521)))
LISTENER parameter "log_file" set to /u01/oracle/diag/tnslsnr/myserver/listener/alert/log.xml
The command completed successfully
LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully

From a remote client, execute telnet against the listener port, in my case the default port 1521.

Use the "time" command in Linux to see how long the command executes:

[vk@myclient]$ time telnet myserver.mydomain.com 1521
Trying 192.168.0.122...
Connected to myserver.mydomain.com
Escape character is '^]'.
Connection closed by foreign host.

real    0m3.010s
user    0m0.001s
sys     0m0.001s

From the listener's log.xml file, we can see the message clearly:

<msg time='2017-01-23T11:09:12.118+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='myserver.mydomain.com'
 host_addr='192.168.0.122'>
 <txt>23-JAN-2017 11:09:12 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.25)(PORT=49264)) * establish * <unknown sid> * 12525
 </txt>
</msg>
<msg time='2017-01-23T11:09:12.119+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='myserver.mydomain.com'
 host_addr='192.168.0.122'>
 <txt>TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred
 </txt>
</msg>

From Oracle 10g and onwards, thet the default setting for INBOUND_CONNECT_TIMEOUT in listener.ora is 60 seconds
If set to zero, you disable the connect timeout functionality altogether. In such a situation, the telnet session above would simply "hang" and wait for its connection request to be completed.

Oracle recommends setting the INBOUND_CONNECTION_TIMEOUT in listener.ora in conjunction with INBOUND_CONNECT_TIMEOUT in sqlnet.ora.
Set the sqlnet.ora value slightly higher than the listener.ora value.

Sources:

http://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF210
http://docs.oracle.com/database/121/NETRF/listener.htm#NETRF312

How to manually register the database with the listener

Sometimes there may be a delay before pmon register itself with the listener.

lsnrctl status will show

The listener supports no services
The command completed successfully

To manually force pmon to register, execute

alter system register;