Oracle

To provide a database for ConSol CM or for CMRF the same steps are required. For the CMRF database just change the database/database user name.

Installation prerequisites

An Oracle instance with database character set AL32UTF8 is required. This can be checked with the following SQL statement:

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

The result should be:

Parameter	Value
NLS_CHARACTERSET	AL32UTF8

Initialization parameter settings

The following Oracle instance parameters must be changed to the following values or higher:

Parameter	Value
processes	20 + 200 * (number of CM connection pool) If shared with other servers, that + 200 for each CM connection pool
open_cursors	1000 Note: Necessary for statement caching in application server

The parameters can be changed as sysdba via sqlplus on the default instance with the following commands:

# sqlplus / as sysdba

> ALTER SYSTEM SET processes=420 SCOPE=SPFILE;

> ALTER SYSTEM SET open_cursors=1000 SCOPE=SPFILE;

Note that changes are only effective after restarting the database instance. This can be performed by:

> SHUTDOWN;

> STARTUP;

General preparations

Create a separate database user account (placeholder <CM_USER>) for each CM installation with the roles CONNECT and RESOURCE.

The password of this application user must not expire, as it is used in the database connection pool of the application server.

This can be created by the sqlplus command line interpreter via the following commands:

# sqlplus /nolog (start sqlplus client)

SQL> connect <SYSTEM>/<PASSWORD> --use your DBA user and password, connection to the default DB

-- (or SQL>connect sys/<PASSWORD> as sysdba)

SQL> create user '<CM_USER>' identified by '<PASSWORD>';

SQL> grant connect, resource to '<CM_USER>';

SQL> grant create procedure to '<CM_USER>';

SQL> grant create view to '<CM_USER>';

For Oracle databases the user name is case insensitive during CREATE USER and GRANT. But it is stored in upper case in the database meta data. Therefore you must use the upper case name in SELECT statements on meta data as dba_users and dba_role_privs.

Tablespace for application-specific tables and indexes

The database user <CM_USER> needs a tablespace where he can store his tables and indexes.

In case a dedicated tablespace should be used for the ConSol CM application, the following SQL is the easiest way to create a tablespace:

SQL> create bigfile tablespace '<TABLESPACE_NAME>';

This only works if OMF is enabled in the Oracle database. This should be the case for most databases.

If the default tablespace of the database (default: USERS) should not be used, the default tablespace of the user must be changed. This can be changed by the following command:

SQL> alter user '<CM_USER>' default tablespace '<TABLESPACE_NAME>';

For Oracle 12c, it is also required that the database user has a quota on this tablespace:

SQL> alter user '<CM_USER>' QUOTA UNLIMITED ON '<TABLESPACE_NAME>';

It is not possible to use more than one tablespace for one CM installation.

The application-specific database objects are created by the CM application during the first startup of the application server.

Transactions recovery

The following settings must be applied for the user accessing an Oracle XA data source in order for XA recovery to operate correctly. The value <CM_USER> is the database user defined to connect from JBoss to Oracle.

sqlplus / as sysdba

#check version

SELECT * FROM V$VERSION;

#get correct spelling of CMUSER

SELECT username FROM dba_users where username = upper('<CM_USER>');

GRANT SELECT ON sys.dba_pending_transactions TO '<CM_USER>';

GRANT SELECT ON sys.pending_trans$ TO '<CM_USER>';

GRANT SELECT ON sys.dba_2pc_pending TO '<CM_USER>';

GRANT EXECUTE ON sys.dbms_xa TO '<CM_USER>';

Post-installation tasks

After the CM-specific database objects have been created during the first successful start of the CM application (see Setting up the ConSol CM application), you can set the starting ticket number. By default, the ticket numbers are assigned starting with 1, but you can determine that they should start with another number.

For example, execute the following statement as application Oracle user to start the ticket numbers with 10000:

drop sequence TICKET_SEQUENCE;

CREATE SEQUENCE TICKET_SEQUENCE" MINVALUE 10000 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 100000 CACHE 20 NOORDER NOCYCLE ;