Skip navigation.

Laurent Schneider

Syndicate content
Oracle Certified Master
Updated: 4 hours 56 min ago

Configure your Oracle Net Client for AD LDAP Signing

Mon, 2014-08-25 12:14

If you don’t know about the “Require Signing” option, read this http://support.microsoft.com/kb/935834

LDAP is a cool alternative to tnsnames.ora. If your MSAD is highly available and you have friends there, you could go down that route : tnsnames and active directory

Amoung others, the AD “Require Signing” option prevents the ldap clients from sending clear text passwords to the Microsft Active Directory Server.

Since 11gR2, NAMES.LDAP_AUTHENTICATE_BIND=TRUE, enable authenticated bind. If you turn on “Require Signing” on the ActiveDirectory, you will need to use SSL. Unsigned search queries (tnsping/sqlplus/…) will be rejected.

Therefore the default port 389 won’t do the trick any more.


C:\> nslookup

> set type=all
> _ldap._tcp.dc._msdcs.example.com

_ldap._tcp.dc._msdcs.example.com     SRV service location:
          priority       = 0
          weight         = 100
          port           = 389
          svr hostname   = msad01.example.com

You must hardcode the SSL port in your ldap.ora


DIRECTORY_SERVERS=example.com:389:636
DIRECTORY_SERVER_TYPE = AD
DEFAULT_ADMIN_CONTEXT = "OU=Oracle,OU=Test,DC=example,DC=com"

And that’s not it! You need to have a wallet
So in your sqlnet.ora


WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\oracle\WALLET)))
NAMES.DIRECTORY_PATH= (LDAP)
NAMES.LDAP_AUTHENTICATE_BIND=TRUE

And in your wallet, you need the root certificates of your Active Directory server. If you do not where to download them, try
1) Internet Explorer – Tools – Internet Options – Contents – Certificates
2) Double click on your Trusted certicate “Example.com Root CA”
3) Details – Copy to file – Base64 – Save

You may need intermediate “issuer” authorities too. Check with your AD Admin if you are in doubt.

Now create your wallet


mkdir c:\oracle\wallet
orapki wallet create -wallet c:\oracle\WALLET -auto_login -pwd welcome1
orapki wallet add -wallet c:\oracle\WALLET -pwd welcome1 -trusted_cert -cert c:\oracle\WALLET\ExampleComIssuingCA.cer
orapki wallet add -wallet c:\oracle\WALLET -pwd welcome1 -trusted_cert -cert c:\oracle\WALLET\ExampleComRootCA.cer
orapki wallet display -wallet c:\download\WALLET 

That’s all you need


C:\> tnsping DB01
OK (10 msec)

The best tools to debug are
1) tnsping. Increase the trace level in sqlnet.ora


TNSPING.TRACE_LEVEL = ADMIN
TNSPING.TRACE_DIRECTORY = C:\temp
DIAG_ADR_ENABLED=off

Without SSL and Require Signing, tnsping was using the SNNFL cache (DNS?):


Adding parameter DIRECTORY_SERVERS=MSAD01.example.com discovered from SNNFL into cache

With SSL and DIRECTORY_SERVERS, you should get


Inserted value DIRECTORY_SERVSSL=example.com:636 at index 0 into NLPA_CACHE
Inserted value DIRECTORY_SERVERS=example.com:389 at index 0 into NLPA_CACHE

2) ldapsearch
the one in %ORACLE_HOME%\bin


ldapsearch -d 2147483647 -b "OU=Oracle,OU=Test,DC=example,DC=com" -h example.com -p 636 -D "CN=Laurent Schneider,OU=Users,DC=example,DC=com" -w MyWindowsPW -W file:c:/oracle/WALLET -P welcome1 -s sub "(&(objectclass=*)(cn=DB01))" orclNetDescString 

3) ldp
A Microsoft LDAP gui

Once you get it, you will feel a lot more secure :)

DBMS_METADATA.GET_DDL in sqlplus

Wed, 2014-08-20 10:10

Some settings matter when using dbms_metadata.

define large clobs


set long 1000000 

large long columns

set longchunksize 32000 

long lines

set linesize 32000 

no trailing spaces

set trimspool on  

no header

set heading off  

no page size

set pages 0  

no page feed (^L)

set newpage none  

no start of page

set embedded on  

no tabulator (^T)

set tab off  

no feedback (n rows returned)

set feedback off  

no echo

set echo off

Per default you get no terminator


exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)

