Enabling Encryption in JDBC Connect Strings

To enable client encryption and integrity checking in a JDBC connect string, use the following syntax:

jdbc:oracle:thin:@//hostname:1521/hrdb ?oracle.net.encryption_client=REQUIRED
&oracle.net.encryption_types_client=AES256
&oracle.net.crypto_checksum_client=REQUIRED
&oracle.net.crypto_checksum_types_client=SHA1

Once you are connected, run the following sql to verify the settings. Look for lines that list ciphers and contain the words “service adapter”. Ignore the other lines. This tells us that encryption and/or integrity checking is active for the connection.

select sid, network_service_banner
  from v$session_connect_info
 where sid = sys_context('USERENV','SID');

SID NETWORK_SERVICE_BANNER
--- --------------------------------------------------
247 TCP/IP NT Protocol Adapter for: Version 19.0.0.0.0
247 Encryption service for: Version 19.0.0.0.0
247 AES256 Encryption service adapter for: Version 19.0.0.0.0
247 Crypto-checksumming service for: Version 19.0.0.0.0
247 SHA1 Crypto-checksumming service adapter for: Version 19.0.0.0.0

ORA-39325: TABLE_EXISTS_ACTION cannot be applied to “MYSCHEMA”.”MYTABLE”

I ran an export which included the table EMP. Later I imported this into a test instance using TABLE_EXISTS_ACTION=SKIP, and received error ORA-39325: TABLE_EXISTS_ACTION cannot be applied to “MYSCHEMA”.”EMP”.

Upon further investigation, there was already a view named MYSCHEMA.EMP in the target database. So the object already existed, but not as a table. So it threw error ORA-39325.

Monitoring connection rate at listener

Here’s an example of counting logons (established connections) at the listener:

By minute:

fgrep "23-FEB-2017 10:" listener.log | fgrep "establish" | awk '{ print $1 " " $2 }' |
awk -F: '{ print $1 ":" $2 }' | sort | uniq –c

      3 23-FEB-2017 10:00
      4 23-FEB-2017 10:01
      2 23-FEB-2017 10:02
      2 23-FEB-2017 10:03
      1 23-FEB-2017 10:04

By hour:

fgrep "23-FEB-2017 " listener.log | fgrep "establish" | awk '{ print $1 " " $2 }' |
awk -F: '{ print $1  }' | sort | uniq -c


    143 23-FEB-2017 00
    132 23-FEB-2017 01
    136 23-FEB-2017 02
    158 23-FEB-2017 03
    149 23-FEB-2017 04

To filter by IP address, add this in:

    grep -E "10.1.10.10|10.1.10.11"

Db file sequential read: Which object is it waiting on?

If you encounter a ‘db file sequential read’ wait, the P1 and P2 parameters from v$session_wait can be used to identify which object the process is waiting on. This may be helpful during troubleshooting. Here’s a sample query that looks at all ‘db file sequential read’ waits:

column owner format a10
column object_name format a15
column object_type format a11

select w.sid,
       w.p1, w.p2, w.p3,
       o.owner,
       substr(o.object_name,1,30) object_name,
       o.object_type
  from dba_objects o, v$session_wait w, x$bh bh
 where bh.obj  = o.object_id(+)
   and w.p1    = bh.file#(+)
   and w.p2    = bh.dbablk(+)
   and w.event = 'db file sequential read'
union
select w.sid,
       w.p1, w.p2, w.p3,
       o.owner,
       substr(o.object_name,1,30) object_name,
       o.object_type
  from dba_objects o, v$session_wait w, x$bh bh
 where bh.obj  = o.data_object_id(+)
   and w.p1    = bh.file#(+)
   and w.p2    = bh.dbablk(+)
   and w.event = 'db file sequential read'
 order by 1
/


       SID         P1         P2         P3 OWNER      OBJECT_NAME     OBJECT_TYPE
---------- ---------- ---------- ---------- ---------- --------------- -----------
       113        308    2345988          1 HR         EMP             TABLE

       978        242    3909403          1 HR         DEPT            TABLE

      1159        187   52585099          1 HR         EMP_IDX1        INDEX

      1159        187   56776227          1 HR         DEPT_IDX1       INDEX

SQL Developer connection reset when sqlnet data integrity is enabled

A developer reported getting the following connection reset message box while using SQL Developer 4.1.3.20, and a 12c database in which encryption and sqlnet data integrity (checksumming) had been enabled. Here’s the error from Sql Developer:

"Your database connection has been reset.
Any pending transactions or session state has been lost."

Server sqlnet.ora:

SQLNET.CRYPTO_CHECKSUM_SERVER = REQUESTED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA512, SHA384, SHA256)

The developer reported that the error did not occur while using SQL Developer 4.0.0.13. I was able to reproduce the error with 4.1.3.20.

I commented out the crypto_checksum…server lines from the database server’s sqlnet.ora and reloaded the listener. Sql Developer connected successfully! So it appears that Sql Developer or its jdbc thin driver doesn’t work well with sqlnet data integrity (checksumming).

SQL Developer – Location of JDBC Driver

