TCP Parameter in SQLNET file of Oracle

TCP Parameter in SQLNET file of Oracle

TCP.CONNECT_TIMEOUT:
Parameter value specify the time for a client to establish a TCP connection with the database Server.Default value is 60 seconds.
If connection is not established in specified time then following error occurred:
ORA-12170: TNS:Connect timeout occurred

Example:

TCP.CONNECT_TIMEOUT=30 ms

TCP.VALIDNODE_CHECKING
Valid node checking is used to enable or disable the verify the nodes in incoming connection.
Parameter value is YES, then incoming connections are allowed only if they stasfiy the values of both parameters TCP.INVITED_NODES or
TCP.EXCLUDED_NODES parameters. Default value is NO.
Example in SQLNET.ora file

TCP.VALIDNODE_CHECKING=yes

TCP.INVITED_NODES
In this parameter we specify the value or IP address or Host-name of the client which will allow to make connection with database. Parameter works with validnode_checking value is YES.

Example:

TCP.INVITED_NODES=(ORCL.oracle.com,192.0.*,2321:BD4:3456:566A/32)

Note: Parameter supports IPv4 , IPv6 and wild-cards character for multiple entries

TCP.EXCLUDED_NODES
In this parameter we specify that address which deny access to the database. you can specify host-name and ip address as values. Parameter works with validnode_checking value is YES.

Example:

TCP.EXCLUDED_NODES=(orcl.oracle.com,192.10.10.1,172.10.*,2321:BD4:3456:566A/32)

Note: Parameter supportsvIPv4 , IPv6 and wildcards character for multiple entries

Advertisements

INBOUND CONNECT TIMEOUT Parameter in SQLNET file of Oracle

INBOUND_CONNECT_TIMEOUT Parameter in SQLNET file of Oracle

Time need for a client to make connection with the Database Server and authenticate it.
If client failed to establish and authenticate the connection in given time then database terminate the connection.
Value sepecified in this parameter is ms, sec or min. Default value is 60 seconds.

Get the following errors:
ORA-12170: TNS:Connect timeout occurred
ORA-12547: TNS:lost contact
ORA-12637: Packet receive failed

Oracle recommends to set parameter of listener and sqlnet.ora file both
INBOUND_CONNECT_TIMEOUT_listener_name parameter
SQLNET.INBOUND_CONNECT_TIMEOUT parameter

Note: Value for INBOUND_CONNECT_TIMEOUT_listener_name parameter is set lower than SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

Example

SQLNET.INBOUND_CONNECT_TIMEOUT=60

Note: if you do not specified the ms, sec or min then it will pick default as seconds.

EXPIRE TIME Parameter in SQLNET file of Oracle

SQLNET.EXPIRE_TIME Parameter in SQLNET file of Oracle

Expire time parameter is used to limit the time of inactive connection between Server/Client connectivity. If connection is ideal for that particular time then it will disconnect by oracle with help of this parameter.
Default value of this parameter is 0 means unlimited and specify the value of time limit in this parameter is in minutes. If you specify value as 10 then its 10 minutes.
Oracle Net Services automatically uses the enhanced detection model and tunes the TCP keepalive parameters.

Note: It is client file so use this in Client side.

Default value

SQLNET.EXPIRE_TIME=0

Recommended

SQLNET.EXPIRE_TIME=10

AUTHENTICATION SERVICES Parameter in SQLNET file of Oracle

AUTHENTICATION_SERVICES Parameter in SQLNET file of Oracle

Authentication Service parameter is used to list the authentication method of connectivity with oracle database.

SQLNET.AUTHENTICATION_SERVICES value all the server attempts to authenticate using each of the following methods.

Authentication Method available in Oracle:
NONE for no authentication method. When parameter is set to none, a valid user name and password can be used to access the database.
ALL for all authentication methods. Default value is all.
BEQ for native operating system authentication for operating systems other than Microsoft Windows
KERBEROS5 for Kerberos authentication
NTS for Microsoft Windows native operating system authentication
RADIUS for Remote Authentication Dial-In User Service (RADIUS) authentication
TCPS for SSL authentication

DEFAULT VALUE

SQLNET.AUTHENTICATION_SERVICES = ALL

Example of windows Server

1. Value for SQLNET.AUTHENTICATION_SERVICES is NTS.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

2. Try to connect with SQLPLUS with OS User then its connected.

C:\Users\e3019447>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 12 14:26:38 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

3. Change the value of parameter to NONE.

