Posts Tagged ‘Oracle’

Recent history

BEA WebLogic 9.0, 9.1 and 9.2 were released from 2007: the main features were: a new console, WLST (WebLogic ScriptingTool), deployment plans, WebLogic Diagnostic Framework (WLDF), new security providers (RDBMS, SAML 1.1, etc.), JMS performance improvements, support of Java EE 4, JDK 5, Spring, OpenJPA, Kodo, etc.

Since this date, some events happened:

• Oracle bought Sun (2009)
• Oracle released WebLogic 10.3 (2008)
• Oracle bought BEA (2008)

WebLogic Server 10 General Features

• Developer productivity ehancements
  • JDK 6, Java EE 5
  • Support of EJB3 and JPA
  • BEA enhancements
• Web Services: more annotations, less XML
  • JAX-RPC Web Services Enhancements
  • JAX-WS 2.0 Web Services Implementation
• Misc:
  • Better administration console
  • Auto-Record of Admin Console actions as WLST scripts
  • Automatic JTA Transaction Recovery Service (TRS) migration
  • SNMP 3.0
  • Production Application Redeployment enhancements
  • Clustering – Unicast messaging (in addition to Multicast)

Programmer Perspective

• New persistence engine: TopLink
• OEPE (Oracle Entreprise Pack for Eclipse): sequence of tools and plugins for Eclipse: remote deployment, debugging,  editors for weblogic.xml and weblogic-application.xml, wizards, facets, Weblogic ClientGen, WSDLC and JAXB wizards
• Optimizations for Spring integration and certication
• Web 2.0:
  • Ajax / Dojo client support
  • Http publish / submit engine for collaborative applications:
    • Bayeux protocol
    • data exchange within applications over persistent connections
    • scalability for Dojo clients
• Ad-hoc tools for:
  • Oracle Database
  • Spring
  • JAX-WS webservices

Lightweight WebLogic Server

WebLogic 10 offers a light weight server:

• Install only “core” WebLogic server
• Optionally, startup other services (JDBC, EJB, JMS, etc.)
• FastSwap: modify classes without requiring redeployment.

Architect Perspective

Architects have to consider WebLogic as a complete suite, and not only WebLogic Server:

• Oracle RAC integration: Connectivity to RAC with load balancing, failover, transactions
• Enterprise Messaging with JMS: High performance and reliable JMS messaging engine “built-in”
• ActiveCache with Coherence*Web and EJB/JPA: Coherence Data Grid caching included and integrated
• Operations Automation: Tools for automating management of applications and servers
• Operations Insight: Tools for diagnosing problems in development and production
• Other features
  • Development tools: Choice of tools for developer productivity
  • Web Services: Enterprise Web Services for SOA
  • TopLink: Persist application data to stores with performance and productivity. It works in a way similar to Hibernate L2 cache.
  • Spring: Enable flexible choice of dev frameworks with same WebLogic QOS

Production and Support Perspective

WebLogic 10 provides a tool: JRockit Mission Control

• monitors more than 150 parameters:
• CPU
  • memory
  • leaks
  • latency spikes
  • threads
  • object references
  • JDBC connections
  • JMS
  • pools
  • clusters
  • configuration files
  • etc.
• allows to compare WebLogic domains
• Runtime Analyzer: runtime capture for offline analysis, Garbage Collector analysis, etc.

Coherence – ActiveCache

Coherence is the Data Grid offered by Oracle. It allows to store Java objects in memory, and share them between all instances. From a certain viewpoint, Coherence looks like the GigaSpaces.

Roadmap for Future WebLogic Releases

• Support of Java EE 6 (ratified by the community in last December)
• OSGi deployment
• More native integration for WebLogic Server – Coherence – Oracle Database
• JRockit Flight Recorder for constant record
• Virtualization
• More integration with Maven, Hudson and Cruise Control
• Shared Library: use the same JAR for many applications, rather than packing the same JAR in different EARs.
• On long term:
  • IDE
    • NetBeans to be oriented onto J2ME development
    • JDevelopper to remain Oracle strategic IDE
    • Contributions to Eclipse to go on
  • JRockit and Sun HotSpot JVMs to be merged.

Case:

You need debug information on SQL queries, for an application within BEA WebLogic 9.2. You need p6spy.

