Saturday, October 15, 2016

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

Saturday, October 1, 2016

Moving PDB from RAC CDB to Single Instance CDB

The RAC CDB and the non-RAC CDB are both 12.1.0.2 SE2 instances. Currently the single instance CDB doesn't have any PDB plugged on it. SE2 CDBs only support single tenancy due to license restriction. The RAC CDB has a PDB available on both instances.
INSTANCE     NAME         OPEN_MODE  STATUS    RES
------------ ------------ ---------- --------- ---
stdcdb1      PDB$SEED     READ ONLY  NORMAL    NO
stdcdb1      STDPDB       READ WRITE NORMAL    NO
stdcdb2      PDB$SEED     READ ONLY  NORMAL    NO
stdcdb2      STDPDB       READ WRITE NORMAL    NO
Both CDBs has same components except for RAC components which is set not available on the single instance CDB. DB registry of the RAC CDB
SQL> select comp_name,status from dba_registry order by 1;

COMP_NAME                                STATUS
---------------------------------------- -----------
JServer JAVA Virtual Machine             VALID
Oracle Application Express               VALID
Oracle Database Catalog Views            VALID
Oracle Database Java Packages            VALID
Oracle Database Packages and Types       VALID
Oracle Multimedia                        VALID
Oracle Real Application Clusters         VALID
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle XDK                               VALID
Oracle XML Database                      VALID
DB Registry of the non-RAC CDB
SQL> select comp_name,status from dba_registry order by 1;

COMP_NAME                                STATUS
---------------------------------------- -----------
JServer JAVA Virtual Machine             VALID
Oracle Application Express               VALID
Oracle Database Catalog Views            VALID
Oracle Database Java Packages            VALID
Oracle Database Packages and Types       VALID
Oracle Multimedia                        VALID
Oracle Real Application Clusters         OPTION OFF
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle XDK                               VALID
Oracle XML Database                      VALID
Process of moving the PDB is similar to earlier post except for few RAC and ASM related tasks. To summarize, before the PDB could be moved from currently plugged in CDB, it must be stopped, unplugged and dropped. The unplugging will create a PDB descriptor file and on in the DB file names will appear in lower case while rest of the path appear upper case.
cat stdpdb_desc.xml | grep path
      +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/DATAFILE/system.291.894734477
      +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/DATAFILE/sysaux.292.894734481
      +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/TEMPFILE/temp.295.922121143
      +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/DATAFILE/users.293.894734495
      +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/DATAFILE/tapp.294.894734495
However when data files are copied out of ASM using asmcmd they will have the names in upper case (e.g. SYSTEM.291.894734477). As a result plugging will fail as it is unable to identify the file name
create pluggable database stdpdb as clone using '/home/oracle/stdcdb/datafiles/stdpdb_desc.xml'
*
ERROR at line 1:
ORA-19505: failed to identify file "/home/oracle/stdcdb/datafiles/system.291.894734477"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

SQL> ! ls "/home/oracle/stdcdb/datafiles/system.291.894734477"
ls: /home/oracle/stdcdb/datafiles/system.291.894734477: No such file or directory

