Skip navigation.

Laurent Schneider

Syndicate content
Oracle Certified Master
Updated: 2 hours 6 min ago

SSL with PKCS12 truststore

Wed, 2014-12-03 14:31

Many many moons ago I vaguely remember having a similar issue with java keystore / truststore and microsoft certificates stores.

When you start using SSL for your listener, you could potentially face a large number of issues amoung your toolsets. In my opinion, the most disastrous one is that you cannot monitor your database with Enterprise Manager and a TCPS listener. I tried with 10g, 11g, 12c and I seriously doubt it will come in 13c, even a dozen of ERs have been filled. The best workaround I found is to use a separate listener to monitor your database and monitor the ssl-listener itself with IPC.

Today I had to deal with a driver from Datadirect, which finally works perfectly fine with SSL, but the challenge was to know what to put in the keystore and truststore…

In SQLNET, you use the single-sign-on wallet (cwallet.sso) created by OWM/orapki or ldap.

In Java, per default, you use a java keystore, that you generate with keytool (or even use the default cacerts). There is only a lexical difference between a keystore and a truststore, they could both be the same file. As documented in the JSSE Ref
A truststore is a keystore that is used when making decisions about what to trust

But for some other tools, the java keytool won’t do the trick, if the truststore cannot be of the type JKS.

One common type is the PKCS12. This is the your ewallet.p12 you get with the Wallet Manager.

E.g. from java :


  -Djavax.net.ssl.trustStore=ewallet.p12
  -Djavax.net.ssl.trustStoreType=PKCS12
  -Djavax.net.ssl.trustStorePassword=***

To use single-sign-on, use trustStoreType=SSO as I wrote there : jdbc-ssl

Other command formats are X509 base64 or DER file. The openssl command line allows you easy conversion


openssl pkcs12 -in ewallet.p12 -out file.pem
openssl x509 -outform der -in file.pem -out file.der

or in Windows Explorer, just click on your p12 file and then click on the certificate to export in the certificate store.

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

Tue, 2014-12-02 08:21

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

It works pretty fine until 11.2.0.2, 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

sqlnet.ora

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=.)))
ssl_cipher_suites=(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA)
NAMES.DIRECTORY_PATH=(TNSNAMES)

tnsnames.ora