P6spy is a jar which play the role of a bridge between your application and your actual JDBC driver (in my case: oracle.jdbc.driver.OracleDriver for Oracle 10g)

Solution:

• Change JDBC Driver in Weblogic Web Console
  • Services > JDBC > Data Sources > (your data source) > Configuration > Connection Pool > Driver Classname  =  com.p6spy.engine.spy.P6SpyDriver
• Copy p6spy-1.3.jar and spy.properties in %WL_HOME%/servers/lib/ext
• Check that spy.properties contains : realdriver=oracle.jdbc.driver.OracleDriver
• In the spy.properties choose the stdoutLogger,  rather than log4jLogger appender
• In the CLASSPATH of startManagedWebLogic.cmd, add the following path:%WL_HOME%/servers/lib/ext/p6spy-1.3.jar;%WL_HOME%/servers/lib/ext

On last Friday I attented a presentation about Oracle (10g) Partitioning. Here is a summary of interesting points:

Why?

On some projects, data bases reach tens of TB.  For so huge volume of data, developpers often are used to archiving or historizing datas. Partitioning the base is another way to face mass data.

What?

Partitioning is allowing a table or an index to be divided into smaller pieces, called “partitions”. Please notice that, from a logical point of view, we keep one object (table or index) ; yet, physically, we have many physical partitions.

When?

• DBAs advise to consider partitioning prior to create the tables. Indeed, partitioning when the table reaches a critical size is more expensive than sooner.
• Tables may be partionioned if they are or are assumed to be 2GB or more.

Partition key/ pruning

• From a certain viewpoint, a partition key may be seen as a “super index”
• Rows of the base are assigned to a unique partition.
• The partitioning key contains one column (or more) which determine the partition where any row is to be stored.
• During insert/update/delete operations, the right partition is automatically found owing to the partition key.
• Warning: any table with columns of type LONG or LONG_RAW cannot be partitioned.

Purpose

• Improvement of availability and performance of the base. At first glance, on select operations the benefit is small, but on insert operations the benefit is felt.
• Reduction of administrative burden and input/ouput access
• Anticipation of high volumes of data and cost of development

TYPES

Three types of partitions are available:

• range: eg by month, year, etc.
• list: eg by country, currency, etc. This is the case when the column values are among an enumeration
• hash

Composite keys are allowed, yet they are not advised to be used.

INDEX

Indexes can be partitioned, too. All combinations are allowed: partioned indexes for partioned tables, partioned indexes for global tables, etc.

Syntax

[sql]CREATE TABLE range_example
(
RANGE_KEY_COLUMN   DATE,
MY_DATA            VARCHAR2 (20)
)
PARTITION BY RANGE (range_key_column)
(
PARTITION part_1 VALUES LESS THAN (TO_DATE (’01/01/2005′, ‘dd/mm/yyyy’)),
PARTITION part_2 VALUES LESS THAN (TO_DATE (’01/01/2006′, ‘dd/mm/yyyy’)))[/sql]

Oracle 11g

Oracle 11g will bring a lot of improvements on partitioning: extended composite partitioning, reference partitioning, partitioning on virtual columns, etc.

Case

• let’s consider an EJB Bean AnimalBean, linked to a table JL_Animal
• let’s have a human understandable name for the Pojo, let’s say: Animal
• let an EJB entity have following finders:

[java]
* @ejb.finder
* signature = "Collection findByBirthDate(java.lang.Integer birthDate)"
* query = "SELECT OBJECT(o) FROM AnimalBean AS o WHERE o.birthDate = ?1"
*
* @ejb.finder
* signature = "Collection findByCountryAndBirthDate(java.lang.String from,java.lang.Integer birthDate)"
* query = "SELECT OBJECT(o) FROM AnimalBean AS o WHERE o.country = ?1 AND o.birthDate = ?2"[/java]

Read the rest of this entry »

Case:

I need copy the content of all tables from one Oracle schema to another one, specifically: I must copy raw data from a production DB to a developper one.

Context:

