Oracle™ Audit Trail Database Setup

This section addresses the setup and installation of an Oracle audit trail database. The section includes setup of Oracle, creation of the Oracle audit trail database and schema.

Prerequisites

The following sections assume as basic understanding of Oracle database server installation, configuration and the SQL/PLUS utility.

Before an audit trail database can be created, a properly installed and configured instance of an Oracle database server must exist. Please refer to the Infinity Process Platform Release Notes for details on the supported versions.

Oracle can be downloaded from the Oracle Download Website.

After downloading Oracle, install Oracle onto the database server. The server installation directory is referred to as %ORACLE_HOME%. The JDBC driver jar file to download should be ojdbc<version>.jar depending on the used Java version.

Infinity Process Platform databases must run in read-committed mode. If the transaction isolation level is set to another mode, please change it. You can set the isolation level of a transaction by using the following command at the beginning of a transaction:

SQL>SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The next sections discuss database, user and schema creation for an Oracle audit trail database. This process utilizes Oracle's SQL/Plus utility and the Infinity Process Platform System Console utility. To execute properly, the Oracle JDBC driver must be copied into the default/lib folder of your working environment.

More information on the System Console utility can be found in chapter Using the Sysconsole Command.

Note
Please take care to keep your statistics for tables and indexes created and used by IPP up-to-date to improve performance. As Oracle is using a cost-based optimizer (CBO) which relies on current statistics this should be done regularly.

User Creation and Setup

Within Oracle, a user must be created to hold the contents of a database schema. To create the user, launch the SQL/Plus utility and execute the following commands from the Oracle command line:

SQL> CREATE USER <dbName> IDENTIFIED BY <password>;
SQL> GRANT CONNECT TO <dbName>;
SQL> GRANT RESOURCE TO <dbName>;

Additional parameters can be utilized to control tablespaces and other Oracle specific settings of the new user if desired.
Based on your database environment, additional grants may be necessary to complete the audit trail setup process.

Schema Setup

Once the user has been created, the audit trail database schema must be created within Oracle. This process utilizes the System Console utility provided with Infinity Process Platform. The user specified with the '-d' parameter must have sufficient privileges to create tables, indexes, and sequences.

Execute the System Console with the following arguments:

sysconsole -v -r <jdbcDriver> -dbtype Oracle -dbschema <dbschema> -l jdbc:oracle:thin:@<host>:<port>:<dbname> -d <user> -s <password> createschema

The following is displayed after the command is invoked:

Infinity Process Platform (TM) Administration Console, Version 4.x
Copyright (C) SunGard CSA LLC, 2000-2011. All rights reserved.

Create Infinity Process Platform schema:

Database type   : Oracle
Database URL    : jdbc:oracle:thin:@<host>:<port>:<dbname>
Database user   : <user>
Database driver : <jdbcDriver>

Do you want to proceed? (Y/N):

Answer Y to proceed. The following is displayed after successful creation of the audit trail database schema.

Schema created.

Alternative Schema Setup

If you do not have the ability to directly create the database schema because of privileges within your database environment or desire to create a file with the appropriate SQL commands, the following procedures can be followed to create a file containing the appropriate DDL and DML statements that can be executed within the database as an SQL script.

sysconsole -v -dbtype Oracle ddl -file <fileName> -schemaName <schemaName>

Once the DDL/DML file has been created by the previous command, follow the Database setup instructions from the previous sections if not already completed, and then have a user who has appropriate privileges execute the created DDL/DML script file as such:

SQL> @<fileName>

Local and Remote Mode Setup

The following sections discuss the datasource / database connection pool setup of the audit trail database based on the deployment mode.

Spring Local Mode Setup

If you use Infinity Process Platform in Spring local mode, you have to perform the following steps, e.g.:

  1. In your carnot.properties file add the following properties:
    AuditTrail.Type = ORACLE
    AuditTrail.DriverClass = oracle.jdbc.OracleDriver
    AuditTrail.URL = jdbc:oracle:thin:@<host>:<port>:<dbname>
    AuditTrail.User = <yourDBUserName>
    AuditTrail.Password = <yourDBUserPassword>
    Set the type of the audit trail database to Oracle and use the database driver class oracle.jdbc.OracleDriver.
  2. In your carnot-spring-ds-jndi-context.xml replace the parameter ${AuditTrail.XaDataSourceClass} in the carnotXaAuditTrailDataSourceLocalUrl bean part with class oracle.jdbc.xa.client.OracleXADataSource:
    <bean id="carnotXaAuditTrailDataSourceLocalUrl" class="oracle.jdbc.xa.client.OracleXADataSource" lazy-init="true">
        <property name="URL" value="${AuditTrail.URL}" />
        <property name="user" value="${AuditTrail.User}" />
        <property name="password" value="${AuditTrail.Password}" />
    </bean>

Remote Mode Setup

The audit trail database datasource and connection pool setup is configured based on the application server/container utilized for the Infinity Process Platform deployment. Refer to Application Server Setup for more information regarding datasource and connection pool setup for an audit trail database.