Oracle TNS/Listener Interview questions and Answer
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 below 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 registeringSQL> 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
1.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.
nijam =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nijam)
)
)
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 nijam 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.
2.What is TNS?
TNS stands for TRANSPARENT NETWORK SUBSTRATE
3.How will you find how many listeners are running in my server?
$ps -ef|grep -i tns
4.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_nijam =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nijam-oracle)(PORT = 1521))
)
)
SID_LIST_LISTENER_nijam =
(SID_LIST =
(SID_DESC =
(SID_NAME = nijam)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
)
------------------------
LISTENER_mohammed =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nijam-oracle)(PORT = 1522))
)
)
SID_LIST_LISTENER_mohammed =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/product/10.2.0/db_1)
(SID_NAME = nijam)
)
)
Make sure that the port is different. Start the listener using.$lsnrctl start listener_nijam
$lsnrctl start listener_mohammed
5.How will you find whether a port is available?$ netstat -tunlp |grep portnumber.
6.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_nijam =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nijam-oracle)(PORT = 1521))
)
)
SID_LIST_LISTENER_nijam =
(SID_LIST =
(SID_DESC =
(SID_NAME = nijam)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
(SID_DESC =
(SID_NAME = mohammed)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
(SID_DESC =
(SID_NAME = nijam1)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
)
7.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_nijam
LISTENER_nijam =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nijam-oracle)(PORT = 1521))
)
)
SID_LIST_LISTENER_nijam =
(SID_LIST =
(SID_DESC =
(SID_NAME = nijam)
(ORACLE_HOME = /u01/product/10.2.0/db_1)
)
(SID_DESC =
(SID_NAME = mohammed)
(ORACLE_HOME = /u01/product/9.2.0.4/db_1)
)
(SID_DESC =
(SID_NAME = nijam1)
(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
Solution :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