tns and listener configure linux ---oracle
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
============================================
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
============================================
Comments
Post a Comment