SAINORA

HomeBlogsTns and Listener

Tns And Listener

Registering listener in oracle 11G

Error :
[oracle@oraclehost admin]$ sqlplus system/manager@test

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 21 11:16:06 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

Make sure the belo entry exists ...

[oracle@oraclehost admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

ADR_BASE_LISTENER_TEST = /u01/app/oracle

LISTENER_TEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost)(PORT = 1521))
)
)
[oracle@oraclehost admin]$ cat tnsnames.ora

test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)

You may get the below error when registering ...

SQL> alter system set local_listener='LISTENER_TEST';
alter system set local_listener='LISTENER_TEST'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_TEST'
Solution :

SQL > alter system set LOCAL_LISTENER='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclehost)(PORT=1521))))' sid='test';

[oracle@oraclehost admin]$ sqlplus system/manager@test

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 21 11:14:47 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ==================================================

How will you connect a DB in linux from windows?
First check the connectivity in OS(windows) level using
ping -t 10.3.2.10(linux server)
Then install Oracle Client in Windows and configure tnsnames.ora.
TNS can be configures using netca.

SSS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sss)
)
)

 

Server side, Make sure that listener is configured in listener.ora and it is running.
Listener consists of a header and body like

LISTENER =
...
...

SID_LIST_LISTENER =
...
...
...

then use C:\> tnsping sss to check the response from listener. It is not mandatory that Database should be UP and running.

What is default location of listener.ora and tnsnames.ora ? Can this be changed?

The default location is $ORACLE_HOME/network/admin. This can be changed by using TNS_ADMIN parameter.

What is TNS?

TNS stands for TRANSPARENT NETWORK SUBSTRATE

How will you find how many listeners are running in my server?
$ps -ef|grep -i tns

Is it possible to have more than one listener for one database?
Yes, You can have more than one listener for one database. It is configured as show below.
LISTENER_SSS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sss-oracle)(PORT = 1521))
)
)

SID_LIST_LISTENER_SSS =
(SID_LIST =
(SID_DESC =
(SID_NAME = SSS)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
)
------------------------
LISTENER_SAKTHI =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sss-oracle)(PORT = 1522))
)
)

SID_LIST_LISTENER_SAKTHI =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/product/10.2.0/db_1)
(SID_NAME = SSS)
)
)

Make sure that the port is different.  Start the listener using.

$lsnrctl start listener_sss
$lsnrctl start listener_sakthi

How will you find whether a port is available?
$ netstat -tunlp |grep portnumber.
 
Is it possible to have one listener for more than one database?

Yes, You can have one listener for more than one database. It is configured as show below.
LISTENER_SSS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sss-oracle)(PORT = 1521))
)
)

SID_LIST_LISTENER_SSS =
(SID_LIST =
(SID_DESC =
(SID_NAME = SSS)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
(SID_DESC =
(SID_NAME = Sakthi)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
(SID_DESC =
(SID_NAME = SSS1)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
)

How will you configure single listener for multiple versions of databases?

Just change the ORACLE_HOME and then reload the listener using

$ lsnrctl reload LISTENER_SSS

 

LISTENER_SSS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sss-oracle)(PORT = 1521))
)
)

SID_LIST_LISTENER_SSS =
(SID_LIST =
(SID_DESC =
(SID_NAME = SSS)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
(SID_DESC =
(SID_NAME = Sakthi)
(ORACLE_HOME = /u01/product/9.2.0.4/db_1)
)
(SID_DESC =
(SID_NAME = SSS1)
(ORACLE_HOME = /u01/product/10.1.0/db_1)
)
)
============================================

 

Error : Starting ORACLE instance (normal)
Wed Feb 20 12:00:52 2013
Process m000 died, see its trace file
Wed Feb 20 12:00:52 2013
ksvcreate: Process(m000) creation failed
Wed Feb 20 12:01:53 2013
Process m000 died, see its trace file
Wed Feb 20 12:01:53 2013
ksvcreate: Process(m000) creation failed
Wed Feb 20 12:02:54 2013
Process m000 died, see its trace file
Wed Feb 20 12:02:54 2013
ksvcreate: Process(m000) creation failed
Wed Feb 20 12:03:56 2013
Process m000 died, see its trace file
Wed Feb 20 12:03:56 2013
ksvcreate: Process(m000) creation failed
Wed Feb 20 12:04:57 2013

Ans :

Check you have sufficient resources in OS level, if yes , increase or
increase the processes parameter accordingly.

=================================================
WARNING: inbound connection timed out (ORA-3136)
Wed Feb 20 12:21:06 2013
WARNING: inbound connection timed out (ORA-3136)
Wed Feb 20 12:21:07 2013
WARNING: inbound connection timed out (ORA-3136)
Solution:

In sqlnet.ora set

SQLNET.INBOUND_CONNECT_TIMEOUT= 0 or more value > 120

default value is 60

also
set in listener.ora

INBOUND_CONNECT_TIMEOUT_<Listener-name> = 0 or > 120

============================================

           

Published on 24th March 2012

Published by Gopinathan D