DB01=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=srv01.example.com)(PORT=1521))(CONNECT_DATA=(SID=DB01)))

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


    System.setProperty("oracle.net.ssl_cipher_suites", "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 11.2.0.3/4 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.
sqlnet.ora


# comment out ssl_cipher_suites=(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA)

java

// comment out : System.setProperty("oracle.net.ssl_cipher_suites", "SSL_DH_anon_WITH_DES_CBC_SHA");
System.setProperty("javax.net.ssl.trustStore","keystore.jks");
System.setProperty("javax.net.ssl.trustStoreType","JKS");
System.setProperty("javax.net.ssl.trustStorePassword","***");

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

How to *really* send a script to the background

Thu, 2014-11-27 10:55

Let’s check this small script

foo.sh


#!/bin/sh
echo foo.1:`date` | tee $HOME/tmp/foo.txt
sleep 3
echo foo.2:`date` | tee -a $HOME/tmp/foo.txt


$ $HOME/tmp/foo.sh
foo.1:Thu Nov 27 17:34:53 CET 2014
foo.2:Thu Nov 27 17:34:56 CET 2014

Very obvious, I write to the console, wait three seconds, then write to the console.

Ok, let’s take another script that would call this script in the background using &

bar.sh


#!/bin/sh
echo bar.1:`date`
$HOME/tmp/foo.sh &
echo bar.2:`date`


$ $HOME/tmp/bar.sh
bar.1:Thu Nov 27 17:36:32 CET 2014
bar.2:Thu Nov 27 17:36:32 CET 2014
$ 
foo.1:Thu Nov 27 17:36:32 CET 2014
foo.2:Thu Nov 27 17:36:35 CET 2014

bar is printing the date, calling foo in the background, then printing the date, then it returns to you, and foo is still running.

BUT this is only in a relative background …

Let’s try this


$ time $HOME/tmp/bar.sh > /dev/null

real    0m0.01s
user    0m0.00s
sys     0m0.00s

So it takes no time to run bar you believe ?

Let’s try, for instance, over ssh (or cron or whatever)


$ time ssh localhost $HOME/tmp/bar.sh > /dev/null
real    0m3.81s
user    0m0.01s
sys     0m0.01s

running bar suddenly waits 3 seconds for foo to finish.

To be sure the script is sent to the farest background, you need to close the file descriptors, stdin, stdout, stderr

I rewrote it as

baz.sh


#!/bin/sh
echo bar.1:`date`
$HOME/tmp/foo.sh <&- >&- 2>&- &
echo bar.2:`date`


$ time ssh localhost $HOME/tmp/baz.sh >/dev/null
real    0m0.44s
user    0m0.00s
sys     0m0.00s

Now the script baz is immediately finished and does not wait for foo to complete

KeepAlive socket in 12c listener

Fri, 2014-11-21 06:50

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

12.1.0.2 on AIX

Mon, 2014-11-17 04:22

just released today http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

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

#oracle12c #db12102 is out for a bunch of platform #aix #os390 #hpux #zlinux

— laurentsch (@laurentsch) November 17, 2014

This is the first and last patchset for 12cR1

#oracle 12.1.0.2 is the last patch set for Release 12.1.

— laurentsch (@laurentsch) October 9, 2014

one more stragg

Thu, 2014-11-13 04:01


select
  to_char(
    sum(
      power(100,rownum-1)*
      deptno
    ),
    'FM99G99G99G99G99', 
    'NLS_NUMERIC_CHARACTERS=,;'
  ) deptlist 
from dept

DEPTLIST       
---------------
40;30;20;10

I also wrote about distinct listagg. The same applies for sum distinct.


select 
  to_char(
    sum(power(1e3,d-1)*deptno),
    'FM999G999G999', 
    'NLS_NUMERIC_CHARACTERS=,;'
  ) deptsum, 
  to_char(
    sum(distinct power(1e2,d-1)*deptno),
    'FM99G99G99', 
    'NLS_NUMERIC_CHARACTERS=,;'
  ) deptsumdist, 
  to_char(
    sum(power(1e1,d-1)),
    'FM9G9G9', 
    'NLS_NUMERIC_CHARACTERS=,;'
  ) deptcount, 
  to_char(
    sum(power(1e4,c-1)*comm),
    'FM9999G9999G9999G9999G9999', 
    'NLS_NUMERIC_CHARACTERS=,;'
  ) commlist 
from (
  select comm, deptno, 
    dense_rank() over (order by deptno) d, 
    dense_rank() over (order by comm) c 
  from emp);   

DEPTSUM      DSUMDIST COUNT COMMLIST
------------ -------- ----- -------------------
180;100;030  30;20;10 6;5;3 1400;0500;0300;0000

xml to csv in powershell

Wed, 2014-11-12 11:59

Powershell is very strong with XML, to convert an XML document to something flat like a CSV file, it is incredibly powerfull.

Let’s take a file called emp.xml


<EMPTABLE>
  <DEPT>
    <DEPTNO>10</DEPTNO>            
    <EMPLIST>
      <ENAME>CLARK</ENAME>
      <ENAME>MILLER</ENAME>
      <ENAME>KING</ENAME>
    </EMPLIST>
  </DEPT>
  <DEPT>
    <DEPTNO>20</DEPTNO>
    <EMPLIST>
       <ENAME>SMITH</ENAME>
       <ENAME>FORD</ENAME>
       <ENAME>ADAMS</ENAME>
       <ENAME>SCOTT</ENAME>
       <ENAME>JONES</ENAME>
    </EMPLIST>
  </DEPT>
  <DEPT>
    <DEPTNO>30</DEPTNO>     
    <EMPLIST>
       <ENAME>ALLEN</ENAME>
       <ENAME>WARD</ENAME>
       <ENAME>MARTIN</ENAME>
       <ENAME>BLAKE</ENAME>
       <ENAME>TURNER</ENAME>
       <ENAME>JAMES</ENAME>
    </EMPLIST>
  </DEPT>
</EMPTABLE>

To get all employees, it is awfully easy
([xml](gc emp.xml)).EMPTABLE.DEPT.EMPLIST.ENAME


CLARK
MILLER
KING
SMITH
FORD
ADAMS
SCOTT
JONES
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

Now I actually want to have each employee together with his department. I create an object for each department add the ename and the deptno


$d=([xml](gc emp.xml)).EMPTABLE.DEPT | % { 
  foreach ($i in $_.EMPLIST.ENAME) {
    $o = New-Object Object
    Add-Member -InputObject $o -MemberType NoteProperty -Name DEPTNO -Value $_.DEPTNO
    Add-Member -InputObject $o -MemberType NoteProperty -Name ENAME -Value $i
    $o
  }
}
$d


DEPTNO     ENAME
------     -----
10         CLARK
10         MILLER
10         KING
20         SMITH
20         FORD
20         ADAMS
20         SCOTT
20         JONES
30         ALLEN
30         WARD
30         MARTIN
30         BLAKE
30         TURNER
30         JAMES

This could be convert to multiple format.

HTML
$d|ConvertTo-HTML

DEPTNO ENAME 10 CLARK 10 MILLER 10 KING 20 SMITH 20 FORD 20 ADAMS 20 SCOTT 20 JONES 30 ALLEN 30 WARD 30 MARTIN 30 BLAKE 30 TURNER 30 JAMES

CSV
$d|ConvertTo-CSV


"DEPTNO","ENAME"
"10","CLARK"
"10","MILLER"
"10","KING"
"20","SMITH"
"20","FORD"
"20","ADAMS"
"20","SCOTT"
"20","JONES"
"30","ALLEN"
"30","WARD"
"30","MARTIN"
"30","BLAKE"
"30","TURNER"
"30","JAMES"

JSON
$d|ConvertTo-JSon


[
    {
        "DEPTNO":  "10",
        "ENAME":  "CLARK"
    },
    {
        "DEPTNO":  "10",
        "ENAME":  "MILLER"
    },
    {
        "DEPTNO":  "10",
        "ENAME":  "KING"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "SMITH"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "FORD"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "ADAMS"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "SCOTT"
    },
    {
        "DEPTNO":  "20",
        "ENAME":  "JONES"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "ALLEN"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "WARD"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "MARTIN"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "BLAKE"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "TURNER"
    },
    {
        "DEPTNO":  "30",
        "ENAME":  "JAMES"
    }
]

Or even to xml with ($d|ConvertTo-XML).OuterXml

It is so lightening fast that you could process pretty large files (millions of lines) in just a few seconds

poor man ActiveDirectory password checker

Mon, 2014-11-10 07:45

To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation.

You probably were discouraged by the long long route to kerberos, where the 11.2.0.2 bugs are fixed in 11.2.0.4, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that won’t be welcome by your sysadmins / winadmins.

Okay, to partly cover the password expiration issue, you could check in a profile function that the password is the one from AD.

Firstly, without SSL


CREATE OR REPLACE FUNCTION pw_function_AD
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
BEGIN
  sess := DBMS_LDAP.init(
    'example.com',dbms_ldap.PORT);
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'@example.com', 
    password);
  rc := DBMS_LDAP.unbind_s(sess);
  RETURN(TRUE);
