Change in 12c JDBC Behavior - setDate & getDate Does Not Truncate Timestamp

The 12c driver (12.1.0.2, ojdbc7.jar) certified for JDK7 and JDK8 does not truncate or set to "00:00:00" the time component when called with setDate and getDate methods. Same could be observed for 12c driver for JDK 6 as well (ojdbc6.jar). The test code given at the end of the post could be used to demonstrate this change in behavior compared to 11g2 (11.2.0.4, ojdbc6.jar certified for JDK6, JDK7 and JDK8) driver. Various driver/database/JDK compatible matrix could be found here.
The test java code runs on JDK 8 and database is a 12.1.0.2 CDB. The only thing changes between tests is the JDBC driver.
First the test is run with 11.2 (11.2.0.4) driver. The output (truncated here) will have no time component on the values inserted or returned by setDate and getDate methods. Timestamp method is used to check what is stored in the table. The output is of the following form: the method (getDate or getTimestamp) used, the driver used to insert the value and value returned by get method used.

Date 11.2.0.4.0 2016-10-12 00:00:00
Timestamp 11.2.0.4.0 2016-10-12 00:00:00.0

There's no issues to reading these values with a 12.1 (12.1.0.2, ojdbc7.jar) driver, the output will be the same (run the test commenting calls to delete and insert methods)

Date 11.2.0.4.0 2016-10-12 00:00:00
Timestamp 11.2.0.4.0 2016-10-12 00:00:00.0

Changing the driver to 12c results in date being inserted with a time component (run test by un-commenting the previously commented methods).

Date 12.1.0.2.0 2016-10-12 12:26:12
Timestamp 12.1.0.2.0 2016-10-12 12:26:12.0

Depending on the application logic upgrade to 12c driver could cause issues due to this change in behavior. In such cases going back to 11.2 driver may work in some cases, as getDate would truncate the time component. But the actual value stored has the time component, shown here in the getTimestamp method, so the behavior is not entirely reversible by reverting to the 11.2 driver.

Date 12.1.0.2.0 2016-10-12 00:00:00
Timestamp 12.1.0.2.0 2016-10-12 12:26:12.0

There's few bug reports on MOS related this issue (Bug 19297927, Bug 20551186, 1944845.1). But the final outcome seem to be that this deliberate (Bug 17766200) and expected behavior in 12c driver (2177909.1) and that oracle documentation isn't reflecting it (Bug 18124680).



Therefore, if the use of 12c driver is a must then oracle has few workarounds and patches for this. One option is to use a calendar object and set the time components to 0 (1944845.1). Other is to apply patch 21161279 (2177909.1). The patch 21161279 supersedes patch 19297927 which only patched the setDate method. So getDate will return values inserted with time component. Once the patch 21161279 is applied following JVM option must be set in order for the set/get Date method to behave as 11.2 driver's methods. Patch has no effect unless this option is set

-Doracle.jdbc.DateZeroTime=true

Once patched and JVM option is set the run the test with 12.1 driver. The time components will be set to 0.

Date 12.1.0.2.0 2016-10-12 00:00:00
Timestamp 12.1.0.2.0 2016-10-12 00:00:00.0

This behavior and output values are same as 11.2 driver.
Same patch and JVM option could be used for 12.1 driver for JDK 6 as well (ojdbc6.jar).

Useful metalink notes
getDate() Gets Time After Upgrading To 12c [ID 2177909.1]
JDBC 12c Adds Timestamp to java.sql.Date After Upgrading From 11.2.0.4 [ID 1944845.1]
Bug 19297927 : CHANGE OF BEHAVIOR IN JDBC 12.1 DUE TO BUG 14389749 CAUSES QUERIES TO FAIL
Bug 17766200 : GETDATE AND SETDATE DO NOT TRUNCATE TIME IN JDBC 12C
Bug 20551186 : GETDATE DIFFERS WITH THE JDBC DRIVER VERSION 11G AND 12C
Bug 18124680 : GETDATE AND SETDATE DO NOT TRUNCATE TIME IN JDBC 12C BUT DOC STATES OTHERWISE
Bug 17228297 : JDBC DRIVER OJDBC*.JAR 12.1.0.1.0 SPECIFICATION VIOLATION REGRESSION

Related Post
java.sql.SQLException: Could not commit with auto-commit set on When Using 12c JDBC Driver

Update 29 March 2017
Same behavior is observed with 12.2.0.1 driver. To remove the time portion the JVM option (-Doracle.jdbc.DateZeroTime=true) is required. No patch is needed.

Test Table DDL

create table datetest (version varchar2(20), indate date);

Java Test Code

public class OJDBC7DateTest {

    public static void main(String[] args) throws SQLException {

        OracleDataSource ds = new OracleDataSource();
        ds.setUser("asanga");
        ds.setPassword("asa");
        ds.setURL("jdbc:oracle:thin:@192.168.0.66:1521/onepdb");

        Connection con = ds.getConnection();
        con.setAutoCommit(false);

        System.out.println("Auto commit status : " + con.getAutoCommit());
        DatabaseMetaData meta = con.getMetaData();

        System.out.println("Driver Name " + meta.getDriverName());
        System.out.println("Driver Version " + meta.getDriverVersion());
        System.out.println("Driver Major Version " + meta.getDriverMajorVersion());
        System.out.println("Driver Minor Version " + meta.getDriverMinorVersion());
        System.out.println("Database Major Version " + meta.getDatabaseMajorVersion());
        System.out.println("Database Minor Version " + meta.getDatabaseMinorVersion());

        System.out.println("delete");
        delete(con);
        
        System.out.println("insert");
        insert(con,meta.getDriverVersion());
        
        System.out.println("select");
        select(con);
              
        con.close();
    }

    public static void select(Connection con) throws SQLException {

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        PreparedStatement ps = con.prepareStatement("select * from datetest order by 1");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {

            System.out.println("Date " + rs.getString(1) + " " + sdf.format(rs.getDate(2)));
            System.out.println("Timestamp " + rs.getString(1) + " " + rs.getTimestamp(2));
        }
        rs.close();
        ps.close();

    }

    public static void insert(Connection con, String version) throws SQLException {

        PreparedStatement ps = con.prepareStatement("insert into datetest values(?, ?)");
        Date cd = new Date(System.currentTimeMillis());
        ps.setString(1, version);;
        ps.setDate(2, cd);
        ps.execute();
        con.commit();
        ps.close();

    }
    
    public static void delete(Connection con) throws SQLException {

        PreparedStatement ps = con.prepareStatement("delete from datetest");
        ps.execute();
        con.commit();
        ps.close();

    }
}

Related Posts
java.sql.SQLException: Could not commit with auto-commit set on When Using 12c JDBC Driver
getBoolean in 12c JDBC Driver and java.sql.SQLException: Fail to convert to internal representation

Oracle vs Standard JDBC Batching For Updates

Oracle provided two flavors of batching with the JDBC drivers. One was called standard which followed the JBDC 2.0 specification and the other flavor was an Oracle specific implementation (called Oracle Batching) which is independent of JDBC 2.0. It is not possible to mix both these modes in the same application. However with the latest implementation of the JDBC driver (12c) Oracle update batching is depreciated. Evolution of the documentation over the years on this is given below.
From 10.2 JDBC developers guide Oracle update batching is a more efficient model because the driver knows ahead of time how many operations will be batched. In this sense, the Oracle model is more static and predictable. With the standard model, the driver has no way of knowing in advance how many operations will be batched. In this sense, the standard model is more dynamic in nature.
From 11.2 JDBC developers guide Oracle recommends that you use JDBC standard features when possible. This recommendation applies to update batching as well. Oracle update batching is retained primarily for backwards compatibility.
From 12.1 JDBC developers guide Starting from Oracle Database 12c Release 1 (12.1), Oracle update batching is deprecated. Oracle recommends that you use standard JDBC batching instead of Oracle update batching.
As the Oracle mode batching is depreciated the comparison only holds for lower versions of the JDBC drivers. The test related to this post was carried out using 11.2 JDBC driver. The java code used for the test is given at the end of the post. Two tests were carried out. First involving updating number of rows in a table with standard, standard implementation of oracle batching and oracle batching. Standard batching test only execute the update batch once. The standard implementation mimics what the Oracle standard does by executing the update batch at fixed intervals (500 and 1000 updates at a time). Finally Oracle batching will use the values of 500 and 1000 for batch values. This test is to check if there's any considerable difference in the timing of the updates. Result table is given below.