SQL> ! ls -l /home/oracle/stdcdb/datafiles/*
-rw-r--r-- 1 oracle oinstall      6925 Sep 12 10:47 /home/oracle/stdcdb/datafiles/stdpdb_desc.xml
-rw-r----- 1 oracle oinstall 550510592 Sep 12 10:44 /home/oracle/stdcdb/datafiles/SYSAUX.292.894734481
-rw-r----- 1 oracle oinstall 293609472 Sep 12 10:45 /home/oracle/stdcdb/datafiles/SYSTEM.291.894734477
-rw-r----- 1 oracle oinstall  20979712 Sep 12 10:45 /home/oracle/stdcdb/datafiles/TAPP.294.894734495
-rw-r----- 1 oracle oinstall   5251072 Sep 12 10:45 /home/oracle/stdcdb/datafiles/USERS.293.894734495
So before the plugging rename the file names same as on descriptor file.
 mv SYSTEM.291.894734477 system.291.894734477
 mv SYSAUX.292.894734481 sysaux.292.894734481
 mv TAPP.294.894734495 tapp.294.894734495
 mv USERS.293.894734495 users.293.894734495
Also in ASM datafiles and tempfiles have separate paths, as a result the tempfile location must also specified in the source_file_name convert even though file itself is not necessary to be copied to new location. Temp file get dropped when the PDB is dropped even though keep data file options is used.
drop pluggable database stdpdb keep datafiles
Deleted Oracle managed file +DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/TEMPFILE/temp.295.922364799
Completed: drop pluggable database stdpdb keep datafiles


So the final command to plug the PDB into single instance will have two file name convert entires, one for data files and another for temp files
create pluggable database stdpdb as clone using '/home/oracle/stdcdb/datafiles/stdpdb_desc.xml' 
source_file_name_convert=(
'+DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/DATAFILE','/home/oracle/stdcdb/datafiles',
'+DATA/STDCDB/238FA674AFE26E9DE0535E00A8C0F5A1/TEMPFILE','/home/oracle/stdcdb/datafiles') move;
Once the PDB is plugged open it
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 STDPDB                         MOUNTED

SQL> alter pluggable database stdpdb open;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 STDPDB                         READ WRITE NO

SQL> select name from v$datafile;

NAME
-----------------------------------------------------------------------------------------------
/ssdoracle/oradata/STDCDB/datafile/o1_mf_undotbs1_cx2j8d05_.dbf
/ssdoracle/oradata/STDCDB/3C4F4864ACAE0C3DE0534200A8C05C7A/datafile/o1_mf_system_cxf6fp5g_.dbf
/ssdoracle/oradata/STDCDB/3C4F4864ACAE0C3DE0534200A8C05C7A/datafile/o1_mf_sysaux_cxf6fp5h_.dbf
/ssdoracle/oradata/STDCDB/3C4F4864ACAE0C3DE0534200A8C05C7A/datafile/o1_mf_users_cxf6fp5j_.dbf
/ssdoracle/oradata/STDCDB/3C4F4864ACAE0C3DE0534200A8C05C7A/datafile/o1_mf_tapp_cxf6fp5k_.dbf
Looking at the PDB violation view will show the warning related to missing RAC option.
SQL> select name,status,action,message from PDB_PLUG_IN_VIOLATIONS where cause='OPTION';

NAME     STATUS    ACTION                                             MESSAGE
-------- --------- -------------------------------------------------- -------------------------------------------------------------------------------------------
STDPDB   PENDING   Fix the database option in the PDB or the CDB      Database option RAC mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
Login into the PDB and turn off the RAC options
SQL> alter session set container=stdpdb;

SQL> show con_name

CON_NAME
---------
STDPDB

SQL> select comp_name,status from dba_registry order by 1;

COMP_NAME                                STATUS
---------------------------------------- -----------
JServer JAVA Virtual Machine             VALID
Oracle Application Express               VALID
Oracle Database Catalog Views            VALID
Oracle Database Java Packages            VALID
Oracle Database Packages and Types       VALID
Oracle Multimedia                        VALID
Oracle Real Application Clusters         VALID
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle XDK                               VALID
Oracle XML Database                      VALID

11 rows selected.

SQL> exec dbms_registry.OPTION_OFF('RAC');

SQL> select comp_name,status from dba_registry order by 1;

COMP_NAME                                STATUS
---------------------------------------- -----------
JServer JAVA Virtual Machine             VALID
Oracle Application Express               VALID
Oracle Database Catalog Views            VALID
Oracle Database Java Packages            VALID
Oracle Database Packages and Types       VALID
Oracle Multimedia                        VALID
Oracle Real Application Clusters         OPTION OFF
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle XDK                               VALID
Oracle XML Database                      VALID
Close and open the PDB. The PDB violation view will show the issue has been resolved.
SQL> select name,status,action,message from PDB_PLUG_IN_VIOLATIONS where cause='OPTION';

NAME     STATUS    ACTION                                             MESSAGE
-------- --------- -------------------------------------------------- -------------------------------------------------------------------------------------------
STDPDB   RESOLVED  Fix the database option in the PDB or the CDB      Database option RAC mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
This concludes the moving of PDB from RAC CDB to single instance CDB.

Related Posts
Move a PDB Between Servers
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link