SQLNET.AUTHENTICATION_SERVICES = (NONE)

4. It will give the permission error while tried to connect again with OS user.

C:\Users\e3019447>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 12 14:26:58 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

ALLOWED LOGON VERSION CLIENT & SERVER Parameter in SQLNET

ALLOWED_LOGON_VERSION_CLIENT & ALLOWED_LOGON_VERSION_SERVER Parameter in SQLNET file of Oracle

ALLOWED_LOGON_VERSION_CLIENT
Set the minimum authentication protocol allowed for clients and when a server is acting as a client, such as connecting over a database link, when connecting to Oracle Database instances.

Note: VERSION refers to the version of the authentication protocol, not the Oracle Database release.

Error Occurred
If the version does not meet or exceed the value defined by this parameter, then
authentication fails and following errors will generated:
ORA-28040: No matching authentication protocol error

Parameter Value:
12a for Oracle Database 12c Release 1 (12.1.0.2) or later (strongest protection)
12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (stronger protection)
11 for Oracle Database 11g authentication protocols (default)
10 for Oracle Database 10g authentication protocols
8 for Oracle8i authentication protocol

Example
If an Oracle Database 12c database hosts a database link to an Oracle Database 10g
database, then the SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameter should be set as
follows in order for the database link connection to proceed:

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

ALLOWED_LOGON_VERSION_SERVER Parameter
Set the minimum authentication protocol allowed when connecting to Oracle Database instances with password version.
Check the password version with dba_users table with column password_versions.

Error Occurred
ORA-28040: No matching authentication protocol error
ORA-03134: Connections to this server version are no longer supported error

Note:
If value of parameter is set to 8 it will permits all password versions and allows any combination of the DBA_USERS.PASSWORD_VERSIONS values 10G, 11G, and 12C
A setting of 12a permits only the 12C password version. List of password versions can be seen in DBA_USERS.PASSWORD_VERSIONS.

Example
If the client uses Oracle Database 10g, then the client will receive an ORA-03134: Connections to this server version are no longer supported error message.
To allow the connection, set the SQLNET.ALLOWED_LOGON_VERSION_SERVER value to 8.
Ensure the DBA_USERS.PASSWORD_VERSIONS value for the account contains the value 10G.
It may be necessary to reset the password for that account

SQLNET.ALLOWED_LOGON_VERSION_SERVER values
12a For oracle 12.1.0.2 or later connect to server. Password version is 12c
12 For Oracle 11.2.0.3 or later connect to server. Password version is 11g,12C
11 For Oracle 10g or later connect to server. Password version is 10G, 11G, 12C
10 For Oracle 10g or later connect to server. Password version is 10G, 11G, 12C
9 For Oracle 9i or later connect to server. Password version is 10G, 11G, 12C
8 For Oracle 8i or later connect to server. Password version is 10G, 11G, 12C

Default Value is

SQLNET.ALLOWED_LOGON_VERSION_SERVER=12

It means you can connect with 11.2.0.3 or later version to the Server and Password version belong to 11g or 12c.

NAMES.DIRECTORY_PATH Parameter use in SQLNET.ORA in Oracle

NAMES.DIRECTORY_PATH Parameter use in SQLNET.ORA in Oracle

USe of NAMES.DIRECTORY_PATH parameter in SQLNET.ora is to specified the order of the naming method used for client name resolution.

Default value in order is NAMES.DIRECTORY_PATH=(tnsnames, ldap, ezconnect)

Naming Method value and description
TNSNAMES : Local Naming Method set to resolve the service name with tnsnames.ora file
LDAP : Directory Naming Method set to resolve the service name with a directory server.
EZCONNECT : Easy Connect Naming Method set to resolve with tcp/ip connect identifier with hostname, port number and service name.
NIS : External naming method set to resolve service name with NIS ()National Information Service)

Example of use only tnsnames entries

NAMES.DIRECTORY_PATH=(tnsnames)

Check current version of Listener Control utility in Oracle

Check current version of Listener Control utility in Oracle

Version command is used to check the version of the listener. If during upgrade you want to verify that you listener is upraded or not then you can check version of your listener.

Syntax

lsnrctl VERSION listener_name

Note: listener_name: The listener name, if the default name of LISTENER is not used.

Example

C:\Users\Oracle>lsnrctl version LISTENER

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 11-JUN-2018 14:38:38

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Windows NT Named Pipes NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Producti
on
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production,,
The command completed successfully