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:
SQL*Plus: Release 220.127.116.11.0 Production on Mon Jul 8 08:05:04 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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 18.104.22.168.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 22.214.171.124.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 :
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:
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 ,