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.name+"="+$p.orclnetdescstring) >> tnsnames.ora
}
goodies 
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
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
NAMES.DEFAULT_DOMAIN = (EXAMPLE.COM)
NAMES.DIRECTORY_PATH = (TNSNAMES)
C:\tmp>type dir1\tnsnames.ora
db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.com)(PORT=1521))(CONNECT_DATA=(SID=db01)))
db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.org)(PORT=1521))(CONNECT_DATA=(SID=db01)))
C:\tmp>type dir2\sqlnet.ora
NAMES.DEFAULT_DOMAIN = (EXAMPLE.ORG)
NAMES.DIRECTORY_PATH = (TNSNAMES)
C:\tmp>type dir2\tnsnames.ora
db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.org)(PORT=1521))(CONNECT_DATA=(SID=db02)))
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 12.1.0.1.0 - Production on 25-NOV-2013 15:47:31
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
C:\tmp\dir1\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
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 12.1.0.1.0 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 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from global_name;
GLOBAL_NAME
-------------------------------------------------
DB02.EXAMPLE.ORG
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
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.
%SYSTEMROOT%\syswow64\WindowsPowerShell\v1.0\powershell.exe
First, we run powershell(x86)
Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME
ORACLE_HOME ORACLE_HOME_NAME
----------- ----------------
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;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()
For the 64 bits version, it boils down to the same as odbc 32 bit on 32 bit os
%SYSTEMROOT%\system32\WindowsPowerShell\v1.0\powershell.exe
Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME
ORACLE_HOME ORACLE_HOME_NAME
----------- ----------------
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;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()
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
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
Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME
ORACLE_HOME ORACLE_HOME_KEY
----------- ---------------
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;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()
Note: odbcconf will be removed in future release, prefer Add-OdbcDsn
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.
PS C:\> gci HKLM:\SOFTWARE\ORACLE
Hive: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Name Property
---- --------
KEY_agent12c1 ORACLE_HOME : D:/oracle\core\12.1.0.1.0
ORACLE_HOME_NAME : agent12c1
ORACLE_GROUP_NAME : Oracle - agent12c1
NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252
KEY_oracle_sysman_db_12_1_0_2_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.db.discovery.plugin_12.1.0.2.0
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.1.0.2.0
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.1.0.1.0
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.1.0.1.0
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
ORACLE_BUNDLE_NAME : Enterprise
NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252
OLEDB : D:\oracle\product\11.2.0\client_1\oledb\mesg
ORACLE_HOME_KEY : SOFTWARE\ORACLE\KEY_OraClient11g_home1
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
NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252
ORACLE_BUNDLE_NAME : Enterprise
MSHELP_TOOLS : D:\oracle\product\11.2.0\tg_1\MSHELP
SQLPATH : D:\oracle\product\11.2.0\tg_1\dbs
ORACLE_HOME_KEY : SOFTWARE\ORACLE\KEY_OraGtw11g_home1
KEY_sbin12c1 ORACLE_HOME : D:\oracle\sbin
ORACLE_HOME_NAME : sbin12c1
ORACLE_GROUP_NAME : Oracle - sbin12c1
ODP.NET
remexecservicectr remaining_time : 120000
SYSMAN
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.
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 192.168.0.1 (RX1303)
[Yes][No]
Ok, I have to click one more button, but at least I can deny access to my screen 🙂
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
laurentschneider@example.com
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")
$connection.open()
$command=new-object Oracle.DataAccess.Client.OracleCommand("select ename from emp",$connection)
$reader = $command.ExecuteReader()
$a = @()
while ($reader.Read()) {
$a = $a + $reader.GetString(0)
}
$connection.close()
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)
$listbox.Items.AddRange($a)
$listbox.Add_DoubleClick({$form.Close();})
$form.Controls.Add($listbox)
$form.Topmost = $True
$form.Add_Shown({$form.Activate()})
[void] $form.ShowDialog()
Show the result (or use it in your powershell scripts)
PS> $listbox.SelectedItems[0]
SCOTT
Pretty cool! No compiler needed, directly run from the powershell prompt
One customer reported a not-correctly displayed Euro Symbol (€) in the database from sqlplus (msdos).
Why?
First, the character set did not support it.
select * from v$nls_parameters where PARAMETER like '%CHARACTERSET%';
PARAMETER VALUE
------------------------------ ---------------
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16
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 11.1.0.6.0 - 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 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select chr(128) from dual;
C
-
■
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 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select chr(128) from dual;
C
-
Ç
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:\>Lucida
H:\>sqlplus.exe scott/tiger
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select chr(128) from dual;
C
-
€
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
powershell
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
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 support.microsoft.com.
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
Amazing!
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.
E.g.:
Unix:
SQL> host echo $NLS_LANG
AMERICAN_SWITZERLAND
Windows:
SQL> HOST ECHO %NLS_LANG%
%NLS_LANG%
The correct setting is revealed by @.[%NLS_LANG%]
E.g.:
Unix:
SQL> @.[$NLS_LANG]
SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"
Windows:
SQL> @.[%NLS_LANG%]
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
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 🙂
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 ❗
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!