Hi Guys,
This article will explain how to configure the listeners and tnsnames as well as how to check the service status.
Basically listeners registration will happen in two ways.
1. Dynamic listeners
2.Static listeners
While installing the oracle database software if you choose database creation as a option at that time database will create a default listener, its name is LISTENER and that is dynamic listener. After that when your creating extra listeners all those will be part of static listeners.
The major difference between dynamic listener and static listener:
Whenever we are bringing up the database , the listener automatically bringup its services also. But in static listener we need to manually start the listeners and need to check the its status also.
From oracle 11g Release 2 onwards, oracle given a option that we can create dynamic listeners which is created for user requirement.
Basically using netca command we can create listeners and tnsnames . In unix flavour or windows flavour netca command is available in $ORACLE_HOME/bin directory.
PMON will search for tnsnames.ora in the following order:
How to create listeners using Putty or SILENT MODE
Step1: Copy the netca response file[file name is netca_typ.rsp] to some other location.
Login through putty,
copy the file from $ORACLE_HOME/network/install
#su - oracle
$. oraenv
$cd $ORACLE_HOME/network/install
$pwd
/u01/app/oracle/product/11.2.0.3/server/network/install
$ cp netca_typ.rsp /u01/app/oracle
Step2 : Modify the response file
$cd /u01/app/oracle
$vi netca_typ.rsp
Ex: Below is the file content
[GENERAL]
RESPONSEFILE_VERSION="11.2"
CREATE_TYPE="CUSTOM"
[Session]
TOPLEVEL_COMPONENT={"oracle.net.ca","11.2"}
[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""custom""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER_DB11G_1590"}
LISTENER_PROTOCOLS={"TCP;1590"}
LISTENER_START=""LISTENER_DB11G_1590""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
Based on your requirement change LISTENER_NAMES, LISTENER_PROTOCOLS's port number, LISTENER_START values and save it.
Step 3: Configure the listener
Got to $ORACLE_HOME/bin
$cd $ORACLE_HOME/bin
[oracle@inatechsrv bin]$ ./netca /silent /responsefile /mnt/netca_typ.rsp
Parsing command line arguments:
Parameter "silent" = true
Parameter "responsefile" = /mnt/netca_typ.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Configuring Listener:LISTENER_DB11G_1590
Listener configuration complete.
Oracle Net Listener Startup:
Running Listener Control:
/u01/app/oracle/product/11.2.0.3/server/bin/lsnrctl start LISTENER_DB11G_1590
Listener Control complete.
Listener started successfully.
Profile configuration complete.
Oracle Net Services configuration successful. The exit code is 0
[oracle@inatechsrv bin]$ lsnrctl status LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 13:10:38
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DB11G_1590
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 01-MAR-2012 13:10:24
Uptime 0 days 0 hr. 0 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.3/server/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/inatechsrv/listener_db11g_1590/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590)))
The listener supports no services
Now listener is create but still its showing NO SUPPORT SERVICEs it means this listener will not accept the new connections. Now You change as either static listener or dynamic listener to accept the new connections .
STEP4:
--------------------------------------------
Case 1: STATIC LISTENER
Step1:
If you want static listener , go to listener.ora check current listener entry ,it should be similar to below entry.
Note: In Oracle 11g Release 2 , by default it will show dynamic listener format.
If you want to enable as static listener , change your LISTENER as similiar as below. May be some entries will not be there just add it.
Ex:
LISTENER_DB11G_1590 =(ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))
Step3: Add the listener entry into Initialization parameter file .
If you want dynamic listener , we need to set LOCAL_LISTENER parameter
Note: If database uses spfile then
$sqlplus '/as sysdba'
SQL>alter system set local_listener=LISTENER_DB11G_1590 scope=spfile;
SQL>shut immediate
SQL>startup
Note: If you want you can set multiple listeners as a dynamic listeners
SQL>alter system set local_listener=LISTENER_DB11G_1590,LISTENER_DB11G_1600 scope=spfile;
When database is bringup pmon will check these listeners entry is available or not in tnsnames.ora . If not then database will not UP. I hope now you understand while listener name entry should present in TNSNAMES.ORA file.
If database using pfile, just open the pfile and add the entry, save it. Rebounce the database.
step4: Check the listener status
[oracle@inatechsrv admin]$ lsnrctl status LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 13:52:15
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DB11G_1590
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 22-FEB-2012 11:15:34
Uptime 8 days 2 hr. 36 min. 41 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.3/server/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/inatechsrv/listener_db11g_1590/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.com" has 1 instance(s).
Instance "DB11G", status READY, has 1 handler(s) for this service...
The command completed successfully
Note: Status READY means its dynamic listener.
HOWEVER , For same database we can create multiple listeners, we can configure some listeners as dynamic and some listeners as static.
----------------------------------------------------------
STEP5: Configure the tnsname entry
When you create listeners automatically tnsnames. ora file also creating.
Tnsnames entry required if you want to access the database remotely otherwise no need to worry for this step.
As per oracle 11g Release 2
tnsname.ora entry should be
DB11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G.amk.com)
)
)
This service name should be equavalent to database service name.
To find database service name
SQL>show parameter services_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string DB11G.amk.com
Note: If these two things not matches means your connection will not establish.
If you have multiple listeners then net service would be
ex:
DB11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))
(ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1580))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G.amk.com)
)
)
As per above example same database using two listeners and two ports, it can either static or dynamic or both.
STEP6: Check the listener services
[oracle@inatechsrv admin]$ lsnrctl service LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 14:47:14
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.amk.com" has 1 instance(s).
Instance "DB11G", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
Here ESTABLISHED IS 0 means no connections till now.
[oracle@inatechsrv admin]$ sqlplus sys@DB11G
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 14:47:22 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> !
[oracle@inatechsrv admin]$ lsnrctl service LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 14:47:39
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.amk.com" has 1 instance(s).
Instance "DB11G", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[oracle@inatechsrv admin]$
Here established is 1 means one connection is accepted. If still shows its zero means some where you missed one step.
Now listener is configured and its accepting new connections from remotely also.
[oracle@inatechsrv admin]$ tnsping DB11G
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 15:04:07
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.3/server/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))) (CONNECT_DATA = (SERVICE_NAME = DB11G.amk.com)))
OK (0 msec)
You have new mail in /var/spool/mail/oracle
[oracle@inatechsrv admin]$
Note: OK means its able to reach within 0 msecs. We can accept upto 20msec. For above 20msecs, we say its connectivity issue it can network or database performance issue.
[oracle@inatechsrv admin]$ trcroute DB11G
Trace Route Utility for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 15:07:13
Copyright (c) 1995, 2011, Oracle. All rights reserved.
Route of TrcRoute:
------------------
Node: Client Time and address of entry into node:
-------------------------------------------------------------
01-MAR-2012 15:07:13 ADDRESS= PROTOCOL=TCP HOST=inatechsrv.blr.com PORT=1590
Node: Server Time and address of entry into node:
-------------------------------------------------------------
01-MAR-2012 15:07:13
SOME ISSUES
1. SQL> startup nomount
Issue:
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
Solution: Local_listener parameter is not setted in parameter file.
2.
Issue:
SQL>STARTUP;ORA-00119: invalid specification for system parameter LOCAL_LISTENERORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))
Cause: The listener address specification is not valid.
Action: Make sure that all fields in the listener address (protocol, port, host, key, ...) are correct.
3.
Issue: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.
Solution: Service names not matching.
4.
Issue: The listener support no services.
Solution: PMON not yet registered. Local_listener parameter is not matching with its listener.
Let us know if any doubt on this document. Please contact me murali9231@gmail.com
This article will explain how to configure the listeners and tnsnames as well as how to check the service status.
Basically listeners registration will happen in two ways.
1. Dynamic listeners
2.Static listeners
While installing the oracle database software if you choose database creation as a option at that time database will create a default listener, its name is LISTENER and that is dynamic listener. After that when your creating extra listeners all those will be part of static listeners.
The major difference between dynamic listener and static listener:
Whenever we are bringing up the database , the listener automatically bringup its services also. But in static listener we need to manually start the listeners and need to check the its status also.
From oracle 11g Release 2 onwards, oracle given a option that we can create dynamic listeners which is created for user requirement.
Basically using netca command we can create listeners and tnsnames . In unix flavour or windows flavour netca command is available in $ORACLE_HOME/bin directory.
PMON will search for tnsnames.ora in the following order:
- $HOME/.tnsnames.ora
- $TNS_ADMIN/tnsnames.ora
- /var/opt/oracle/tnsnames.ora or /etc/tnsnames.ora (depending on platform)
- $ORACLE_HOME/network/admin/tnsnames.ora
How to create listeners using Putty or SILENT MODE
Step1: Copy the netca response file[file name is netca_typ.rsp] to some other location.
Login through putty,
copy the file from $ORACLE_HOME/network/install
#su - oracle
$. oraenv
$cd $ORACLE_HOME/network/install
$pwd
/u01/app/oracle/product/11.2.0.3/server/network/install
$ cp netca_typ.rsp /u01/app/oracle
Step2 : Modify the response file
$cd /u01/app/oracle
$vi netca_typ.rsp
Ex: Below is the file content
[GENERAL]
RESPONSEFILE_VERSION="11.2"
CREATE_TYPE="CUSTOM"
[Session]
TOPLEVEL_COMPONENT={"oracle.net.ca","11.2"}
[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""custom""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER_DB11G_1590"}
LISTENER_PROTOCOLS={"TCP;1590"}
LISTENER_START=""LISTENER_DB11G_1590""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
Based on your requirement change LISTENER_NAMES, LISTENER_PROTOCOLS's port number, LISTENER_START values and save it.
Step 3: Configure the listener
Got to $ORACLE_HOME/bin
$cd $ORACLE_HOME/bin
[oracle@inatechsrv bin]$ ./netca /silent /responsefile /mnt/netca_typ.rsp
Parsing command line arguments:
Parameter "silent" = true
Parameter "responsefile" = /mnt/netca_typ.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Configuring Listener:LISTENER_DB11G_1590
Listener configuration complete.
Oracle Net Listener Startup:
Running Listener Control:
/u01/app/oracle/product/11.2.0.3/server/bin/lsnrctl start LISTENER_DB11G_1590
Listener Control complete.
Listener started successfully.
Profile configuration complete.
Oracle Net Services configuration successful. The exit code is 0
[oracle@inatechsrv bin]$ lsnrctl status LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 13:10:38
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DB11G_1590
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 01-MAR-2012 13:10:24
Uptime 0 days 0 hr. 0 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.3/server/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/inatechsrv/listener_db11g_1590/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590)))
The listener supports no services
Now listener is create but still its showing NO SUPPORT SERVICEs it means this listener will not accept the new connections. Now You change as either static listener or dynamic listener to accept the new connections .
STEP4:
--------------------------------------------
Case 1: STATIC LISTENER
Step1:
If you want static listener , go to listener.ora check current listener entry ,it should be similar to below entry.
Note: In Oracle 11g Release 2 , by default it will show dynamic listener format.
If you want to enable as static listener , change your LISTENER as similiar as below. May be some entries will not be there just add it.
Ex:
LISTENER_DB11G_1590= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590)))) SID_LIST_LISTENER_DB11G_1590= (SID_LIST= (SID_DESC= (SID_NAME=DB11G) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/server) (PROGRAM=extproc)))
ADR_BASE_LISTENER_DB11G_1590 = /u01/app/oracle
Step2: Check the listener services
$lsnrctl status LISTENER_DB_1590
it will show
[oracle@inatechsrv admin]$ lsnrctl status LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 13:36:43
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr..com)(PORT=1590)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DB11G_1590
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 22-FEB-2012 11:15:34
Uptime 8 days 2 hr. 21 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.3/server/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/inatechsrv/listener_db11g_1580/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.amk.com" has 1 instance(s).
Instance "DB11G", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@inatechsrv admin]$
STATUS of the LISTENER
------------------------
Alias LISTENER_DB11G_1590
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 22-FEB-2012 11:15:34
Uptime 8 days 2 hr. 21 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.3/server/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/inatechsrv/listener_db11g_1580/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.amk.com" has 1 instance(s).
Instance "DB11G", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@inatechsrv admin]$
Now listener is fine, it will accept the incoming connection. Note: UNKNOWN means its static listener
Case 2: DYNAMIC LISTENER
Step1:
By default in oracle 11g release 2 onwards If you are creating any listeners, we will get dynamic listener configuration format.
It should be similar to
ADR_BASE_LISTENER_DB11G_1580 = /u01/app/oracle
LISTENER_DB11G_1590 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590)) ) )
Step 2: Check in tnsnames.ora file whether below entry is there or not.
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590)) ) )
Step 2: Check in tnsnames.ora file whether below entry is there or not.
If not just add similar to below
Step3: Add the listener entry into Initialization parameter file .
If you want dynamic listener , we need to set LOCAL_LISTENER parameter
Note: If database uses spfile then
$sqlplus '/as sysdba'
SQL>alter system set local_listener=LISTENER_DB11G_1590 scope=spfile;
SQL>shut immediate
SQL>startup
Note: If you want you can set multiple listeners as a dynamic listeners
SQL>alter system set local_listener=LISTENER_DB11G_1590,LISTENER_DB11G_1600 scope=spfile;
When database is bringup pmon will check these listeners entry is available or not in tnsnames.ora . If not then database will not UP. I hope now you understand while listener name entry should present in TNSNAMES.ORA file.
If database using pfile, just open the pfile and add the entry, save it. Rebounce the database.
step4: Check the listener status
[oracle@inatechsrv admin]$ lsnrctl status LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 13:52:15
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DB11G_1590
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 22-FEB-2012 11:15:34
Uptime 8 days 2 hr. 36 min. 41 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.3/server/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/inatechsrv/listener_db11g_1590/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.com" has 1 instance(s).
Instance "DB11G", status READY, has 1 handler(s) for this service...
The command completed successfully
Note: Status READY means its dynamic listener.
HOWEVER , For same database we can create multiple listeners, we can configure some listeners as dynamic and some listeners as static.
----------------------------------------------------------
STEP5: Configure the tnsname entry
When you create listeners automatically tnsnames. ora file also creating.
Tnsnames entry required if you want to access the database remotely otherwise no need to worry for this step.
As per oracle 11g Release 2
tnsname.ora entry should be
DB11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G.amk.com)
)
)
This service name should be equavalent to database service name.
To find database service name
SQL>show parameter services_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string DB11G.amk.com
Note: If these two things not matches means your connection will not establish.
If you have multiple listeners then net service would be
ex:
DB11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))
(ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1580))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G.amk.com)
)
)
As per above example same database using two listeners and two ports, it can either static or dynamic or both.
STEP6: Check the listener services
[oracle@inatechsrv admin]$ lsnrctl service LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 14:47:14
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.amk.com" has 1 instance(s).
Instance "DB11G", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
Here ESTABLISHED IS 0 means no connections till now.
[oracle@inatechsrv admin]$ sqlplus sys@DB11G
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 14:47:22 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> !
[oracle@inatechsrv admin]$ lsnrctl service LISTENER_DB11G_1590
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 14:47:39
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=inatechsrv.blr.com)(PORT=1590)))
Services Summary...
Service "DB11G.amk.com" has 1 instance(s).
Instance "DB11G", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[oracle@inatechsrv admin]$
Here established is 1 means one connection is accepted. If still shows its zero means some where you missed one step.
Now listener is configured and its accepting new connections from remotely also.
Using the TNSPING Utility to Test Connectivity from the Client
The TNSPING utility determines whether the listener for a service on an Oracle Net network can be reached successfully.[oracle@inatechsrv admin]$ tnsping DB11G
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 15:04:07
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.3/server/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = inatechsrv.blr.com)(PORT = 1590))) (CONNECT_DATA = (SERVICE_NAME = DB11G.amk.com)))
OK (0 msec)
You have new mail in /var/spool/mail/oracle
[oracle@inatechsrv admin]$
Note: OK means its able to reach within 0 msecs. We can accept upto 20msec. For above 20msecs, we say its connectivity issue it can network or database performance issue.
Using the TRCROUTE Utility to Test Connectivity from the Client
The Trace Route Utility (TRCROUTE), in Linux and UNIX environments, enables administrators to discover the path or route a connection is taking from a client to a server. If TRCROUTE encounters a problem, then it returns an error stack to the client .[oracle@inatechsrv admin]$ trcroute DB11G
Trace Route Utility for Linux: Version 11.2.0.3.0 - Production on 01-MAR-2012 15:07:13
Copyright (c) 1995, 2011, Oracle. All rights reserved.
Route of TrcRoute:
------------------
Node: Client Time and address of entry into node:
-------------------------------------------------------------
01-MAR-2012 15:07:13 ADDRESS= PROTOCOL=TCP HOST=inatechsrv.blr.com PORT=1590
Node: Server Time and address of entry into node:
-------------------------------------------------------------
01-MAR-2012 15:07:13
SOME ISSUES
1. SQL> startup nomount
Issue:
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
Solution: Local_listener parameter is not setted in parameter file.
2.
Issue:
SQL>STARTUP;ORA-00119: invalid specification for system parameter LOCAL_LISTENERORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))
Cause: The listener address specification is not valid.
Action: Make sure that all fields in the listener address (protocol, port, host, key, ...) are correct.
3.
Issue: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.
Solution: Service names not matching.
4.
Issue: The listener support no services.
Solution: PMON not yet registered. Local_listener parameter is not matching with its listener.
Let us know if any doubt on this document. Please contact me murali9231@gmail.com
No comments:
Post a Comment
Share your knowledge it really improves, don't show off...