ODI 12c 12.1.3 oci driver

When you connect from ODI Studio 12c to the repository you may get error “no ocijdbc in java.library.path”. In my case I use ODI Studio 12c installed on Windows 7 64bit machine and connecting to ODI 12c linux environment. So why do I get this error?

 

 

On the same note I use Oracle JDBC Driver ODI driver connection. As the studio does not use the tnsnames to connect to the Oracle instance if you try jdbc:oracle:oci8:@<tns_alias> it will not work. That’s why you can give (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SID))) instead. So far so good but still get the same error.

 

This error gives me a signal it might be because of incompatibility of the jdbc drivers coming with ODI 12c and the Oracle client. As a starting point I have Oracle 11.2.0.4 client installed on my station. Let’s check what version ODI 12c uses, probably 12? Check this in <<ODI_HOME>>\oracle_common\modules\oracle.jdbc_12.1.0\ojdbc6dms\META-INF\MANIFEST.MF

 

Implementation-Version: 12.1.0.2.0

 

Now it makes perfect sense. Let’s install Oracle 12.1.0.2.0 client. Once this is done two more things needed. Add the following line in <<ODI_HOME>>\odi\studio\bin\odi.conf\odi.conf

 

AddVMOption -Djava.library.path=<<ORACLE_HOME>>\bin

 

The second thing is to set the system variables for

ORACLE_HOME=C:\Oracle\client12c

LD_LIBRARY_PATH=%ORACLE_HOME%\lib value

PATH=%ORACLE_HOME%\bin;%PATH% value

 

Enjoy ODI 12c world!  

 

Cheers,

Maria

Oracle Database 12c New Features

I think I am a little bit late but on the other hand it’s never too late to comment and share opinion about some among the 500+ new features introduced with Oracle Database 12c.

The first feature which really caught my attention was Multiple Indexes on Same Set of Columns as long as there are differences and only one of them is visible. Before getting into an example , there are some exceptions Oracle Doc :

• You cannot create a B-tree index and a B-tree cluster index on the same set of columns.
• You cannot create a B-tree index and an index-organized table on the same set of columns.

Prior to 12c you know when you try to create more than one index on the same list of columns you get ORA-01408: such column list already indexed error.
If you think, it sounds quite logical. What is that which made this feature be available in Oracle Database 12c now? This is Testing performance and more important Index availability. Now index maintenance and respectively application maintenance is much easier.
Imagine you have to change an existing index(even more a large index)from a non-partitioned index to a partitioned index or convert a B-Tree to a Bitmap index. Instead of having ‘downtime’ and wait for the existing index to be dropped and the set of columns will not be covered by the index. We can create the new one in advance, test and when satisfied make invisible the existing one(or drop it) and make visible the newly created index. Flexible, isn’t it !?!

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 29 11:09:01 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> create table test_idx
( col1 integer, col2 integer, col3 integer, col4 varchar2(100) );
Table created.
SQL> create index idx1 on test_idx(col1,col2,col3);
Index created.
SQL> create bitmap index idx2 on test_idx(col1,col2,col3);
create bitmap index idx2 on test_idx(col1,col2,col3)
 *
ERROR at line 1:
ORA-01408: such column list already indexed

SQL> create bitmap index idx2 on test_idx(col1,col2,col3) invisible;

Index created.

 

We can now test idx2 index and if it performs well drop idx1 and make idx2 visible. There are a bunch of articles already about this new feature, have a look at Tom Kyte’s Blog and Richard Foote’s Blog .

Next, I’d like to mention DEFAULT values using sequences. It is quite handy to have CURRVAL and NEXTVAL as the default values for a column:

SQL> create sequence s_test_seq;
 
Sequence created.
SQL> create table test_seq (col1 NUMBER DEFAULT s_test_seq.NEXTVAL,
 2 col2 varchar2(100) );
 
Table created.
 
SQL> insert into test_seq(col2)values('Test Default value');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test_seq;
 
 COL1 COL2
========== =====================================
 1 Test Default value

 

This means that the trigger-based functionality and writing PL/SQL for populating number id columns with a sequence is now a past with Oracle 12c.

You can also check Tim Hall’s blog for more details.

Other features which impress me at first glance are:

• Oracle Data Redaction
• Online Statistics Gathering for Bulk Loads
• Dynamic Statistics
• Concurrent Statistics Gathering
• Concurrent Execution of UNION and UNION ALL Branches
• Automatic Data Optimization
• Move a Data File Online
• ONLINE Move Partition
• Table-Level Recovery From Backups
• Oracle Data Pump Export View As a Table
• Partial Indexes for Partitioned Tables
• SYSBACKUP Administration Privilege
• Last Login Time Information
• Real-Time Database Operations Monitoring
• Enhanced Online DDL Capabilities(no longer require blocking locks)
• Row Limiting Clause for Top-N Queries
• Synchronous Materialized View Refresh

 

Cheers,

Maria