Categories
12c news support

12.1.0.2 on AIX

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

This is the first and last patchset for 12cR1

Categories
sql

one more stragg


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

Categories
powershell xml

xml to csv in powershell

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




10

CLARK
MILLER
KING



20

SMITH
FORD
ADAMS
SCOTT
JONES



30

ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES



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

Categories
dba ldap security

poor man ActiveDirectory password checker

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

Categories
sql

rowid of the last insert

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

Categories
plsql security

PLS-00201 in stored procedures

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.

Categories
11gR2 12c 12cR2 support

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

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.

Categories
sql sqlplus

select pdf from sqlplus

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 <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 !

Categories
12c

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

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

Categories
12cR2

Oracle on Windows

@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

Categories
dba unicode

import into UTF8 database

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.

Categories
dba installation

change Oracle installer language in Windows


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

Categories
12c dba security

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 :

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

Categories
java security

TCPS and SSLv2Hello

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

Categories
dba security

check if using tcps part II

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

Categories
support

12cR2

Categories
dba sqlplus

DBMS_METADATA.GET_DDL in sqlplus

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.

Categories
12c powershell

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

Categories
12c java

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

Categories
powershell

powershell goodies for Active Directory

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

Categories
12c

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

Categories
java

check jdbc version

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

Categories
linux unix

fun with cron

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!

Categories
11gR2 12c sql

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

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

Categories
dba sql

disable commit in procedure

There is an obscure syntax that prevents a procedure from issuing a commit


alter session DISABLE COMMIT IN PROCEDURE;

According to the doc, it prevents procedure from committing your data

Test case

SQL> alter session DISABLE COMMIT IN PROCEDURE
Session altered.
SQL> create table t(x number)
Table created.
SQL> create or replace procedure p is
begin
commit;
end;
Procedure created.
SQL> insert into t values (1)
1 row created.
SQL> exec p
BEGIN p; END;
Error at line 17
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "SCOTT.P", line 3
ORA-06512: at line 1

But some sys procedures may bypass this restriction

SQL> exec dbms_stats.gather_table_stats(user, 'T')
PL/SQL procedure successfully completed.
SQL> rollback
Rollback complete.
SQL> select * from t

X
----------
1

The row was silently committed.

Categories
sql tuning

Index suggestion from the access advisor

Test case :

create table t(x varchar2(8) primary key,
y varchar2(30));
insert into t(x,y) select
to_char(rownum,'FM00000000'),
object_name from all_objects where rownum<1e4; commit; exec dbms_stats.gather_table_stats(user,'T')

One user wants to filter on x but does not do the casting properly

SQL> select * from t where x=00000001;

X Y
-------- ------------------------------
00000001 CON$

He received the expected data.

Let's check his plan

SQL> explain plan for
select * from t where x=00000001;
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------
Plan hash value: 2153619298
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------

Predicate Information
(identified by operation id):
-----------------------------------
1 - filter(TO_NUMBER("X")=00000001)

Obviously, he is not using the primary key index. He should use single quotes literal

select * from t where x='00000001'

Okay, let's tune 😉

SQL> VAR start_time VARCHAR2(32)
SQL> VAR end_time VARCHAR2(32)
SQL> exec select to_char(sysdate,
'MM-DD-YYYY HH24:MI:SS') into :start_time
from dual
SQL> select * from t where x=00000001;

X Y
-------- ------------------------------
00000001 CON$
SQL> exec select to_char(sysdate,
'MM-DD-YYYY HH24:MI:SS') into :end_time
from dual
SQL> VAR task_id NUMBER
SQL> VAR task_name VARCHAR2(32)
SQL> EXEC :task_name := 'ADV01'
SQL> EXEC DBMS_ADVISOR.CREATE_TASK (
DBMS_ADVISOR.SQLACCESS_ADVISOR,
:task_id, :task_name)
SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER
(:task_name, 'EXECUTION_TYPE', 'INDEX_ONLY')
SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER
(:task_name, 'VALID_TABLE_LIST', 'SCOTT.T')
SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER
(:task_name, 'START_TIME', :start_time)
SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER
(:task_name, 'END_TIME', :end_time)
SQL> exec DBMS_SQLTUNE.CREATE_SQLSET ('STS01')
SQL> declare
c DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open c for select value(t) from table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE) t;
DBMS_SQLTUNE.LOAD_SQLSET('STS01', c);
end;
SQL> exec DBMS_ADVISOR.ADD_STS_REF
(:task_name, null, 'STS01')
SQL> EXEC DBMS_ADVISOR.EXECUTE_TASK (:task_name)
SQL> select
dbms_advisor.get_task_script(:TASK_NAME)
from dual;

