ISSUE:-
Many times we encountered an issue where the database listener was unable to connect to DB service although the associated database service is up and running.I am trying to connect to database it is throwing no listener error
[ora12c@server1 ~]$ sqlplus scott@DBname/*******;
SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 16 15:25:25 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS:no listener
Enter user-name: ^C
[ora12c@server1 ~]
When we try to check the status of listener , it throws below error.
[ora12c@server1 ~]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-NOV-2017 17:18:00
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-NOV-2017 15:14:35
Uptime 0 days 2 hr. 3 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /rdbms/app/12.1.0/grid/network/admin/listener.ora
Listener Log File /rdbms/app/12.1.0/grid_base/diag/tnslsnr/server1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.co.in)(PORT=1521)))
Services Summary...
The listener supports no services
The command completed successfully
To overcome issue , I found the below solution. It worked successfully in my environment & listener started listening to a port.
SOLUTION:-
Step 1 :- Assign similar port in listener.ora and tnsnames.ora file . In my case I am using 1521.
Export the ASM profile and make an appropriate port changes in the listener.ora
Export the DB profile and make an appropriate port changes in the tnsnames.ora
Step 2:- Set the local_listener parameter in the database.
[ora12c@server1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 16 13:47:38 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL>
SQL>
SQL> sh ow parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
Here you can see the local_listener parameter is set to NULL.
Run the below command to set local_listener parameter.
select 'alter system set local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=' || host_name || ')(PORT = 1521))" scope=both;' from v$instance; // Select appropriate port number
SQL> select 'alter system set local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=' || host_name || ')(PORT = 1521))" scope=both;' from v$instance;
'ALTERSYSTEMSETLOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST='||HOST_NAME||')(POR
--------------------------------------------------------------------------------
alter system set local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=server1
.co.in)(PORT = 1521))" scope=both;
SQL> alter system set local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=server1.co.in)(PORT = 1521))" scope=both;
System altered.
SQL>
SQL>
Step 3:- Once all above conditions has fulfilled , start the listener and check the status of the listener.
[ora12c@server1 ~]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-NOV-2017 15:16:08
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.co.in)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-NOV-2017 15:14:35
Uptime 0 days 0 hr. 1 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /rdbms/app/12.1.0/grid/network/admin/listener.ora
Listener Log File /rdbms/app/12.1.0/grid_base/diag/tnslsnr/server1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.co.in)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "DBName" has 1 instance(s).
Instance "DBName", status READY, has 1 handler(s) for this service...
Service "DBNameXDB" has 1 instance(s).
Instance "DBName", status READY, has 1 handler(s) for this service...
The command completed successfully.
Check are you able to connect to DB by sqlplus
[ora12c@server1 ~]$
[ora12c@server1 ~]$ sqlplus scott@DBname/*******;
SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 17 14:23:15 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri Nov 17 2017 01:52:41 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL>
SQL>
Its successful , that's it
Many times we encountered an issue where the database listener was unable to connect to DB service although the associated database service is up and running.I am trying to connect to database it is throwing no listener error
[ora12c@server1 ~]$ sqlplus scott@DBname/*******;
SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 16 15:25:25 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS:no listener
Enter user-name: ^C
[ora12c@server1 ~]
When we try to check the status of listener , it throws below error.
[ora12c@server1 ~]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-NOV-2017 17:18:00
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-NOV-2017 15:14:35
Uptime 0 days 2 hr. 3 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /rdbms/app/12.1.0/grid/network/admin/listener.ora
Listener Log File /rdbms/app/12.1.0/grid_base/diag/tnslsnr/server1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.co.in)(PORT=1521)))
Services Summary...
The listener supports no services
The command completed successfully
To overcome issue , I found the below solution. It worked successfully in my environment & listener started listening to a port.
SOLUTION:-
Step 1 :- Assign similar port in listener.ora and tnsnames.ora file . In my case I am using 1521.
Export the ASM profile and make an appropriate port changes in the listener.ora
Export the DB profile and make an appropriate port changes in the tnsnames.ora
Step 2:- Set the local_listener parameter in the database.
[ora12c@server1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 16 13:47:38 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL>
SQL>
SQL> sh ow parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
Here you can see the local_listener parameter is set to NULL.
Run the below command to set local_listener parameter.
select 'alter system set local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=' || host_name || ')(PORT = 1521))" scope=both;' from v$instance; // Select appropriate port number
SQL> select 'alter system set local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=' || host_name || ')(PORT = 1521))" scope=both;' from v$instance;
'ALTERSYSTEMSETLOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST='||HOST_NAME||')(POR
--------------------------------------------------------------------------------
alter system set local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=server1
.co.in)(PORT = 1521))" scope=both;
SQL> alter system set local_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=server1.co.in)(PORT = 1521))" scope=both;
System altered.
SQL>
SQL>
Step 3:- Once all above conditions has fulfilled , start the listener and check the status of the listener.
[ora12c@server1 ~]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-NOV-2017 15:16:08
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.co.in)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-NOV-2017 15:14:35
Uptime 0 days 0 hr. 1 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /rdbms/app/12.1.0/grid/network/admin/listener.ora
Listener Log File /rdbms/app/12.1.0/grid_base/diag/tnslsnr/server1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.co.in)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "DBName" has 1 instance(s).
Instance "DBName", status READY, has 1 handler(s) for this service...
Service "DBNameXDB" has 1 instance(s).
Instance "DBName", status READY, has 1 handler(s) for this service...
The command completed successfully.
Check are you able to connect to DB by sqlplus
[ora12c@server1 ~]$
[ora12c@server1 ~]$ sqlplus scott@DBname/*******;
SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 17 14:23:15 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri Nov 17 2017 01:52:41 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL>
SQL>
Its successful , that's it
Excellent .. Worked for Me !
ReplyDeleteVery helpful blog ,simple and direct solution
ReplyDeleteThanks, Worked for me.
ReplyDeleteThank you very much , it's working :)
ReplyDeleteNot Working
ReplyDelete