ORA-12514, TNS:listener does not.. in 11.2.0.2 and 11.2.0.3 Rac env.

This morning in the Dutch environment we have been bitten by that bug again on one of the databases:

Bug 11772838 – oraagent modifies LOCAL_LISTENER with an incorrect value, when the parameter was set in spfile (Doc ID 11772838.8)

So clearly a case of Monday again and both 11.2.0.2 and 11.2.0.3 Rac environments might be affected!

Customer complained that his connection to the instance was not working:
oracle.javatools.db.DBException: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

I logged into the  instances and due to the fact that customer could tell me connection to second instance working I compared local_listener again.
And indeed  that one had been altered as is described in the note by the cluster agent last friday  :

SQL> show parameter listener

## wrong
NAME TYPE VALUE
———————————— ———– ——————————
listener_networks string
local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=195.233.333.77)(PORT=1521))))

## correct

local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=mysrvr36r-vip)(PORT=33007))))

The fix is straight forward  from the note ( and the workaround  used is  indeed  to add a blank ( grins saved by a blank  !)

ALTER SYSTEM SET local_listener='(DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mysrvr35r-vip)(PORT=33007))))’ SCOPE=BOTH SID=’MYDBWHP1′;

ALTER SYSTEM SET local_listener='(DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mysrvr36r-vip)(PORT=33007))))’ SCOPE=BOTH SID=’MYDBWHP2′;

 

I searched the alert file of the instance on the 35r and indeed found  in the alert file:

Fri May 16 21:47:01 2014
ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=195.233.333.77)(PORT=1521))))’ SCOPE=MEMORY SID=’MYDBWHP1′;

Note:
I have checked the cluster logfiles but I cannot find a true issue there .

and both Customer and Dba happy again.

happy reading and best of luck,

Mathijs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s