Introduction:

After setting up a taf service in one of  my rac environments i checked if the (local) listener that was configured for the database was showing the services as to be expected. Much to my surprise that  was not the case. So puzzled as i was ( after all this was not the first rac I build) I went on to a hunt to find what was going on. In this blog is my  share my steps / thoughts how i came to the happy end that of course was out there waiting for me.

First lets add a service to  the database:

With this command (all in one line) i have added a service to the database i was working with.

EXECUTE DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME => 'MYDB1_TAF.prod.nl',NETWORK_NAME => 'MYDB1_TAF.prod.nl',AQ_HA_NOTIFICATIONS => TRUE,FAILOVER_METHOD => DBMS_SERVICE.FAILOVER_METHOD_BASIC,FAILOVER_TYPE => DBMS_SERVICE.FAILOVER_TYPE_SELECT, FAILOVER_RETRIES => 5, FAILOVER_DELAY => 5);

Next step  was to add the Taf service to the Clusterware (Grid Infrastructure)

srvctl add service -d MYDB1 -s MYDB1_TAF.prod.nl -r MYDB11,MYDB12

Once the service is defined in the cluster we can start it as is shown below:

srvctl start service -d MYDB1 -s MYDB1_TAF.prod.nl

After that i checked my database  to see if it reflected the new service in place (and that was  the case !):

SQL> show parameter service

NAME                                                        TYPE VALUE

———————————— ———– ——————————

service_names                                      string                MYDB1_TAF.prod.nl

I also checked  db_unique_name (MYDB1) and domain (prod.nl) setting in the Database cause they have to be set up properly as well !

After all this checking it was time to configure my tnsnames.ora to be able to work / connect to this service.

My entry in the tnsnames  looks like this:

MYDB1.prod.nl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mysrvr1-vip)(PORT = 33006))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYDB1_TAF.prod.nl)
)
)

OK so t hat means the service_name has been set up as MYDB1_TAF.prod.nl.

Time to do a connect test:

sqlplus system@MYDB1.prod.nl

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 8 08:05:04 2013

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

Enter password:

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Oh but that was a bit unexpected so that means double-check the configurations to make it work the way it should be.

So  I started with a check the (local) listener:

lsnrctl status LISTENER_MYDB1
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 08-JUL-2013 08:17:51
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_MYDB1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_MYDB1
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                10-MAY-2013 14:01:28
Uptime                    58 days 18 hr. 16 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/11203_ee_64/db/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/nlrap1hr/listener_MYDB1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_MYDB1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.233.120.83)(PORT=33006)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.233.120.91)(PORT=33006)))
The listener supports no services
The command completed successfully

Wait a minute no services present!  Well it did give me an idea that i should check  the  Database again for its setup of the local listener.  And indeed i noticed  i had not added proper local listener setting to the database because the database was showing :

*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)))'

I thought that’s it ! With a big pff  that I had forgotten to set it up! However I Wanted 2 b 2 fast  again (as always when in a rush so I altered local listener in the database in this way). I have added the defined local listener to the information in the spfile) in the following way:

*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.233.120.91)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.233.120.91)(PORT=33006))))'

Then I checked and on the first node it was ok tle local listener was finally showing services it would work for!  But after checking the listener on the second node I had a hold on  wait a minute  experience because the second node still showed does not know of any services ..  Coin fell late this time ,  I re – examined  settings and altered then to show  the correct setting now  (and indeed thinking how could I forget that each instance in a rac uses its own local listener ! ).

So one more time (and this time with paying close attention):

My  local listener set up looked like this:
MYDB11.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.233.120.91)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.233.120.91)(PORT=33006))))'
MYDB12.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.233.120.92)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.233.120.92)(PORT=33006))))'

Once that was done , the listener knew properly of the taf service.

Happy reading ,

Mathijs