Dump TNSNAMES.ORA from ActiveDirectory

Having all connections string in ActiveDirectory is nice, but maybe you need sometimes to push it to an external system (e.g. DMZ or Linux).

echo "# AD" > tnsnames.ora
$o = New-Object DirectoryServices.DirectorySearcher
$o.Filter = 'objectclass=orclNetService'
foreach ($p in $o.FindAll().Properties) {
[String]($"="+$p.orclnetdescstring) >> tnsnames.ora

goodies :mrgreen:

ldap security windows

Oracle 18c/19c and ActiveDirectory

With Oracle 18c and even better in Oracle 19c, you can manage your Oracle database users in Active Directory. This was supposed to be a very nice new feature as many of us struggle with many thousand users spread over many versions, environments, platforms and even cloud or exadata.

Is this going to help you?

No 😛

Oracle provides support for Active Directory only on Windows. Client and database must run on Windows

Net Services Administrator’s Guide

Maybe some of my readers working on a Linux-free environment may still like it

dba windows

sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies

if you use tnsping.exe and sqlplus.exe, the way the sqlnet.ora and tnsnames.ora are located differs

Let’s take the following setup

C:\tmp>type dir1\sqlnet.ora

C:\tmp>type dir1\tnsnames.ora

C:\tmp>type dir2\sqlnet.ora

C:\tmp>type dir2\tnsnames.ora

You set TNS_ADMIN to dir1 and your current directory is dir2.

Let’s try TNSPING.EXE first

C:\tmp>cd dir2

C:\tmp\dir2>set TNS_ADMIN=C:\tmp\dir1

C:\tmp\dir2>tnsping db

TNS Ping Utility for 64-bit Windows: Version - Production on 25-NOV-2013 15:47:31

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
OK (0 msec)

TNSPING.EXE is using the sqlnet.ora in %TNS_ADMIN% directory (EXAMPLE.COM domain) and the tnsnames.ora in the current directory (db02)

Let’s try with sqlplus

C:\tmp>cd dir2

C:\tmp\dir2>set TNS_ADMIN=C:\tmp\dir1

C:\tmp\dir2>sqlplus -L system@db

SQL*Plus: Release Production on Mon Nov 25 16:01:15 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from global_name;


SQLPLUS.EXE is using the sqlnet.ora in the current directory (EXAMPLE.ORG) and the tnsnames.ora in the current directory (db02)

This does not reproduce on Linux


ODBC 32bits for Windows 64bits

Windows-On-Windows 64-bit (WOW64) enables you to run 32bits applications in 64bits OS.

You will see there is another powershell, another registry, another ODBC tool, another Oracle client.


First, we run powershell(x86)


----------- ----------------
C:\oracle\product\11.2.0\client_32 client32bit_11203

Only the Oracle 32bit client is displayed

cmd /c "%SYSTEMROOT%\syswow64\odbcconf.exe /a {configdsn ""Oracle in client32bit_11203"" ""DSN=helloworld32|SERVER=DB01""}"

We registered ODBC with a wow64 configurator (odbcconf) or assistant (odbcad32).

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld32;uid=scott;pwd=tiger;"
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()

For the 64 bits version, it boils down to the same as odbc 32 bit on 32 bit os



----------- ----------------
C:\oracle\product\11.2.0\client_64 client64bit_11203

cmd /c "%SYSTEMROOT%\system32\odbcconf.exe /a {configdsn ""Oracle in client64bit_11203"" ""DSN=helloworld64|SERVER=DB01""}"

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld64;uid=scott;pwd=tiger;"
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()

Do not get confused by Windows32 and WOW64.

Windows32 is the default windows system directory with a bad name from upon a time where 32 meant something very big (compared to 16bit software); and WOW64 which is a special directory to run 32bits windows application on a 64bits os.

Note: odbcconf will be removed in future release, prefer Add-OdbcDsn


Oracle ODBC hello world with powershell

Demo :

cmd /c "odbcconf.exe /a {configdsn ""Oracle in OraClient11g_home1"" ""DSN=helloworld|SERVER=DB01""}"

Create a helloworld data source connecting to your DB01 tns alias in your OraClient11g_home1 Oracle Home.

It is easy to get the Oracle Home name with


----------- ---------------
C:\oracle\product\11.1.0\client_1 OraClient11g_home1
C:\oracle\product\11.2.0\client_1 OraClient11g_home2

Then we create the connection (as we did in ADO or ODP) :

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld;uid=scott;pwd=tiger;"
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()

Note: odbcconf will be removed in future release, prefer Add-OdbcDsn


Default Oracle Home in Windows

In Oracle Universal Installer and OPatch User’s Guide it is documented that The first Oracle home is named the “DEFAULT_HOME” and registers itself in the Windows NT Registry.

Remember, NT means New Technology 🙂

There is apparently a Home Selector that is a part of the installation software, maybe something like D:\oracle\product\11.2.0\client_1\bin\selecthome.bat. Sometimes. Not sure

But there is no DEFAULT HOME in the registry.



Name Property
---- --------
KEY_agent12c1 ORACLE_HOME : D:/oracle\core\
ORACLE_HOME_NAME : agent12c1
ORACLE_GROUP_NAME : Oracle - agent12c1
KEY_oracle_sysman_db_12_1_0_2_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.db.discovery.plugin_12.
0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_db_12_1_0_2_0_discovery_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_db_12_1_0_2_0_discovery_Home0
KEY_oracle_sysman_emas_12_1_0_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.emas.discovery.plugin_12.
2_0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_emas_12_1_0_2_0_discovery_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_emas_12_1_0_2_0_discovery_Home0
KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.oh.agent.plugin_12.
0_agent_Home0 ORACLE_HOME_NAME : oracle_sysman_oh_12_1_0_1_0_agent_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_agent_Home0
KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.oh.discovery.plugin_12.
0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_oh_12_1_0_1_0_discovery_Home0
ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_discovery_Home0
KEY_OraClient11g_home1 ORACLE_HOME : D:\oracle\product\11.2.0\client_1
ORACLE_HOME_NAME : OraClient11g_home1
ORACLE_GROUP_NAME : Oracle - OraClient11g_home1
OLEDB : D:\oracle\product\11.2.0\client_1\oledb\mesg
MSHELP_TOOLS : D:\oracle\product\11.2.0\client_1\MSHELP
SQLPATH : D:\oracle\product\11.2.0\client_1\dbs
KEY_OraGtw11g_home1 ORACLE_HOME : D:\oracle\product\11.2.0\tg_1
ORACLE_HOME_NAME : OraGtw11g_home1
ORACLE_GROUP_NAME : Oracle - OraGtw11g_home1
MSHELP_TOOLS : D:\oracle\product\11.2.0\tg_1\MSHELP
SQLPATH : D:\oracle\product\11.2.0\tg_1\dbs
KEY_sbin12c1 ORACLE_HOME : D:\oracle\sbin
ORACLE_GROUP_NAME : Oracle - sbin12c1
remexecservicectr remaining_time : 120000

How do I set my Oracle Home?

Actually if you enter a command like “lsnrctl start”, the OS will search in the PATH for lsnrctl and determines the Oracle Home name accordingly.

Therefore, the only thing you must do to change your default Oracle Home is to set the PATH environment variable. Only then your LSNRCTL START will find the right binary and right parameter file to start your listener.

security unix windows

xhost+ security hole part 2

Five years ago I wrote xhost+ is a huge security hole, I turned out red this morning when my neighbour sent me a smiley via X.

Do I really want everyone to have full access to my screen? No, I don’t. And I don’t do xhost+.

So why did it happen to me ???

I am using X-Window Attachmate aka Reflection X. And in this tool, according to the doc, the default X policy is unrestricted. This is in my opinion a huge flaw in the security design. Make sure you always change this to something more secure.

In Reflection X Manager Settings, Category Security, choose for instance User-based security and Prompt. Configuring X Cookies is probably more cumbersome.

Then when you or someone else will start an XTERM on your desktop, you will get a nice dialog box :

[Reflection X]
Client could not successfully authenticate itself to Reflection X server. Would you like Reflection X to connect to this client as an UNTRUSTED client ? Client originated from (RX1303)

Ok, I have to click one more button, but at least I can deny access to my screen 🙂

powershell windows

[Windows] Email of current user

I hate having to type my email address, so I created a long one-liner to do the trick of getting my email from Exchange and copying it in my clipboard

powershell -noprofile -command "$o=New-Object DirectoryServices.DirectorySearcher; $o.SearchRoot=New-Object DirectoryServices.DirectoryEntry;$o.Filter='samaccountname='+$ENV:USERNAME;write-host ($o.FindOne().Properties.mail)" | clip

save this as “C:\WINDOWS\E.BAT”

Then, when you have to enter your email in a form or document or login screen,
[⊞+R] [e] [enter] [CTRL+V]
4 keystrokes (+ 2 mod)

Explanation :

C:\>powershell -noprofile
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\> $o=New-Object DirectoryServices.DirectorySearcher;
### new object to browse Microsoft AD
PS C:\> $o.SearchRoot=New-Object DirectoryServices.DirectoryEntry;
### the base dn
PS C:\> $o.Filter='samaccountname='+$ENV:USERNAME;
### the search filter, your Username
PS C:\> $o.FindOne().Properties.mail
### find one (not necessarly 100% safe) and print the mail property

sql windows

My first .NET gui in Powershell

I managed to interface Oracle and a GUI via powershell.

First, load the Oracle and the .NET assemblies

[void] [Reflection.Assembly]::LoadFile("C:\oracle\product\11.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll")
[void] [Reflection.Assembly]::LoadWithPartialName("Drawing")
[void] [Reflection.Assembly]::LoadWithPartialName("Windows.Forms")

Now, let’s retrieve EMP in a powershell array. I hope one of my reader will advise me on a better way 🙂

$connection=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")
$command=new-object Oracle.DataAccess.Client.OracleCommand("select ename from emp",$connection)
$reader = $command.ExecuteReader()
$a = @()
while ($reader.Read()) {
$a = $a + $reader.GetString(0)

last, let’s create a simple window (a Form) with a list (a List box) where you can select an item with a doubleclick.

Initialize the list with the array from EMP

$form = New-Object Windows.Forms.Form
$form.Text = "Select employee !"
$form.Size = New-Object Drawing.Size(640,480)
$form.StartPosition = "CenterScreen"
$listbox = New-Object Windows.Forms.ListBox
$listbox.Location = New-Object Drawing.Point(10,10)
$listbox.Size = New-Object Drawing.Size(620,460)
$form.Topmost = $True
[void] $form.ShowDialog()

Show the result (or use it in your powershell scripts)

PS> $listbox.SelectedItems[0]

Pretty cool! No compiler needed, directly run from the powershell prompt

sqlplus windows

EURO symbol, sqlplus, cmd.exe and various issues

One customer reported a not-correctly displayed Euro Symbol (€) in the database from sqlplus (msdos).


First, the character set did not support it.

select * from v$nls_parameters where PARAMETER like '%CHARACTERSET%';

------------------------------ ---------------

If you are still using WE8ISO8859P1, consider migrating to WE8MSWIN1252 using csalter

sqlplus "/ as sysdba" @?/rdbms/admin/csminst
csscan "'sys/sys as sysdba'" full=y tochar=we8mswin1252 array=1024000 process=5
sqlplus "/ as sysdba" @?/rdbms/admin/csalter.plb

It is not always that straight forward, check output from csscan (scan.*) carefully before running csalter.

Ok, now retry

H:\>set NLS_LANG=american_america.we8pc850

H:\>sqlplus.exe scott/tiger

SQL*Plus: Release - Production on Thu May 10 11:28:01 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;


Not good!

Obviously, the PC850 client character is not good enough. Let’s switch to mswin1252 on the client.

H:\>chcp 1252
Active code page: 1252

H:\>set NLS_LANG=american_america.we8mswin1252

H:\>sqlplus.exe scott/tiger

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;


Well, what’s missing now? The font ! Let’s change it from “Raster Fonts” to “Lucida Console”. Either by clicking on the command com properties, or even dynamically with that gem (tested on XP) !

H:\>type Lucida.cs
using System;
using System.Runtime.InteropServices;

public class Lucida
const int STD_OUT_HANDLE = -11;

[DllImport("kernel32.dll", SetLastError = true)]
static extern int SetConsoleFont(IntPtr hOut, uint dwFontSize);

[DllImport("kernel32.dll", SetLastError = true)]
static extern IntPtr GetStdHandle(int dwType);

public static void Main()
SetConsoleFont(GetStdHandle(STD_OUT_HANDLE), 6);

H:\>csc Lucida.cs
Microsoft (R) Visual C# 2010 Compiler version 4.0.30319.1
Copyright (C) Microsoft Corporation. All rights reserved.

H:\>sqlplus.exe scott/tiger

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;


powershell windows

change system wide PATH variable in Windows

This is utterly simple and mostly you did it with the mouse.

To do it with command line and powershell, proceed this way.

Open a powershell window


or if you have no right to edit the machine-wide environment, runas admin

runas /user:yourdomain\youruserplusadmin powershell

then access the environment assembly

[environment]::setEnvironmentVariable("PATH","c:\oracle\product\11.2.0\client_1\bin;C:\WINDOWS\;C:\WINDOWS\system32;C:\Program Files\Putty;C:\Program Files\Perforce;C:\Program Files\TortoiseSVN\bin;C:\WINDOWS\system32\WindowsPowerShell\v1.0","MACHINE")

MACHINE is a for a Machine-wide setting and USER is for a USER-wide setting.

I hope it will be of some use

Completly unrelated, Enterprise Manager 12c for AIX is out. Do not wait it for HPUX, it is not planned. For Windows 64bit, it will come “between March and May 2012” according to note 793512.1 on metalink


Large zip on Windows

I have never been a Microsoft fanatic nor an anti-microsoft terrorist, but today I could not believe that large compressed folders got corrupted in Windows !

I have send a relatively small zip file (5gb, peanuts) from AIX to Windows per sftp and in Windows Explorer, some files in the compressed folder (read zip) were just pointing to the wrong content.

I had some issues with large zip files on unix, but this was last century! Howcome could a modern filesystem/operating system have such issues?

I have found a few bugs on

Ex: Compressed folder becomes corrupted when larger than 2 gigabytes
Workaround : make sure that you limit the size of a compressed folder to 2 GB or less


linux sqlplus unix windows

What is the current setting of NLS_LANG in sqlplus?

I just learnt a neat trick from Oracle Support.

How do you see the current value of NLS_LANG in SQLPLUS ?

HOST is not the right answer.


SQL> host echo $NLS_LANG



The correct setting is revealed by @.[%NLS_LANG%]

SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"


SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"

It could well be that both return the same answer, but not necessarly, as shown above.

The unix discrepancy is related to the subshell created by HOST. The subshell may read some .profile and overwrite the value of NLS_LANG

In Windows, the NLS_LANG setting may be set by sqlplus according to some registry entries

sqlplus windows

What’s your favorite shell in Windows?

I just wrote one of my first powershell script yesterday, it has a pretty nice syntax actually, and no need to download anything like cygwin or other unix-like shell to your PC.

PS> $stmt = "set hea off`n"
PS> $stmt += "select 'hello world' from dual;"
PS> $res = ($stmt | sqlplus -s scott/tiger)
PS> $res

hello world

Pretty cool 🙂

Blogroll dba vista windows

Start Oracle in Vista with one click

I have been using Vista for about 3 months and finally found a way to start my databases with a single click 🙂

The method I used until yesterday was a .BAT file that starts the services, I had then to right click on the shortcut, run as administrator, confirm the UAC warning. 3 Clicks. Way to much…

I have googled quite a lot and found Vista setuid – How to elevate without prompting

Ok, here we go :

1) run mmc
2) file – add snapin
3) Task Scheduler Library – Create Task
4) Name: startoracle
Run with highest privileges
Actions – New – Start a program – Program:net – Arguments: start OracleOraDb11g_home1TNSListener
Actions – New – Start a program – Program:net – Arguments: start OracleServiceLSC01
Actions – New – Start a program – Program:net – Arguments: start OracleServiceLSC02

5) create a batch file that runs : schtasks /run /tn startoracle

Same for stoporacle

It seems to work fine. Do not play too much with mmc if you do not know it. It is a powerful tool ❗

vista windows

Resize partition in Vista

It is quite a while I have not posted about Linux. The reason is I have bought in new notebook three months ago with Vista and did not have a program to resize the partition…

Do I need a program?
NO, I do not !!!

Vista has disk management that can resize my online partition.

Run: compmgmt.msc /s
Or go to control panel.

And shrink/extend your volumes!