MySource Matrix Resources

Content Tools

Main Content

Oracle Database Setup Guide

Introduction

This guide will demonstrate how to create a user and an Oracle tablespace for use with MySource Matrix. It will also define the format of the DSN connection strings used to connect to the database.

Creating a Tablespace

Connect to your database instance as a privileged user to create the necessary database components needed by MySource Matrix. Ensure that you have the correct environmental variables set.

$ export ORACLE_SID=matrix
$ sqlplus 'sys as sysdba'

Create a tablespace for MySource Matrix and choose an appropriate size and location for your datafile. The datafiles for a tablespace generally reside in the $ORACLE_BASE/<DB_SID> directory on the database server. You can always append more datafiles to your tablespace as your database resource requirements grow.

SQL> CREATE TABLESPACE matrix
DATAFILE '/opt1/oradata/DB_SID/matrix.dbf' SIZE 100M
/

Creating a User

Create a user to connect to your database instance and grant the appropriate permissions for that user. Specify the tablespace that we previously created as the user's default tablespace.

SQL> CREATE USER matrix IDENTIFIED BY password
SQL> DEFAULT TABLESPACE matrix 
SQL> TEMPORARY TABLESPACE temp
/
SQL> GRANT CONNECT, RESOURCE TO matrix
/

For security reasons, you should replace password with a more appropriate password for your user.

Oracle 10g only

For Oracle 10g, the Matrix database user requires an additional privilege: the ability to create views, as views are created by the step_02.php installation script. This can be granted using the following SQL statement:

SQL> GRANT CREATE VIEW TO matrix;

Setting the Date Format

MySource Matrix requires that dates selected from Oracle are in the ISO-8601 date format. The easiest way to ensure that this is the case is to set the NLS_DATE_FORMAT variable to the required format. Note that you will be required to restart the instance for this change to take effect. You will need to perform this change as a user with sysdba privileges.

SQL> ALTER SYSTEM SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' scope=spfile;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Creating the Search Re-Indexing Job (Version 3.12+)

This step is not required in versions 3.10 or earlier of MySource Matrix.

If you have the Search package in version 3.12 RC1 or later of MySource Matrix, the Search package uses Oracle's Text package to enhance search functionality. However, this requires the Oracle Text index to be regularly regenerated, otherwise data will not be available to searches within MySource Matrix. This requires the creation of a scheduled job within Oracle.

First, you will need to first grant access for Oracle Text's CTX_DDL package - which contains the function required to re-sync the search index - to the Matrix database user. You will need to perform this change as a user with sysdba privileges.

SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO matrix;
Oracle 10g only: Grant Job Create Permissions to Matrix User

For Oracle 10g, you also need to grant the Matrix database user permission to create jobs within the scheduler, which can be done using this SQL statement (as the same sysdba user):

SQL> GRANT CREATE ANY JOB TO matrix;
Oracle 9i

On an Oracle 9i system, this script will install the job into the Oracle scheduler:

Without modification, this script will regenerate the index every 30 minutes. To change this interval, modify the fractional part of the "interval" setting (specified in fractions of a day - for example, 'SYSDATE+1/24' equates to an hourly regeneration instead.)

SQL> DECLARE JobID NUMBER;
  2  BEGIN
  3    SYS.DBMS_JOB.SUBMIT
  4    (
  5      job          => JobID,
  6      what         => 'CTX_DDL.SYNC_INDEX(''sq_sch_idx_value'');',
  7      next_date    => SYSDATE+5/86400,
  8      interval     => 'SYSDATE+30/1440',
  9      no_parse     => TRUE
 10    );
 11  COMMIT;
 12  END;
 13  /
Oracle 10g

In Oracle 10g, full-text indexes can be created with an optional SYNC parameter, allowing the regular regeneration of the index to be setup without a separate command. However, as MySource Matrix cannot distinguish between different versions of Oracle, the index needs to be dropped and re-created.

The following SQL commands will drop the index the MySource Matrix installation creates, and will re-create the index with the SYNC attribute, to be regenerated every 30 minutes (this can be changed by modifying the part after the "EVERY" keyword - in the same format as in Oracle 9i):

DROP INDEX sq_sch_idx_value;
CREATE INDEX sq_sch_idx_value ON sq_sch_idx (value)
    INDEXTYPE IS CTXSYS.CONTEXT
    PARAMETERS('STOPLIST ctxsys.default_stoplist SYNC (EVERY "SYSDATE+(30/1440)")');
COMMIT;

Creating a Data Source Name (DSN)

The prefix for Oracle DSNs is oci8. Using the credentials and the database name in this example, all your DSNs should look like this.

oci8://matrix:password@dbserver.example.com

Note that dbserver.example.com should be replaced with your host, or your Oracle connection identifier.

Version 3.18.0 RC2 and later: (please note, support in 3.18.0 RC2 is experimental)

In this version, the database details have been moved to a new file, db.inc. The SQ_CONF_DB_DSN variable and its brethren no longer exist in main.inc. These have to be set up slightly differently to previous versions - each connection is defined by an array of settings, including the data source name (DSN), which is provided in a different (PDO-compatible) format.

For Oracle, each database connection's entry takes the form of the following example extract. Note that the prefix is now 'oci' and not 'oci8', and in the DSN setting, and the 'DSN' entry should should be set to your connection identifier - either a server name, or an Instant Client connection string.

        'db' => array (
                'DSN' => 'dbname.example.com',
                'user' => 'developers',
                'password' => '',
                'type' => 'oci',
               ),