anonymous cypher suites for SSL (and a 12c pitfall)

If you configure your listener for encryption only, you do not really need authentication.

It works pretty fine until, I wrote multiple posts on ssl.

You add SSL_CLIENT_AUTHENTICATION=FALSE to your server sqlnet.ora and listener.ora and specify an “anon” cipher suite in your client. You do not need to validate the certificate, so a default wallet will do.

orapki wallet create -wallet . -auto_login_only





or if you use java, the default truststore -usually located in $JAVA_HOME/jre/lib/security/cacerts, will also do.

    System.setProperty("", "SSL_DH_anon_WITH_DES_CBC_SHA");

On some plateform however you may get something like : IBM’s Client TrustManager does not allow anonymous cipher suites.

So far so good, but if you upgrade your listener to or 12c, the anonymous suites won’t be accepted if not explicitely set up in sqlnet.ora. This is documented in Note 1434966.1

You will get something like “ORA-28860: Fatal SSL error”, “TNS-12560: TNS:protocol adapter error” in Oracle or “SSLHandshakeException: Received fatal alert: handshake_failure”, “SQLRecoverableException: I/O-Error: Received fatal alert: handshake_failure” in java.

There are two -obvious- ways to fix this. The preferred approach is to not use anonymous suite (they seem to have disappeared from the supported cypher suites in the doc).

For this task, you use another cipher suite. The easiest way is to not specify any or just use one like TLS_RSA_WITH_AES_128_CBC_SHA (java) / SSL_RSA_WITH_AES_128_CBC_SHA (sqlnet). Even if you do not use client authentication, you will then have to authenticate the server, and import the root ca in the wallet or the keystore.

# comment out ssl_cipher_suites=(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA)


// comment out : System.setProperty("", "SSL_DH_anon_WITH_DES_CBC_SHA");

Or, as documented in metalink, define the suite in sqlnet.ora and listener.ora if you use or

KeepAlive socket in 12c listener

A not uncommon issue with firewalls and listeners are timeouts. Your production database may be behind a firewall, you may connect from a remote location, even your Windows workstation may have some firewall activated, possibly you use ssh tunnels or TCPS. All those occasionally lead to timeouts and connection abortion, for instance ORA-03113 end-of-file on communication channel, ORA-03135: connection lost contact, TNS-12547 Lost contact.

The good news is that Oracle 12c now implements Socket Options (see man setsockopt), as documented in Net admin new features and more nicely in Note 1591874.1
dcd visualized

I made until now a positive experience with this keepalive behavior, especially with SSL listener. The default value for SQLNET.EXPIRE_TIME is 0, so you must set it to a non-zero value first, the recommended value is 10 (minutes). on AIX

just released today

only in Enterprise Edition at the moment, and now available on HPUX, zLinux and AIX

This is the first and last patchset for 12cR1

to R1 or to R2

In the past, most of my customers skipped R1 releases. That is, 8.1.7 -> 9.2 -> 10.2 -> 11.2. SAP does the same. For the very first time SAP plans to go to + some PSU in spring 2015. But only to avoid running out of support and without any fancy feature like Multitenant or in Memory., which is not available on AIX yet, will be the last patchset of 12cR1. It is the first and only patchset for that release. It is actually more than a patchset, as it introduced in memory database and JSON in the database.

The next release is expected beginning of 2016 on Linux. patching ends January 2018.

Should I I go to an already aborted release or should I buy extended support for until 2018 ?

Probably I will go both ways, depending on the applications.

select a.b.c.d.e from t a

I just learnt a new syntax. With, you can dive into your JSON document within sql, it looks a bit unusual to me

Table created.

SQL> insert into t(b) values 
1 row created.

SQL> select a.b.c.d.e from t a;

Largely inspired from Tom Kyte session

ssl version

I wrote about ssl version in jdbc thin yesterday

The default version also no longer works for the thick client with 12c client and 11g Server.

With 11gR2 :

TNS Ping Utility for 64-bit Windows: Version
OK (100 msec)

with 12cR1 :

TNS Ping Utility for 64-bit Windows: Version
TNS-12560: TNS:protocol adapter error

in trace file I see

