Tuesday, May 14, 2013

Unsupported Bind Variable Syntax in PreparedStatments and 10.2/11.1/11.2 JDBC Drivers

Oracle supports two kind of bind variable placeholder syntax in Java PreparedStatments. A bind variable placeholder could be denoted with a colon and a number ":1" or with a question mark "?". However there are situation where developers may use a syntax such as question mark followed by a number "?1". Though this syntax is not supported in Oracle, when using JDBC drivers 10.2 and 11.1 the Java code would execute without an error and returns results as expected. But with 11.2 JDBC driver this will give the error ORA-00933: SQL command not properly ended and java code would not execute as before. So as part of database upgrade if JDBC driver is also upgraded and if java code has the syntax similar to "?1" etc then code that previously worked would not work anymore.
The problem here lies in using the unsupported bind variable placeholders. According to Oracle the fact that it works with previous version of JDBC driver is a merer accident and not the expected behavior. If it's not possible to modify the code the easiest option to remedy to the situation is to downgrade the JDBC driver to 11.1. As the issue comes as a result of the JDBC driver not because of the database
Java code given at the end of the post could be used to test the unsupported syntax against various databases and JDBC drivers. Following table list summary of findings from running the test code with 10.2/11.1/11.2 JDBC drivers and databases.

JDBC Driver VersionDatabase Version"?1" Works
10.2.0.510.2.0.5YES
10.2.0.511.1.0.7YES
10.2.0.511.2.0.3YES
11.1.0.710.2.0.5YES
11.1.0.711.1.0.7YES
11.1.0.711.2.0.3YES
11.2.0.310.2.0.5NO
11.2.0.311.1.0.7NO
11.2.0.311.2.0.3NO




Create and populate table used in the test code
SQL> create table x (a varchar2(10), b number);

Table created.

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

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.
Test code
   
        OracleDataSource ds = new OracleDataSource();
        ds.setUser("asanga");
        ds.setPassword("asa");
        ds.setURL("jdbc:oracle:thin:@192.168.0.66:1521:ent11g2");

        Connection con = ds.getConnection();
        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("Database Product Name " + meta.getDatabaseProductName());
        System.out.println("Database Product Version " + meta.getDatabaseProductVersion());

        String SQL = "select * from x where b = ?1"; // works on 10gR2, 11gR1 jdbc driver but not on 11gR2 jdbc driver
//        String SQL = "select * from x where b = ?";  // works on all drivers

        PreparedStatement pr = con.prepareStatement(SQL);
        pr.setInt(1, 10);
        ResultSet rs = pr.executeQuery();

        while(rs.next()){

            System.out.println(rs.getString("A")+" "+rs.getInt("B"));
        }
              
        rs.close();
        pr.close();
        con.close();

Useful metalink notes
ORA-00933 When Using Bind Variables in JDBC 11.2 [ID 1304235.1]

Friday, May 3, 2013

Script For Listing 11gR2 Cluster Resource Status in CRS_STAT Output Format

crs_stat -t is depreciated in 11gR2 and replaced with crsctl stat res -t. Following script could be used to generate resource status output in a format that is similar to crs_stat -t in 11gR1.
#!/bin/sh
# Sample 11gR2 CRS resource status query script
# gives output similar to crs_stat -t in 11gR1
# Asanga Pradeep

awk \
  'BEGIN {printf "%-35s %-25s %-18s %-18s\n", "Resource Name", "Type", "Target","State";
          printf "%-35s %-25s %-18s %-18s\n", "-----------", "------", "-------","--------";}'
crsctl stat res | egrep -w "NAME|TYPE|TARGET|STATE" | sed -e "s/   / /g" | awk \
 'BEGIN { FS="="; state = 0; }
  $1~/NAME/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TYPE/ && state == 1 {apptype = $2; state=2;}
  $1~/TARGET/ && state == 2 {apptarget = $2; state=3;}
  $1~/STATE/ && state == 3 {appstate = $2; state=4;}
  state == 4 {
split(apptarget,targetarray,", ");
x=length(targetarray);
split(appstate,statearray,", ");
for(ix=1;ix<=x;ix++)
printf "%-35s %-25s %-18s %-18s\n", appname, apptype,targetarray[ix], statearray[ix]; state=0;}'




Output
Resource Name                       Type                      Target             State
-----------                         ------                    -------            --------
ora.CLUSTERDG.dg                    ora.diskgroup.type        ONLINE             ONLINE on rac4
ora.CLUSTERDG.dg                    ora.diskgroup.type        ONLINE             ONLINE on rac5
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE on rac4
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE on rac5
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE on rac4
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE on rac5
ora.LISTENER.lsnr                   ora.listener.type         ONLINE             ONLINE on rac4
ora.LISTENER.lsnr                   ora.listener.type         ONLINE             ONLINE on rac5
ora.LISTENER_SCAN1.lsnr             ora.scan_listener.type    ONLINE             ONLINE on rac5
ora.asm                             ora.asm.type              ONLINE             ONLINE on rac4
ora.asm                             ora.asm.type              ONLINE             ONLINE on rac5
ora.cvu                             ora.cvu.type              OFFLINE            OFFLINE
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.net1.network                    ora.network.type          ONLINE             ONLINE on rac4
ora.net1.network                    ora.network.type          ONLINE             ONLINE on rac5
ora.oc4j                            ora.oc4j.type             OFFLINE            OFFLINE
ora.ons                             ora.ons.type              ONLINE             ONLINE on rac4
ora.ons                             ora.ons.type              ONLINE             ONLINE on rac5
ora.rac11g2.db                      ora.database.type         ONLINE             ONLINE on rac4
ora.rac11g2.db                      ora.database.type         ONLINE             ONLINE on rac5
ora.rac11g2.test.svc                ora.service.type          ONLINE             ONLINE on rac4
ora.rac11g2.test.svc                ora.service.type          ONLINE             ONLINE on rac5
ora.rac4.vip                        ora.cluster_vip_net1.type ONLINE             ONLINE on rac4
ora.rac5.vip                        ora.cluster_vip_net1.type ONLINE             ONLINE on rac5
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE on rac4
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE on rac5
ora.scan1.vip                       ora.scan_vip.type         ONLINE             ONLINE on rac5

