Workaround for error ORA-00141: all addresses specified for parameter LOCAL_LISTENER are invalid

When trying to update your database's LOCAL_LISTENER parameter like this:

alter system set local_listener=LISTENER_CDB scope=both

and you get the following error stack:

ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00141: all addresses specified for parameter LOCAL_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'LISTENER_CDB'

The solution is to first change your $TNS_ADMIN/tnsadmin.ora to correspond to the value you wish to set the local_listener parameter to. For example, change the following line:

LISTENER =
  (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.oric.no)(PORT = 1521))

to

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

For more information about the local_listener parameter, see this earlier post

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 lookup ip addresses from mac addresses on Windows

Use the arp utility (Adress Resolution Protocol).

arp -a

it will print the arp table for you, where you can map ip addresses to mac addresses.

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

Missing trailing slash in listener.ora caused ORA-27101 when attempting to connect

Not too long ago, I got an error when connecting to my database using TOAD:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
IBM AIX RISC System/6000 Error: 2: No such file or directory

However, when connecting to the same database with sqlplus from the command line from a remote client, I could connect without errors.


Doc ID 1296982.1 "DVCA receives ORA-01034, ORA-27101" pointed me in the right direction:


"The value of ORACLE_HOME (or ORACLE_SID) passed to the DVCA utility does not match the value of ORACLE_HOME (or ORACLE_SID) that was in effect when the instance was started.

The shared memory segment key for an Oracle instance uses a hashed value based on the contents of ORACLE_HOME and ORACLE_SID. So, if one or the other of these values does not match what was used to start the instance, the resulting hash will not match, and one will encounter "ORA-27101: shared memory realm does not exist" when trying to connect to the instance.

This can commonly be caused by the presence (or lack thereof) of trailing slashes in the string for ORACLE_HOME, as passed to the -oh parameter of DVCA."

I changed my listener.ora file to read as follows:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb)
      (ORACLE_HOME = /u01/oracle/product/11204 )
      (SID_NAME = mydb)
    )
  )

to

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb)
      (ORACLE_HOME = /u01/oracle/product/11204/) <-- Note the trailing slash character at the end of the ORACLE_HOME path
      (SID_NAME = mydb)
    )
  )

Reload the listener with lsnrctl reload, and the listener once again accepted connections from TOAD.

How to create and start a service with dbms_service

Create the service. The first parameter name is the service name, can be up to 64 characters long.
The second parameter is the network name of the service as used in SQLNet connect descriptors for client connections

# sqlplus / as sysdba
SQL> exec dbms_service.create_service(service_name=>'online_users', network_name=>'online_users');

Start the service:

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

Update the service_names parameter. Without this, your new service would exist, but have to be manually started with every database restart:

SQL> alter system set service_names='proddb01, online_users' scope=both;

Check the parameter settings:

show parameter service

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
service_names                        string                            online_users, proddb01

A typical connect string when using JDBC would be

connect string : jdbc:oracle:thin:@//prodserver01:1521/online_users

If you are relying on tns naming, in other words, using a local tnsnames.ora file, make sure you set up your tnsnames.ora correctly.
The following is an example of a tnsnames.ora entry for the service we just created:

online_users =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = online_users)
    )
  )

Note: If you have specified a value for the database parameter domain_name, you need to add this to the SERVICE_NAME-clause of your tnsnames.ora entry, otherwise you will have trouble connecting:

show parameter domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      mydomain.com

In the case shown above, we have indeed specified a value for domain_name, and therefore the service_name will inherit this value by default.
So make sure you specify the FQDN in the SERVICE_NAME clause for your tnsnames.ora entry, like this:

(SERVICE_NAME = online_users.mydomain.com)

The command

lsnrctl services

will show you how the services are automatically registered with the listener.
The first 3 services are automatically created and always created by default with any Oracle installation. The last service registered with the listener is the one we created manually previously in this post:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521)))
Services Summary...
Service "proddb01" has 1 instance(s).
  Instance "proddb01", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "proddb01.mydomain.com" has 1 instance(s).
  Instance "proddb01", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
Service "proddb01XDB.mydomain.com" has 1 instance(s).
  Instance "proddb01", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=myserver.mydomain.com)(PORT=19394))
