Category Archives: sqlnet

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()