ntzgsvp: no SSL version specified - using default version 0
ntzdosecneg: SSL handshake failed with error 29048.
ntzCreateConnection: returning NZ error 29048 in result structure
ntzCreateConnection: failed with error 542
nserror: nsres: id=0, op=65, ns=12560, ns2=0; nt[0]=29048, nt[1]=542, nt[2]=0; ora[0]=29048, ora[1]=0, ora[2]=0

I could not see this as a documented change yet, but if you force ssl_version to be 3.0, both client versions works

TNS Ping Utility for 64-bit Windows: Version
OK (80 msec)
C:> find "version" tnsping.trc sqlnet.ora

---------- TNSPING.TRC
nlstddp_dump_ptable:   ssl_version = 3.0
ntzGetStringParameter: found value for "ssl_version" configuration parameter: "3.0"

---------- SQLNET.ORA

dotNet transaction guard

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=, 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> $


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

One row inserted

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

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

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

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"
    OracleDataSource ods=new OracleDataSource();
    OracleConnection conn = (OracleConnection) 
    LogicalTransactionId ltxid = conn.
    try {
        "insert into t values (1)").execute();
      if (Math.random() > .5) {
        throw new Exception();
    } catch (Exception e) {
      OracleConnection conn2 = 
        (OracleConnection) ods.getConnection();
      CallableStatement c = conn2.prepareCall(
        "declare b1 boolean; b2 boolean; begin" 
        +"b2); ? := case when B1 then "
        +"'COMMITTED' else 'UNCOMMITTED' end; "
      c.setBytes(1, ltxid.getBytes());
      System.out.println("Status = "+

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

declare PARAMETER_ARRAY dbms_service.

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

C:\> java TG

C:\> java TG

C:\> java TG

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

Now it fails and I know it!

in memory option

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

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

distinct listagg

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

  LISTAGG (JOB, ',') 

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

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

  LISTAGG (job, ',') 
    WITHIN GROUP (ORDER BY job) jobs,
  LISTAGG (deptno, ',') 
    WITHIN GROUP (ORDER BY deptno) deptnos
      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                                      
-------- ----------------------------------------

Too bad the DISTINCT keyword was not implemented

Get the secondmax, again

Just bouncing on 2008/07/secondmax.

Another way of getting secondmax would be with an ordered collection. While collection methods like (n), first, last, count are not in SQL, I used PLSQL (within SQL)

WITH FUNCTION f (c sys.odcinumberlist, n NUMBER) RETURN number
          sal AS NUMBER
        ) ORDER BY sal DESC
      AS SYS.odcinumberlist
  ) as secondmax
FROM emp; 


Another 12c syntax would be

SELECT sal secondmax
FROM emp


strings larger than 4000 in 12c

Back in Oracle 7, the maximum length for VARCHAR2 was 2000. In 11gR2, it is still 4000 for varchar2/char columns and for literals. Any attempt to use something larger will produce an infamous ora-910, ora-1704 or ora-1489 error.

SQL> create table t(x varchar2(5000));
create table t(x varchar2(5000))
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> select 'x-
... 100 times 
' from dual;

select 'x-
ERROR at line 1:
ORA-01704: string literal too long

SQL> select 'x'||
... 100 times 
'x' from dual;

select 'x'||
ERROR at line 1:
ORA-01489: result of string concatenation is too long

In 12c it is possible to extends the varchar2 to 32k.

This is not the default and it is controlled by max_string_size

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade quiet
ORACLE instance started.
Database mounted.
Database opened.
SQL> alter system set max_string_size=extended;
System altered.
SQL> @?/rdbms/admin/utl32k
SQL> shu immediate
Database closed.
Database dismounted.
SQL> startup quiet
ORACLE instance started.
Database mounted.
Database opened.

Now we can create, insert and select longer strings.

SQL> create table t(x varchar2(6000));
Table created.
SQL> desc t
 Name                  Null?    Type
 --------------------- -------- ----------------
 X                              VARCHAR2(6000)
SQL> insert into t values ('-
... 100 times 
1 row created.
SQL> insert into t values (
... 100 times 
1 row created.

Oracle 8i came with a bunch of issue (cannot index a varchar2(4000) column) because the datatype was really increased to 4000. In 12c, however, it is silently using clob.

SQL> select TABLE_NAME,COLUMN_NAME,SEGMENT_NAME from user_lobs where table_name='T';

---------- ---------- ------------------------------
T          X          SYS_LOB0000022083C00001$$

It also works for NVARCHAR2 and RAW (who wasn’t increased to 4000). But not for CHAR.

This is a smart move to provide larger text fields to developers. On the other hand, why limit it to 32k if it is a clob? VARCHAR(MAX) in SQL Server does not have such a limitation.

SQL> create table t(x varchar2(32768));
create table t(x varchar2(32768))
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> select 'x'||
... 999 times 
'x' from dual;
ERROR at line 1:
ORA-01489: result of string concatenation is too long

old-hash, SHA-1, SHA-2/512

Until pretty recently, only the dubious unsalted proprietary algorithm was available to store Oracle passwords. A bunch of tool where at the time able to decode any 6-8 characters in no time, and the rainbow approach was to precalculate all possibles passwords for a specific user.

Those time are not really for away, only starting at Oracle 11g, you could have salted/case sensitive passwords. Salted means that Scott may have many different passwords keys for tiger.

 SQL> select spare4 from user$ where name='SCOTT';

SQL> alter user scott identified by tiger;

User altered.

SQL> select spare4 from user$ where name='SCOTT';

Some users may have only the 10g version (password not changed after migrating to 11g), some may have the 11g version of both, and -who knows- some may have already have SHA-2/512 passwords. SHA2 has many advantages. The chance that 2 passwords provides exactly the same string are much lower than in SHA1 (collision) and it performs twice faster on 64 bits servers.

SQL> select username, password_versions from dba_users where username like 'U_;
USERNAME                       PASSWORD
------------------------------ --------
U1                             10G
U2                             11G
U3                             10G 11G
U4                             12C

Probably you never saw this unless you are in beta 12. But actually it is documented in the 11gR2 Documentation.

12C if a new SHA-2 based SHA-512 hash exists

Enterprise Manager command line interface

emcli has been around for a while, but in 12c the installation has never been easier

do not search on otn for the jar, go to

download with : Setup –>My Preferences –>Command line interface –> download

install with : java -jar emclikit.jar client -install_dir=/u01/app/oracle/emcli

configure with : emcli setup -url= -username=sysman -password=sysmanpw -dir=/u01/app/oracle/emcli.

That’s it.

Let’s try

$ emcli get_targets
Status  Status           Target Type           Target Name                        
1       Up               host                      
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/e
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/O
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/e
-9      n/a              metadata_repository   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
-9      n/a              metadata_repository   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
1       Up               oracle_apache         /EMGC_GCDomain/instance1/ohs1      
1       Up               oracle_apm            /EMGC_GCDomain/GCDomain/EMGC_OMS1/o
1       Up               oracle_beacon         EM Management Beacon               
1       Up               oracle_database       LSC01                              
1       Up               oracle_database       LSC02                              
1       Up               oracle_database       LSC03                              
1       Up               oracle_database       LSC05                              
1       Up               oracle_database       LSC04                              
1       Up               oracle_dbsys          LSC01_sys                          
1       Up               oracle_dbsys          LSC03_sys                          
1       Up               oracle_dbsys          LSC04_sys                          
1       Up               oracle_dbsys          LSC02_sys                          
1       Up               oracle_em_service     EM Console Service                 
1       Up               oracle_em_service     EM Jobs Service                    
1       Up               oracle_emd           
1       Up               oracle_emrep          Management Services and Repository 
-9      n/a              oracle_home           oms12g1_8_precision                
-9      n/a              oracle_home           WebLogicServer10_3_5_0_0_precision 
-9      n/a              oracle_home           OraDb10g_home1_5_precision         
-9      n/a              oracle_home           OraDb11g_home1_1_precision         
-9      n/a              oracle_home           agent12g1_13_precision             
-9      n/a              oracle_home           webtier12g1_24_precision           
-9      n/a              oracle_ias_farm       EMGC_GCDomain                      
1       Up               oracle_listener     
1       Up               oracle_oms  
1       Up               oracle_oms_console
1       Up               oracle_oms_pbs
-9      n/a              weblogic_domain       /EMGC_GCDomain/GCDomain            
1       Up               weblogic_j2eeserver   /EMGC_GCDomain/GCDomain/EMGC_OMS1  
1       Up               weblogic_j2eeserver   /EMGC_GCDomain/GCDomain/EMGC_ADMINS

All green (one could argue command line has no color)