Open Clinica installation with Oracle XE (Final DB configurations)
by Ritesh
Posted on September 18, 2010 at 9:00 PM
Assuming that the Oracle XE is installed.
The SQL scripts for Oracle needs to be executed. The SQL scripts are found in
the unzipped folder "OpenClinica-2.5.6/config/database/Oracle/2.5.6/install".
| The following scripts should be available in the folder : oracle_alter_script1.sql oracle_create_mv.sql oracle_create_table.sql oracle_create_tablespace.sql oracle_create_user.sql oracle_extras.sql oracle_insert_default_data.sql oracle_package.sql oracle_sequences.sql oracle_setup_db.sql oracle_setup_objects.sql oracle_table_foreign_key.sql oracle_table_index.sql oracle_table_primary_key.sql oracle_table_triggers.sql oracle_table_unique_key.sql oracle_update_default_data.sql oracle_user_grants.sql Out of all these scripts, The oracle_setup_db.sql & oracle_setup_objects.sql scripts should be executed one after another in sequence to create the necessary schema. |
| [ritesh@chennai
ritesh_cdm]$ cd /usr/local/oc/OpenClinica-2.5.6/ config/database/Oracle/2.5.6/install [ritesh@chennai install]$ |
| [ritesh@chennai
install]$ ls oracle_alter_script1.sql oracle_setup_db.sql oracle_create_mv.sql oracle_setup_objects.sql oracle_create_table.sql oracle_table_foreign_key.sql oracle_create_tablespace.sql oracle_table_index.sql oracle_create_user.sql oracle_table_primary_key.sql oracle_extras.sql oracle_table_triggers.sql oracle_insert_default_data.sql oracle_table_unique_key.sql oracle_package.sql oracle_update_default_data.sql oracle_sequences.sql oracle_user_grants.sql |
SQL*PLUS is required to execute the two scripts. Usually SQLPLUS can be found in the Applications menu > Oracle Database 10g Express Edition > Run SQL Command Line [I found it at Office > Oracle Database 10g Express Edition > Run SQL Command Line]  |
Want to use terminal for SQLPLUS ? :
| [ritesh@chennai install]$ source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh | Shell script to identify oracle environment |
Rest of the steps are same whether using terminal or Sqlplus directly |
|
| [ritesh@chennai
install]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 9 18:42:35 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL>conn system/chennai@xe |
connect
sqlplus plus system=username chennai=password (use your password) XE is the default oracle instance for Oracle Express edition. |
|
SQL>@
/usr/local/oc/OpenClinica-2.5.6/config/database/Oracle/2.5.6/install/oracle_setup_db Specify Tablespace name for Clinica Data: octable If the tablespace is new, please specify the following Tablespace Size ( (S)mall - 32MB, (M)edium - 256MB, (L)arge -512MB Specify Tablespace type for Clinica Data (S/M/L): L Please make sure there is enough space available on the disk. DataFile Name (with exact path): /usr/lib/oracle/xe/oradata/XE/octable.dbf create tablespace octable This script will create the user Clinca. Parameter: TablespaceName The user is created with a default tablespace. Specify Tablespace name for Clinica objects: octable create user clinica identified by clinica Grant necessary privileges to the user Clinica Please check spool_oracle_setup_db.txt for errors If the tablespace and the user is created/exists run the script oracle_setup_objects.sql as user CLINICA Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production [ritesh@chennai install]$ |
@ is used to
execute sqlscript (oracle_setup_db.sql) |
|
When asked
to specify tablespace name enter octable. |
|
 |
|
| [ritesh@chennai install]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 9 18:42:35 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL>connect clinica/clinica@xe |
use username
as clinica use password as clinica |
|
SQL>@
/usr/local/oc/OpenClinica-2.5.6/config/database/Oracle/2.5.6/install/oracle_setup_objects ...................................................... ...................................................... 1 row created 1 row created Reseting all the sequences based on the maximum value in each table PL/SQL procedure successfully completed Clinica objects are created in the schema clinica. Please check the spool_oracle_create_objects.txt file for errors. Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production [ritesh@chennai install]$ |
execute sqlscript (oracle_setup_objects.sql) |
|
The oracle driver supplied with openclinica-2.5.6 'ojdbc14-10g.jar' might create connectivity issues with Oracle XE , download ojdbc14.jar from oracle website. |
|
|
[ritesh@chennai
install]$
su root Password: [root@chennai install]# su tomcat [tomcat@chennai install]$ cp /home/ritesh/ojdbc14.jar /usr/local/tomcat/common/lib |
as tomcat
user copy the downloaded ojdbc14.jar to proper tomcat folder Note : remove the old driver if present |
| [tomcat@chennai install]$ mkdir /usr/local/tomcat/openclinica.data/ | Create directory that store the CRF's |
| [tomcat@chennai install]$ cp /usr/local/oc/OpenClinica-2.5.6/distribution/OpenClinica.war /usr/local/tomcat/webapps | Deploy the java package (war) to tomcat. The war file when copied to tomcat webapps folder will decompress and create necessary files and folders automatically. Take a coffee break! |
|
Open the
datainfo.properties file from folder /usr/local/tomcat/webapps/OpenClinica/properties
using a text editor and at least change the following : dataBase=oracle (default is postgres) sysURL=http://localhost:8080/OpenClinica/MainMenu (remove the default http://svn.akazaresearch.com:8080/OpenClinica/MainMenu) |
|
|
Open the OpenClinica.xml
file from folder /usr/local/tomcat/conf/Catalina/localhost/ using
a text editor and at least change the following : Remove or comment this part : <Realm className="org.apache.catalina.realm.JDBCRealm" debug="99" driverName="org.postgresql.Driver" digest="MD5" connectionURL="jdbc:postgresql://192.168.15.70:5432/openclinica" connectionName="clinica" connectionPassword="clinica" userTable="user_account" userNameCol="user_name" userCredCol="passwd" userRoleTable="study_user_role" roleNameCol="role_name"/> <Resource name="SQLPostgres" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" username="clinica" password="clinica" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://192.168.15.70:5432/openclinica" maxActive="100" maxIdle="30" maxWait="10000"/>  Uncomment
and use this part in file :
change connectionURL =oracle.jdbc:thin:@localhost:1521:xe
<Realm className="org.apache.catalina.realm.JDBCRealm" debug="99" |
|