EXCEPTION
  WHEN OTHERS THEN
    rc := DBMS_LDAP.unbind_s(sess);
    raise;
END;
/
GRANT EXECUTE ON pw_function_ad TO PUBLIC;
CREATE PROFILE AD LIMIT 
  PASSWORD_VERIFY_FUNCTION pw_function_AD;
ALTER PROFILE AD LIMIT 
  PASSWORD_LIFE_TIME 30;
ALTER PROFILE AD LIMIT 
  PASSWORD_REUSE_MAX UNLIMITED;

alter user lsc profile AD;

When the password expires, the user must change it to its AD Password.

If I try with a dummy password, the profile will reject this


SQL> conn lsc/pw1
ERROR:
ORA-28001: the password has expired

Changing password for lsc
New password:anypassword
Retype new password:anypassword
ERROR:
ORA-28003: password verification for 
  the specified password failed
ORA-31202: DBMS_LDAP: LDAP client/server 
  error: Invalid credentials. 
  80090308: LdapErr: DSID-0C0903A9, 
  comment: AcceptSecurityContext error, 
    data 52e, v1db1
Password unchanged
Warning: You are no longer connected to ORACLE.

I need to enter my Windows password


SQL> conn lsc/pw1
ERROR:
ORA-28001: the password has expired