Service "online_users.mydomain.com" has 1 instance(s).
  Instance "proddb01", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER

If for some reason the service is not registered automatically with the listener within reasonable time, you can try to force a registration, like this:

alter system register;

If your listener is running on the default port 1521, there should be no need to touch your listener configuration, except stop/start/reload.
If you use a non-default listener configuration, such as a port != 1521 or a listener name != LISTENER, you need to configure the parameter local_listener.
In the exammple below, I configure the listener to run on port 1526 instead of 1521:

alter system set local_listener='(address=(protocol=tcp)(host=myserver)(port=1526))' scope=both;

Once again, note that in this case, the parameter db_domain is set so you only need to include the server name, not the domain name. If you do use the FQDN, it doesn't make any difference to your setup.





Verify that your connection can be used by using sqlplus to create a connection:

sqlplus scott/tiger@online_users
SQL> select sys_context('userenv','SERVICE_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
online_users.mydomain.com

To remove a service:

-- stop the service
SQL> exec dbms_service.stop_service('online_users');

-- after stopping the service, it may be deleted
SQL> exec dbms_service.delete_service('online_users');

PL/SQL procedure successfully completed.

A useful query for checking relevant parameters:

set lines 200
column name format a30
column network_name format a30
column value format a60

select name,value
from v$system_parameter
where name in ('service_names','db_domain','local_listener');

SELECT name,
       network_name
FROM   v$active_services
ORDER BY 1;

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;

How to use the nmap tool for Oracle networking troubleshooting

The Network exploration tool and security / port skanner (nmap) came in handy as I was checking the prerequisites for a Golden Gate installation.

We had to open ports in a firewall between the two servers to allow the Golden Gate Manager processes on each side to communicate with one another.

Initially, the ports seemed to be closed even though the firewall administrator claimed it was open.

Oracle Golden Gate needs two-way communication over the designated manager port, which by default is 7809.

So I used nmap to prove that it was indeed closed.

When the nmap status is closed or filtered, the man pages explains their state as


"Closed ports have no application listening on them, though they could open up at any time. Ports are classified as unfiltered when they are responsive to nmap's probes, but nmap cannot determine whether they are open or closed.

Filtered ports means that a firewall, filter, or other network obstacle is blocking the port so that nmap cannot tell whether it is open or closed."


Port 1521 was opened, as requested from the firewall team:

[root@myserver2 ~]#  nmap -p 1521 myserver1
Starting Nmap 5.51 ( http://nmap.org ) at 2015-03-24 14:02 CET

Nmap scan report for myserver1 (159.216.45.70)
Host is up (0.0018s latency).
rDNS record for 159.216.45.70: myserver1.mydomain.no
PORT     STATE SERVICE
1521/tcp open  oracle

Port 7809 was closed, as seen by the output below:

[root@myserver2 ~]# nmap -p 7809 myserver1 

Starting Nmap 5.51 ( http://nmap.org ) at 2015-03-24 15:14 CET
Nmap scan report for myserver1 (159.216.45.70)
Host is up.
rDNS record for 159.216.45.70: myserver1.mydomain.no
PORT     STATE    SERVICE
7809/tcp filtered unknown

Nmap done: 1 IP address (1 host up) scanned in 2.08 seconds

Later, the port range 7809-7820 was opened, as can be seen below. Note that there is no activity on ports 7810-7820 so they are for the time being marked as closed:

root@myserver2 ~]# nmap  -p 7809-7820 myserver1

Starting Nmap 5.51 ( http://nmap.org ) at 2015-03-24 15:48 CET
Nmap scan report for myserver1(159.216.45.70)
Host is up (0.0024s latency).
rDNS record for 159.216.45.70: myserver1.mydomain.no

PORT     STATE  SERVICE
7809/tcp open   unknown
7810/tcp closed unknown
7811/tcp closed unknown
7812/tcp closed unknown
7813/tcp closed unknown
7814/tcp closed unknown
7815/tcp closed unknown
7816/tcp closed unknown
7817/tcp closed unknown
7818/tcp closed unknown
7819/tcp closed unknown
7820/tcp closed unknown