Friday, February 10, 2012

listener supports no services



I have 2 node RAC system, when try to connect to the database using the TNS-Entry I am got the below error message

oracle> sqlplus system/manager@bhu1

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

When I checked the listener status, it was display as below and specified as no service are running

bhuora01[BHU1_1]>lsnrctl stat LSNR_VIPB_BHU1

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-FEB-2012 11:27:14

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LSNR_VIPB_BHU1)))
STATUS of the LISTENER
------------------------
Alias                     LSNR_VIPB_BHU1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                09-FEB-2012 11:01:32
Uptime                    0 days 0 hr. 25 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/BHU1/11202/network/admin/listener.ora
Listener Log File         /oracle/BHU1/diag/tnslsnr/bhuora01/lsnr_vipb_BHU1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LSNR_VIPB_BHU1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.21.13.17)(PORT=1524)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.21.13.65)(PORT=1524)))
The listener supports no services
The command completed successfully


I try to do “alter system register” and try multiple things. But nothing work out for me.

Reason for no service are displayed in the listener status, No service are register with the listener. To register the service in the listener, we need to configure local_listener,remote_listener & listener_networks properly


Note: I am configuring the listener_networks, because I use a second IP or different IP for the data guard services.

Please find same local_listener, remote_listener & listener_network parameters

alter system set listener_networks='((name=BHU1_n1)(local_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora01-dg-vip)(PORT=1521)))) (remote_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora02-dg-vip)(PORT=1521)))))' sid='BHU1_1' scope=spfile;

alter system set listener_networks='((name=BHU1_n2)(local_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora02-dg-vip)(PORT=1521)))) (remote_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora01-dg-vip)(PORT=1521)))))' sid='BHU1_2' scope=spfile;


alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora01-vip)(PORT=1524)))' sid='BHU1_1' scope=spfile;

alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora02-vip)(PORT=1524)))' sid='BHU1_2' scope=spfile;

alter system set remote_listener='racorabhua-scan:1529' SID='ZE1_1' scope=spfile;
alter system set remote_listener='racorabhua-scan:1529' SID='ZE1_2' scope=spfile;


Once you restart the database, if you see the status of the listener. We should see the service up and running


If you still feel that service are running, then issue the below command on each instance

SQL> ALTER SYSTEM REGISTER;

bhuora01[BHU1_1]>lsnrctl stat LSNR_VIPB_BHU1

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-FEB-2012 12:56:12

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LSNR_VIPB_BHU1)))
STATUS of the LISTENER
------------------------
Alias                     LSNR_VIPB_BHU1
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                09-FEB-2012 12:55:29
Uptime                    0 days 0 hr. 0 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/GRID/11202/network/admin/listener.ora
Listener Log File         /oracle/BASE/diag/tnslsnr/bhuora01/lsnr_vipb_BHU1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LSNR_VIPB_BHU1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.21.13.17)(PORT=1524)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.21.13.65)(PORT=1524)))
Services Summary...
Service "BHU1_B" has 1 instance(s).
  Instance "BHU1_1", status READY, has 1 handler(s) for this service...
Service "BHU1_B.UK.CENTRICAPLC.COM" has 1 instance(s).
  Instance "BHU1_1", status READY, has 1 handler(s) for this service...
The command completed successfully
bhuora01[BHU1_1]>

Hope this help you. Happy learning

1 comment:

  1. Another solution:

    Go to
    1.Net manager->Listeners->(click on Your Listener)->In the top dropdown list select Database services
    2.Key in the correct input for the fields.

    Ex.
    1.Database global name :tc
    2.oralce-Home: E:\oracle_installation\product\11.1.0\db_3
    3.SID:tc

    ReplyDelete