Difference between revisions of "TNS"
From dbawiki
m |
|||
| Line 3: | Line 3: | ||
* [http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/ diagnosing connection issues - edstevensdba.wordpress.com] | * [http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/ diagnosing connection issues - edstevensdba.wordpress.com] | ||
* [http://edstevensdba.wordpress.com/2011/03/19/ora-12514/ tracking down solutions to tns error messages - edstevensdba.wordpress.com] | * [http://edstevensdba.wordpress.com/2011/03/19/ora-12514/ tracking down solutions to tns error messages - edstevensdba.wordpress.com] | ||
| + | |||
| + | ===Connections fail 10% of the time. How to fix?=== | ||
| + | There are some settings that can help... | ||
| + | ====sqlnet.ora==== | ||
| + | Setting a value greater than 0 ensures that connections are not left open indefinitely, due to, for example, an abnormal client termination. | ||
| + | <pre> | ||
| + | # sqlnet.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/sqlnet.ora | ||
| + | # Generated by Oracle configuration tools. | ||
| + | |||
| + | NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) | ||
| + | |||
| + | ADR_BASE = /oracle/product | ||
| + | |||
| + | DIAG_ADR_ENABLED=OFF | ||
| + | |||
| + | sqlnet.expire_time=10 | ||
| + | DEFAULT_SDU_SIZE=11280 | ||
| + | </pre> | ||
| + | ====tnsnames.ora==== | ||
| + | Setting the local_listener in the database to the connection string instead of the connect identifier can help | ||
| + | <pre> | ||
| + | # tnsnames.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/tnsnames.ora | ||
| + | # Generated by Oracle configuration tools. | ||
| + | |||
| + | LISTENER_FEUK11UD = | ||
| + | (ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521)) | ||
| + | |||
| + | |||
| + | FEUK11UD = | ||
| + | (DESCRIPTION = | ||
| + | (ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521)) | ||
| + | (CONNECT_DATA = | ||
| + | (SERVER = DEDICATED) | ||
| + | (SERVICE_NAME = FEUK11UD) | ||
| + | ) | ||
| + | ) | ||
| + | </pre> | ||
| + | <pre> | ||
| + | alter system set local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))" scope=both; | ||
| + | </pre> | ||
| + | |||
| + | ====listener.ora==== | ||
| + | Setting STARTUP_WAIT_TIME_LISTENER | ||
| + | Setting CONNECT_TIMEOUT_LISTENER | ||
| + | <pre> | ||
| + | # listener.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/listener.ora | ||
| + | # Generated by Oracle configuration tools. | ||
| + | |||
| + | STARTUP_WAIT_TIME_LISTENER_FEUK11UD = 0 | ||
| + | CONNECT_TIMEOUT_LISTENER_FEUK11UD = 30 | ||
| + | LISTENER_FEUK11UD = | ||
| + | (DESCRIPTION_LIST = | ||
| + | (DESCRIPTION = | ||
| + | (ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521)) | ||
| + | (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) | ||
| + | ) | ||
| + | ) | ||
| + | |||
| + | ADR_BASE_LISTENER_FEUK11UD = /oracle/product | ||
| + | </pre> | ||
Revision as of 16:03, 24 November 2015
- static and dynamic listener registration - artincarstenbach.wordpress.com
- Exploring the LOCAL_LISTENER parameter - edstevensdba.wordpress.com
- diagnosing connection issues - edstevensdba.wordpress.com
- tracking down solutions to tns error messages - edstevensdba.wordpress.com
Contents
Connections fail 10% of the time. How to fix?
There are some settings that can help...
sqlnet.ora
Setting a value greater than 0 ensures that connections are not left open indefinitely, due to, for example, an abnormal client termination.
# sqlnet.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) ADR_BASE = /oracle/product DIAG_ADR_ENABLED=OFF sqlnet.expire_time=10 DEFAULT_SDU_SIZE=11280
tnsnames.ora
Setting the local_listener in the database to the connection string instead of the connect identifier can help
# tnsnames.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_FEUK11UD =
(ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))
FEUK11UD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FEUK11UD)
)
)
alter system set local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))" scope=both;
listener.ora
Setting STARTUP_WAIT_TIME_LISTENER Setting CONNECT_TIMEOUT_LISTENER
# listener.ora Network Configuration File: /oracle/product/11.2.0.4/network/admin/listener.ora
# Generated by Oracle configuration tools.
STARTUP_WAIT_TIME_LISTENER_FEUK11UD = 0
CONNECT_TIMEOUT_LISTENER_FEUK11UD = 30
LISTENER_FEUK11UD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HFE2PrePrdOra)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER_FEUK11UD = /oracle/product
