WARNING: Subscription for node down event still pending

SYMPTOMS

We are receiving the following warning messages in the listener log file constantly:

'WARNING: Subscription for node down event still pending'

CHANGES

This may be a new installation or a recent upgrade to 11g or newer.

CAUSE

These messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). In a non-RAC environment it is recommended to disable this subscription.   This feature was introduced in Oracle 10g.

SOLUTION

Set the following parameter in the listener.ora:

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF

Where <listener_name> should be replaced with the actual listener name configured in the

LISTENER.ORA file.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> parameter is to be placed by itself on an empty line.

It will be necessary to restart or reload the listener following the addition of this parameter.

This will prevent the messages from being written to the log file and may also prevent the TNS

Please Note: Setting SUBSCRIBE_FOR_NODE_DOWN_<listener_name> to OFF disables a necessary RAC functionality. The above workaround is recommended only for non-RAC environments.

The issue may be present in all 11g and newer installations.

What is Database link(DB link), Private and Public DB link

A database link enables a one-way connection to a remote database from a local database. The link is one-way only. The remote database users can’t use this link to connect to the local database—they must create a separate database link for that.

A database link allows us to gain access to a different database though a remote database user account; we don’t have to be a user in the remote database. Our privileges on that database will be identical to the privileges of the user account you use when creating the database link. Database links are useful when we want to query a table in a distributed database or even insert data from another database’s table into a local table. Database links allow users to access multiple databases as a single logical database.

We can create private and public database links. In the following sections, we’ll look at examples of how to create both types of database links.

Creating a Private Database Link

A private database link is owned by the user that creates the link. In the following statement, the SYSTEM user creates a private database link. The database link enables a connection to the remote database using the hr user’s username and password in that database.

SQL> CONNECT system/system_passwd@finance
Connected.
SQL>
SQL> CREATE DATABASE LINK MONITOR
2 CONNECT TO hr IDENTIFIED BY hr
3 USING 'monitor';
Database link created.
SQL>

Note: To create a database link, a user must have the CREATE PRIVATE DATABASE LINK privilege or the CREATE PUBLIC DATABASE LINK privilege in the local database.

After the link is created, the SYSTEM user can query the hr.employees table in the remote
database.

SQL> SELECT COUNT(*) FROM hr.employees@monitor;
COUNT(*)
----------
107

In the preceding statement, note that the database link’s name is MONITOR, which is the same as the remote database’s TNS name alias (Oracle Net Service alias), but it could be anything we want. The CONNECT TO . . . IDENTIFIED BY clause means that the user of this database link will use that username and password to enter the remote database. The USING 'monitor' clause simply specifies the TNS name alias for the linked remote database.
Because this is a private database link, only the SYSTEM user can use it. When the hr user tries to use this link to a remote database, this is what happens:

SQL> CONNECT hr/hr;
Connected.
SQL> SELECT count(*) FROM hr.employees@monitor;
select count(*) from hr.employees@monitor
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
SQL>

Creating a Public Database Link

A public database link, unlike a private database link, enables any user or any PL/SQL program unit to access the remote database objects. The creation statement is very similar to that for a private database link. We just add the PUBLIC keyword to the CREATE DATABASE LINK statement:

SQL> connect system/system_passwd as sysdba;
Connected.

SQL> CREATE PUBLIC DATABASE LINK MONITOR
2 CONNECT TO hr IDENTIFIED BY hr
3 USING 'monitor';
Database link created.
SQL>

Once the public MONITOR link is created, any user can log into a remote database using that
link. In the following example, the user tester uses the public database link to query the remote database, MONITOR.

SQL> CONNECT tester/tester1;
Connected.
SQL> SELECT COUNT(*) FROM hr.employees@monitor;
COUNT(*)
----------
107
SQL>

Note: We can create a public database link if several users require access to a remote Oracle database from a local database. Otherwise, create a private database link, which will allow only the owner of the private database link to access database objects in the remote database.

How to resolve network - database connectivity issue

DBA generally face this issue many times that a user has reported that he or she can’t connect to a database. You know there are many components involved with network connectivity and want to figure out the root cause of the problem.

Use the below steps as guidelines when diagnosing Oracle database network connectivity issues:

1. Use the operating system ping utility to determine whether the remote box is accessible—for example:
$ ping dwdb
dwdb is alive

If ping doesn’t work, work with your system or network administrator to ensure you have server-to-server connectivity in place.

2. Use telnet to see if you can connect to the remote server and port (that the listener is listening on)—for example:
$ telnet ora03 1521
Trying 127.0.0.1...
Connected to ora03.
Escape character is '^]'.

The prior output indicates that connectivity to a server and port is okay. If the
prior command hangs, then contact your SA or network administrator for
further assistance.

3. Use tnsping to determine whether Oracle Net is working. This utility will verify that an Oracle Net connection can be made to a database via the network—for example:

$ tnsping dwrep
..........
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = dwdb1.us.farm.com)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DWREP)))
OK (500 msec)

If tnsping can’t contact the remote database, verify that the remote listener and database are both up and running. On the remote box, use the lsnrctl status command to verify that the listener is up. Verify that the remote database is available by establishing a local connection as a non-SYS account (SYS can often connect to a troubled database when other schemas will not work).


4. Verify that the TNS information is correct. If the remote listener and database are working, then ensure that the mechanism for determining TNS information (like the tnsnames.ora file) contains the correct information.

Sometimes the client machine will have multiple TNS_ADMIN locations and
tnsnames.ora files. One way to verify whether a particular tnsnames.ora file is
being used is to rename it and see whether you get a different error when
attempting to connect to the remote database.


Network connectivity issues can be troublesome to diagnose because there are several architectural components that have to be in place for it to work correctly. You need to have the following in place:

• A functional network
• Open ports from point to point
• Oracle Net correctly installed and configured
• Target database and listener up and running
• Correct navigational information from the client to the target database

If you’re still having issues, examine the client sqlnet.log file and the remote server listener.log file. Sometimes these log files will show additional information that will pinpoint the issue.