DBMS_ADVISOR.GET_TASK_SCRIPT(:TASK_NAME)
----------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.4.0 -
Rem
Rem Username: SCOTT
Rem Task: TASK_54589
Rem Execution date:
Rem

CREATE INDEX "SCOTT"."T_IDX$$_D53D0000"
ON "SCOTT"."T"
(TO_NUMBER("X"))
COMPUTE STATISTICS;

I have retrieved the index suggestion from the SQL Cache for the table T.

Let's blindly implement it...

SQL> CREATE INDEX "SCOTT"."T_IDX$$_D5150000"
ON "SCOTT"."T"
(TO_NUMBER("X"))
COMPUTE STATISTICS;
SQL> explain plan for
select * from t where x=00000001
Explain complete.
SQL> select * from table(dbms_xplan.display)

PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 4112678587

-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | T_IDX$$_D5150000 |
-----------------------------------------------

Predicate Information
(identified by operation id):
-----------------------------------------------
2 - access(TO_NUMBER("X")=00000001)

Much better. But ...

SQL> insert into t(x) values('UNKNOWN');
insert into t(x) values('UNKNOWN')
Error at line 1
ORA-01722: invalid number

Adding a function-based-index on to_number(x) to the table also implies that no number is allowed in that column. This is an application change. Be aware...

Categories
sql developer

execute Sybase procedures from Oracle SQL Developer

Oracle SQL Developer provides access to data and code from other database systems than Oracle

If you run Sybase procedures (or Microsoft SQL Server I suppose), you cannot use the BEGIN / DECLARE / EXEC in anonymous blocks


begin
print 'HELLO'
end



Error starting at line : 1 in command -
begin
print 'HELLO'
end
Error report -
Incorrect syntax near the keyword 'begin'.



exec p



Error starting at line : 1 in command -
exec p
Error report -
Incorrect syntax near the keyword 'BEGIN'.

Ô rage ô désespoir!

But actually there is a way to twist the syntax so that it does not look like an oracle exec !

But in fact you create a procedure with CREATE PROCEDURE and end the procedure with a slash. Within the procedure you can use all the keywords you want. To call a procedure, do not use the exec keyword


set echo on

drop procedure p1
/

drop procedure p2
/

create procedure p1 as
return 123
/

create procedure p2 as
declare @x int, @y varchar(255)
exec @x = p1
set @y = convert(varchar(255), @x)
print @y
/

p2



> drop procedure p1
procedure P1 dropped.
> drop procedure p2
procedure P2 dropped.
> create procedure p1 as
return 123
PROCEDURE P1 compiled
> create procedure p2 as
declare @x int, @y varchar(255)
exec @x = p1
set @y = convert(varchar(255), @x)
print @y
PROCEDURE P2 compiled
> p2
123

Okay, it looks a bit like a sqlplus mutant, but it does the trick sometimes when you need to only call a procedure and get it’s return code.

The sames applies for other rdbms, here db2


set echo on

drop procedure p
/

CREATE PROCEDURE P
LANGUAGE SQL
P1: BEGIN
DECLARE X int;
END P1
/

call p



> drop procedure p
procedure P dropped.
> CREATE PROCEDURE P
LANGUAGE SQL
P1: BEGIN
DECLARE X int;
END P1
PROCEDURE P compiled
> call p
call p

UPDATE: check dermotoneill for additional tricks!

Categories
12c sql

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
IS BEGIN RETURN c (n); END;
SELECT
f(
CAST(
COLLECT(
CAST(
sal AS NUMBER
) ORDER BY sal DESC
)
AS SYS.odcinumberlist
),
2
) as secondmax
FROM emp;

SECONDMAX
----------
3000

Another 12c syntax would be


SELECT sal secondmax
FROM emp
ORDER BY sal DESC
OFFSET 1 ROW
FETCH FIRST 1 ROW ONLY;

SECONDMAX
----------
3000

Categories
linux unix

Testing for (non-)empty string in shell

One way to test for (non-)empty string is to use test and -z (-n)


$ x=foo
$ test -z "$x"
$ echo $?
1

This is mostly seen with an if and [ -z … ] syntax

$ y=bar
$ if [ -n "$y" ];
then echo non-empty;
fi
non-empty

Instead of a variable, it could be the output of a script.

Like

