cp -r preserves links

Linux is not Unix, I keep finding out differences years after years.

I’ve been using cp -r for over 25 years to find out today, the good old cp -r is cp -Lr on Linux.

e.g. AIX

$ touch foo
$ ln -s foo bar
$ cp -r bar boz
$ ls -la boz
-rw-r--r-- boz

cp convert symlink bar to file boz.

Now I found out in Linux, cp acts like a tar

$ touch foo
$ ln -s foo bar
$ cp -r bar boz
$ ls -la boz
lrwxrwxrwx boz -> foo

This special effect of -r is not documented in the manpage.  The old man used to RTFM. More than once.

CP(1)   User Commands CP(1)
  cp - copy files and directories
  -R, -r, --recursive
    copy directories recursively

Time passes, users no longer read MAN, they read INFO

11.1 ‘cp’: Copy files and directories

 When copying from a symbolic link, ‘cp’ normally follows the link
only when not copying recursively or when ‘--link’ (‘-l’) is used. This
default can be overridden with the ‘--archive’ (‘-a’), ‘-d’,
‘--dereference’ (‘-L’), ‘--no-dereference’ (‘-P’), and ‘-H’ options. If
more than one of these options is specified, the last one silently
overrides the others.

Ok, so, for recursion, let’s dereference.

$ rm baz
$ cp -rL boz baz
$ ls -la baz
-rw-r----- baz

Thinks change. 18 years of blogging. I learnt something new today



Drop table if exists has been there for ages, in Oracle MySQL, Oracle NoSQL and others. Now we got this in Oracle Database 23c too.

First thing I do on my sandbox database is creating SCOTT tables. Now, for the first time, I can make my script 100% error free.

SQL> ed ?/rdbms/admin/utlsampl
conn / as sysdba
whenever sqlerror exit failure
alter session set container=freepdb1;
create user scott;
alter session set current_schema=scott;
SQL> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 23c Free, Release - Developer-Release
$ echo $?

23c is out

but only for developers. You can get it from oracle.com as a RH8 rpm : https://www.google.ch/search?q=download+oracle+23c

You need about 6Gb in /opt and there is a

/etc/init.d/oracle-free-23c configure -i

command that does create a database for you.

Some goodies :

select 1;

select true;

create table if not exists t(x number);

arg 1 def xxx

select '&1'

Some badies that’ll make you swear:

Process ora_pmon_SID is now db_pmon_SID

desupport mkstore and owm

desupport 8/9/10/11 client (SQLNET.ALLOWED_LOGON_VERSION_CLIENT)

desupport 32bit clients

Read the doc and follow this blog 🙂

grant select on sysman view

Quite often you select from the sysman schema, let’s say

select target_name from sysman.mgmt$target;

To do so, you better be sys.. because SYS can see everything !

Let’s see what’s missing with a standard user

