Category Archives: sqlnet

KeepAlive socket in 12c listener

A not uncommon issue with firewalls and listeners are timeouts. Your production database may be behind a firewall, you may connect from a remote location, even your Windows workstation may have some firewall activated, possibly you use ssh tunnels or TCPS. All those occasionally lead to timeouts and connection abortion, for instance ORA-03113 end-of-file on communication channel, ORA-03135: connection lost contact, TNS-12547 Lost contact.

The good news is that Oracle 12c now implements Socket Options (see man setsockopt), as documented in Net admin new features and more nicely in Note 1591874.1
dcd visualized

I made until now a positive experience with this keepalive behavior, especially with SSL listener. The default value for SQLNET.EXPIRE_TIME is 0, so you must set it to a non-zero value first, the recommended value is 10 (minutes).

OracleContext as top object in Active Directory

When you do expand your Active Directory schema, it is not reversible; how to decide to use the OracleContext as a top object or not?

On the one hand, for tnsnames resolution, you could hide your context down in your AD structure and change the path in ldap.ora


DIRECTORY_SERVER_TYPE=AD
DEFAULT_ADMIN_CONTEXT="OU=Oracle,OU=Misc,DC=example,DC=com"

For debugging, I set TNSPING.TRACE_LEVEL=ADMIN and TNSPING.TRACE_DIRECTORY=C:\TEMP


PS> TNSPING DB01
Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(
  PROTOCOL=TCP)(HOST=SRV01)(PORT=1521)))(CONNECT_DATA=(SID=DB01)))
OK (20 msec)

PS> Select-String "DB01" C:\temp\tnsping.trc

nnflrne1: Quering the directory for dn: cn=DB01,cn=OracleContext,
  OU=Oracle,OU=Misc,DC=example,DC=com
nnflqbf: Search:  Base: cn=DB01,cn=OracleContext,OU=Oracle,OU=Misc,
  DC=example,DC=com; Scope: 0; filter: (objectclass=*) returns 0x0
nnflgne:   DN : cn=DB01,cn=OracleContext,OU=Oracle,OU=Misc,
  DC=example,DC=com
nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.2)
  (PORT=1521))(CONNECT_DATA=(SID=DB01)))

So far so good; but on the other hand, it prevents you from using connection identifier like DB01.EXAMPLE.COM


PS> TNSPING DB01.EXAMPLE.COM
TNS-03505: Failed to resolve name

PS> Select-String "DB01" C:\temp\tnsping.trc

nnflfdn: Turning simplified name DB01.EXAMPLE.COM into a dn.
nnflfdn:     The resulting dn is cn=DB01,cn=OracleContext,
  dc=EXAMPLE,dc=COM
nnflrne1: Quering the directory for dn: cn=DB01,cn=OracleContext,
  dc=EXAMPLE,dc=COM
nnflqbf: Search:  Base: cn=DB01,cn=OracleContext,dc=EXAMPLE,
  dc=COM; Scope: 0; filter: (objectclass=*) returns 0x20
nnfun2a: address for name "DB01.EXAMPLE.COM" not found

This no longer works. Your database domain name must match your Active Directory domain name and your object must be a top object domain

Platform guide for Windows : Oracle Context is the top-level Oracle entry in the Active Directory tree

It is probably wiser to follow this recommendation.

Also new in 11gR2 is NAMES.LDAP_AUTHENTICATE_BIND=TRUE, which removes the need of allowing anonymous ldap bind in AD

specify TNSNAMES for one program

Monday I wrote on tnsping.exe inconsistencies. Actually there is one good thing in having Oracle Client on Windows looking in the current directory first : you can set one tnsnames for a specific shortcut ! It is quite a viable alternative to .bat files with set TNS_ADMIN=path.

Demo :

First I create a small EXE in C#

HelloWorld.cs:


using System;
using System.Threading;
using Oracle.DataAccess.Client;

class HelloWorld
{
  static void Main() {
    OracleConnection connection=
      new OracleConnection("Data Source=DB01; User Id=scott; password=tiger");
    try {
      connection.Open();
      Console.WriteLine("Msg: " + (new OracleCommand(
        "select * from global_name",connection)).ExecuteScalar());
      connection.Close();
    } catch(Exception e) {
      Console.WriteLine("Exception Occured :{0}",e.Message);
    } finally {
      connection.Dispose();
    }
    Thread.Sleep(5000);
  }
}

Compile


C:\Windows\Microsoft.NET\Framework\v4.0.*\csc.exe /R:C:\oracle\product\11.2.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll HelloWorld.cs

Create a specific tnsnames and sqlnet

Tnsnames.ora


DB01.example.com=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=DB01)(PORT=1521)))(CONNECT_DATA=(SID=DB01)))

SQLNET.ora


NAMES.DIRECTORY_PATH=TNSNAMES
NAMES.DEFAULT_DOMAIN=EXAMPLE.COM

test


C:\TEMP> HelloWorld
Msg: DB01.EXAMPLE.COM

To create a desktop icon with the correct WorkingDirectory with powershell


PS> $ws =  New-Object -comObject WScript.Shell
PS> $desktop = [Environment]::GetFolderPath("Desktop")
PS> $s = $ws.createshortcut($desktop+"\HelloWorld.lnk")
PS> $s.TargetPath = "C:\TEMP\HelloWorld.exe"
PS> $s.WorkingDirectory = "C:\TEMP"
PS> $s.Save()