if [ -n "$(grep ORA- alertDB01.log)" ]
then
echo there is an error in the alert log
else
echo "fine :)"
fi

This will work for years until one day you get :

ksh: no space

Why that? This is the way the shell works. Your shell (here ksh on AIX) starts having errors as soon as your subshell (here the grep) is exhausting the space.

$ wc -l alertDB01.log
2 alertDB01.log
$ if [ -n "$(grep ORA- alertDB01.log)" ];
then echo non-empty;
else echo "fine :)";
fi
non-empty
$ wc -l alertDB01.log
75025 alertDB01.log
$ if [ -n "$(grep ORA- alertDB01.log)" ];
then echo non-empty;
else echo "fine :)";
fi
ksh: no space

You got a memory error, how the shell will react is random (core dump, errors, continue, crashes). It will just bug and you do not want this.

There is more than one to circumvent this. For instance you could use the return code of grep

$ if grep ORA- alertDB01.log >/dev/null;
then echo non-empty;
else echo "fine :)";
fi
non-empty

Different shells (Bash / Bourne) and different OSs (Linux / AIX / HPUX) may react differently. If AIX crashed with a 50’000 lines, it may scale up to millions of lines in recent Linux’s – but still use trucks of memory

Categories
dba security

The long long route to Kerberos

If you want to single-sign-on to your database with your Windows credentials, be aware, it is hard! But the benefit is quite valuable, no more saved password on the client, central password management and user expiration, compliance to the security guidelines, and at no extra cost

Landscape for my setup

  • One PC with Windows (PC01.EXAMPLE.COM)
  • One DB Server with Unix (DBSRV01.EXAMPLE.COM)
  • One Microsoft Active Directory Server (MSAD01.EXAMPLE.COM)

Username

  • user01

Tools for troubleshooting

My Software

  • PC : Oracle Client 11.2.0.3
  • Unix : Oracle Server 11.2.0.4
  • On AD : MSAD 2008

There are a lot of buggy releases (it makes me think Oracle does not test Kerberos properly)

Some hits :
11.2.0.2 : Bug 12635212 : TCP/88 is not working.
12.1.0.1 : Bug 17890382 : ZTK return value: 6

Also your PC must be using Kerberos (which is the case if you login to your Active Directory). The DB server needs some client libraries (krb5.client.rte on AIX).

System changes:

  • PC : edit etc\services

  • C:\> find " 88" %SystemRoot%\system32\drivers\etc\services

    ---------- C:\WINDOWS\SYSTEM32\DRIVERS\ETC\SERVICES
    kerberos 88/tcp kerberos5 krb5 kerberos-sec #Kerberos
    kerberos 88/udp kerberos5 krb5 kerberos-sec #Kerberos

  • Unix : edit /etc/services

  • $ grep -w 88 /etc/services
    kerberos 88/tcp kerberos5 krb5 # Kerberos
    kerberos 88/udp kerberos5 krb5 # Kerberos

  • On AD : disable pre-authentication
    this option has to be set for every user, under user -> user01 -> Properties -> Account -> Account options -> Select “Do not require Kerberos preauthentication”

Those are quite painful. There is a bug 2458563 fixed in 8.1.7.4.99 (whatever it means) that should have addressed pre-authentication. still required on 11.2.0.4 apparently no longer needed with a 11.2.0.4 client
Editing etc/services to add the “kerberos5” string means you need admin rights on Windows and root on Unix.

Okay, now you need to create the config files. You probably should use Kerberos v5 MIT.

Kerberos5 was released in 1993, not sure why you want to use something older than this… Okay, for kerberos4, released in the 80’s, you would need on the PC and on the DB Server something like

EXAMPLE.COM
EXAMPLE.COM MSAD01.EXAMPLE.COM admin server

Otherwise you need to specify : sqlnet.kerberos5_conf_mit=true
I have an open SR to support regarding : 12c upgrade guide
The SQLNET.KERBEROS5_CONF_MIT networking parameter is no longer supported in sqlnet.ora

Okay, here the configuration files

krb5.conf on the database server and on the PC

[libdefaults]
default_realm = EXAMPLE.COM

[realms]
EXAMPLE.COM = {
kdc = MSAD01.EXAMPLE.COM
}

[domain_realm]
.example.com = EXAMPLE.COM
example.com = EXAMPLE.COM

The config file location (kerberos4 or 5) is specified by sqlnet.kerberos5_conf.

There should be a technical account for your db server created on the MSAD that matched your db server.