# Updates	Standared Batching	Standared Batching - 500	Standared Batching - 1000	Oracle Batching - 500	Oracle Batchnig - 1000
100	0.35	0.35	0.36	0.36	0.36
200	0.62	0.62	0.65	0.63	0.63
500	1.43	1.41	1.47	1.43	1.45
1000	2.76	2.73	2.88	2.81	2.81
2000	5.72	5.38	5.64	5.54	5.68
5000	13.55	13.43	13.99	13.67	13.79
10000	27.82	26.58	28.05	27.31	27.58
25000	80.01	75.43	78.65	76.42	76.93
50000	165.24	158.99	164.32	159.13	159.04
75000	243.25	242.92	248.73	242.17	242.79
100000	330.92	330.84	333.34	327.65	331.22

There's not much difference in terms of time it takes to complete all the updates whether it is via standard batching or oracle batching. Scatter plot shown below.

The next test involved updating a column with a blob (size 8KB). The results table is given below.

# Updates	Standared Batching	Standared Batching - 500	Standared Batching - 1000	Oracle Batching - 500	Oracle Batchnig - 1000
100	1.47	1.53	1.50	1.68	1.76
200	2.79	2.96	2.91	3.14	3.19
500	6.88	7.37	7.14	7.68	7.87
1000	13.60	14.70	14.20	15.30	15.50
2000	27.31	29.25	30.13	30.07	30.41
5000	68.30	74.10	74.89	77.23	74.82
10000	143.31	151.05	143.14	151.68	152.43
25000	348.83	351.34	365.54	384.77	394.99
50000	701.67	727.28	707.79	857.79	859.15

There's not much difference in the update time between the different standard batching tests.

However there's some advantage when using standard batching compared to oracle standard as the number of updates are 25,000 or over. But the increase number of batching size requires more memory and this situation may be uncommon. For lower values again there's not much difference in terms of time saved.

These tests have shown that batching mode (Oracle or standard) does not have a real influence on the time taken for the updates. As mentioned earlier as of 12c the Oracle standard batching is depreciated and if these results hold true for 12c driver as well then there shouldn't be any performance degradation.

Test java code used

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.pool.OracleDataSource;

/**
 *
 * @author Asanga
 */
public class Update {
    