Related Post
Script to get full name of the resources from crs_stat

Update 13 May 2013
In the above script both state and host name are in the same column. Below script will have a separate column for state and hostname
#!/bin/sh
# Sample 11gR2 CRS resource status query script
# gives output similar to crs_stat -t in 11gR1
# Asanga Pradeep

awk \
  'BEGIN {printf "%-35s %-25s %-18s %-18s %-18s\n", "Resource Name", "Type", "Target","State","Host";
          printf "%-35s %-25s %-18s %-18s %-18s\n", "-------------", "------", "-------","--------","----------";}'
crsctl stat res | egrep -w "NAME|TYPE|TARGET|STATE" | sed -e "s/   / /g" | awk \
 'BEGIN { FS="="; state = 0; }
  $1~/NAME/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TYPE/ && state == 1 {apptype = $2; state=2;}
  $1~/TARGET/ && state == 2 {apptarget = $2; state=3;}
  $1~/STATE/ && state == 3 {appstate = $2; state=4;}
  state == 4 {
split(apptarget,targetarray,", ");
x=length(targetarray);
split(appstate,statearray,", ");
  for(ix=1;ix<=x;ix++){
  split(statearray[ix],statehostarray," ");
  printf "%-35s %-25s %-18s %-18s %-18s\n", appname, apptype,targetarray[ix], statehostarray[1], statehostarray[3]; state=0;}

}'
Output
Resource Name                       Type                      Target             State              Host
-------------                       ------                    -------            --------           ----------
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m1
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m2
ora.MYLISTENER_SCAN1.lsnr           ora.scan_listener.type    ONLINE             ONLINE             rhel6m1
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m1
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m2
ora.cvu                             ora.cvu.type              ONLINE             ONLINE             rhel6m1
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m1
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m2
ora.oc4j                            ora.oc4j.type             ONLINE             ONLINE             rhel6m1
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m1
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m2
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m1
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m2
ora.rhel6m1.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m1
ora.rhel6m2.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m2
ora.scan1.vip                       ora.scan_vip.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m2
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m1
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m2

Wednesday, May 1, 2013

RMAN-06025: no backup of archived log for thread X with sequence Y and starting SCN of Z found to restore

"Restore validate" command could be used to determine if the backups are intact and could be used for a restore operation without any issue. The command could be used to validate database restore as well as archive log restore.
During archivelog restore it is possible to encounter errors such as below
RMAN> restore archivelog all validate;

Starting restore at 25-APR-13
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/25/2013 16:18:21
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1714 and starting SCN of 43439995 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1713 and starting SCN of 43408508 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1712 and starting SCN of 43389822 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1711 and starting SCN of 43364551 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1710 and starting SCN of 43333559 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1709 and starting SCN of 43240943 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1708 and starting SCN of 43203417 found to restore
The reason for this error is that "archive all" try to validate restore of all archivelogs and doesn't consider the retention policy.



Solution for this is to restore archivelog within the retention period. In this case retention period was
RMAN> show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
Use the retention period in the validate command as
RMAN>  restore archivelog from time='sysdate - 2' validate;

Starting restore at 25-APR-13
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archived log /data/flash_recovery/ENT11G2/archivelog/2013_04_24/o1_mf_1_1_8qjkzmll_.arc
channel ORA_DISK_1: scanning archived log /data/flash_recovery/ENT11G2/archivelog/2013_04_25/o1_mf_1_2_8qkj6mv6_.arc
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /data/flash_recovery/ENT11G2/backupset/2013_04_24/o1_mf_annnn_TAG20130424T161933_8qhy05yr_.bkp
channel ORA_DISK_1: piece handle=/data/flash_recovery/ENT11G2/backupset/2013_04_24/o1_mf_annnn_TAG20130424T161933_8qhy05yr_.bkp tag=TAG20130424T161933
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /data/flash_recovery/ENT11G2/backupset/2013_04_24/o1_mf_annnn_TAG20130424T162300_8qhy6njm_.bkp
channel ORA_DISK_1: piece handle=/data/flash_recovery/ENT11G2/backupset/2013_04_24/o1_mf_annnn_TAG20130424T162300_8qhy6njm_.bkp tag=TAG20130424T162300
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 25-APR-13

RMAN> restore archivelog from time='sysdate - 4' validate;

Starting restore at 25-APR-13
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/25/2013 16:23:51
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 1714 and starting SCN of 43439995 found to restore

Useful metalink notes
OERR: RMAN-6025 { Message text depends on version } [ID 48184.1]
No Backup Of Log Thread 1 Seq 4079 Scn 6508223992 Found To Restore Rman-6025 [ID 308289.1]
RMAN RESTORE ARCHIVELOG ALL VALIDATE fails with RMAN-06025: no backup of log [ID 1391263.1]