On Active Directory, you create a user (e.g. : oracle_DBSRV01) who must not change password on first login. Then you extract the keytab with ktpass

ktpass.exe -princ oracle/dbsrv01.example.com@EXAMPLE.COM -mapuser oracle_DBSRV01 -crypto all -pass password -out c:\dbsrv01.keytab

As an Oracle DBA, you will probably ask this to another team who is used to Kerberos.

To verify it, you can list the content of the keytab

$ $ORACLE_HOME/bin/oklist -k dbsrv01.keytab
Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 12:45:08

Copyright (c) 1996, 2013 Oracle. All rights reserved.

Service Key Table: dbsrv01.keytab

Ver Timestamp Principal
4 01-Jan-1970 01:00:00 oracle/dbsrv01.example.com@EXAMPLE.COM

The principal name must match your full qualified host name. You cannot use a DNS alias.

On your PC check for the login name :

PS> $o = New-Object DirectoryServices.DirectorySearcher;
$o.Filter = 'servicePrincipalName=oracle/dbsrv01.example.com';
$o.FindOne().properties.samaccountname
oracle_DBSRV01

And you can verify the principal of that user

C:\> setspn -L oracle_DBSRV01
Registered ServicePrincipalNames for CN=oracle_DBSRV01,OU=MiscUsers,DC=example,DC=com
:
oracle/dbsrv01.example.com

Now you’ve got your keytab, this must be on the DB Server only (and must be readable for oracle). The location is specified by SQLNET.KERBEROS5_KEYTAB.

Next step is the credential cache (CC) parameter. On your PC with the Oracle 11g client, you must set sqlnet.kerberos5_cc_name to OSMSFT://
On the server it is not neeeded. On Oracle 12c client, you must set it MSLSA:, but due to bug 17890382, it is not working yet (metalink comment : We will have to wait […] bugs are under investigation).

But before you start, you may want to test the ticket.

On Unix, you can get the ticket with kinit and check it with klist. You need to have your configuration in /etc/krb5/krb5.conf (OS Dependent). Do not forget to destroy your credential cache with kdestroy / okdstry while testing

For the DB Server

$ /usr/krb5/bin/kinit -k -t dbsrv01.keytab oracle/dbsrv01.example.com@EXAMPLE.COM
$ /usr/krb5/bin/klist
Ticket cache: FILE:/var/krb5/security/creds/krb5cc_99
Default principal: oracle/dbsrv01.example.com@EXAMPLE.COM

Valid starting Expires Service principal
01/16/14 17:41:26 01/17/14 03:41:26 krbtgt/EXAMPLE.COM@EXAMPLE.COM
Renew until 01/17/14 17:41:26


$ /usr/krb5/bin/kinit user01@EXAMPLE.COM
Password for user01@EXAMPLE.COM:
Ticket cache: FILE:/var/krb5/security/creds/krb5cc_99
Default principal: user01@EXAMPLE.COM

Valid starting Expires Service principal
01/16/14 17:35:57 01/17/14 03:35:57 krbtgt/EXAMPLE.COM@EXAMPLE.COM
Renew until 01/17/14 17:35:57

Now we can test the okinit (oracle kinit) tool to do the same. There are some specific trace options that could be set

Here the complete sqlnet.ora on the server

DIAG_ADR_ENABLED = OFF
TRACE_DIRECTORY_OKINIT = /var/opt/oracle/krb/cc
TRACE_FILE_OKINIT = okinit
TRACE_LEVEL_OKINIT = SUPPORT
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle
SQLNET.AUTHENTICATION_SERVICES= (BEQ,KERBEROS5)
SQLNET.KERBEROS5_CC_NAME = /var/opt/oracle/krb/cc/krb5cc_99
SQLNET.KERBEROS5_CONF = /var/opt/oracle/krb/krb5.conf
SQLNET.KERBEROS5_KEYTAB = /var/opt/oracle/krb/dbsrv01.keytab
sqlnet.kerberos5_conf_mit=true

Note the authentication service. If kerberos is not working, you may no longer be able to log / as sysdba and also some db links may no longer work.

Also note SQLNET.AUTHENTICATION_KERBEROS5_SERVICE, which is the prefix of your principal, oracle/dbsrv01.example.com@EXAMPLE.COM

Then we use okinit as we did for kinit

$ $ORACLE_HOME/bin/okinit -k -t dbsrv01.keytab oracle/dbsrv01.example.com@EXAMPLE.COM

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 17:52:21

Copyright (c) 1996, 2013 Oracle. All rights reserved.
$ $ORACLE_HOME/bin/oklist

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 17:55:27

