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

6 comments:

  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
  2. Thank you so much it worked for me with me alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora01-vip)(PORT=1524)))' sid='BHU1_1' scope=spfile;

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = PRIMARY)
    (ORACLE_HOME = F:\app\Hegde\product\11.2.0\dbhome_1)
    (SID_NAME = PRIMARY)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = PRIMARY_DGMGRL)
    (ORACLE_HOME = F:\app\Hegde\product\11.2.0\dbhome_1)
    (SID_NAME = PRIMARY)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = STANDBY)
    (ORACLE_HOME = F:\app\Hegde\product\11.2.0\dbhome_1)
    (SID_NAME = STANDBY)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = STANDBY_DGMGRL)
    (ORACLE_HOME = F:\app\Hegde\product\11.2.0\dbhome_1)
    (SID_NAME = STANDBY)
    )
    )
    # listener.ora Network Configuration File: F:\app\Hegde\product\11.2.0\grid\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = hegde-pc)(PORT = 1521))
    )
    )

    ADR_BASE_LISTENER = F:\app\Hegde

    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
    the above is my listener.ora file when I'm try start the listener it shows listener supperts no serveice.please give me solution.
    LSNRCTL> start
    Starting tnslsnr: please wait...

    TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
    Log messages written to f:\app\hegde\diag\tnslsnr\hegde-pc\listener\alert\log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hegde-pc)(PORT=1521)))

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
    Start Date 01-MAY-2016 23:13:14
    Uptime 0 days 0 hr. 0 min. 4 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Log File f:\app\hegde\diag\tnslsnr\hegde-pc\listener\alert\log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hegde-pc)(PORT=1521)))
    The listener supports no services
    The command completed successfully

    ReplyDelete