When running the spooled code, allow blank lines


set sqlblanklines on

Get rid of &


set define off

Get rid of a leading #


set sqlprefix off

Get rid of . on a single line


set blockterminator OFF

To get rid of a trailing -, you may use set lin bigger than set longc and trimsp off, but I could not make it bug yet


CREATE TABLE T(x number default -
1);

would give 1 instead of -1 when run in sqlplus. But METADATA translates it to

  CREATE TABLE "SCOTT"."T"
   (    "X" NUMBER DEFAULT -
1
   )

So just forget about trailing dash for now.

Now try


SQL> set SQLBL ON SQLPRE OFF DEF OFF BLO OFF
SQL> create view v as select '
  2
  3  .
  4  #?
  5  &_date
  6  ' x from dual;

View created.
SQL> set long 1000000 longc 32000 lin 32000 trims on hea off pages 0 newp none emb on tab off feed off echo off
SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)
SQL> select dbms_metadata.get_ddl('VIEW','V') from dual;

  CREATE OR REPLACE FORCE VIEW "SCOTT"."V" ("X") AS
  select '

.
#?
&_date
' x from dual;

Now you are safer to use dynamic sql in sqlplus. But hardly ever 100% safe.

Plenty of useful transformation parameters there to get rid of storage, tablespace and others.

dotNet transaction guard

Mon, 2014-08-11 10:16

also with ODP in 12c, you can check the commit outcome as in jdbc

let’s create a table with a deferred primary key


create table t (x number primary key deferrable initially deferred);

Here an interactive Powershell Demo


PS> [Reflection.Assembly]::LoadFile("C:\oracle\product\12.1.0\dbhome_1\ODP.NET\bin\4\Oracle.DataAccess.dll")

GAC    Version        Location
---    -------        --------
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_64\Oracle.DataAccess\v4.0_4.121.1.0__89b483f429c47342\Oracle.DataAccess.dll

I first load the assembly. Some of my frequent readers may prefer Load(“Oracle.DataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342″) rather than hardcoding the oracle home directory.

PS> $connection=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")

create the connection

PS> $connection.open()

connect

PS> $cmd = new-object Oracle.DataAccess.Client.OracleCommand("insert into t values (1)",$connection)

prepare the statement

PS> $txn = $connection.BeginTransaction()

begin transaction

PS> $ltxid = ($connection.LogicalTransactionId -as [byte[]])

Here I have my logical transaction id. Whatever happends to my database server, crash, switchover, restore, core dump, network disconnection, I have a logical id, and I will check it later.


PS> $cmd.executenonquery()
1

One row inserted


PS> $connection2=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger")
PS> $connection2.open()

I create a second connection to monitor the first one. Monitoring your own session would be too much unsafe and is not possible.


PS> $txn.Commit()

Commit, no error.


PS> $connection2.GetLogicalTransactionStatus($ltxid)
     Committed     UserCallCompleted
     ---------     -----------------
          True                  True

It is committed. I see it Committed from $connection2. This is what I expected.

Because I have a primary key, let’s retry and see what happend.


PS> $txn = $connection.BeginTransaction()
PS> $ltxid = ($connection.LogicalTransactionId -as [byte[]])
PS> $cmd.executenonquery()
1
PS> $txn.Commit()
Exception calling "Commit" with "0" argument(s): "ORA-02091: Transaktion wurde zur├╝ckgesetzt
ORA-00001: Unique Constraint (SCOTT.SYS_C004798) verletzt"
At line:1 char:1
+ $txn.Commit()
+ ~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OracleException
PS> $connection2.GetLogicalTransactionStatus($ltxid)
     Committed     UserCallCompleted
     ---------     -----------------
         False                 False

The commit fails, and from the connection2 we see it is not committed. It is a huge step toward integrity, as Oracle tells you the outcome of the transaction.

We see Committed=False.

Transaction guard

Fri, 2014-08-08 08:05

Getting the logical transaction id in 12c will greatly simplify your error handling and enhance your business continuity in your application.

In 11g and below, your java code use to look like


try {
  insert into...
} catch () {
  error_handling()
}

but one probably assumed the insert failed when it was committed (e.g. database server process core dump).

Now in 12c, you can get a logical transaction id and then later, from another session, check if that transaction was committed. Which solves quite a bunch of integrity issues (e.g. duplicate rows)

Let’s try


import java.sql.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.*;