Copyright (c) 1996, 2013 Oracle. All rights reserved.

Ticket cache: /var/opt/oracle/krb/cc/krb5cc_99
Default principal: oracle/dbsrv01.example.com@EXAMPLE.COM

Valid Starting Expires Principal
16-Jan-2014 17:54:30 17-Jan-2014 01:54:30 krbtgt/EXAMPLE.COM@EXAMPLE.COM

$ $ORACLE_HOME/bin/okinit user01@EXAMPLE.COM

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 18:15:02

Copyright (c) 1996, 2013 Oracle. All rights reserved.

Password for user01@EXAMPLE.COM:
$ $ORACLE_HOME/bin/oklist

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 18:15:12

Copyright (c) 1996, 2013 Oracle. All rights reserved.

Ticket cache: /var/opt/oracle/krb/cc/krb5cc_99
Default principal: user01@EXAMPLE.COM

Valid Starting Expires Principal
16-Jan-2014 18:15:06 17-Jan-2014 02:15:02 krbtgt/EXAMPLE.COM@EXAMPLE.COM

In case of error, grep for “k5” in the trace file.

Do the same on the PC01. To test okinit, temporary change the CC cache to a file


sqlnet.authentication_services=(kerberos5)
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle

DIAG_ADR_ENABLED=off
TRACE_DIRECTORY_CLIENT=C:\oracle\krb5
TRACE_UNIQUE_CLIENT=on
TRACE_FILE_CLIENT=kerb_client

sqlnet.kerberos5_conf_mit=true
sqlnet.kerberos5_conf=C:\oracle\krb5\krb5.conf
#sqlnet.kerberos5_cc_name=OSMSFT://
sqlnet.kerberos5_cc_name=C:\oracle\krb5\krbcc

and test as in Unix.

Some errors will be easier to find out with a network sniffer on port 88

With AIX

tcpdump -v -v port 88

On Windows
Start -> Microsoft Network Monitor -> Microsoft Network Monitor -> New capture -> Display filter

Frame.Ethernet.Ipv4.TCP.Port == 88 or Frame.Ethernet.Ipv4.UDP.Port == 88

-> Apply -> Start

If you for instance only see UDP packets but no TCP packets, you probably hit bug 12635212.

I still have some KDC_ERR_S_PRINCIPAL_UNKNOWN errors with my working setup, don’t worry about those.

Ok, now that okinit works, the next step is to log in the database.

Check the adapters on the db server

$ adapters

Installed Oracle Net transport protocols are:

IPC
BEQ
TCP/IP
SSL
RAW

Installed Oracle Net naming methods are:

Local Naming (tnsnames.ora)
Oracle Directory Naming
Oracle Host Naming
Oracle Names Server Naming
NIS Naming

Installed Oracle Advanced Security options are:

RC4 40-bit encryption
RC4 56-bit encryption
RC4 128-bit encryption
RC4 256-bit encryption
DES40 40-bit encryption
DES 56-bit encryption
3DES 112-bit encryption
3DES 168-bit encryption
AES 128-bit encryption
AES 192-bit encryption
AES 256-bit encryption
MD5 crypto-checksumming
SHA-1 crypto-checksumming
Kerberos v5 authentication
RADIUS authentication

Create the user on the database db01 on the server dbsrv01. You need to have OS_AUTHENT_PREFIX=”” and do not set REMOTE_OS_AUTHENT (if you have it set, why would you need Kerberos?)


SQL> create user user01 identified externally as 'user01@example.com';
User created.
SQL> grant create session to user01;
Grant succeeded.

Connect from the PC

$ sqlplus -L /@db01

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 16 18:40:43 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

This also works in Toad, SQL-Developer and other tools using the OCI thick client. Just let “User” and “Password” blank.

In SQLDeveloper, make sure you do not check Kerberos but you use OCI Thick and no username and password

There is probably a way to do it with the jdbc thin client as document in Note 1523651.1, I have not gone that far yet

Update:
Note 303436.1 : Improper format of configuration file: Remove TAB characters from KRB5.conf file. Replace with spaces.

Categories
license security

Do you really need ASO?

If you only use the Advanced Security Option for SSL, you may not need to pay for it !

License 11.2
When used with Oracle Real Application Clusters, Oracle Advanced Security SSL/TLS is included.

But also
License 11.1
Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.

If SSL/TLS is no longer part of Advanced Security, what is then Oracle Advanced Security SSL/TLS ?