Changing password for lsc
New password: mywindowspassword
Retype new password: mywindowspassword
Password changed
Connected.

Secondly, with SSL.

Maybe simple bind without SSL is not possible (check http://support.microsoft.com/kb/935834). And for sure it is better to not send unencrypted plain text password over the network.

Create a wallet with password with the ROOT Certification Authority that signed your AD. You probably could download this in your trusted root certification authorities in Internet Explorer.

Internet Explorer – Tools – Internet Options – Content – Certificates – Trusted root.

Then you create a ewallet.p12 with orapki. No need for user certificate and no need for single-sign-on. Only import the trusted root (and intermediaries if applicable).

Here is the modified code


CREATE OR REPLACE FUNCTION pw_function_AD
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
BEGIN
  sess := DBMS_LDAP.init(
    'example.com',dbms_ldap.SSL_PORT);
  rc := DBMS_LDAP.open_ssl(
    sess, 'file:/etc/wallet/MSAD', 
    'welcome1', 2);
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'@example.com', 
    password);
  rc := DBMS_LDAP.unbind_s(sess);
  RETURN(TRUE);
EXCEPTION
  WHEN OTHERS THEN
    rc := DBMS_LDAP.unbind_s(sess);
    raise;
END;
/

If you get SSL Handshake, be prepared, it could be anything! Check your wallet, your certificate, your permission, your wallet password.

One step further could be to expire users as soon as they change their password in AD or when they expire there.

For instance with powershell goodies for active directory


PS> (Get-ADuser lsc -properties PasswordLastSet).PasswordLastSet

Montag, 6. Oktober 2014 08:18:23

PS> (Get-ADuser king -properties AccountExpirationDate).AccountExpirationDate

Mittwoch, 16. Juli 2014 06:00:00

And in the database


SQL> SELECT ptime FROM sys.user$ 
  WHERE name ='LSC';

PTIME
-------------------
2014-11-10_10:33:08

If PTIME is less than PasswordLastSet or if AccountExpirationDate is not null, expire the account.

In conclusion : if you do not want to use Kerberos, nor Oracle “OctetString” Virtual Directory ovid nor Oracle Internet directory oid, this workaround may help to increase your security by addressing the “shared” and “expired” accounts problematic

There an additional hidden benefit. You could set up a self-service password reset function and send a generated expired password per mail, that the user won’t be able to change without its AD password

rowid of the last insert

Fri, 2014-11-07 06:42

If you look for the last insert result, check returning into.

Is it identity column, the rowid, any expression, get it back


SQL> var r varchar2(24)
SQL> var x number
SQL> var d varchar2(30)
SQL> insert into t values (default) 
  returning rowid,x,sysdate into :r,:x,:d;

1 row created.

SQL> print

R
--------------------
AAAaFTAAIAAAAILAAD

         X
----------
         6

D
--------------------------------
2014-11-07_13:33:03

It is documented in the SQL Reference. No need for PL/SQL here. I it very usefull if you use sequences too.


SQL> insert into t(x) values (s.nextval) returning x into :x;

1 row created.

SQL> print x

         X
----------
         2

PLS-00201 in stored procedures

Mon, 2014-10-13 03:36

When you grant table access thru a role, you cannot use that role in a stored procedure or view.


create role r;

create user u1 identified by ***;
grant create procedure, create session to u1;

create user u2 identified by ***;
grant create procedure, create session, r to u2;

