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
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
Another solution:
ReplyDeleteGo 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
Thank you, that works....
DeleteThank 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;
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteSID_LIST_LISTENER =
ReplyDelete(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
Not working
ReplyDelete