Information provided as a “Tip” might be helpful for various reasons: time savings, risk reduction, etc. - it goes without saying that we advise to follow our guides meticulously

meticulous \muh-TIK-yuh-luhs\, adjective:
Extremely or excessively careful about details.

You should carefully read information marked with “Important”. Ignoring such information is typically not a good idea.

Warnings should not be ignored (risk of data loss, etc.)

2 Prerequisites

As a first step you must download the following software packages:

Download openCRX for Oracle from here (e.g. opencrx-1.10.0-core.oracle-8.zip [if your Oracle installation supports Unicode by default] or opencrx-1.10.0-core.oracle-8N.zip [if your Oracle installation does not support Unicode by default].
Download Oracle Database Server from here. You will require an OTN account.
You can find the Oracle JDBC driver inside the Oracle client distribution; alternatively, you can download it from here.

Please ensure that you install the correct JDBC driver (i.e. matching JDK, Oracle version, etc.) and one JDBC driver only! Ignoring this wisdom leads to problems as the connection to the database will fail.

As a next step you must install Oracle. The Oracle documentation explains in detail how to install the database.

This document assumes that you use the Oracle dbca tool and the Sql*Plus for database administration. The JDBC driver is required for the application server installation.

3 Upgrading from previous versions

If you already have Oracle for openCRX installed, upgrade the database as explained below. You can then skip the rest of this document.

	Do not forget to backup your database before you run any upgrade or migrate scripts!
	Please consult http://www.opencrx.org/faq.htm#upgrade and find out whether there exist specific instructions for your openCRX version. Instructions below are generic and might not cover all steps required to successfully upgrade your openCRX version.

3.1 The SQL Script upgrade-from-...

In a first step you must upgrade your database. openCRX distributions provide an SQL script of the form

upgrade-from-<version from>-to-<version to>.sql

If you have installed openCRX 1.9.1, for example, and you want to upgrade to version 1.10.0 you have to run the script upgrade-from-1.9.1-to-1.10.0.sql on your database instance.

3.2 The SQL Script migrate-from-...

In a second step you must migrate your database. openCRX distributions often times provide an SQL script of the form

migrate-from-<version from>-to-<version to>.sql

If you have installed openCRX 1.9.1, for example, and you want to upgrade to version 1.10.0 you have to run the script upgrade-from-1.9.1-to-1.10.0.sql on your database instance.

3.3 The SQL Script drop-from-...

Next you can drop unused tables from your database. openCRX distributions often times provide an SQL script of the form

drop-from-<version from>-to-<version to>.sql

If you have installed openCRX 1.9.1, for example, and you want to drop tables not used by openCRX 1.10.0 you can run the script drop-from-1.9.1-to-1.10.0.sql on your database instance. Alternatively, you can also rename such tables, e.g. from transition_type to _unused_transition_type. Also, it goes without saying that you should never drop a table before you made a backup!

3.4 The SQL Script dbcreate-views.sql

Most new openCRX versions make use of new/changed views, i.e. if an openCRX distribution includes an SQL script of the form

dbcreate-views.sql

then you should run that script. If you have installed openCRX 1.9.1, for example, and you want to upgrade to openCRX 1.10.0 you should run the script dbcreate-views.sql on your database instance. Make sure that old views are indeed dropped and new views properly created.

3.5 The SQL Script dbcreate-indexes.sql

Most new openCRX versions make use of new/changed indexes, i.e. if an openCRX distribution includes an SQL script of the form

dbcreate-indexes.sql

then you should run that script. If you have installed openCRX 1.9.1, for example, and you want to upgrade to openCRX 1.10.0 you should run the script dbcreate-indexes.sql on your database instance.

3.6 Populate Preferences

The last step involves deleting old preferences and populating the table with new ones. Run the SQL script populate-preferences.sql to do this.

Make sure that old preferences are indeed removed and news ones loaded. This table contains the configuration of the openMDX database plugin, i.e. openCRX persistency will not work properly if the loaded preferences do not match the version of openCRX.

4 Create the database

An existing database may be utilized, or alternatively you may create a new database.

A new database instance may be created with the Oracle Database Configuration Assistant (dbca). The following snapshots are taken from a windows installation, but the procedure is identical for all supported platforms. It is assumed the base oracle installation directory (Oracle Home) is "c:/oracle".

Start dbca as the Oracle owner account and Create a new "General Purpose" database as shown below:

Figure 1: Create a new database

Enter CRX as database name as shown below:

Figure 2: Create the schema CRX

Continue through the screens, tuning the database parameters as required (the defaults will work in most simple deployments). Finally, select Create Database and select Finish in Step 7.

Next you must create a database user, tablespaces and grant this user access to the newly created database. Ensure your shell environment is setup with the following variables:

Listing 1: Shell variables

ORACLE_SID=CRX
ORACLE_HOME=c:/oracle
PATH=${ORACLE_HOME}/bin:${PATH}

Launch a terminal and execute command sequence as demonstrated below:

Figure 3: Create user, tablespace

Of course Oracle Enterprise Manager, Toad and various other 3rd party tools will achieve the equivalent goal, but rarely with the same speed and control if you are not scared of a command line.

You are now done creating the database and database schema.

5 Install the openCRX Database Schema Objects

After creating the schema you are now ready to install the openCRX database schema objects. The following scripts must be executed:

dbcreate-tables.sql
dbcreate-views.sql
dbcreate-indexes.sql
populate-preferences.sql

Do not execute any other scripts included in the distribution.

Oracle has issues with the null lines in the DDL scripts. This can be resolved by manually editing the files, or alternatively using a command to strip the empty lines out as demonstrated below:

Listing 2: Strip Null lines from DDL scripts

$ cat dbcreate-tables.sql |
> sed -n '/^$/!p' |
> sed -n '/^[ \t\f\n\r\v]*$/!p' > dbcreate-tables-nonulllines.sql

$ cat dbcreate-views.sql |
> sed -n '/^$/!p' |
> sed -n '/^[ \t\f\n\r\v]*$/!p' > dbcreate-views-nonulllines.sql

$ cat dbcreate-indexes.sql |
> sed -n '/^$/!p' |
> sed -n '/^[ \t\f\n\r\v]*$/!p' > dbcreate-indexes-nonulllines.sql

$ cat populate-preferences.sql |
> sed -n '/^$/!p' |
> sed -n '/^[ \t\f\n\r\v]*$/!p' > populate-preferences-nonulllines.sql

Start Oracle Sql*Plus, and login as opencrx_user, password opencrx_user. Copy/paste the database script dbcreate-tables.sql (or dbcreate-tables-nonulllines.sql if you stripped Null lines) and execute it as shown below:

Figure 4: Install Database Schema Objects

Similarly, execute the remaining scripts:

dbcreate-views.sql (or dbcreate-views-nonulllines.sql)
dbcreate-indexes.sql (or dbcreate-indexes-nonulllines.sql)
populate-preferences.sql (or populate-preferences-nonulllines.sql)

The scripts should run without errors.

6 Next Steps

If you have completed successfully the database installation you are ready to use the openCRX database. The application server installation guides explain how to connect the application server to the openCRX database instance.