    public static void main(String[] args) {
        try {
            OracleDataSource dataSource = new OracleDataSource();
               dataSource.setURL("jdbc:oracle:thin:@192.168.0.66:1521:ent11g2");
               dataSource.setUser("asanga");
               dataSource.setPassword("asa");               
               
//               String SQL = "update x set b = ?, c = ? where a = ?"; // update blob
               String SQL = "update x set b = ? where a = ?";
               
               Connection con = dataSource.getConnection();
               con.setAutoCommit(false);
               
               long t1 = System.nanoTime();
               
//               byte[] lob = new byte[8*1024];
//               lob[10] =10;
//               lob[8000]=20;
               
               PreparedStatement pr = con.prepareStatement(SQL);
//               ((OraclePreparedStatement)pr).setExecuteBatch(1000);
               for(int i =1; i <= 75000; i++){
                   
                   StringBuilder b = new StringBuilder("khf").append(i);
                   
                   pr.setString(1, b.toString());
//                   pr.setBytes(2,lob );
                   pr.setInt(2, i);
                   
                   pr.addBatch();
                   
                   if((i > 0) && (i%1000 == 0)){
                       int[] ret =pr.executeBatch();
                       System.out.println(ret.length);
                   }
//                   pr.executeUpdate();
               }
               
               int[] ret = pr.executeBatch();
//               ((OraclePreparedStatement)pr).sendBatch();
               con.commit();
               long t2 = System.nanoTime();
               
               System.out.println("returned "+ret.length+" time : "+(t2-t1));
//               System.out.println("time : "+(t2-t1));
               
               pr.close();
               con.close();
               
        } catch (SQLException ex) {
            Logger.getLogger(Update.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

Using OracleConnectionWrapper to Clear Session Context in JDBC Connection Pools

Using application context to limit to the data visibility is one way VPDs are implemented. However in application with JDBC session pools this creates an additional challenge since session context is not cleared automatically when the connection close method is closed. It's only a logical close and connection return to the pool with session context values intact. Following java code illustrate this behavior. The JDBC libraries used are

Driver Name Oracle JDBC driver
Driver Version 11.2.0.3.0
Driver Major Version 11
Driver Minor Version 2
Database Major Version 11
Database Minor Version 2

The DBConnection class which is the connection pool class is the same class used earlier and not listed below.

public class Test {

    public static void main(String[] args) {

        try {

            DBConnection pool = new DBConnection("sbx", "sbx", "192.168.0.99", "1521", "fgacdb");
            
            System.out.println("setting context");
            testSet(pool);
            System.out.println("context set done connection closed");
            Thread.sleep(1 * 5 * 1000);
            System.out.println("getting from pool after close");
            testGet(pool);
            System.out.println("explicit clearing");
            testClear(pool);
            System.out.println("explicit clearing done");
            Thread.sleep(1 * 5 * 1000);
            System.out.println("getting context value after clear");
            testGet(pool);

        } catch (Exception ex) {

            ex.printStackTrace();
        }

    }

    public static void testSet(DBConnection pool) throws SQLException {

        Connection[] cons = new Connection[10];

        for (int i = 0; i < 10; i++) {

            cons[i] = pool.getConnection();
        }

        for (int i = 0; i < 10; i++) {
            try {
                Connection con = cons[i];

                CallableStatement cls = con.prepareCall("{call fgac_ctx_pkg.fgac_set_context(?,?)}");
                cls.setString(1, "user_id");
                cls.setString(2, "user " + i);
                cls.executeUpdate();
                cls.close();
                con.close();

            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public static void testGet(DBConnection pool) throws SQLException {

        Connection[] cons = new Connection[10];

        for (int i = 0; i < 10; i++) {

            cons[i] = pool.getConnection();
        }

        for (int i = 0; i < 10; i++) {
            try {
                Connection con = cons[i];

                PreparedStatement pr = con.prepareStatement("select sys_context ('fgac_ctx','user_id') from dual");
                ResultSet rs = pr.executeQuery();

                while (rs.next()) {

                    System.out.println(rs.getString(1));
                }

                rs.close();
                pr.close();
                con.close();

            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public static void testClear(DBConnection pool) throws SQLException {

        Connection[] cons = new Connection[10];

        for (int i = 0; i < 10; i++) {

            cons[i] = pool.getConnection();
        }

        for (int i = 0; i < 10; i++) {
            try {
                Connection con = cons[i];

                CallableStatement cls = con.prepareCall("{call fgac_ctx_pkg.fgac_clear_context(?)}");
                cls.setString(1, "user_id");

                cls.executeUpdate();

                cls.close();
                con.close();

            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

Code uses following PL/SQL package

create or replace package fgac_ctx_pkg is

procedure fgac_set_context( i_attribute in varchar2, value in varchar2 );
procedure fgac_clear_context (i_attribute in varchar2 );

end fgac_ctx_pkg;
/

create or replace package body fgac_ctx_pkg is

procedure fgac_set_context
( i_attribute in varchar2, value in varchar2 )
is
begin
dbms_Session.set_context('fgac_ctx',i_attribute, value);
end;

procedure fgac_clear_context
( i_attribute in varchar2 )
is
begin
dbms_Session.clear_context('fgac_ctx',attribute => i_attribute);
end;

end fgac_ctx_pkg;
/

create or replace context fgac_ctx using FGAC_CTX_PKG;

Running the code would give the following output

setting context
context set done connection closed
getting from pool after close
user 2
user 1
user 0
user 3
user 4
user 5
user 7
user 6
user 8
user 9
explicit clearing
explicit clearing done
getting context value after clear
null
null
null
null
null
null
null
null
null
null

It could be seen from the output that after setting session context and calling the connection close method, it is still possible to get the session context values when connections are borrowed from the connection pool. The solution is to explicitly call the session clear method. After the explicit clear the session context values are no longer there hence the null output.


Explicitly setting and clearing maybe feasible for a small program but when application is already developed and trying to add these additional lines may require considerable amount of work and testing. This is where the OracleConnectionWrapper comes in handy which allows to wrap the connection close method and allows to call the explicit clear method from one place. This requires minimum work for an existing application. Similarly setting the context value would be done by overriding the getConnection method of the connection pool class.
Extend OracleConnectionWrapper to create a customized wrapper.

public class MyOracleConnectionWrapper extends OracleConnectionWrapper {

    public MyOracleConnectionWrapper(Connection con) {

        super((OracleConnection) con);
    }

    @Override
    public void close() throws SQLException {


        System.out.println("clearing");

        CallableStatement cls = super.prepareCall("{call fgac_ctx_pkg.fgac_clear_context(?)}");
        cls.setString(1, "user_id");
        cls.executeUpdate();
        cls.close();
        super.close();
    }
}

Modify Connection Pool class by overriding getConnection method and populating session context with user id using application specific methods.

public class DBConnection {

    private PoolDataSource pds = null;

    public DBConnection(String username, String password, String host, String port, String sid) {
        try {
            pds = PoolDataSourceFactory.getPoolDataSource();
            pds.setUser(username);
            pds.setPassword(password);
            pds.setURL("jdbc:oracle:thin:@" + host + ":" + port + ":" + sid);
            pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
            pds.setInitialPoolSize(10);
            pds.setMinPoolSize(10);
            pds.setMaxPoolSize(15);

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public Connection getConnection() throws SQLException {

        MyOracleConnectionWrapper con = new MyOracleConnectionWrapper((OracleConnection) pds.getConnection());

        CallableStatement cls = con.prepareCall("{call fgac_ctx_pkg.fgac_set_context(?,?)}");
        cls.setString(1, "user_id");
        cls.setString(2, "user " + applicationSessionObj.getUserID());
        cls.executeUpdate();
        cls.close();

        return con;

    }
}

With this code when a connection is borrowed from the connection pool it will have session context values associated with that user id and when connection close method is called session context will be cleared.
Similar to session context auto commit set at connection level is also not reset when returned to pool. To examine this behavior change for loop code where connections are borrowed from pool as follows on testSet method

for (int i = 0; i < 10; i++) {

            cons[i] = pool.getConnection();
            System.out.println(cons[i].getAutoCommit());
            cons[i].setAutoCommit(false);

        }

and on testGet method

 for (int i = 0; i < 10; i++) {

            cons[i] = pool.getConnection();
            System.out.println(cons[i].getAutoCommit());
        }

This will show the status of auto commit when connections are initially borrowed from the connection pool versus status after auto commit is set to false, connection closed and returned to pool and borrowed from pool again.

Use of WM_CONCAT Can Exhaust Temporary Tablespace in 11gR2 & 10gR2

WM_CONCAT function could be used aggregate multiple row values into single row. Below is a short example

create table x (a number, b varchar2(10));

SQL> insert into x values(1,'a');
1 row created.
SQL> insert into x values (2,'b');
1 row created.
SQL> insert into x values(1,'c');
1 row created.
SQL> insert into x values(2,'d');
1 row created.
SQL> commit;

SQL> select a,wm_concat(b) as concats from x group by a;

         A CONCATS
---------- ----------
         1 a,c
         2 b,d

However wm_concat is an undocumented function not for direct use by customers (more on this later on) but that doesn't prevent developers from using this function.
However there's a danger that extensive invocation of this function could exhaust the temporary tablespace. Primary reason for this is that wm_concat has a sort aggregation in it that result in temporary tablespace segments being used

SQL> explain plan for select wm_concat('abc'||'def') from dual;

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Plan hash value: 3910148636

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

(no matter what, tested db instance had 7G SGA, 3G PGA and this was the only test case running, plenty of memory to do the sort in memory) but these are not released until the connection is physically close, which means exit from sql*plus or closing a connection pool in a the case of jdbc connection pools. This effect could be demonstrated with following test case using 11gR2 (11.2.0.3.3) Linux 64-bit instance.
Create a temporary tablespace of 5M and assign it as the default temporary tablespace for the test user

create temporary tablespace temp5m TEMPFILE size 5m autoextend on next 1m maxsize 5m;
alter user asanga temporary tablespace temp5m;

Run the sql with wm_concat from 5 different sql*plus session and monitor the temporary tablespace segment usage.
Temporary tablespace usage before test starts

SELECT A.inst_id,
  A.tablespace_name TABLESPACE,
  D.mb_total,
  SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
  D.mb_total         - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM gv$sort_segment A,
  (SELECT B.INST_ID,
    B.name,
    C.block_size,
    SUM (C.bytes) / 1024 / 1024 mb_total
  FROM gv$tablespace B,
    gv$tempfile C
  WHERE B.ts#  = C.ts#
  AND c.inst_id=b.inst_id
  GROUP BY B.INST_ID,
    B.name,
    C.block_size
  ) D
WHERE A.tablespace_name = D.name
and a.tablespace_name='TEMP5M'
AND A.inst_id           =D.inst_id
GROUP BY a.inst_id,
  A.tablespace_name,
  D.mb_total
ORDER BY 1,2;

 INST_ID TABLESPACE   MB_TOTAL    MB_USED    MB_FREE
-------- ---------- ---------- ---------- ----------
       1 TEMP5M              5          0          5

Run sql on first sql*plus session

SQL> select wm_concat('abc'||'def') as X from dual;

X
--------
abcdef

Temporary segment usage

 INST_ID TABLESPACE   MB_TOTAL    MB_USED    MB_FREE
-------- ---------- ---------- ---------- ----------
       1 TEMP5M              5          1          4

The above sql could be run on the same sql*plus session multiple times without increasing the temporary segment usage. As soon as the sql is run from a different sql*plus session the temporary segment usage increase. After running on second sql*plus session

 INST_ID TABLESPACE   MB_TOTAL    MB_USED    MB_FREE
-------- ---------- ---------- ---------- ----------
       1 TEMP5M              5          2         3

Similarly 4 sql*plus session could be used to run the above mentioned sql and at the end of running the sql on 4th sqlplus session temporary segment usage will be

 INST_ID TABLESPACE   MB_TOTAL    MB_USED    MB_FREE
-------- ---------- ---------- ---------- ----------
       1 TEMP5M              5          4          1

Running on the 5th sql*plus session will result in the following error

SQL> select wm_concat('abc'||'def') as X from dual;
ERROR:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP5M
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 31

Use the following sql to verify the sql statments using temporary segments (SQL from 317441.1)

SQL> SELECT a.username,
  2    a.sid,
  3    a.serial#,
  4    a.osuser,
  5    b.tablespace,
  6    b.blocks,
  7    b.segtype,
  8    c.sql_text
  9  FROM v$session a,
 10    v$tempseg_usage b,
 11    v$sqlarea c
 12  WHERE a.saddr    = b.session_addr
 13  AND c.address    = a.sql_address
 14  AND c.hash_value = a.sql_hash_value
 15  ORDER BY b.tablespace,
 16    b.blocks;

USERNA        SID    SERIAL# OSUSER TABLESPACE     BLOCKS SEGTYPE   SQL_TEXT
------ ---------- ---------- ------ ---------- ---------- --------- --------------------------------------------------
ASANGA          9       6487 oracle TEMP5M            128 LOB_DATA  select wm_concat('abc'||'def') as X from dual
ASANGA        202        209 oracle TEMP5M            128 LOB_DATA   select wm_concat('abc'||'def') as X from dual
ASANGA        136       3207 oracle TEMP5M            128 LOB_DATA  select wm_concat('abc'||'def') as X from dual
ASANGA         78        377 oracle TEMP5M            128 LOB_DATA  select wm_concat('abc'||'def') as X from dual

The temporary segments will not be released until sql*plus session is close (by exit). Even though the table has no LOB column the temp segments are shown as LOB_DATA. Lobs are used within the wm_concat it is why the temp segment appear as LOB_DATA.


On java applications that use connection pool, (UCP or DataSource) connections are not physically closed when the connection close function is called. The close is a logical close and physical connection remains open in the connection pool ready to be used by another session. As such the temporary segment usage will keep on increasing until the temporary tablespace is exhausted since connection pool is never closed except when the application (or application server in most cases) is restarted,dies or crashed.
Following java code could be used to demonstrate how this could affect java application using connection pool. Class imports are not shown

public class DBConnection {

    private PoolDataSource pds = null;

    public DBConnection(String username,String password, String host,String port, String sid) {
        try {
            pds = PoolDataSourceFactory.getPoolDataSource();
            pds.setUser(username);
            pds.setPassword(password);
            pds.setURL("jdbc:oracle:thin:@"+host+":"+port+":"+sid);
            pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
            pds.setInitialPoolSize(10);
            pds.setMinPoolSize(10);
            pds.setMaxPoolSize(15);
            
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public Connection getConnection() throws SQLException {

         return pds.getConnection();
    }
}


public class TempSegThread extends Thread {

    private DBConnection pool;

    public TempSegThread(DBConnection pool) {
        this.pool = pool;
    }

    @Override
    public void run() {
        try {
            for (int i = 0; i < 1; i++) {

                int j = 0;
                Connection con = pool.getConnection();
                PreparedStatement pr = con.prepareStatement("select wm_concat('abc'||'def') from dual");
                ResultSet rs = pr.executeQuery();
                while (rs.next()) {
                    System.out.println(getName() +" "+ rs.getString(1));
                }
                rs.close();
                pr.close();
                con.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(TempSegThread.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}


public class ThreadExec {

    public static void main(String[] args) {

//       if (args.length != 5) {
//
//            System.out.println("usage: java ThreadExec username password host port sid");
//        } else {

            try {

                DBConnection pool = new DBConnection("asanga","asa","192.168.0.66","1521","ent11g2");
                //Change to TempSegThread[4] to run without unable to extend temp segment
                TempSegThread [] tempThread = new TempSegThread[5];

                for(int i = 0 ; i < tempThread.length; i++){
                    tempThread[i] = new TempSegThread(pool);
                    tempThread[i].start();
                }

                for(TempSegThread t : tempThread){
                    t.join();
                }
                Thread.sleep(5 * 60 * 60 * 1000);

            } catch (Exception ex) {
                ex.printStackTrace();
            }
//        }
    }
}

Program run the same sql used in sql*plus test and run it in 5 threads using 5 different connections. One of the threads will fail with unable to extend temporary segment while others finish without error. Changing the thread count to 4 will allow 4 threads to run without error and at the end of their execution program will sleep for 5 minutes giving enough time to execute temporary segment usage SQL to see that temporary segments are still being held and usage hasn't gone down even after connections are closed.
As mentioned in metalink note 1384829.1 using "60025 trace name context forever" is of no help in this case even though temporary segments are classified as lob_data.
Oracle has mentioned in many documents that wm_concat "is not meant to be used by customers directly, and could be changed/updated without notice by Oracle Development. Do not use the WMSYS.WM_CONCAT view in your application" (From 1300595.1). So the best case is not to use it as if there's any issue it's unlikely this would qualify for support. (When a SR was raised this is exactly what Oracle said, wm_concat is not for end users).
The other versions tested 11gR1 (11.1.0.7.12) didn't have this issue but 10gR2 (10.2.0.5.8) did have the same issue as 11gR2.

Useful metalink notes
WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function [ID 1336219.1]
Problem with WMSYS.WM_CONCAT Function after Upgrading [ID 1300595.1]
SQL USING WM_CONCAT RUNS SLOWER IN 10.2.0.5 and 11.2.0.2 [ID 1393596.1]
How to Release Temporary LOB Segments without Closing the JDBC Connection [ID 1384829.1]
How to Release the Temp LOB Space and Avoid Hitting ORA-1652 [ID 802897.1]

Related Post
WM_CONCAT Removed From 12c Workspace Manager?

Calling Web Service with wget

Wget - "The non-interactive network downloader." (from the man page) could be used to call web service if a request XML is available. This is useful to quickly test a web service.
1. First step is to capture the SOAP request in XML format. Wireshark could be used for this. Below is a sample of a request which is used to test the failover setup for application server connection when RAC is used. The request will send the client's machine name as the input and will respond with the instance connected at the back end.

<?xml version='1.0' encoding='UTF-8'?>
  <S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
      <ns2:getAppAndDBServerNames xmlns:ns2="http://asanga/">
        <arg0>pc120</arg0>
      </ns2:getAppAndDBServerNames>
    </S:Body>
</S:Envelope>

2. Save the XML request in a file. This file will be used as the input to the wget.
3. Execute the wget command as shown below

wget http://192.168.0.66:8080/FailOverTest/FailOverTestPort --post-file=input.xml 
--header="Content-Type: text/xml" --output-document=soapResponse.xml

input.xml is the file with xml request created in step 2 above. Response from the WS will be saved into soapResponse.xml. 192.168.0.66:8080 is the IP and port of the server where web service is deployed and /FailOverTest/FailOverTestPort is the service called.

$ wget http://192.168.0.66:8080/FailOverTest/FailOverTestPort --post-file=input.xml --header="Content-Type: text/xml" --output-document=soapResponse.xml
--2012-09-20 11:47:08--  http://192.168.0.66:8080/FailOverTest/FailOverTestPort
Connecting to 192.168.0.66:8080... connected.
HTTP request sent, awaiting response... 200 OK
Length: 331 [text/xml]
Saving to: `soapResponse.xml'

100%[============================================================================================================>] 331         --.-K/s   in 0s

2012-09-20 11:47:08 (35.1 MB/s) - `soapResponse.xml' saved [331/331]

4. The soapResponse.xml will have the output in XML format.

<?xml version="1.0" ?>
  <S:Envelopexmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
    <S:Body>
      <ns2:getAppAndDBServerNamesResponse xmlns:ns2="http://asanga/">
        <return>
          Connected to ent11g2 DB Server hpc1.domain.net Application Server on Thu Sep 20 11:34:02 BST 2012
        </return>
      </ns2:getAppAndDBServerNamesResponse>
    </S:Body>
  </S:Envelope>

Connection Leak Detection on Applications Servers

Connection leak detection on a 3-tier application servers present a challenge as the connecting database user name would be same for all the connections. Therefore when a connection leak happens unless the usecase is known, or if it's a something recently started then leak is probably due to some new deployment it's diffcult to detect from the database site. One way is finding the number of session with the same sqlid for previous sql

select inst_id,machine,username, prev_sql_id,count(*) from gv$session where username='ASANGA' group by inst_id,machine,username, prev_sql_id order by 1,2,3,4

SQL id with the highest count is a suspect but may not always be the case.

Use of logon triggers would be of no use if a connection pool is used as when connections are handed out from the pool logon trigger doesn't fire.

To identify the exact point in the code where the leak happening is to populate the CLIENT_INFO column with line and class name before handing over the connection. One way this could be accomplished is by overrding the getConnection method.

Throwable throwable = new Throwable();
StackTraceElement[] stackTraceElements = throwable.getStackTrace();
StackTraceElement element = stackTraceElements[stackTraceElements.length > 0 ? 1 : 0];
CallableStatement clm = con.prepareCall("begin DBMS_APPLICATION_INFO.set_client_info(?); end;");
// String x = ste.getClassName() + ":" + ste.getMethodName() + ":" + ste.getLineNumber();
// client info column has a limit of varchar2(64),String is truncated to be 63 chars
StringBuilder y = new StringBuilder(ste.getClassName()).append(":").append(ste.getMethodName()).append(":").append(ste.getLineNumber());
clm.setString(1, (y.length() > 63 ? y.substring(0,63).toString(): y.toString()) );
clm.execute();
clm.close();

The above code snippet will populate the CLIENT_INFO column with the class name, method name and line number.Quering the v$session will show these values

select username,client_info from v$session where username is not null;

USERNAME CLIENT_INFO
-------- ---------------------------
ASANGA   com.Test : main : 86
ASANGA   com.Test : main : 79
ASANGA   com.Test : main : 79
ASANGA   com.Test : main : 79
ASANGA   com.Test : main : 85

Update 02 October 2012
There's another way to populate the v$session columns without making a database call. This is done by setting end to end metrics on the connection. Above code which makes a call using DBMS_APPLICATION_INFO could be changed to use end to end metric (this will populate client_identifier column instead of client_info column as above)

Throwable throwable = new Throwable();
StackTraceElement[] stackTraceElements = throwable.getStackTrace();
StackTraceElement element = stackTraceElements[stackTraceElements.length > 0 ? 1 : 0];
String metrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
// client_identifier has a limit of varchar2(64),String is truncated to be 63 chars
StringBuilder y = new StringBuilder(ste.getClassName()).append(":").append(ste.getMethodName()).append(":").append(ste.getLineNumber());
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = y.length() > 63 ? y.substring(0, 63).toString() : y.toString();//ste.getClassName() + " : " + ste.getMethodName() + " : " + ste.getLineNumber();
((OracleConnection) con).setEndToEndMetrics(metrics, (short) 0);

This way there's no need to make upfront db call to populate the columns in v$session. Beside end to end client id index other constants that could be used are

OracleConnection.END_TO_END_ACTION_INDEX :- populate action column
OracleConnection.END_TO_END_MODULE_INDEX :- populate module column

Columns are not populated as soon as the metrics are set on the connection, a sql query must be executed for the columns to get populated. This is explained in Bug 3735857 - V$SESSION.OSUSER not populated for JDBC clients [ID 3735857.8]

Update 05 October 2012
How to Set the value for the column "PROGRAM" of View V$SESSION from a Universal Connection Pool (UCP) [ID 1152523.1] explain somewhat similar procedure. Main difference is earlier information to populate the v$session was set at connection level whereas on this note using UCP it is done at connection pool level meaning all connections will have the same information.

Update 16 January 2013
Beside the method for setting class name, method name and line number there must be a mechanism to clear this information when the jdbc connection is closed. This information is not cleared when connection close method is called. Therefore it won't be possible to distinguish between connections that are opened but not closed vs closed connection. OracleConnectionWrapper could be used to wrap the close method and clear the text set in the getConnection. Following is an example where text "closed" is set on client_info column for closed connections.

@Override
    public void close() throws SQLException {

        CallableStatement clm = super.prepareCall("begin DBMS_APPLICATION_INFO.set_client_info(?); end;");
        clm.setString(1,"closed");
        clm.execute();
        clm.close();
        super.close();

    }

Clearing cannot be done using end to end metrics as they require an SQL to be executed for the columns to get populated. Also both PL/SQL (for clearing) and end to end metric (for setting) cannot be used together as they populate two different columns.

Useful metalink notes
Simple EndToEndMetrics Demonstration [ID 1264780.1]
Bug 3735857 - V$SESSION.OSUSER not populated for JDBC clients [ID 3735857.8]
No more data to read from socket" is Thrown When End-to-end Metrics is Used [ID 1081275.1]

New On Java 7

Following are some of the things new on Java 7. Taken from various java documentations.

Catching More Than One Type of Exception with One Exception Handler

In Java SE 7 and later, a single catch block can handle more than one type of exception. This feature can reduce code duplication and lessen the temptation to catch an overly broad exception.

In the catch clause, specify the types of exceptions that block can handle, and separate each exception type with a vertical bar (|):http://www.blogger.com/img/blank.gif

catch (IOException|SQLException ex) {
logger.log(ex);
throw ex;
}

Note: If a catch block handles more than one exception type, then the catch parameter is implicitly final. In this example, the catch parameter ex is final and therefore you cannot assign any values to it within the catch block.


Using Non-Reifiable Parameters with Varargs Methods

Generics in varargs
The compiler issues a warning "warning: [unchecked] unchecked generic array creation" when generics are passed as varargs. This warning could be suppressed with @SafeVarArgs annotation on the method, which would ensure that the method wouldn't perform any unsafe operation.

public static void main(String[] args) {
        compute(new HashMap(), 
new TreeMap());
       }

@SafeVarargs
    static void compute(Map... args) {
        }
}

Using Strings in switch Statements
A switch works with the byte, short, char, and int primitive data types. It also works with enumerated types (discussed in Enum Types), the String class, and a few special classes that wrap certain primitive types: Character, Byte, Short, and Integer (discussed in Numbers and Strings).

In Java SE 7 and later, you can use a String object in the switch statement's expression.

public static int getMonthNumber(String month) {

int monthNumber = 0;

if (month == null) { return monthNumber; }

switch (month.toLowerCase()) {
case "january":    monthNumber =  1; break;
case "february":   monthNumber =  2; break;
case "march":      monthNumber =  3; break;
case "april":      monthNumber =  4; break;
case "may":        monthNumber =  5; break;
case "june":       monthNumber =  6; break;
case "july":       monthNumber =  7; break;
case "august":     monthNumber =  8; break;
case "september":  monthNumber =  9; break;
case "october":    monthNumber = 10; break;
case "november":   monthNumber = 11; break;
case "december":   monthNumber = 12; break;
default:           monthNumber =  0; break;
}

return monthNumber;
}

The String in the switch expression is compared with the expressions associated with each case label as if the String.equals method were being used.

Diamond syntax
Until Java 7, generic instances were created with

Map<Integer, String> map = new HashMap<Integer, String>();

In Java 7 only refernce requires type information and actual type could be without type information.

Map<Integer, String> map = new HashMap<>();

Binary literal and underscore in numeric literals
Binary values could be defined as

int number = 0b1100110; // decimal 102

Lengthy numeric values can be separated using underscores

long longnum = 28_06_86L;
long verylongnumber = 2334_7656_4503_3844L;

Automatic Resource Management
With the new Java 7 try block syntax, resources declared while defining the try block will automatically close when the control comes out of the try block.

try (BufferedReader reader = new BufferedReader(new FileReader(fileName))) {
while ((line = reader.readLine()) != null) {
         //do some work
       }
    } catch (IOException ex) {
 }

Fast Connection Failover Behavior Change in 11.2

Fast Connection Failover (FCF) allows jdbc connections to failover to surviving nodes in a RAC when one or more nodes are unavailable. This is very useful to have with middleware application server connection pools so when rolling upgrades or patch application is done on the database the connections in the middleware server would failover to surviving instances and client connections would not receive any errors.

It seem with 11.2.0.2 and 11.2.0.1 this doesn't work same as in 11gR1 and 10gR2.

There are two metalink notes available to test fcf on 10gR2 (How to Verify and Test Fast Connection Failover (FCF) Setup from a JDBC Thin Client Against a 10.2.x RAC Cluster [ID 433827.1]) and 11gR1 (Fast Connection Failover (FCF) Test Client Using 11g JDBC Driver and 11g RAC Cluster [ID 566573.1]).

In this case the java code provided with 566573.1 was used to test the failover of 10gR2 (10.2.0.4) , 11gR1 (11.1.0.7) and 11gR2 (11.2.0.1 and 11.2.0.2)

There are some differences between 433827.1 and 566573.1 note. In 10gR2 note the ons configuration uses vip hostname whereas in the 11gR1 note machine hostname is used. But for the testing purpose vip hostnames were used throughout (and 11gR1 has been deployed on production system using the vip hostname for ons configuration and failover worked without any problem), for 11gR2 it was tested with both vip hostname and hostname but there was no change in the result.

Furthermore the ons remote port on 11gR2 is changed to 6200 (same as 10gR2) but on 11gR1 it was 6251. The onsctl ping command on 10gR2 and 11gR1 provided the remote port this is no longer the case on 11gR2 but remote port could be observed with onsctl debug.

$GI_HOME/bin/onsctl ping
ons is running ...

$GI_HOME/bin/onsctl debug
HTTP/1.1 200 OK
Content-Length: 2488
Content-Type: text/html
Response:

== rac4.domain.net:6200 3640 11/03/02 15:36:32 ==
Home: /opt/app/11.2.0/grid

======== ONS ========
IP ADDRESS                   PORT    TIME   SEQUENCE  FLAGS
--------------------------------------- ----- -------- -------- --------
192.168.0.86  6200 4d6e6210 00000002 00000008

Listener:

TYPE                BIND ADDRESS               PORT  SOCKET
-------- --------------------------------------- ----- ------
Local                                  127.0.0.1  6100      5
Remote                                       any  6200      7
Remote                                       any  6200      

ojdbc6.jar and ons.jar from a 11gR2 home was used for all the test.

The test code gets 5 jdbc connections from the connection pool and prints to which instance these connections point to and close them and waits 30 seconds and re-tries the same procedure. During the 30 second wait one of the instnaces is shutdown with

srvctl stop instance -d dbname -i instancename -o abort

If the FCF is working properly then connections to the aborted instance would have been cleaned up and all the connections in the pool will be valid connections, connected to surviving node(s).

Output from running against the 10gR2 RAC

java support.au.fcf11g.FCFDemo
>> PROGRAM using JDBC thin driver, no oracle client required
>> ojdbc5.jar (for JDK 1.5) or ojdbc6.jar (for JDK 1.6) and ons.jar from $ORACLE_HOME/opmn/lib directory must be in the CLASSPATH
>> Press CNTRL C to exit running program

** Retrieving 5 connections from pool **
5 connections have been retrieved..

=============
Database Product Name is ... Oracle
Database Product Version is  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

-- Connection number #1
Instance -> rac10g21
Host -> rac1
Service -> rac10g2.domain.net
-- Connection number #2
Instance -> rac10g21
Host -> rac1
Service -> rac10g2.domain.net
-- Connection number #3
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #4
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #5
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..
Woke up, will continue now..

** Retrieving 5 connections from pool **
5 connections have been retrieved..

-- Connection number #1
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #2
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #3
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #4
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net
-- Connection number #5
Instance -> rac10g22
Host -> rac2
Service -> rac10g2.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..

The two connections that were connected to instance rac10g21 has been cleaned up and all the connections now point to rac10g22.

Output from running against 11gR1 RAC

java support.au.fcf11g.FCFDemo
>> PROGRAM using JDBC thin driver, no oracle client required
>> ojdbc5.jar (for JDK 1.5) or ojdbc6.jar (for JDK 1.6) and ons.jar from $ORACLE_HOME/opmn/lib directory must be in the CLASSPATH
>> Press CNTRL C to exit running program

** Retrieving 5 connections from pool **
5 connections have been retrieved..

=============
Database Product Name is ... Oracle
Database Product Version is  Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

-- Connection number #1
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #2
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #3
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #4
Instance -> rac11g12
Host -> rac2
Service -> rac11g1.domain.net
-- Connection number #5
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..
Woke up, will continue now..

** Retrieving 5 connections from pool **
5 connections have been retrieved..

-- Connection number #1
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #2
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #3
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #4
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net
-- Connection number #5
Instance -> rac11g11
Host -> rac1
Service -> rac11g1.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..

There was one connection to rac11g12 instnace and this has been cleaned up and all the connections now point to rac11g11.

Now the 11.2 RACs first against 11.2.0.1

java support.au.fcf11g.FCFDemo
>> PROGRAM using JDBC thin driver, no oracle client required
>> ojdbc5.jar (for JDK 1.5) or ojdbc6.jar (for JDK 1.6) and ons.jar from $ORACLE_HOME/opmn/lib directory must be in the CLASSPATH
>> Press CNTRL C to exit running program

** Retrieving 5 connections from pool **
5 connections have been retrieved..

=============
Database Product Name is ... Oracle
Database Product Version is  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

-- Connection number #1
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #2
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net
-- Connection number #3
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net
-- Connection number #4
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #5
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..
Woke up, will continue now..

** Retrieving 5 connections from pool **
5 connections have been retrieved..

-- Connection number #1
Error running query: No more data to read from socket
-- Connection number #2
Error running query: No more data to read from socket
-- Connection number #3
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net
-- Connection number #4
Error running query: No more data to read from socket
-- Connection number #5
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 3
-----------

Sleeping for 30 seconds..

As seen from the above output the FCF hasn't happened and an error is thrown on the java program.

Running against 11.2.0.2

java support.au.fcf11g.FCFDemo
>> PROGRAM using JDBC thin driver, no oracle client required
>> ojdbc5.jar (for JDK 1.5) or ojdbc6.jar (for JDK 1.6) and ons.jar from $ORACLE_HOME/opmn/lib directory must be in the CLASSPATH
>> Press CNTRL C to exit running program

** Retrieving 5 connections from pool **
5 connections have been retrieved..

=============
Database Product Name is ... Oracle
Database Product Version is  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.2.0.2.0
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 2
=============

-- Connection number #1
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net
-- Connection number #2
Instance -> rac11g22
Host -> rac5
Service -> rac11g2.domain.net
-- Connection number #3
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #4
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #5
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 0
-----------

Sleeping for 30 seconds..
Woke up, will continue now..

** Retrieving 5 connections from pool **
5 connections have been retrieved..

-- Connection number #1
Error running query: No more data to read from socket
-- Connection number #2
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #3
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #4
Instance -> rac11g21
Host -> rac4
Service -> rac11g2.domain.net
-- Connection number #5
Error running query: No more data to read from socket

-- Displaying Cache Details --
NumberOfAvailableConnections: 0
NumberOfActiveConnections: 5
Number of query failures: 2
-----------

Sleeping for 30 seconds..

Since the FCF is not working when the application receives connections that are connected to the instnace that was shutdown error is thrown.

SR has been raised blog will be updated with the outcome.





Update 03 March 2011

Oracle has pointed out several metalink note regarding this. First it was stated this is expected behavior and mentioned in the metalink note
How To Use JDBC FCF Feature To Detect That a RAC Node Instance Has Been Shutdown ? [ID 364005.1]This error would have thrown if a node went down after a connection is taken from the pool and before doing some work with it. But the behavior change does not lie there but how connection pool cleans up invalid connections.

Two more metalink notes were pointed out Oracle Universal Connection Pool (UCP) and Deprecation of JDBC Implicit Connection Cache [ID 743726.1] and How to Verify Universal Connection Pool (UCP) / Fast Connection Failover (FCF) Setup [ID 1064652.1]

It seem with 11gR2 implicit connectoin caching is deprecated. Tests wer done using the code provided with 1064652.1 on 11gR2 and 11gR1 and still (with this whatever implicit caching mechanism present or not present on the jdbc side using UCP was common on both test cases) the difference on connection pool clean up is there. First test was on 11gR2 RAC.

----------- UCP Details ---------
NumberOfAvailableConnections: 4 <== 4 connection in the pool
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #13  -> instance[rac11g22], host[rac5], service[rac11g2.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 4
BorrowedConnectionsCount: 1
---------------------------------

====================== instance shutdown =============================

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Connection retry necessary : No more data to read from socket
** FCFTest :
** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Connection retry necessary : No more data to read from socket <== expected behavior on 11gR2
** FCFTest :
** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #16  -> instance[rac11g22], host[rac5], service[rac11g2.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 2 <== connection pool cleaned up of invalid connections
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #17  -> instance[rac11g22], host[rac5], service[rac11g2.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 2
BorrowedConnectionsCount: 1
---------------------------------

Now with 11gR1

java au.support.jdbc.scan.fcf.FCFTest
Started UCP FCF Test at Thu Mar 03 11:21:03 GMT 2011
** UCPPool : retrieveConnection
** FCFTest : Query #1  -> instance[rac11g12], host[rac2], service[rac11g1.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 4 <== 4 connections in the pool
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #2  -> instance[rac11g11], host[rac1], service[rac11g1.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 4
BorrowedConnectionsCount: 1
---------------------------------
================== instance shutdown and connection pool gets cleaned up immediately.

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #3  -> instance[rac11g12], host[rac2], service[rac11g1.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 1 <== pool is cleaned up immediately no connection retry
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #4  -> instance[rac11g12], host[rac2], service[rac11g1.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 1
BorrowedConnectionsCount: 1
---------------------------------

SR is ongoing will be updated.

Update 11 March 2011
Running the ucp test case with logging enabled showed the failover events in 11gR2 was missing the domain part of the service name whereas in 11gR1 the full service name including domain was sent. Logs below from the test for 11gR1

** UCPPool : connection returned to pool
2011-03-07T12:15:57.904+0000 UCP FINEST seq-43,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setEventType eventType: database/event/service
2011-03-07T12:15:57.904+0000 UCP FINEST seq-44,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.validateEventType eventType: database/event/service
2011-03-07T12:15:57.904+0000 UCP FINEST seq-45,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent. eventType: database/event/service, eventBody: VERSION=1.0 service=rac11g1.domain.net instance=rac11g11 database=rac11g1 host=rac1 status=down reason=user
2011-03-07T12:15:57.905+0000 UCP FINEST seq-46,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setServiceName serviceName: rac11g1.domain.net
2011-03-07T12:15:57.905+0000 UCP FINEST seq-47,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setInstanceName instanceName: rac11g11
2011-03-07T12:15:57.905+0000 UCP FINEST seq-48,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setDbUniqueName dbUniqueName: rac11g1
2011-03-07T12:15:57.906+0000 UCP FINEST seq-49,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setHostName hostName: rac1
2011-03-07T12:15:57.906+0000 UCP FINEST seq-50,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setStatus status: down
2011-03-07T12:15:57.906+0000 UCP FINEST seq-51,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setReason reason: user

On 11gR2 the domain name is missing on the service name

** UCPPool : connection returned to pool
2011-03-07T12:41:47.951+0000 UCP FINEST seq-49,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setEventType eventType: database/event/service
2011-03-07T12:41:47.951+0000 UCP FINEST seq-50,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.validateEventType eventType: database/event/service
2011-03-07T12:41:47.952+0000 UCP FINEST seq-51,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent. eventType: database/event/service, eventBody: VERSION=1.0 service=rac11g2 instance=rac11g21 database=rac11g2 host=rac4 status=down reason=USER
2011-03-07T12:41:47.952+0000 UCP FINEST seq-52,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setServiceName serviceName: rac11g2
2011-03-07T12:41:47.952+0000 UCP FINEST seq-53,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setInstanceName instanceName: rac11g21
2011-03-07T12:41:47.953+0000 UCP FINEST seq-54,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setDbUniqueName dbUniqueName: rac11g2
2011-03-07T12:41:47.953+0000 UCP FINEST seq-55,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setHostName hostName: rac4
2011-03-07T12:41:47.953+0000 UCP FINEST seq-56,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setStatus status: down
2011-03-07T12:41:47.954+0000 UCP FINEST seq-57,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setReason reason: user

According to Oracle Documentation provided throught the SRthis service name should match the service name in dba_services. Looking at this dba view

SQL> select service_id,name from dba_services;

SERVICE_ID NAME
---------- ------------
1 SYS$BACKGROUND
2 SYS$USERS
3 rac11g2sXDB
4 rac11g2s.domain.net
5 rac11g2XDB
6 rac11g2.domain.net

6 rows selected.

SQL> show parameter service

NAME TYPE VALUE
------------- ------ -------------------
service_names string rac11g2.domain.net

Pointed this out to oracle and was told this is related to "RDBMS Bug 9363352: SERVICE NAME HAS NO DOMAIN FOR UP/DOWN EVENTS, closed as not a bug" and at this point Oracle couldn't reproduce the error seen above (No more data to read from socket). Looking at the service name oracle is using noticed that it is a service name without a domain.

So created service name without a domain and tested.

srvctl add service -d rac11g2 -s nodomain -r "rac11g21,rac11g22"

srvctl start service -d rac11g2 -s nodomain

srvctl status service -d rac11g2 -s nodomain
Service nodomain is running on instance(s) rac11g21,rac11g22

SQL> show parameter service

NAME TYPE VALUE
------------- ------ ----------------------------
service_names string rac11g2.domain.net,nodomain

SQL> select name from dba_services;

NAME
---------------------
SYS$BACKGROUND
SYS$USERS
rac11g2sXDB
rac11g2s.domain.net
rac11g2XDB
rac11g2.domain.net
nodomain

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 11-MAR-2011 10:34:57

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                07-MAR-2011 12:48:29
Uptime                    3 days 21 hr. 46 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/rac4/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.86)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.90)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "nodomain.domain.net" has 1 instance(s).
Instance "rac11g21", status READY, has 1 handler(s) for this service...
Service "rac11g2.domain.net" has 2 instance(s).
Instance "rac11g21", status UNKNOWN, has 1 handler(s) for this service...
Instance "rac11g21", status READY, has 1 handler(s) for this service...

Ran the test with logging enabled. This time the service name has the domain name even though it was created without a one and connection pool get refresh and validated quickly just as it was with 11gR1

** UCPPool : connection returned to pool
2011-03-11T10:25:00.022+0000 UCP FINEST seq-75,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setEventType eventType: database/event/service
2011-03-11T10:25:00.022+0000 UCP FINEST seq-76,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.validateEventType eventType: database/event/service
2011-03-11T10:25:00.022+0000 UCP FINEST seq-77,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent. eventType: database/event/service, eventBody: VERSION=1.0 service=nodomain.domain.net instance=rac11g22 database=rac11g2 host=rac5 status=down reason=USER
2011-03-11T10:25:00.023+0000 UCP FINEST seq-78,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setServiceName serviceName: nodomain.domain.net
2011-03-11T10:25:00.023+0000 UCP FINEST seq-79,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setInstanceName instanceName: rac11g22
2011-03-11T10:25:00.023+0000 UCP FINEST seq-80,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setDbUniqueName dbUniqueName: rac11g2
2011-03-11T10:25:00.024+0000 UCP FINEST seq-81,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setHostName hostName: rac5
2011-03-11T10:25:00.024+0000 UCP FINEST seq-82,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setStatus status: down
2011-03-11T10:25:00.024+0000 UCP FINEST seq-83,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setReason reason: user

Waiting for Oracle reply on this.

Update 06 April 2011
Oracle came back with this is similar to "unpublished bug 9740127 fixed in version 12" also mentioned couple of bugs 8779597 and 9740127 and pointed out that if a application service was created with domain name (instead of using the default database service) failover works as expected.

So created an application service (additional service as before)

srvctl add service -d rac11g2 -s withdomain.domain.net -r "rac11g21,rac11g22"

Ran the test again and the connection failover was as before.

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #9  -> instance[rac11g21], host[rac4], service[withdomain.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 4
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #10  -> instance[rac11g21], host[rac4], service[withdomain.domain.net]

----------- UCP Details ---------
NumberOfAvailableConnections: 0
BorrowedConnectionsCount: 1
---------------------------------

In other versions (10gR2 and 11gR1) default service with or without domain would work fine in a failover situation.
Created another database without a domain name and ran the test again and saw that failover wasn't working as expected.

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #181  -> instance[testdb1], host[rac4], service[testdb]

----------- UCP Details ---------
NumberOfAvailableConnections: 1
BorrowedConnectionsCount: 1
---------------------------------

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Connection retry necessary : No more data to read from socket
** FCFTest : Apr 6, 2011 2:14 PM SUCCESS     Connections:(Available=1 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=1 upEventNewConns=1
Apr 6, 2011 2:14 PM SUCCESS      Connections:(Available=2 Affected=1 FailedToProcess=0 MarkedDown=1 Closed=1)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)
Apr 6, 2011 2:13 PM SUCCESS     Connections:(Available=1 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=1 upEventNewConns=1
Apr 6, 2011 2:12 PM SUCCESS      Connections:(Available=1 Affected=1 FailedToProcess=0 MarkedDown=1 Closed=1)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)
Apr 6, 2011 2:12 PM SUCCESS     Connections:(Available=1 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=1 upEventNewConns=1
Apr 6, 2011 2:11 PM SUCCESS      Connections:(Available=2 Affected=1 FailedToProcess=0 MarkedDown=1 Closed=1)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)
Apr 6, 2011 2:11 PM SUCCESS     Connections:(Available=1 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=1 upEventNewConns=1
Apr 6, 2011 2:10 PM SUCCESS      Connections:(Available=3 Affected=2 FailedToProcess=0 MarkedDown=2 Closed=2)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)
Apr 6, 2011 2:10 PM SUCCESS     Connections:(Available=2 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=2 upEventNewConns=2
Apr 6, 2011 2:09 PM SUCCESS      Connections:(Available=6 Affected=4 FailedToProcess=0 MarkedDown=4 Closed=4)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)
Apr 6, 2011 2:09 PM SUCCESS     Connections:(Available=3 Affected=0 FailedToProcess=0 MarkedDown=0 Closed=0)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0) cardinality=2 targetedToTear=0 tornDown=0 markedToClose=0 targetUpEventNewConns=3 upEventNewConns=3
Apr 6, 2011 2:08 PM SUCCESS      Connections:(Available=5 Affected=2 FailedToProcess=0 MarkedDown=2 Closed=2)(Borrowed=0 Affected=0 FailedToProcess=0 MarkedDown=0 MarkedDeferredClose=0 Closed=0)

** UCPPool : connection returned to pool
** UCPPool : retrieveConnection
** FCFTest : Query #183  -> instance[testdb2], host[rac5], service[testdb]

----------- UCP Details ---------
NumberOfAvailableConnections: 0
BorrowedConnectionsCount: 1
---------------------------------

Oracle confirmed this is related to using default database service and suggested to create and use application services instead of the default database service.

Update 12 March 2012
UCP delay in processing ONS events during data guard or RAC failover using FCF [ID 1380527.1]
Apply 12596492 patch (available for 11.2.0.2 and 11.2.0.3)

Oracle Security Alert for Java

Oracle issued a security alert for java which reads "This Security Alert addresses security issue CVE-2010-4476 (Java Runtime Environment hangs when converting "2.2250738585072012e-308" to a binary floating-point number), which is a vulnerability in the Java Runtime Environment component of the Oracle Java SE and Java for Business products. This vulnerability allows unauthenticated network attacks ( i.e. it may be exploited over a network without the need for a username and password). Successful attack of this vulnerability can result in unauthorized ability to cause a hang or frequently repeatable crash (complete Denial of Service) of the Java Runtime Environment. Java based application and web servers are especially at risk from this vulnerability."

This alert is valid for both (formerly Sun) Oracle JDK as well as jrockit versions 1.5 and 1.6.

For Oracle JDK the patch is available via an updater tool For Jrockit refer metalink note 1291950.1 for relevant patch number.

Patch is a jar file and easy to apply.Set the correct java binary in the PATH and

java -jar fpupdater.jar -u -v
FPUpdater
java.home: /usr/local/java/jdk1.6.0_17/jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_17
os.name: Linux
Checking for update for major: 1.6.0 minor: 17
Retrieved update jar file from tool: /usr/local/java/jdk1.6.0_17/jre/tmpUpdate264186544245683182/tmpUpdate4837341576526462084.jar
Updating files. Please note this can take several minutes to run. Allow FPUpdater tool to complete.
Jar file /usr/local/java/jdk1.6.0_17/jre/lib/rt.jar.fpupdater succesfully verified.
Done backup of rt.jar to /usr/local/java/jdk1.6.0_17/jre/lib/rt.jar.fpupdater
Made working copy of rt.jar: /usr/local/java/jdk1.6.0_17/jre/lib/tmpUpdate8923180845720805243/copyofRt.jar
Jar file /usr/local/java/jdk1.6.0_17/jre/lib/tmpUpdate8923180845720805243/copyofRt.jar succesfully verified.
Moving working copy of rt.jar back to live rt.jar.
Update applied successfully to java.home path : /usr/local/java/jdk1.6.0_17/jre

After the apply verify it with

java -jar fpupdater.jar -t -v
FPUpdater
java.home: /usr/local/java/jdk1.6.0_17/jre
java.vendor: Sun Microsystems Inc.
java.version: 1.6.0_17
os.name: Linux
Verification test available
Verification test beginning, this should take less than one minute..
Verification test passed.
Your Java Runtime is patched for java.home: /usr/local/java/jdk1.6.0_17/jre

MongoDB CRUD

MongoDB is an open source NoSQL database (used by the open source social networking site Diaspora). This blog is about basic Create,Read,Update and Delete operation using Java.

1.Installing

Download MnogoDB (in this case linux version) and untar.

create the directory /data/db, if don't want to or can't create the directory under root then create it somewhere else (/opt/data/db) and create a symbolic link in the / directory.

Start the MongoDB daemon with

mongodb-linux-x86_64-1.6.4]$ bin/mongod
bin/mongod --help for help and startup options
Thu Nov 25 16:43:57 MongoDB starting : pid=5221 port=27017 dbpath=/data/db/ 64-bit
Thu Nov 25 16:43:57 db version v1.6.4, pdfile version 4.5
Thu Nov 25 16:43:57 git version: 4f5c02f8d92ff213b71b88f5eb643b7f62b50abc
Thu Nov 25 16:43:57 sys info: Linux domU-12-31-39-06-79-A1 2.6.21.7-2.ec2.v1.2.fc8xen #1 SMP Fri Nov 20 17:48:28 EST 2009 x86_64
BOOST_LIB_VERSION=1_41
Thu Nov 25 16:43:57 [initandlisten] waiting for connections on port 27017
Thu Nov 25 16:43:57 [websvr] web admin interface listening on port 28017
Thu Nov 25 16:44:15 [initandlisten] connection accepted from 127.0.0.1:52961 #1
Thu Nov 25 16:45:21 allocating new datafile /data/db/asangadb.ns, filling with zeroes...
Thu Nov 25 16:45:21 done allocating datafile /data/db/asangadb.ns, size: 16MB,  took 0.029 secs
Thu Nov 25 16:45:21 allocating new datafile /data/db/asangadb.0, filling with zeroes...
Thu Nov 25 16:45:21 done allocating datafile /data/db/asangadb.0, size: 64MB,  took 0.123 secs
Thu Nov 25 16:45:21 [conn1] building new index on { _id: 1 } for asangadb.asangadb
Thu Nov 25 16:45:21 allocating new datafile /data/db/asangadb.1, filling with zeroes...

From another command shell connect to the DB with

bin/mongo
MongoDB shell version: 1.6.4
connecting to: test

That's it, now the DB is ready to receive connections.

2. Creating & Connecting

In MongoDB databases aren't created explicitly. A database is lazily created when it is used for the very first time.

Download the drivers for Java from the MongoDB site.

To connect to the DB specify the host name (ip) and port. (There are other ways to connect as well).

Mongo m = new Mongo("192.168.0.76", 27017);
DB db = m.getDB( "pradeepdb" );

If required it is also possible to secure the connection with password.
This case a database called "pradeepdb" will be created if it is not present, if it's present then a connect will be made to it. Whenever a new database is created following could be seen in the starter daemon (mongod) output

Thu Nov 25 17:10:30 [initandlisten] connection accepted from 192.168.0.29:52274 #11
Thu Nov 25 17:10:30 allocating new datafile /data/db/pradeepdb.ns, filling with zeroes...
Thu Nov 25 17:10:30 done allocating datafile /data/db/pradeepdb.ns, size: 16MB,  took 0.03 secs
Thu Nov 25 17:10:30 allocating new datafile /data/db/pradeepdb.0, filling with zeroes...
Thu Nov 25 17:10:30 done allocating datafile /data/db/pradeepdb.0, size: 64MB,  took 0.121 secs
Thu Nov 25 17:10:30 allocating new datafile /data/db/pradeepdb.1, filling with zeroes...
Thu Nov 25 17:10:30 [conn11] building new index on { _id: 1 } for pradeepdb.pradeepdb
Thu Nov 25 17:10:30 [conn11] done for 0 records 0.012secs

In the command shell switch between databases using

use dbname

3. Inserting

By default a a collection is created with the same name as the database name. If required a separate collection could be created. Again not explicitly, if one doesn't exists it will be created on first use.
To create or get the relevant collection use the db reference created earlier

 DBCollection col = db.getCollection("pradeep2");

To insert a key-value into this collection

DBObject obj = new BasicDBObject();
obj.put("pradeep", 200);
col.insert(obj);

To insert values in the command shell

db.pradeep2.insert({"pradeep":500});

4.Reading

To read the first object in a collection

DBObject obj =  col.findOne();

To read all the objects in a collection

DBCursor cur = col.find();
while(cur.hasNext()) {

  System.out.println(cur.next());
}

To read one specific object in a collection, first create the object with desired properties and then query the collection using that object.

BasicDBObject query = new BasicDBObject();
query.put("asanga", 100);
DBCursor cur = col.find(query);
while(cur.hasNext()) {

  System.out.println(cur.next());
}

On the command shell following could be used to get the first value and all the values

db.pradeep2.findOne();
db.pradeep2.find();

5. Updating

To update create two objects that represents past image and new image. Past image will be updated by the new image.

  DBObject oldObj = new BasicDBObject();
oldObj.put("asanga", 100);
            
DBObject newObj = new BasicDBObject();
newObj.put("asanga", 200);

col.update(oldObj, newObj);

On the command prompt use

db.pradeep2.update({"asanga":200},{"asanga":500});

6. Deleting

Create an object to repsent the object being deleted and use the remove function to delete it from the database.

DBObject obj = new BasicDBObject();
obj.put("asanga", 500);

col.remove(obj);

On command shell use

db.pradeep2.remove({"asanga":500});

Follow the MongoDB Java Tutorial for more.

Closing ResultSet early invalidates result cache

When using the result cache (/*+ result_cache */) in a SQL during the query execution phase all the dependent objects will be identified for the query and will have the status PUBLISHED in result cache object view. But it is during the result fetch phase that the actual result will be cached. Status of the result set in the aforementioned view will be NEW when the first result is fetched and then on will be ByPass for subsequent row fetches for the same result set in the same session, once all the rows are fetched without an error status will be PUBLISHED.

If for some reason result set was closed half way through (or an error happens, which is not considered in this post) the result will have a status of invalid. Subsequent running of the sql will try to create a new result set to cache but will also end of invalid if result set is closed without being traversed the full length.

However if one session traverse the entire result set and subsequent sessions only traverse few rows of the result set, these latter sessions will be using the result cache.

Test case is give below.

1. Create the tables and functions needed

SQL> create table x (a number, b varchar2(100), c as (mod(a,4)));

Table created.

SQL> begin
  2  for i in 1 .. 1000
  3  loop
  4  insert into x (a,b) values (i,i||'abcdefg');
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

create or replace package rescachetest as
type ret_type is ref cursor;
function getvalues return ret_type;
end;
/

create or replace Package Body Rescachetest As

Function Getvalues Return Ret_Type Is

Ret_Val Ret_Type;
  Begin
    Open Ret_Val For
      Select /*+ result_cache */ * From X Where C In (3,4);
    
      return ret_val;
  end;
End;
/

This uses the result cache hint inside the pl/sql function which returns a ref cursor type. But it could be verified for pure sql as well. The java code used to test use be used to run both these cases.

2. Run the java code which will take only 90 rows from the result set and close the result set.

public class ResCacheTest {

    public static void main(String[] args) {
        try {
            OracleDataSource dataSource = new OracleDataSource();
            dataSource.setURL("jdbc:oracle:thin:@url");
            dataSource.setUser("username");
            dataSource.setPassword("password");

            Connection con = dataSource.getConnection();


            // PL/SQL
            CallableStatement clm = con.prepareCall("begin ? := rescachetest.getvalues; end;");
            clm.registerOutParameter(1, OracleTypes.CURSOR);
            clm.execute();


            // SQL
//            PreparedStatement pr = con.prepareStatement("select /*+ result_cache */ * from X Where C In (3,4)");

//            System.out.println("executed");
//            Thread.sleep(10000);

            ResultSet rs = ((OracleCallableStatement)clm).getCursor(1);

//            ResultSet rs = pr.executeQuery();
            int i = 0;
            while(rs.next()){

                i++;
                System.out.println(rs.getInt(1)+" "+rs.getString(2));

//                System.out.println("get result");
//                Thread.sleep(10000);
                if(i == 90){
                    break;
                }
            }

//            System.out.println("closing");
            rs.close();
            clm.close();
//            pr.close();
            con.close();
            dataSource.close();


        } catch (Exception ex) {
            Logger.getLogger(ResCacheTest.class.getName()).log(Level.SEVERE, null, ex);
        }
   }

}

3. ADMon has been used to query the v$result_cache_objects view.

When the clm.execute(); has been called observed the following
When the first row was taken from the result set
Subsequent row fetches
Once the result set was closed after taking 90 rows
4. If the java code was to run without closing the result set after taking 90 rows results will be published in the result cache.
Subsequent execution of java code where result set is closed after taking 90 rows will not result in creating additional invalid result caches as before, and these execution will use the already publish result cache.