• The DBA do not allow DBLinks
• Oracle source and destination schemas share the same structure (name of the tables, names and types of columns, etc.

Fix:

Here is a small script I wrote, of course it can be improved:
[bash]
#!/usr/bin/bash
SRC_LOGIN=XYZ
SRC_PASSWORD=XYZ
SRC_DB=production
DBDEST_LOGIN=XYZ
DEST_PASSWORD=XYZ
DEST_DB=developmentDB

echo "select object_name from user_objects where object_type = ‘TABLE’ order by object_name;" | \
sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB | \
grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" | \
sed "1,9d" | tac | sed "1,3d" | tac | \
sort | uniq > allTables.txt

for table in `more allTables.txt`
do
rm -rf $table.sql
echo "exporting $table"
exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y
echo "importing $table"
echo "truncate table $table;" | \
sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB
imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql \
buffer=10485867 tables="$table" \
fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y
rm -rf $table.sql
done

rm -rf allTables.txt[/bash]

A little more explanations:

• echo "select object_name from user_objects where object_type = 'TABLE';" | sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB: this allow to retrieve the names of all the tables from the DB
• grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" : this removes empty lines and useless trace from Oracle SQLPlus
• sed "1,9d" | tac | sed "1,3d" | tac: removes the 9 first lines and the 3 last ones. tac in Unix allow to reverse the content of a file
• sort | uniq : sort lines and remove duplicates
• exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y: exports the content of the table $table
• echo "truncate table $table;" | sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB: truncates destination table. This is useful in case the script must be played more than once, for instance.
• imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql buffer=10485867 tables="$table" fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y: import the content of the table

Some improvement ideas:

• handle sequences, triggers, stored procedures, etc.
• create the complete destination DB owing to source DB, ie do not assume anymore that the DBs share the same structure

Very interesting case. At firt, I spotted these errors:

SQL Error: -22, SQLState: S0002

and:

java.sql.SQLException: Table not found in statement

But here is the complete stacktrace:

"2009-10-13 12:29:17,934 ERROR hbm2ddl.SchemaExport - Unsuccessful: create table MY_ORACLE_TABLE (myOracleTableId integer not null, optimisticTimestamp timestamp not null, position double, primary key (myOracleTableId))
2009-10-13 12:29:17,934 ERROR hbm2ddl.SchemaExport - Unexpected token: POSITION in statement [create table MY_ORACLE_TABLE (myOracleTableId integer not null, optimisticTimestamp timestamp not null, position]
(...)
2009-10-13 12:29:17,949 ERROR hbm2ddl.SchemaExport - Table not found: MY_ORACLE_TABLE in statement [alter table MY_ORACLE_TABLE]
2009-10-13 12:29:17,980 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest-testFindmyOracleTableByPK_OK.xml
2009-10-13 12:29:17,996 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:17,996 DEBUG util.HibernatePersistenceTestCase - --------------- Using dataset : HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:18,355 WARN util.JDBCExceptionReporter - SQL Error: -22, SQLState: S0002
2009-10-13 12:29:18,355 ERROR util.JDBCExceptionReporter - Table not found in statement [select (...) where myOracleTable0_.myOracleTableId=?]
2009-10-13 12:29:18,371 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest-testFindmyOracleTableByPK_OK.xml
2009-10-13 12:29:18,371 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:18,387 DEBUG util.HibernatePersistenceTestCase - --------------- Using dataset : HibernateMyOracleTableDAOUnitTest.xml

org.hibernate.exception.SQLGrammarException: could not load an entity: [com.(...)myOracleTable#147]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1799)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:47)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:41)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:2730)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:365)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:346)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:123)
at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:161)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:87)
at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:862)
at org.hibernate.impl.SessionImpl.load(SessionImpl.java:781)
at org.hibernate.impl.SessionImpl.load(SessionImpl.java:774)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
at $Proxy0.load(Unknown Source)
(...)HibernateMyOracleTableDAOUnitTest.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: java.sql.SQLException: Table not found in statement [select (...) where myOracleTable0.myOracleTableId=?]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:442)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
... 42 more"

I have helped you with red bold words… Can you see the issue?

Yes, indeed position is a reserved keyword in SQL! To fix this issue, you have no choice but to rename your column.

Case: you need the list of all tables in an Oracle DB.

Solution: run this query:
[sql]select * from user_objects where object_type = ‘TABLE’;[/sql]
You can use a similar way to filter on table names, or get other types object such us triggers, procedures and indexes.

Abstract: we need the max of two dates

Read the rest of this entry »