The JDK directory can be specified in the sqldeveloper.conf file. This file is located in SQL_DEVELOPER_HOME\sqldeveloper\bin\sqldeveloper.conf. (Reference MOS Doc ID 603917.1)

Examples:

SetJavaHome C:\Oracle\product\11.2.0\client_1\jdk   (v1.5.5)

SetJavaHome ../../jdk   (v4.1.3.20) 

“ERROR: Unable to get logical block size for spfile” when spfile is stored on ASM

This is similar to another post “ORA-01565 Unable to open Spfile when spfile is stored on ASM”.

New 12.1.0.2 install. Created database using dbca. Specified omf name for spfile.

After some database activity, the following error began appearing in the alert log:
    ERROR: Unable to get logical block size for spfile ‘+DATA/dwtst1/spfiledwtst1.ora’.

SQL> show parameter spfile

NAME       TYPE      VALUE
---------- --------- ------------------------------
spfile     string    +DATA/dwtst1/PARAMETERFILE/spfile.261.902329725

Created an alias for the spfile in ASM:

[grid]$ asmcmd
ASMCMD> cd +DATA/dwtst1

ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/

ASMCMD> ls PARAMETERFILE/
spfile.261.902329725

ASMCMD> mkalias +DATA/dwtst1/PARAMETERFILE/spfile.261.902329725 
                +DATA/dwtst1/spfiledwtst1.ora

ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    TEMPFILE/
PARAMETERFILE  UNPROT  COARSE   MAR 05 18:00:00  N    spfiledwtst1.ora => 
                             +DATA/dwtst1/PARAMETERFILE/spfile.261.902329725

ASMCMD> exit
[grid]$

This resolved the error.

Then optionally set the spfile to this alias.

SQL> alter system set spfile='+DATA/dwtst1/spfiledwtst1.ora'
                scope=spfile;
...
SQL> show parameter spfile

NAME       TYPE      VALUE
---------- --------- ------------------------------
spfile     string    +DATA/dwtst1/spfiledwtst1.ora

Possible causes:

Check if there is an init.ora in $ORACLE_HOME/dbs that references the nonexistent spfile. The spfile is normally in +DATA/dwtst1/PARAMETERFILE/. This is the scenario described in MOS Note 2145349.1 – Unable To Get Logical Block Size For Spfile Reported in Database Instance Alert Log File. An alternative is to update the spfile path that is listed in the init.ora.

Enabling encryption for sqlnet connections

To enable client encryption and integrity checking, add the following lines to the client’s sqlnet.ora:

#
# Encryption
#
SQLNET.ENCRYPTION_CLIENT = REQUESTED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, AES192, AES128)

SQLNET.ENCRYPTION_SERVER = REQUESTED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)

#
# Network integrity
#
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUESTED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA512, SHA384, SHA256)

SQLNET.CRYPTO_CHECKSUM_SERVER = REQUESTED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA512, SHA384, SHA256)

So far, so good. But how do we verify it is working? We can check using a sql query, or we can enable a sqlnet level 16 trace on the client and review trace files with encryption disabled, and with encryption enabled.

Option 1: Query to show encryption status for the current session

In the query results, look for lines that list ciphers and contain the words “service adapter”. Ignore the other lines. This tells us that encryption and/or integrity checking is active for the connection.

select sid, network_service_banner
from v$session_connect_info
where sid=sys_context('USERENV','SID');

SID NETWORK_SERVICE_BANNER
---------- ----------------------------------------------------

1465 TCP/IP NT Protocol Adapter for: Version 19.0.0.0.0

1465 Encryption service for : Version 19.0.0.0.0

1465 AES256 Encryption service adapter for: Version 19.0.0.0.0

1465 Crypto-checksumming service for: Version 19.0.0.0.0

Option 2: Query to show encrypted clients

In the query results, look for lines that list ciphers and contain the words “service adapter”. Ignore the other lines. This tells us that encryption and/or integrity checking is active for the connection.

select v.sid, v.username, v.program ,v.machine, 
       i.NETWORK_SERVICE_BANNER
     /*  ,i.*  */
  from v$session v, 
       v$session_connect_info i
 where v.sid = i.sid
   and v.serial# = i.serial#
   and username is not null
 order by v.sid, v.username;

134	SYSTEM	sqlplus.exe	Laptop	Encryption service for: Version 19.0.0.0.0
134	SYSTEM	sqlplus.exe	Laptop	AES256 Encryption service adapter for: Version 19.0.0.0.0
134	SYSTEM	sqlplus.exe	Laptop	Crypto-checksumming service for: Version 19.0.0.0.0
134	SYSTEM	sqlplus.exe	Laptop	SHA512 Crypto-checksumming service adapter for: Version 19.0.0.0.0

Option 3: Sqlnet Client Trace

In the client sqlnet.ora, add the following line: TRACE_LEVEL_CLIENT=16.

Here are some excerpts from a couple of trace files. Both traces are the result of a sqlplus session connecting to the database and issuing the command ‘select sysdate from dual;’:

Client trace without encryption:

nam_gnsp:Reading parameter "SQLNET.ENCRYPTION_CLIENT" from parameter file
nam_gnsp:Parameter not found
naequad:Using default value "ACCEPTED"