connect  sys/***@oem01 as sysdba;
create user user1 identified by ***;
grant create session to user1;
conn user1/***@oem01;
select count(*) from sysman.mgmt$target;
SQL Error: ORA-00942: table or view does not exist
Fair enough, we don’t have select
connect  sys/***@oem01 as sysdba;
grant create session to user1;
grant select on sysman.mgmt$target to user1;
conn user1/***@oem01;
select count(*) from sysman.mgmt$target;
This is more than enough to confuse the innocent ! the query returns 0 row! The witchcraft behind the scene is called virtual private database (VPD).
One lazy dba would grant you exempt access policy
connect  sys/***@oem01 as sysdba;
drop user user1 cascade;
create user user1 identified by ***;
grant create session, exempt access policy to user1;
conn user1/***@oem01;
select count(*) from sysman.mgmt$target;
But this is hurting the need to know principle. I really want to give only select on that view.
Creating a view on the view doesn’t do the trick
connect  sys/***@oem01 as sysdba;
create or replace view sysman.v as 
  select * from sysman.mgmt$target;
grant create session to user1;
grant select on sysman.v to user1;
conn user1/***@oem01;
select count(*) from sysman.v ;
Let’s try to add PL/SQL in the view :
connect  sys/***@oem01 as sysdba;
create or replace function sysman.f 
  return sysman.MGMT_TARGET_ARRAY 
  bulk collect into r 
  from sysman.mgmt$target; 
  return r; 
create or replace view sysman.v as select * from table(f);
grant select on sysman.v to user1;
conn user1/yF_5lOoxNp_g03Z#[email protected];
select count(*) from sysman.v;
Well, it doesn’t help yet, but now one more magic trick.
create or replace function sysman.f 
return sysman.MGMT_TARGET_ARRAY is 
  pragma autonomous_transaction;
  bulk collect into r 
  from sysman.mgmt$target; 
  return r; 
create or replace view sysman.v as select * from table(f);
grant select on sysman.v to user1;
conn user1/***@oem01;
select count(*) from sysman.v;

ODP standalone

Without Administrative privilege, how would you install ODP.NET?

Note 784309.1 describes the required files if you have no Oracle client

  1. download XCOPY ODAC from Oracle .NET, Visual Studio, and VS Code ODAC Downloads for Oracle Database
  2. Copy the files Oracle.DataAccess.dll OraOps.dll orannzsbb.dll oraociei.dll oci.dll to your directory (ex: C:\ODAC)
  3. Add that directory to your PATH
  4. Create your sqlnet.ora and tnsnames.ora or use ezconnect string host:1521/service.example.com

That’s it. It even works with kerberos






  default_realm = EXAMPLE.COM
    kdc = dc1.example.com
.example.com = EXAMPLE.COM
example.com = EXAMPLE.COM

let’s try

cd C:\ODAC
$conn = New-Object Oracle.DataAccess.Client.OracleConnection(
  'User Id=/;Data Source=DB01')
$com.CommandText='select * from dual';

PS: kerberos5pre is deprecated but kerberos5 doesn’t work with credential guard, there are a few unpublished bugs (33825536 and others)


I just established a connection from R to Oracle / MSSQL using ODBC.

First, I installed R

sudo yum install R

Then, ODBC

sudo yum install unixODBC-devel freetds-libs

I set up my mirror in .Rprofile


I install ODBC Library in R

R -s <<EOF

I configure my odbc driver to Oracle Instant Client 21c in ~/.odbcinst.ini

Description = Oracle ODBC driver
Driver = /usr/lib/oracle/21/client64/lib/libsqora.so.21.1

I configure my database in ~/.odbc.ini

Driver = OracleODBC
ServerName = DB01

Quick check (info: here I use Kerberos without password)

$ isql DB01
SQL> quit()

Let’s do it R

odbc_con <- dbConnect(odbc(), dsn="DB01")
dbGetQuery(odbc_con, 'select sysdate from dual');
   1 2022-11-25 16:08:03

Done 🙂

Recommend lecture for SQL Server : R package: odbc (renenyffenegger.ch)

connect to mssql with python/linux

done for a friend :

  1. install rpm’s
    sudo yum install -y freetds unixODBC unixODBC-devel freetds-libs python3-pyodbc
  2. create odbc.ini and odbcinst.ini
    1. for sybase go there Unix ODBC Sybase
    2. for oracle go there Unix ODBC Oracle
    3. for mssql
      1. ~/.odbc.ini : the Database definition
        Driver = FreeTDS
        Description = DB01
        Server = src01
        Port = 1433
        Database = DB01
      2. ~/.odbcinst.ini : the driver definition
        Description = Free Sybase & MS SQL Driver
        Driver64 = /usr/lib64/libtdsodbc.so.0
        Setup64 = /usr/lib64/libtdsS.so.2
        Port = 1433
  3. test with isql
    isql -v DB01 myuser mypw
  4. test with python3
    import pyodbc
    conn = pyodbc.connect('DSN=DB01;UID=myuser;PWD=mypw')
    cursor = conn.cursor()
    cursor.execute('select \'hello world\'')
    print (cursor.fetchone()[0])
    hello world


generate Excel from scott.emp

Ages ago I explained how to generate excel from the Unix command line. For instance with HTML or with XML+ZIP
To do this in Windows is way more elegant. You get your dataset from Oracle or any other source.
Then you create an Excel Object, you add content, saves, that’s all folk!
# 1) get a dataset from Oracle 
$oracle_home = (  
  gci -recurse HKLM:\SOFTWARE\Oracle  |     
    Where-Object -Property Property -eq ORACLE_HOME |          
      Where-Object -Property PSChildName -Match KEY |           
        get-itemproperty -name ORACLE_HOME  ).oracle_home;
'[INFO] '+$oracle_home;
Add-Type -Path ($oracle_home+'\ODP.NET\bin\4\Oracle.DataAccess.dll');
$conn = New-Object Oracle.DataAccess.Client.OracleConnection('User Id=/;Password=;Data Source=DB01');
$dataset = New-Object Data.dataset
(New-Object Oracle.DataAccess.Client.OracleDataAdapter("select * from emp",$conn)).fill($dataset)
# 2) get a ComObject from Excel
$excel = New-Object -ComObject excel.application
$excel.visible = $False
$wb = $excel.Workbooks.Add()
$ws= $wb.Worksheets.Item(1)
$ws.Name = "Data Set"
foreach ($colname in $dataset.Tables[0].Columns.ColumnName){
  $ws.Cells.Item($row,$col) = $colname 
foreach ($datarow in $dataset.Tables[0].rows) {
  foreach ($item in  $datarow.itemarray){
    $ws.Cells.Item($row,$col) = $item
$excel.DisplayAlerts = 'False' 

proxy user revisited

A new 10.2 feature I mentioned in su in sqlplus – Laurent Schneider is the ability to use a proxy user in a connection string.

This was documented in What’s New in Oracle Call Interface?

Proxy access for a single client can be set using various connect strings.

Today I got one user complaining about ORA-01045: user USER lacks CREATE SESSION

Proxy was introduced in Oracle 8 (OCI only) and enhanced in numerous versions.

In JDBC, we used openProxySession as defined in  OracleConnection (Oracle Database JDBC Java API Reference)

In C, we used OCILogon2 and OCIConnectionPoolCreate with an empty password for the USER2 as documented in Connect, Authorize, and Initialize Functions (oracle.com)

In both case, the CREATE SESSION was required.

For the C code, check for cdemocpproxy.c in the demo directory or on google and compile it with

cc -lclntsh -I$ORACLE_HOME/rdbms/public -L$ORACLE_HOME/lib cdemocpproxy.c -o cdemocpproxy && ./cdemocpproxy

For Java, here is a sample code

import java.util.Properties;

import java.sql.*;

import oracle.jdbc.OracleConnection;

public class Proxy2 {

  public static void main(String argv[]) throws SQLException {

    String url = "jdbc:oracle:thin:@srv01:1521/DB01.example.com";

    Properties props = new Properties();

    props.setProperty("user", "user1");

    props.setProperty("password", "Secret_Passw0rd");

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    Connection conn = DriverManager.getConnection(url, props);

    OracleConnection oc = (OracleConnection)conn;

    props.setProperty(OracleConnection.PROXY_USER_NAME, "user2");

    oc.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);

    ResultSet res = conn.

      prepareCall("select user from dual").





And the preparation steps
create user USER1 identified by Secret_Passw0rd;

create user USER2 identified by Secret_Pr0xYYY;

grant create session to user2;

alter user user2 grant connect through user1;

In multiple examples found on the Internet including OracleBase, AskTom, MyOracleSupport, the user1 receives CREATE SESSION.

Without CREATE SESSION, both my C and Java examples fail with :

Error - ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied

However, if you don’t use openProxySession but simply use USER1[USER2] as a username, it succeed

As it does in SQLPLUS

SQL> connect USER1[USER2]/[email protected]

Or also in java :

import java.util.Properties;

import java.sql.*;

public class Proxy1 {

  public static void main(String argv[]) throws SQLException {

    String url = "jdbc:oracle:thin:@srv01:1521/DB01.example.com";

    Properties props = new Properties();

    props.setProperty("user", "user1[user2]");

    props.setProperty("password", "Secret_Passw0rd");

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    Connection conn = DriverManager.getConnection(url, props);

    ResultSet res = conn.

      prepareCall("select user from dual").






Or in C

OCIConnectionPoolCreate(... "USER1[USER2]", "Secret_Passw0rd" ...)

This is a major behavior change that I blogged 16 years ago and realized today. I am glad I blogged all my memories 🙂

ODBC/OLEDB/ODP and Kerberos

Precondition : sqlplus can connect to the database server using a kerberos ticket.

I wrote The long long route to Kerberos – Laurent Schneider in 2014, but most of the bugs in / are no longer interesting, so probably you should be fine to just read Configuring Kerberos Authentication (oracle.com)

let’s see

sqlplus /@db01

SQL*Plus: Release - Production on Fri May 20 13:33:00 2022
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Last Successful login time: Fri May 20 2022 12:50:44 +02:00
Connected to:
Oracle Database 19c Enterprise Edition Release - Production

Okay, to use ODBC, you just omit username and password. Either in Excel or in Powershell.

Add-OdbcDsn -name "DB01" -DriverName ("Oracle in "+$ORACLE_HOME_NAME) -SetPropertyValue @("Server=DB01") -dsntype user
$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=DB01"
(new-Object Data.Odbc.OdbcCommand("select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual",$conn)).ExecuteScalar()
Remove-OdbcDsn -name "DB01" -DsnType user

Just don’t specify User Id and Password

To use ODP.NET, it is almost the same, but there is a dirty trick

$ORACLE_HOME = (Get-ItemPropertyValue HKLM:\SOFTWARE\Oracle\KEY_* -name ORACLE_HOME)
Add-Type -Path $ORACLE_HOME\ODP.NET\bin\4\Oracle.DataAccess.dll
$conn = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=/;Password=;Data Source=DB01")
$com.CommandText="select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual"

Here, userid is not empty, but slash. What a joke ! One tool use “” and the other “/”

Ancient Oracle versions use 2.x instead of 4.

What’s more, if you use the Managed Driver, you’ll need the latest 21.6.1 version to allow Kerberos (not tested) New Oracle Features for .NET by Release

The same SLASH Convention also applies to OLEDB. You can either create an empty test.udl file on your desktop to test or use the following code

$conn = New-Object System.Data.OleDb.OleDbConnection ("Provider=OraOLEDB.Oracle;Data Source=DB01;User ID=/;Password=;")
$cmd.CommandText= "select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual"

This concludes my ODBC/ODP/OLEDB topic

Log4J and OEM Part II

After part 1, where Oracle wrote in an official document there is no impact for your targets, Critical Patch Advisory April 2022 reveals multiple vulnerabilities, one of them is Log4J severity 9.8/10.

9.8 means it is kindergarden easy to compromise confidentiality, integrity and availability even without a login.

In the meanwhile, per 30. April, Oracle released 13.5 ru-6.

Don’t be fool, it is unsafe to assume the EM-Ru contains the EM-Cpu.

Yes, you have to apply the RU-5 or RU-6 to your OEM.  Your repository database version must be certified too.

But also, even if you don’t use WLS, Coherence, FMW, FMW 3rd party, ADR, OWMS, OHS, OSS, OPSS, ADF, WebCenter, JDBC, OVD, JDK, SQL Developer, you have to patch each of those components in your OMS home with one or more separate patches.

Just to summarize,  omspatcher applies 15 patches automatically, and later have to manually apply a dozen of one-off patches. Oracle Support told me:

As much as possible, Oracle tries to make Critical Patch Updates cumulative … Fixes for the other products that do not receive cumulative fixes are released as one-off patches.

Okay, once you are done, you can apply the RU-6 to your agents.

Here again, there are additional one-offs. For the central agent, patch the WLS. For all agents, apply the one-off for CVE-2022-21392.

I didn’t know this before researching for log4j issues. I won’t provide you the step here, because they may change over time. Just read the latest CPU very carefully.

Critical Patch Updates, Security Alerts and Bulletins (oracle.com)



jdbc url with ldap ssl in 21c

the basic :

you have tns resolution over ldap. To change from ldap to ldaps, you modify :




now the advanced :

Oracle Support mentions, there is no support for Mode 2 SSL (note 1664857.1)

So one approach was to either not use ldaps, or to use level 1, which means no Server authentication. But both are annoying for your directory server administrators.

There is a way to use Mode 2 with a fresh driver.


import java.util.Properties;
import java.sql.*;
public class TestConnect {
  public static void main(String argv[]) throws SQLException {
    String url = "jdbc:oracle:thin:@ldaps://ldap.example.com:636/DB01,cn=OracleContext,dc=example,dc=com";
    Properties props = new Properties();
    props.setProperty("user", "scott");
    props.setProperty("password", "tiger");
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection conn = DriverManager.getConnection(url, props);
    ResultSet res = conn.
      prepareCall("select 'Hello SSL World' txt from dual").
javac TestConnect.java
java -cp ojdbc8.jar:oraclepki.jar:. -Doracle.net.ldap.ssl.walletLocation=cwallet.sso TestConnect
Hello SSL World

OracleDriver (Oracle Database JDBC Java API Reference) documents five oracle.net.ldap parameters. It seems to be a 21c new feature.

Property Name

This is quite nice ! Just get the ojdbc8.jar from the 21c instant client and start testing 🙂

Log4J and Oracle Enterprise Manager

Log4j issues allow a remote client to execute code without authentication.

Software vendors spent the last two months thinking of the impact and the mitigations.

The bad:

If you have Enterprise Manager, you have multiple web services, like em itself (https://oms.example.com:7799/em), the weblogic console (https://oms.example.com:7102/console), the agent (https://dbsrv01.example.com:3872/emd/main/) and others. This makes you an easy victim.

The good:

If you care about security, you have restricted access to your management server to the outside world. The more network firewalls between Mr Bad and OEM, the better.

What is impacted?

DISCLAIMER: the stated here may no longer be accurate when read

Log4J version 2.0 to 2.16

Other Log4J (version 1.x and 2.17) when used with JMSAppender.

What is fixed?

There is a patch for WLS that upgrade 2.11 to 2.17



After applying 33727616, the version (but not the filename) is 2.17.0

$ unzip -p log4j-2.11.1.jar META-INF/MANIFEST.MF
Manifest-Version: 1.0
Bundle-Description: The Apache Log4j Implementation
Implementation-Title: Apache Log4j
Bundle-SymbolicName: org.apache.logging.log4j
Implementation-Version: 2.17.0
Archiver-Version: Plexus Archiver
Specification-Vendor: The Apache Software Foundation
Specification-Title: Apache Log4j
Bundle-Vendor: The Apache Software Foundation
Implementation-Vendor: The Apache Software Foundation
Bundle-Version: 2.17.0
Created-By: Apache Maven 3.6.3
Build-Jdk: 1.8.0_291

This has to be done on the MWHOME and on the agent of OMS only (the central management agent).

For the regular agents installed on the database servers, the version is 1.2.17 and JMSAppender is present

$ unzip -p log4j-core.jar META-INF/MANIFEST.MF| tail -6

Name: org.apache.log4j

Implementation-Vendor: "Apache Software Foundation"

DynamicImport-Package: *

Implementation-Title: log4j

Implementation-Version: 1.2.17

$ unzip -l log4j-core.jar org/apache/log4j/net/JMSAppender.class

Archive:  log4j-core.jar




1 file
In 2837257.1 Oracle mentions
All Agents impacted (Patch WIP)
No Mitigation Plan
There are multiple notes on log4j in Oracle Support. Main note 2828296.1 mentions : Apart from Central agent no other target agent is Impacted 
Either you know about the issue, and you invest time and money in fixing it
Or you are not sure if you are affected and you state the unknown impact and you keep investigating
Or you know you are not using log4j at all and you safely assume that there is no impact.
But  anything else is just dangerously misleading information

Log4J and Oracle Database

CVE-2021-44228 issue allows an user without authentication to execute code. It is tremendously easy to exploit, it is more a working-as-designed feature than a hard-core memory glitch.

Log4j is a logging library for java. If you enter some unexpected string, your web server may log it to a logfile.

  • What’s your name?
  • John
  • What’s your age?
  • 1000
  • Come on, that’s not possible
2021-01-05 John says he's 1000

So far so good. But one log4j developer probably found useful to add variable expansion and the like

  • What’s your name?
  • John
  • What’s your age?
  • ${JNDI:ldaps://example.com/rm-rf-*}
  • Come on, that’s not possible
2021-01-05 John says he's 1m files removed

Server attacked. This is of course best if multiple flaws exist, there is no firewalls and the web server logs everything with log4j.

Anyway, it isn’t a good feeling to be at risk.

It requires some carefull reading to check if Oracle Database is affected.

One may have check Oracle Support doc 2828877.1:

Oracle Database (all supported versions including 11.2, 12.1, 12.2, 19c, and 21c) are not affected by vulnerability CVE-2021-44228 or CVE-2021-45046.

But the innocent reader may stop here.

Let’s check some software, for instance RDBMS 19.6

$ find . -name "log4j-core*.jar"

What??? Is this not log4j around?

Other notes will tell CVE-2021-45105, CVE-2021-44228 and CVE-2021-45046 are addressed in AHF Version 21.4

$ suptools/tfa/release/tfa_home/bin/tfactl version
TFA Version : 183300
TFA Build ID : 20190315044534

So there I am at risk.

  • WARNING: removing TFA prevents autopatch from patching

Two solutions : update TFA, if you need it or remove it with

tfactl uninstall

(rm will also do)

What does Oracle says about md ?

Vulnerabilities CVE-2021-44228, CVE-2021-45046, CVE-2021-45104, and CVE-2021-45105 concern Spatial in versions 12.2, 18.x, and 21.x. 

If you use Spatial, then patch it. Otherwise, Oracle recommends to remove the whole md/property_graph directory (doc 2652121.1)

WARNING: this may impact cluster verify !

rm -r md/property_graph

12.1 (also client) has a few log4j-core jars, but version 1.x, which is older and less critical. But due to the log4j hystery, you may want to remove some of those, like $ORACLE_HOME/oui/jlib/jlib/log4j-core.

$ unzip -p -l oui/jlib/jlib/log4j-core.jar META-INF/MANIFEST.MF|grep Implementation-Version
Implementation-Version: 1.1.1

Minimizing the impact of log4j is one thing, but pretending we’re unaffected isn’t really helpful.

TNS resolution with LDAP and SSL

Long time ago, ldapsearch without password and without ssl was the way to go. But clear-text authentication (so called simple-bind) is a security leak. More and more, directory server vendors and administrators are closing the default non-ssl port and enforce authentication.

And if you use ldap for TNS naming, things will break.

Back in 2003, Microsoft Active Directory deactivated anonymous bind. So using AD was no longer an option… well, with Oracle 11g client for Microsoft Windows, one was able to set the new sqlnet.ora parameter NAMES.LDAP_AUTHENTICATE_BIND=1. But only for Windows. And of course only if you have a kerberos ticket, but this is always the case if you are in an AD domain.

Later in 2019, Microsoft published advisory ADV190023 to disable non-ssl bind. This breaked again TNS resolution over LDAP. I filed ER 19529903 but cannot tell when it’s going to be fixed.

If you use another directory service, e.g. openldap, then it is the same game. Your directory server admin doesn’t like non-encrypted network traffic.

How to deal with this?

First, patience (if you are reading this article, you probably googled for a long time). It is never working at first try.

Then, let’s do it.

The first thing to ask to your admin is how to connect with openldap.

/usr/bin/ldapsearch -H ldaps://ldap.example.com:636 -b "dc=example,dc=com" cn=db01 -D "" -LLL

dn: cn=db01,cn=OracleContext,dc=example,dc=com
objectclass: top
objectclass: orclservice
objectclass: orcldbserver
objectclass: orclnetservice
objectclass: orcldbserver_92
objectclass: orclapplicationentity
orclnetdescstring: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv01.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db01.example.com)))
orclservicetype: db
cn: db01

This is the first step. It must work.

In openldap, you have your rootca certificates defined either in /etc/openldap or in your .ldaprc
cat ~/.ldaprc
TLS_CACERTDIR /etc/pki/tls/certs

Ok, now let’s try to get the Oracle ldapsearch work.

First let’s create a wallet

orapki wallet add -wallet . -pwd *** -cert allca.pem -trusted_cert
orapki wallet display -wallet .

Trusted Certificates:
Subject:        CN=Root CA,O=My Unit,C=CH

ldapbind -h ldap.example.com -p 636 -D "" -W
file://home/oracle/walletdir -U 3 -P ""

bind successful

Bind successful. What an amazing moment in your dba life!

Now we have a wallet, let’s configure sqlnet.ora


and ldap.ora, notice the ::

DIRECTORY_SERVERS = (ldap.example.com::636)
DEFAULT_ADMIN_CONTEXT = "dc=example,dc=com"

This works like a charm

tnsping db01

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv01.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db01.example.com)))
OK (0 msec)

Identified by values reloaded

To get the correct values string, here is another approach, when you have no access to sys.user$

from dba_users;

USER        SPARE4_12
----------- -------------------------
SCOTT       S:12345678...;T:FEDCBA...
SYSTEM      S:
XS$NULL     S:00000000...

I would then ignore users with empty strings or S:00000000% or S: strings

For Scott, then use

alter user scott identified by values

dbms_metadata.get_xml('USER','SCOTT') gives us way more info than get_ddl 🙂

Pretty JSON in 21c

Oracle 21c now enables JSON as a datatype

12.2, 18c, 19c:

SQL> SELECT json_object(*)
from scott.emp
where ename='SCOTT';



SQL> SELECT json_object(* returning json)
from scott.emp
where ename='SCOTT';


Ok, it looks similar, but it’s a no longer a string (varchar2 or clob), it is a json object.

SQL> create table t(j json);
SQL> insert into t values('{"x":1}');
SQL> select t.j.x from t t;


SQL> desc t

 Name              Null?    Type
 ----------------- -------- ------------
 J                          JSON

What’s more, sqlplus can prettyprint the json

SQL> set jsonprint xxx
SP2-0158: unknown SET option "xxx"
SQL> set jsonpr pret
SQL> sho jsonpr
jsonprint PRETTY
SQL> SELECT json_object(* returning json) from scott.emp where ename='SCOTT';

  "EMPNO" : 7788,
  "ENAME" : "SCOTT",
  "JOB" : "ANALYST",
  "MGR" : 7566,
  "HIREDATE" : "1987-04-19T00:00:00",
  "SAL" : 3000,
  "COMM" : null,
  "DEPTNO" : 20

checksum of a column

Something I always wanted arrived this week, a checksum of a column !

SQL> create table t1(x number);
Table created.
SQL> create table t2(x number);
Table created.
SQL> insert into t1(x) values (1);
1 row created.
SQL> insert into t2(x) values (1);
1 row created.
SQL> select
(select checksum(x) from t1)t1,
(select checksum(x) from t2)t2
from dual;

        T1         T2
---------- ----------
    863352     863352

SQL> insert into t1(x) values (2);
1 row created.
SQL> select
(select checksum(x) from t1)t1,
(select checksum(x) from t2)t2
from dual;

        T1         T2
---------- ----------
    778195     863352

it is much more convenient than minus / intersect / not in and others to find out if two columns have identical values.

Oracle Database 21c which has just been released on Linux have a few more SQL improvement, like MINUS ALL that deals with duplicates and BIT_AND_AGG (OR, XOR) to aggregate bits.

SQL> select
3 replace(
4 replace(
5 replace(
6 replace(
7 replace(
8 replace(
9 replace(
10 replace(
11 replace(
12 replace(
13 replace(
14 replace(
15 replace(
16 replace(
17 replace(
18 replace(to_char(empno, 'FMXXXX'),
19 '0', '0000'),
20 '1', '0001'),
21 '2', '0010'),
22 '3', '0011'),
23 '4', '0100'),
24 '5', '0101'),
25 '6', '0110'),
26 '7', '0111'),
27 '8', '1000'),
28 '9', '1001'),
29 'A', '1010'),
30 'B', '1011'),
31 'C', '1100'),
32 'D', '1101'),
33 'E', '1110'),
34 'F', '1111') BIN
35 from scott.emp
36 /

---------- ----------------
      7369 0001110011001001
      7499 0001110101001011
      7521 0001110101100001
      7566 0001110110001110
      7654 0001110111100110
      7698 0001111000010010
      7782 0001111001100110
      7788 0001111001101100
      7839 0001111010011111
      7844 0001111010100100
      7876 0001111011000100
      7900 0001111011011100
      7902 0001111011011110
      7934 0001111011111110

14 rows selected.

SQL> select
2 bit_and_agg(empno) EMPNO,
3 replace(
4 replace(
5 replace(
6 replace(
7 replace(
8 replace(
9 replace(
10 replace(
11 replace(
12 replace(
13 replace(
14 replace(
15 replace(
16 replace(
17 replace(
18 replace(to_char(bit_and_agg(empno), 'FMXXXX'),
19 '0', '0000'),
20 '1', '0001'),
21 '2', '0010'),
22 '3', '0011'),
23 '4', '0100'),
24 '5', '0101'),
25 '6', '0110'),
26 '7', '0111'),
27 '8', '1000'),
28 '9', '1001'),
29 'A', '1010'),
30 'B', '1011'),
31 'C', '1100'),
32 'D', '1101'),
33 'E', '1110'),
34 'F', '1111') BIN
35 from scott.emp
36 /

---------- ----------------
      7168 0001110000000000

SQL> select
2 bit_or_agg(empno) EMPNO,
3 replace(
4 replace(
5 replace(
6 replace(
7 replace(
8 replace(
9 replace(
10 replace(
11 replace(
12 replace(
13 replace(
14 replace(
15 replace(
16 replace(
17 replace(
18 replace(to_char(bit_or_agg(empno), 'FMXXXX'),
19 '0', '0000'),
20 '1', '0001'),
21 '2', '0010'),
22 '3', '0011'),
23 '4', '0100'),
24 '5', '0101'),
25 '6', '0110'),
26 '7', '0111'),
27 '8', '1000'),
28 '9', '1001'),
29 'A', '1010'),
30 'B', '1011'),
31 'C', '1100'),
32 'D', '1101'),
33 'E', '1110'),
34 'F', '1111') BIN
35 from scott.emp
36 /

---------- ----------------
      8191 0001111111111111

It obviously works

Database link and user defined datatypes

To use an object table or an object column over a database link, a type with the same OID as remote type must exist locally.

SQL> conn user1/***@remotedb01
SQL> create type tt as object (x number)
2 /
Type created.
SQL> create table t (x tt);
Table created.
SQL> insert into t values (tt(1));
1 row created.
SQL> commit;
Commit complete.
SQL> select t.x.x from t t;

SQL> conn user1/***@localdb01
SQL> select t.x.x from [email protected] t;
select t.x.x from [email protected] t
ERROR at line 1:
ORA-22804: remote operations not permitted on object tables or user-defined type columns

$ oerr ora 22804
22804, 00000, "remote operations not permitted on object tables or user-defined type columns"
*Cause: An attempt was made to perform queries or DML
operations on remote object tables or on remote
table columns whose type is one of object, REF,
nested table or VARRAY.

But, there is a solution — otherwise I wouldn’t write this post today

SQL> select type_name,TYPE_OID from [email protected] t;
--------- --------------------------------
TT C6760780CC0BFA67E0539A24840A3B40
SQL> create type tt
2 oid 'C6760780CC0BFA67E0539A24840A3B40'
3 as object(x number)
4 /
Type created.
SQL> select t.x.x from [email protected] t;


It’s that simple, we create the type locally, with the same OID

Inline editting

I come from a no-tempfile world, where you getc and putc

When moving from legacy Unixes to Linux, inline editting became legend. Number of utilities like sed can now edit the file without tempfile.

Your AIX sysadmin probably used to do

sed "s/xxx/yyy/" /etc/importantfile > /tmp/importantfile
mv /tmp/importantfile /etc

which works… BUT it has a lot of issues, like permission, parallel processing and numerous other.

A typical fatality occurs if /tmp gets full, then sed generates only a broken file, and game over.

Okay, Linux save the world

sed -i "s/xxx/yyy/" /etc/importantfile

The file is editted in-place 👿

Of course it is a lie. This is just an extension of sed that does the tempfile magic trick and “apparently” edit the file. The file is not editted.

It get’s a new inode. If it is a link, it is converted to a file, it lose its property and so on

$ ls -li xxx
537 -rwxrwxrwx. 1 root root 4 Apr 10 12:19 xxx
$ sed -i '/wtf/{}' xxx
$ ls -li xxx
500 -rwxrwxrwx. 1 user01 user01 4 Apr 10 12:19 xxx

Just take care with -i, it does some magic, but maybe not all the magic you expected

grep color

When you move away from commercial UNIX to Linux, some goodies are just fun, even if they are simple and old.

Let’s look at grep. By default, the matched pattern is red. But the color could be changed. Some magic regexp could be used to get more than one color

$ tnsping DB01 |
   egrep '^TNS-[0-9]*'
TNS-03505: Failed to resolve name

The color could be changed to green

$ tnsping DB02 |
   GREP_COLORS="ms=1;32" egrep OK
OK (10 msec)

Now I want to get both, RED and GREEN, so I need to grep for “OK” and “TNS” and apply a different color. Pattern ‘OK|^’ returns always true but only OK will be highlighted

$ tnsping DB01 |
   egrep 'OK|TNS-'|
   GREP_COLORS="ms=1;32" egrep --color=always 'OK|^'|
   egrep 'TNS-[0-9]+|^'
TNS-03505: Failed to resolve name
$ tnsping DB02 |
   egrep 'OK|TNS-'|
   GREP_COLORS="ms=1;32" egrep --color=always 'OK|^'|
   egrep 'TNS-[0-9]+|^'
OK (10 msec)

Download Oracle software with the command line

When downloading software to my database server, I used to first download locally and later copy to my Unix box… but wouldn’t be convenient to download it directly on the database server?

Quite often, you get no X and no Browser and no Internet access on your datacenter. Therefore, we’ll use wget to the purpose. CURL is a similar tool that does the trick as well. WGET also exists for Windows by the way.

First, you need WGET
sudo yum install wget

Then, you need Internet
Ask your network colleagues for a proxy and request access to the following domains

  • edelivery.oracle.com
  • aru-akam.oracle.com
  • ccr.oracle.com
  • login.oracle.com
  • support.oracle.com
  • updates.oracle.com
  • oauth-e.oracle.com
  • download.oracle.com
  • edelivery.oracle.com
  • epd-akam-intl.oracle.com

Some of those are documented on Registering the Proxy Details for My Oracle Support but I extended the list for software download (e.g. SQL Developer)

Now, configure your .wgetrc
https_proxy = proxy.example.com:8080
proxy_user = oracle
proxy_passwd = ***
http_user = [email protected]
http_password = ***

The https proxy is your network proxy to access oracle.com from your database server. The proxy user and password may be required on your company proxy. The http user and password are your oracle.com (otn/metalink) credentials.

Later, to figure out the URL, either use the WGET script Oracle sometimes provides

or try to copy the link in your browser, e.g.

At this point, it probably won’t work
$ wget --no-check-certificate "https://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-"
$ htmltree sqldeveloper-
Parsing sqldeveloper-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
<html> @0
<head> @0.0
<script language="javascript" type="text/javascript"> @0.0.0
"\x0afunction submitForm()\x0a{\x0avar hash = location.hash;\x0aif (hash) {\x0aif..."
<base target="_self" /> @0.0.1
<body onload="submitForm()"> @0.1
<noscript> @0.1.0
<p> @
"JavaScript is required. Enable JavaScript to use OAM Server."
<form action="https://login.oracle.com/mysso/signon.jsp" method="post" name="myForm"> @0.1.1

We haven’t login.

Let’s get the login cookie
wget --no-check-certificate --save-cookies=mycookie.txt --keep-session-cookies https://edelivery.oracle.com/osdc/cliauth
Your mycookie.txt file should now contains login.oracle.com credentials.

Depending on the piece of software, e.g. sql developer, the authparam must be passed in. The authparam can be seen once you start the download, e.g. in your Downloads list (CTRL-J). When you use the wget script, when available, it probably provides a token= instead of an authparam=. The authparam typically validates you agreed to the license and possibly expires after 30 minutes. But maybe you can read the cookie and figure out how to pass in how to accept the license without Authparam. I haven’t gone that far yet.
wget --load-cookies=mycookie.txt --no-check-certificate "https://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-"

A long post for a short mission, downloading a file…

I remind you that using no-check-certificate and clear text passwords in .wgetrc isn’t a good security practice

Unannouncement: Oracle 20c release date

Just reading the Doc 742060.1, the release 20c, which was due 2020, disappeared from the roadmap. Don’t wait anymore for 20c, there won’t be one. There was a preview release in the cloud, but Oracle failed to release one new release every year. While we are all used to wait 2-6 years for a new major, switching to yearly versions (18 and 19 were just patchset) is a promise Oracle couldn’t hold.

My two cents : desupporting non-cdb in (invisible) 20c is an headache for many customers…

Pluggable and externally identified users without using remote authentication

Yesterday I was shocked to find a note on metalink that recommends a huge security hole using a deprecated Parameter

2042219.1 : create user c##oracle identified externally + set remote_os_authent=true

This is extremly sad. It is such a non-sense to recommend such a flaw. It makes me really angry 👿

Okay, for my readers I engineered a different approach

First let’s create a common user on the CDB
alter system set os_authent_prefix='C##' scope=spfile;
create user c##user01 identified externally container=all;

Now, let’s create a proxy user for connecting to your pdb
create user c##pdb01 identified by *** container=all;
alter user c##pdb01 grant connect through c##user01 container=all;

Grant some privs
grant create trigger, alter session, create session to c##pdb01 container=all;
alter session set container=pdb01;
grant set container to c##pdb01 container=current;
grant set container to c##user01 container=current;

Grant additional privs if wished
alter session set container=pdb01;
grant create dimension to c##pdb01 container=current;

Create a logon trigger to switch to the right pluggable
create or replace trigger c##pdb01.tr
after logon on c##pdb01.schema
execute immediate 'alter session set container=pdb01';

Now you can, for your user user01, connect to the database pdb01 using OS authentication

sqlplus "[C##PDB01]"
SQL> select sys_context('USERENV','DB_NAME') DB_NAME from dual;


SCP + sudo

Sometimes you like to copy files from A to B and you have sudo rights on A and B and you do a lot of “cp” to /tmp and chmod and chown’s. This is annoying…

Firstly, I dislike tempfiles.

  • they use space
  • they generate bugs when run in parallel
  • they often are prone to code injection
  • they remain on disk for years

Secondly, unix guys like pipes. While would one do
p <a >b
q <b >c

when you can
p <a |q >c

Lastly, I like to type less. So I wrote a small shell script that copies and uses sudo

at the end, I can
scp++ srv1:/dir/file srv2:/dir
using sudo

see comments for the script

cannot open database in NOARCHIVELOG

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount quiet
ORACLE instance started.
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
alter database open
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log

First time I see this. Let’s try to remember how archiving worked in the nineties.

Log_archive_start wasn’t implicit. Why would you need to run an archiver process during business hours, if you could quietly archive log in the evening ? At that time there were no internal jobs or so, the load was predictable and the dba had plenty of time for a handful of databases (or very often only a single database to tune).

To manually archive in Oracle 7, which still work, we could simply do :

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination +DG_RECO01
Oldest online log sequence 157
Current log sequence 161
SQL> archive log 161
ORA-00259: log 1 of open instance DB01 (thread 1) is the current log, cannot archive
SQL> alter system switch logfile;
System altered.
SQL> archive log 161
Statement processed.

What you cannot do, in noarchivelog, is to archive all log automatically

SQL> archive log all
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log

But why would you do that?

Okay, after looking in the alert log (a wonderful source of information 🙂 ), I found out changing the mode to noarchivelog didn’t implicitely deactivate the standby protection mode.

Here you go

SQL> alter database set STANDBY DATABASE TO MAXIMIZE protection;
Database altered.
SQL> alter database open;
alter database open
ERROR at line 1:
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log
SQL> alter database set STANDBY DATABASE TO MAXIMIZE performance;
Database altered.
SQL> alter database open;
Database altered.

ipcalc in powershell

Last day I wrote how to do it in AIX or Linux ip calc with ifconfig

It isn’t that different in PowerShell, the fun is to the calculation yourself. For translating in 0, we can use [IPADDRESS].

Let’s try…

$ip = [IPADDRESS](
(Get-NetIPAddress -AddressFamily "IPv4" -InterfaceAlias "Ethernet*").

$prefix = (
Get-NetIPAddress -AddressFamily "IPv4" -InterfaceAlias "Ethernet*").

The length and the ip of the current interface. In my case I have only one

PS> $ip
Address : 1677830336
AddressFamily : InterNetwork
IPAddressToString :
PS> $prefix

with a prefix length of 24, we need a netmask of 24 bits


which is

11111111.11111111.11111111.11111111 --> 2^32-1
11111111 --> 2^(32-24)-1

to do the math

IPAddressToString :

let’s bitand

$netid = [IPADDRESS]($ip.Address -band $netmask.address)
IPAddressToString :

sqlplus: error while loading shared libraries: libsqlplus.so: wrong ELF class: ELFCLASS64

This error usually while you do something wrong. Wait, what’s an error when you do everything right?

Okay, here it is:

You install the instantclient 32 rpm


On that server, you switch home using oraenv

$ . oraenv
ORACLE_SID = [oracle] ? DB01
The Oracle base has been set to /u01/app/oracle

You start sqlplus

$ sqlplus -v
sqlplus: error while loading shared libraries: 
libsqlplus.so: wrong ELF class: ELFCLASS64

Oops!? what happened?

This dubious made-by-Oracle RPM package simply created a sqlplus link in BIN.

lrwxrwxrwx.  /bin/sqlplus -> /usr/lib/oracle/12.1/client/bin/sqlplus

Then, oraenv did put ORACLE_HOME at the end position

$ echo $PATH

Just change the PATH manually and you’ll be fine.

$ PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/bin
$ sqlplus -v

SQL*Plus: Release - Production

network ip calculation with ifconfig

Most *nix are different. I’ll start with a plain Linux output

ifconfig eth0
eth0: flags=4163  mtu 1500
        inet  netmask  broadcast

to get the network ip, I just bitwise-and the inet and the netmask. To do it with the shell, I convert the ip to an integer and use the & (AND) operator

IP=$(ifconfig eth0|grep inet|awk '{print $2}')
NM=$(ifconfig eth0|grep inet|awk '{print $4}')

I get my IP= and NM= out of the ifconfig output

IPDEC=0;IFS=. ;for f in $IP;do ((IPDEC*=256));((IPDEC+=$f));done
NMDEC=0;IFS=. ;for f in $NM;do ((NMDEC*=256));((NMDEC+=$f));done

By converting the IP-base-256 address, I get IPDEC=1572395042 and NMDEC=4294967040 in decimal


That’s simple. My network IP is 1572395008

Let’s print it


Thanks for reading me that far. Ok let blogger Mathieu Trudel-Lapierre tell you : If you’re still using ifconfig, you’re living in the past

ip addr

ip shows your ip, and ipcalc do the calculation

ipcalc -n "$(ip -o -4  -br address show eth0 |awk '{print $3}')"

ODBC and EZCONNECT or my way out of active directory

The traditional way of connecting Excel (or Access) to Oracle (and other databases) is to use ODBC and TNSNAMES. You install an Oracle client, you create a connection and specify your tnsnames connection alias as server, here below DB01

DB01 = 

But the file is often managed centrally and deployed to the client by the DBA via home scripts. The syntax is pretty ugly and very soon you’ll see, a space or a parenthesis is missing and the loss of service is complete…

When working with Windows, one guy may try to setup active directory resolution. Before Exchange 2003, the schema was extended and that’s it, it works. But later, Microsoft made things more secure, among others by disabling anonymous bind and probably later by enforcing SSL, and one day you’ll see, the connection no longer works. Also, the schema extension could not be reverted, so it is not a thing you do just for fun in production

While there are white papers and blog articles on using authenticated bind, I could not find any support note.
Note 361192.1 mentions :
When anonymous operations are disabled, anonymous operations performed against Active Directory will fail
And note 455031.1 mentions :
Configuring Non-Anonymous LDAP Access Prerequisites: – A working LDAP naming environment should already exist between a client and OID (not Active Directory)
While note 1587824.1 refers the white paper Configuring Microsoft Active Directory for Oracle Net Naming , it clearly states This document is provided for information purposes only
So when AD changes, chances are, you will get an issue. Maybe in 2020Q1 according to https://portal.msrc.microsoft.com/en-us/security-guidance/advisory/ADV190023

Clearly, if you need more, you should go for an Oracle directory server like OID.

But if you need less? maybe you could go for easy connect (EZCONNECT). This is a zero-configuration setup that puts all the configuration out of the DBA tasks. So it seems to be better.

There are plenty of examples on how to use it, even with SSL and RAC and so on. In its simplest form you’ll use

sqlplus scott/[email protected]:1521/DB01

instead of

sqlplus scott/[email protected]

So a little bit more details but no more ActiveDirectory and no more tnsnames.ora.

But does it work with Excel and family? actually yes

You need to specify
Service Name : //srv01:1521/db01

If you don’t prefix with //, it doesn’t work.

Conclusion: if you are not willing to maintain local tnsnames and struggling with ActiveDirectory security enhancement, consider easyconnect but be aware of the additional slashes in the server name