conn u1/***
create procedure u1.p1 is begin null; end; 
/

grant execute on u1.p1 to r;

conn u2/***

create procedure u2.p2 is begin u1.p1; end; 
/

sho err procedure u2.p2

Errors for PROCEDURE U2.P2:

L/COL ERROR
----- -------------------------------------------
1/26  PL/SQL: Statement ignored
1/26  PLS-201: identifier U1.P1 must be declared

However, If i run it in an anonymous block, it works


declare
  procedure p2 is 
  begin 
    u1.p1; 
  end; 
begin
  p2; 
end; 
/

PL/SQL procedure successfully completed.

But this only works when my role is active. If my role is no longer active, then it obviously fails.


set role none;

declare 
  procedure p2 is 
  begin 
    u1.p1; 
  end; 
begin 
  p2; 
end; 
/
ERROR at line 1:
ORA-06550: line 4, column 5:
PLS-00201: identifier 'U1.P1' must be declared
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

It is all written in the doc,

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer’s rights

I knew the behavior but not the reason behind it. Thanks to Bryn for bringing me so much knowledge on plsql.

#OOW14, #DB12c, #BikeB4OOW(!) at 14:45 today, #CON8269: "Doing PL/SQL from SQL: Correctness and Performance" http://t.co/A9BKqrKygv

— Bryn Llewellyn (@BrynLite) September 29, 2014

to R1 or to R2

Thu, 2014-10-09 07:00

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 12.1.0.2 + some PSU in spring 2015. But only to avoid running out of support and without any fancy feature like Multitenant or in Memory.

#oracle 12.1.0.2 is the last patch set for Release 12.1.

— laurentsch (@laurentsch) October 9, 2014

12.1.0.2, 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. 11.2.0.4 patching ends January 2018.

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

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

select pdf from sqlplus

Wed, 2014-10-08 06:48

sqlplus 10gR2 and later allows you to select from a BLOB. If you use linux, you could convert the hex output to binary with xxd


sqlplus -s scott/tiger <<EOF |xxd -p -r >doc.pdf
set pages 0 lin 17000 long 1000000000 longc 16384
select document from emp where ename=user;
EOF

Obviously, it could also be a sound, a video or an image !

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

Tue, 2014-09-30 07:37

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


SQL> CREATE TABLE t
  (b CLOB CHECK (b IS JSON));
Table created.

SQL> insert into t(b) values 
  ('{"c":{"d":{"e":1}}}');
1 row created.

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

Largely inspired from Tom Kyte session

Oracle on Windows

Sun, 2014-09-28 05:31

@mkratoch is speaking at 10am UGF2633: Managing Oracle 12c on Windows

Again, 12cR1ps1 came before AIX and HPUX.


SQL> select dbms_utility.port_string, version 
  from v$instance;

PORT_STRING          VERSION         
-------------------- -----------------
IBMPC/WIN_NT64-9.1.0 12.1.0.2.0        

import into UTF8 database

Thu, 2014-09-25 04:41

A common error when you import single-byte characters (e.g. iso8859p1 or mswin1252) into multi-bytes databases (e.g. utf8) is ORA-12899: value too large for column.

The root cause is the default semantics in a database being BYTE


SQL> select VALUE, ISDEFAULT 
  from v$parameter 
  where NAME='nls_length_semantics'
VALUE   ISDEFAULT
------- ---------
BYTE    TRUE

It means, one char equals one byte. But after conversion, one char is larger than one byte and does not fit any longer.

single-byte


SQL> select VALUE 
  from nls_database_parameters 
  where parameter='NLS_CHARACTERSET';
VALUE
-------------
WE8MSWIN1252
SQL> create table t(x char(1));
Table created.
SQL> insert into t values ('é');
1 row created.
SQL> commit;
Commit complete.
$ expdp scott/tiger dumpfile=t.dmp tables=t
. . exported "SCOTT"."T"            1 rows

multi-byte


SQL> select VALUE 
  from nls_database_parameters 
  where parameter='NLS_CHARACTERSET';
VALUE
-----------
UTF8
$ impdp scott/tiger dumpfile=t.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type 
  TABLE_EXPORT/TABLE/TABLE_DATA
ORA-02374: conversion error loading table 
  "SCOTT"."T"
ORA-12899: value too large for column X 
  (actual: 2, maximum: 1)
ORA-02372: data for row: X : 0X'E9'
. . imported "SCOTT"."T"            0 out of 1 rows

How do I import my data?

1) import the metadata


$ impdp scott/tiger dumpfile=t.dmp content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE

2) change the char_used of the column(s) from (B)yte to (C)har

SQL> select 
  column_name, char_used, data_length, data_type 
  from user_tab_columns 
  where table_name='T' and char_used='B';
COLUMN_NAME  C DATA_LENGTH DATA_TYPE
------------ - ----------- ---------
X            B           1 CHAR
SQL> alter table t modify x char(1 char);
Table altered.

3) import the data

$ impdp scott/tiger dumpfile=t.dmp content=data_only
Processing object type 
  TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T"            1 rows

4) check

SQL> select x, length(x), lengthb(x) from t;
X  LENGTH(X) LENGTHB(X)
- ---------- ----------
é          1          2

My column has now a length of one char and two bytes.

change Oracle installer language in Windows

Wed, 2014-09-24 07:47


setup -J-Duser.country=FR -Duser.language=fr

It works also on UNIX

runInstaller -J-Duser.country=FR -J-Duser.language=fr

Documented in Sap Note : 1431796

ssl version

Tue, 2014-09-16 04:59

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 :


C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521)))
TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0
OK (100 msec)

with 12cR1 :


C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521)))
TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0
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


C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521)))
TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0
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
ssl_version=3.0

TCPS and SSLv2Hello

Mon, 2014-09-15 08:19

Thanks to platform independence, the same java code work on different platforms.


import java.util.Properties;
import java.security.Security;
import java.sql.*;
import javax.net.ssl.*;

public class KeyStore {
  public static void main(String argv[]) 
      throws SQLException {
    String url="jdbc:oracle:thin:@(DESCRIPTION="+
      "(ADDRESS=(PROTOCOL=TCPS)(Host=SRV01)("+
      "Port=1521))(CONNECT_DATA=(SID=DB01)))";
    Properties props = new Properties();
    props.setProperty("user", "scott");
    props.setProperty("password", "tiger");
    props.setProperty("javax.net.ssl.trustStore",
      "keystore.jks");
    props.setProperty(
      "javax.net.ssl.trustStoreType","JKS");
    props.setProperty(
      "javax.net.ssl.trustStorePassword","***");
    DriverManager.registerDriver(
      new oracle.jdbc.OracleDriver());
    Connection conn = 
      DriverManager.getConnection(url, props);
    ResultSet res = conn.prepareCall("select "+
       "sys_context('USERENV','NETWORK_PROTOCOL"+
       "') txt from dual").
         executeQuery();
    res.next();
    System.out.println("PROTOCOL: "+
      res.getString("TXT"));
  }
}

The code above perfectly works with Linux and Windows.

Okay, in AIX you will get IllegalArgumentException SSLv2Hello at com.ibm.jsse2.sb.a if you don’t add


props.setProperty("oracle.net.ssl_version","3.0");

The default does not work with the Oracle AIX client. Just set it to 1.0 and 3.0 and you will be a bit less plateform-dependent

check if using tcps part II

Thu, 2014-09-11 11:31

in your current session, as written there, check sys_context('USERENV', 'NETWORK_PROTOCOL')

in another session, you could grab some hints out of the network service banner. Do the maths, when it is not-not using ssl, it probably is…


select sid,program,
  case when program not like 'ora___@% (P%)' then
  (select max(case
when NETWORK_SERVICE_BANNER like '%TCP/IP%' 
      then 'TCP'
when NETWORK_SERVICE_BANNER like '%Bequeath%' 
      then 'BEQUEATH'
when NETWORK_SERVICE_BANNER like '%IPC%' 
      then 'IPC'
when NETWORK_SERVICE_BANNER like '%SDP%' 
      then 'SDP'
when NETWORK_SERVICE_BANNER like '%NAMED P%' 
      then 'Named pipe'
when NETWORK_SERVICE_BANNER is null 
      then 'TCPS' end)
    from V$SESSION_CONNECT_INFO i 
    where i.sid=s.sid) end protocol
  from v$session s;

       SID PROGRAM         PROTOCOL
---------- --------------- --------
       415 sqlplus(TNS V1- BEQUEATH
       396 sqlplus(TNS V1- IPC     
         6 Toad            TCP     
         9 Toad            TCPS    
         1 oracle(DIAG)            
       403 Toad            TCP     

12cR2

Sun, 2014-09-07 23:49

#db12cR2 release announced for 2016, Doc ID 742060.1

— laurentsch (@laurentsch) September 5, 2014