-------------------

nam_gic:Counting # of items in "SQLNET.ENCRYPTION_TYPES_CLIENT" parameter
nam_gic:Parameter not found
naesno:Using default value "all available algorithms"

--------------------

nam_gnsp:Reading parameter "SQLNET.CRYPTO_CHECKSUM_CLIENT" from parameter file
nam_gnsp:Parameter not found
naequad:Using default value "ACCEPTED"

-------------------

nam_gic:Counting # of items in "SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT" parameter
nam_gic:Parameter not found
naesno:Using default value "all available algorithms"

-------------------

na_tns:	Encryption is not active
na_tns:	Crypto-checksumming is not active


-------------------

nsbasic_bsd:18 73 65 6C 65 63 74 20  |.select.|
nsbasic_bsd:73 79 73 64 61 74 65 20  |sysdate.|
nsbasic_bsd:66 72 6F 6D 20 64 75 61  |from.dua|
nsbasic_bsd:6C 01 01 00 00 00 00 00  |l.......|

Client trace with encryption:

nam_gnsp:Reading parameter "SQLNET.ENCRYPTION_CLIENT" from parameter file
nam_gnsp:Found value "REQUESTED"

-------------------

nam_gic:Counting # of items in "SQLNET.ENCRYPTION_TYPES_CLIENT" parameter
nam_gic:Found 3 items

-------------------

nam_gnsp:Reading parameter "SQLNET.CRYPTO_CHECKSUM_CLIENT" from parameter file
nam_gnsp:Found value "REQUESTED"

-------------------

nam_gic:Counting # of items in "SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT" parameter
nam_gic:Found 3 items

-------------------

naeecom:The server chose the 'AES256' encryption algorithm
naeccom:The server chose the 'SHA512' crypto-checksumming algorithm

-------------------

na_tns:	Encryption is active, using AES256
na_tns:	Crypto-checksumming is active, using SHA512

The trace file from the unencrypted session displays lines indicating “Encryption is not active” and “Crypto-checksumming is not active”. The trace file from the encrypted session shows encryption using AES256, and integrity using SHA512.

The trace file from the unencrypted session shows a packet dump that contains the sql statement that was issued. However this string does not appear in the trace file from the encrypted session.

“rtld: 0712-001” and “rtld: 0712-002” from sqlplus with IOCP disabled on AIX

Installed 12.1.0.2 client on AIX 7.1 Power 64 bit. After the install, sqlplus returned errors “rtld: 0712-001” and “rtld: 0712-002”.

We checked if the I/O Completion ports (IOCP) module was enabled, as we previously had a similar issue while installing the database on another server. It was not enabled.

Here’s the error received when starting up sqlplus:

$ echo $ORACLE_HOME
/u01/oracle/product/12.1.0/client_1

$ sqlplus
Could not load program sqlplus:

rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
      from module /u01/oracle/product/12.1.0/client_1/lib/libttsh12.so(),
      but a runtime definition of the symbol was not found.

rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced
      from module /u01/oracle/product/12.1.0/client_1/lib/libttsh12.so(),
      but a runtime definition of the symbol was not found.

rtld: 0712-002 fatal error: exiting.

$ lsdev | grep iocp
iocp0      Defined       I/O Completion Ports 

Solution: Enable IOCP per Note #1946229.1, and installation guide for AIX.

$ lsdev | grep iocp
iocp0      Available       I/O Completion Ports

$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 25 10:13:12 2016

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

Enter user-name: system
Enter password:
Last Successful login time: Thu Feb 25 2016 10:12:42 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> exit

ORA-01565 “Unable to open Spfile” when spfile is stored on ASM

New 12.1.0.2 install. Created database using dbca. Specified omf name for spfile.

Registered database in OEM, and then started getting error:

ORA-01565: Unable to open Spfile +DATA/dwtst1/spfiledwtst1.ora.

SQL> show parameter spfile

NAME       TYPE      VALUE
---------- --------- ------------------------------
spfile     string    +DATA/dwtst1/PARAMETERFILE/spfile.261.902329725

Created an alias for the spfile in ASM:

[grid]$ asmcmd
ASMCMD> cd +DATA/dwtst1

ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/

ASMCMD> ls PARAMETERFILE/
spfile.261.902329725

ASMCMD> mkalias +DATA/dwtst1/PARAMETERFILE/spfile.261.902329725 
                +DATA/dwtst1/spfiledwtst1.ora

ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    TEMPFILE/
PARAMETERFILE  UNPROT  COARSE   MAR 05 18:00:00  N    spfiledwtst1.ora => 
                             +DATA/dwtst1/PARAMETERFILE/spfile.261.902329725

ASMCMD> exit
[grid]$

This resolved the error.

Then optionally set the spfile to this alias.

SQL> alter system set spfile='+DATA/dwtst1/spfiledwtst1.ora'
                scope=spfile;
...
SQL> show parameter spfile

NAME       TYPE      VALUE
---------- --------- ------------------------------
spfile     string    +DATA/dwtst1/spfiledwtst1.ora