public class TG {
  public static void main(String argv[]) throws
      SQLException {
    String url = "jdbc:oracle:thin:@(DESCRIPTION"
      +"=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)("
      +"Port=1521))(CONNECT_DATA=(SERVICE_NAME="
      +"svc01)))";
    OracleDataSource ods=new OracleDataSource();
    ods.setURL(url);
    ods.setUser("SCOTT");
    ods.setPassword("tiger");
    OracleConnection conn = (OracleConnection) 
      ods.getConnection();
    LogicalTransactionId ltxid = conn.
      getLogicalTransactionId();
    try {
      System.out.println("Start");
      conn.prepareStatement(
        "insert into t values (1)").execute();
      if (Math.random() > .5) {
        throw new Exception();
      }
      System.out.println("OK");
    } catch (Exception e) {
      System.out.println("ERROR");
      OracleConnection conn2 = 
        (OracleConnection) ods.getConnection();
      CallableStatement c = conn2.prepareCall(
        "declare b1 boolean; b2 boolean; begin" 
        +"DBMS_APP_CONT.GET_LTXID_OUTCOME(?,b1,"
        +"b2); ? := case when B1 then "
        +"'COMMITTED' else 'UNCOMMITTED' end; "
        +"end;");
      c.setBytes(1, ltxid.getBytes());
      c.registerOutParameter(2, 
        OracleTypes.VARCHAR);
      c.execute();
      System.out.println("Status = "+
        c.getString(2));
    }
  }
}

getLogicalTransactionId gives me a transaction id (this is internally saved in SYS.LTXID_TRANS so it survives reboots, failover and disconnections) and GET_LTXID_OUTCOME gets the outcome.

There is few preparation steps


GRANT EXECUTE ON DBMS_APP_CONT TO SCOTT;
declare PARAMETER_ARRAY dbms_service.
  svc_parameter_array; 
begin 
  PARAMETER_ARRAY('COMMIT_OUTCOME'):='true';
  dbms_service.create_service(
    'SVC01','TNS01',PARAMETER_ARRAY); 
  dbms_service.start_service('SVC01'); 
end;
/
CREATE TABLE SCOTT.T(x number);

Due to my Random() call, I get exceptions sometimes, but it is always commits


C:\> java TG
Start
OK

C:\> java TG
Start
ERROR
Status = COMMITTED

C:\> java TG
Start
ERROR
Status = COMMITTED

No need to redo the insert.

Now I dropped the table t and run the same code


SQL> drop table scott.t;

Table dropped.

C:\>java TG
Start
ERROR
Status = UNCOMMITTED

Now it fails and I know it!

powershell goodies for Active Directory

Fri, 2014-07-11 07:04

What are my groups?


PS> Get-ADPrincipalGroupMembership lsc |
      select -ExpandProperty "name"
Domain Users
oracle
sybase

Who is member of that group ?

PS> Get-ADGroupMember oracle| 
      select -ExpandProperty "name"
Laurent Schneider
Alfred E. Newmann
Scott Tiger

What is my phone number ?

PS> (get-aduser lsc -property MobilePhone).MobilePhone
+41 792134020

This works like a charm on your Windows 7 PC.
1) Download and install Remote Server Administration Tools
2) Activate the windows feature under control panel program called “Active Directory Module for Powershell”
3) PS> Import-Module ActiveDirectory
Read the procedure there : how to add active directory module in powershell in windows 7

in memory option

Wed, 2014-07-09 05:12

Oracle 12cR1 patchset 1 is due this month and there is a new parameter that you can set to boost your performance. It is a bit of a SET "_FAST"=true parameter.

The in memory parameter is part of the sga. It is not mandatory to size it correctly, even if you do not have enough memory to hold your complete database, you can still play around with this parameter.

In a way, alter table t inmemory reminds me to the Oracle 8i alter table t cache and the Oracle 9i alter table t storage (buffer_pool keep).

But it is not free, I expect something close to the partitioning option, and it surely requires Enterprise Edition.

And also Oracle makes big noise about it, experts talk about a 1000x improvement, watch Database Industry Experts Discuss Oracle Database In-Memory.

The in memory cache is redundant with the database cache. It stores columns instead of blocks (or even results with the RESULT CACHE in 11g)

Don’t miss the Oracle Blog of @db_inmemory

Providing in-memory database is also positioning against HANA, a SAP in memory database. From OTN : Oracle Database In-Memory
Versus SAP HANA

