Steps to migrate Oracle with Pentaho
Step 1:-
Run the script as DB admin.
Script is available in location:- biserver-ce\data\oracle10g.
Modify configuration file:-
- applicationContext-spring-security-hibernate.properties.
Location:-
pentaho-solutions\system\applicationContext-spring-security-hibernate.properties.
original code:-
jdbc.driver=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb: hsql:// localhost:9001/ hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.HSQLDialect
Modified code:-
dbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521/sysdba
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
- hibernate-settings.xml
Location:- pentaho-solutions\system\hibernate\hibernate-settings.xml.
Original code
<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>
Modified code:-
<config-file>system/hibernate/oracle10g.hibernate.cfg.xml</config-file>
oracle10g.hibernate.cfg.xml:-
Location:-
pentaho-solutions\system\hibernate\ oracle10g.hibernate.cfg.xml
Do not need to change any code in this file.. Just need to check everything is perfect or not
<!– Oracle 10g Configuration –>
<property name=”connection.driver_class”>oracle.jdbc.driver.OracleDriver</property>
<property name=”connection.url”>jdbc:oracle:thin:@localhost:1521/sysdba
</property>
<property name=”dialect”>org.hibernate.dialect.Oracle10gDialect</property>
<property name=”connection.username”>hibuser</property>
<property name=”connection.password”>password</property>
<property name=”connection.pool_size”>10</property>
<property name=”show_sql”>false</property>
<property name=”hibernate.jdbc.use_streams_for_binary”>true</property>
<!– replaces DefinitionVersionManager –>
<property name=”hibernate.hbm2ddl.auto”>update</property>
<!– load resource from classpath –>
<mapping resource=”hibernate/oracle10g.hbm.xml” />
- quartz.properties:-
Location:-
pentaho-solutions\system\quartz\quartz.properties
Original Code
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
Modified Code:-
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.oracle.OracleDelegate
4.context.xml:-
Location:-
tomcat\webapps\pentaho\META-INF\context.xml
Original Code
<Resource name=”jdbc/Hibernate” auth=”Container” type=”javax.sql.DataSource”
factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″
maxWait=”10000″ username=”hibuser” password=”password”
driverClassName=”org.hsqldb.jdbcDriver” url=”jdbc:hsqldb: hsql:// localhost/ hibernate”
validationQuery=”select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES” />
<Resource name=”jdbc/Quartz” auth=”Container” type=”javax.sql.DataSource”
factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″
maxWait=”10000″ username=”pentaho_user” password=”password”
driverClassName=”org.hsqldb.jdbcDriver” url=”jdbc:hsqldb: hsql:// localhost/ quartz”
validationQuery=”select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES”/>
Modified Code:-
<Resource validationQuery=”select 1 from dual”
url=” jdbc:oracle:thin:@localhost:1521/sysdba “
driverClassName=”oracle.jdbc.OracleDriver” password=”password”
username=”hibuser” maxWait=”10000″ maxIdle=”5″ maxActive=”20″
factory=”org.apache.commons.dbcp.BasicDataSourceFactory”
type=”javax.sql.DataSource” auth=”Container” name=”jdbc/Hibernate”/>
<Resource validationQuery=”select 1 from dual”
url=” jdbc:oracle:thin:@localhost:1521/sysdba “
driverClassName=”oracle.jdbc.OracleDriver” password=”password”
username=”quartz” maxWait=”10000″ maxIdle=”5″ maxActive=”20″
factory=”org.apache.commons.dbcp.BasicDataSourceFactory”
type=”javax.sql.DataSource” auth=”Container” name=”jdbc/Quartz”/>
6. repository.xml
Location of the file: pentaho-solutions\system\jackrabbit\repository.xml.
Comment this code means (<! – – every thing here – -> )
Active means: Remove comment
i) FileSystem part
Comment this code
<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>
<param name=”path” value=”${rep.home}/repository”/>
</FileSystem>
Active this code:-
<FileSystem class=”org.apache.jackrabbit.core.fs.db.OracleFileSystem”>
<param name=”url” value=”jdbc:oracle:thin:@localhost:1521“/>
<param name=”user” value=”jcr_user”/>
<param name=”password” value=”password”/>
<param name=”schemaObjectPrefix” value=”fs_repos_”/>
<param name=”tablespace” value=”pentaho_tablespace”/>
</FileSystem>
ii) DataStore part
Comment this code
<DataStore class=”org.apache.jackrabbit.core.data.FileDataStore”/>
Active this code:-
<DataStore class=”org.apache.jackrabbit.core.data.db.DbDataStore”>
<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>
<param name=”driver” value=”oracle.jdbc.OracleDriver”/>
<param name=”user” value=”jcr_user”/>
<param name=”password” value=”password”/>
<param name=”databaseType” value=”oracle”/>
<param name=”minRecordLength” value=”1024″/>
<param name=”maxConnections” value=”3″/>
<param name=”copyWhenReading” value=”true”/>
<param name=”tablePrefix” value=””/>
<param name=”schemaObjectPrefix” value=”ds_repos_”/>
</DataStore>
iii) Security part in the FileSystem Workspace part
Comment this code:-
<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>
<param name=”path” value=”${wsp.home}”/>
</FileSystem>
Active this code:-
<FileSystem class=”org.apache.jackrabbit.core.fs.db.OracleFileSystem”>
<param name=”url” value=”jdbc:oracle:thin@localhost:1521/sysdba”/>
<param name=”user” value=”jcr_user”/>
<param name=”password” value=”password”/>
<param name=”schemaObjectPrefix” value=”fs_ws_”/>
<param name=”tablespace” value=”pentaho_tablespace”/>
</FileSystem>
iv) PersistenceManager part
Comment this code:-
<PersistenceManager class=”org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager”>
<param name=”url” value=”jdbc:h2:${wsp.home}/db”/>
<param name=”schemaObjectPrefix” value=”${wsp.name}_”/>
</PersistenceManager>
Active This Code:-
<PersistenceManager class=”org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager”>
<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>
<param name=”driver” value=”oracle.jdbc.OracleDriver”/>
<param name=”user” value=”jcr_user”/>
<param name=”password” value=”password”/>
<param name=”schema” value=”oracle”/>
<param name=”schemaObjectPrefix” value=”${wsp.name}_pm_ws_”/>
<param name=”tablespace” value=”pentaho_tablespace”/>
</PersistenceManager>
v) FileSystem Versioning part
Comment This Code:-
<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>
<param name=”path” value=”${rep.home}/version” />
</FileSystem>
Active This Code:-
<PersistenceManager class=”org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager”>
<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>
<param name=”driver” value=”oracle.jdbc.OracleDriver”/>
<param name=”user” value=”jcr_user”/>
<param name=”password” value=”password”/>
<param name=”schema” value=”oracle”/>
<param name=”schemaObjectPrefix” value=”pm_ver_”/>
<param name=”tablespace” value=”pentaho_tablespace”/>
</PersistenceManager>
Stopping HSQL db start up
In web.xml file
Comment or delete this code (Commenting is preferable)
<!– [BEGIN HSQLDB DATABASES] –>
<context-param>
<param-name>hsqldb-databases</param-name>
<param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>
</context-param>
<!– [END HSQLDB DATABASES] –>
Also comment this code
<!– [BEGIN HSQLDB STARTER] –>
<listener>
<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
</listener>
<!– [END HSQLDB STARTER] –>
You have done with integrating pentaho 5.0.1 CE with Oracle
Now login to the Pentaho server .
URL: localhost:8080/pentaho
Username/Password : Admin/password
Best Open Source Business Intelligence Software Helical Insight is Here