A few years ago, Oracle acquired TimesTen. TimesTen is an in-memory database that works differently, where you can have fast response time (microseconds?) and could lose transactions (better faster than zero-data-loss). While TimesTen improves transaction speed, inMemory mostly improves queries (not writes).

check jdbc version

Wed, 2014-06-25 05:12

There are 2 versions to check when using jdbc.

The first one is in the name of the file : classes12.zip works with JDK 1.2 and later, ojdbc7.jar works with java7 and later.

Even if classes12.zip works fine with JAVA 8, it is not supported.

Be sure you check the support matrix on the Oracle JDBC FAQ

According to the support note 401934.1, only Oracle JDBC driver 11.2.0.3 (and greater) versions support JDK 1.7.

To check your version of the JDBC Driver, there are two methods.

One is with the jar (or zip) utility.


$ jar -xvf ojdbc7.jar META-INF/MANIFEST.MF
 inflated: META-INF/MANIFEST.MF
$ grep Implementation META-INF/MANIFEST.MF
Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC
Implementation-Version: 12.1.0.1.0
$ unzip classes12.zip META-INF/MANIFEST.MF
Archive:  classes12.zip
  inflating: META-INF/MANIFEST.MF
$ grep Implementation META-INF/MANIFEST.MF
Implementation-Title:   classes12.jar
Implementation-Version: Oracle JDBC Driver 
  version - "10.2.0.1.0"
Implementation-Vendor:  Oracle Corporation
Implementation-Time:  Jun 22 18:51:56 2005

The last digit is often related to the java version, so if you have ojdbc6 and use java 6, you’re pretty safe. If you have java 8, you won’t find any ojdbc8 available at the time of writing, a safer bet is to use the latest version and to wait for a support note. The latest notes about ojdbc7.jar currently does not display java 8 certification. Probably we will have to wait for a more recent version of ojdbc7.jar.

Another mean to find the version of the driver is to use DatabaseMetaData.getDriverVersion()


public class Metadata {
  public static void main(String argv[]) 
    throws java.sql.SQLException {
    java.sql.DriverManager.registerDriver(
      new oracle.jdbc.driver.OracleDriver());
    System.out.println(
      java.sql.DriverManager.
        getConnection(
"jdbc:oracle:thin:@SRV01.EXAMPLE.COM:1521:DB01", 
          "scott", "tiger").
            getMetaData().getDriverVersion());
  }
}


$ javac -classpath ojdbc6.jar Metadata.java
$ java -classpath ojdbc6.jar:. Metadata
11.2.0.3.0

fun with cron

Thu, 2014-06-19 03:10

Today I find out that my scheduler was too busy to execute all jobs in my crontab !?


* * * * * (while :;do ssh example.com :; done)
59 23 19 06 * touch /tmp/bang

my while loop is going to produce so much hangs on the cron deamon that it may not be able to read the crontab once a minute. If it reads it at 23:58 and at 00:00, the 23:59 won’t be run.

This is actually the first time I see this behaviour. And -believe me- it’s annoying!

distinct listagg

Fri, 2014-05-16 23:07

One limitation in listagg, you cannot use DISTINCT. Okay, there are plenty of distinct-capable listagg workarounds, but to get the real listagg working, it is a bit of an headache

With one listagg


SELECT 
  DEPTNO,
  LISTAGG (JOB, ',') 
    WITHIN GROUP (ORDER BY JOB) JOBS
FROM (
  SELECT DISTINCT DEPTNO, JOB  FROM EMP)
GROUP BY DEPTNO;

    DEPTNO JOBS                          
---------- ------------------------------
        10 CLERK,MANAGER,PRESIDENT       
        20 ANALYST,CLERK,MANAGER         
        30 CLERK,MANAGER,SALESMAN        

ok, it was not that hard, but it gets more difficult with two listagg’s


SELECT 
  LISTAGG (job, ',') 
    WITHIN GROUP (ORDER BY job) jobs,
  LISTAGG (deptno, ',') 
    WITHIN GROUP (ORDER BY deptno) deptnos
FROM (
  SELECT 
    DECODE(
      ROW_NUMBER () OVER (
        PARTITION BY deptno 
        ORDER BY 1),
      1, deptno) deptno,
    DECODE (
      ROW_NUMBER () OVER (
        PARTITION BY job 
        ORDER BY 1),
      1, job) job
  FROM emp
);
DEPTNOS  JOBS                                      
-------- ----------------------------------------
10,20,30 ANALYST,CLERK,MANAGER,PRESIDENT,SALESMAN

Too bad the DISTINCT keyword was not implemented