Follow the steps below to configure Oracle on a local (in house) server.

Oracle Server Recommendations

Your Oracle server must run Oracle 11g or higher 64-bit (Standard Edition, Standard Edition One for one to five users, or Enterprise Edition), hold the database files, and make backups of your database.

The table below lists our recommendations for your Oracle server. Please note, these recommendations are based on typical requirements. If you have a large number of users, for example, you may need faster servers with more memory.

Oracle Server Recommendations

Item

Recommended

Processor Type

Dual Quad-core

Processor Speed

2.8 GHz

Memory

16 GB

Local Storage

500 GB

Operating System

Red Hat Linux 5.4, Solaris 10, Windows 7 (32 or 64-bit, all editions), Windows Server 2011, Windows Server 2008 and 2008 RT, Windows Server 2003

Oracle Edition (compatible with OS)

Version 11g or higher 64-bit (compatible with OS), Standard, Standard Edition One (recommended for 1-5 users), or Enterprise Edition

Oracle Text Utility Required

To set up Persephone's Oracle database you must install Oracle Text utility. See the Oracle Text FAQ page for more information about Oracle Text and see Oracle's Database Installation and Administration Guide's Chapter 10, "Oracle Text" for steps to install Oracle Text.

Tip

The latest versions of Persephone/Cerberus use a third-party search engines like Lucene or Solr. This removes the requirement to have Oracle Text installed.

Local Oracle Server Setup Steps

Follow the steps below to configure your Oracle database to operate with Persephone.

  1. Login to the host machine (e.g., named "PERSHOST") as user root or Administrator (dependent upon which operating system [OS] you are using).
  2. As root/Administrator, create OS group dba and OS user oracle as part of the OS group dba and allocate diskspaces/directories for installing the Oracle software and the database (e.g., named "PERSEPHONE").
  3. As user root/Administrator or oracle, allocate OS directories (with sufficient storage space) for the Oracle database software and the PERSEPHONE database.
  4. As root/Administrator, install the Oracle Database software (e.g., version 11g or later of Oracle Standard, Standard Edition One [recommended for one to five users], or Enterprise edition version).

Tip

Please visit www.oracle.com/database/index.html for more information about the different Oracle editions and refer to the Oracle Installation Guide for additional documentation covering Oracle installation on Windows and Linux servers.

  1. Create an Oracle database instance (e.g., named "PERSDB") by running the Oracle DBCA utility.

Please note you must use the WE8MSWIN1252 character set. Using any other character set may result in unpredictable complications. After you have configured your Oracle database you can ensure you are using the WE8MSWIN1252 character set by entering the following.

SQL> select * from nls_database_parameters where PARAMETER='NLS_CHARACTERSET' ;
PARAMETER                      VALUE
------------------------------ --------------------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252


Tip

Refer to Oracle's Creating a Database with DBCA page for more information about the DBCA utility.

  1. Login to the database instance (PERSDB) as user "sys as sysdba".
  2. Create a tablespace (e.g., named "PERSEPHONE_TS"). For example, the following would create a 120GB tablespace named "PERSEPHONE_TS" in 30GB segments.

SQL> CREATE TABLESPACE PERSEPHONE_TS datafile '/oradat/persephone/persephone_01.dbf'
size 30g extent management local segment space management auto;
   ALTER TABLESPACE PERSEPHONE_TS add datafile
'/oradat/persephone/persephone_02.dbf' size 30g;
   ALTER TABLESPACE PERSEPHONE_TS add datafile
'/oradat/persephone/persephone_03.dbf' size 30g;
   ALTER TABLESPACE PERSEPHONE_TS add datafile
'/oradat/persephone/persephone_04.dbf' size 30g;

  1. Increase/Resize the TEMP tablespace to 2GB or more as shown in the example below.

SQL> alter tablespace TEMP resize 2GB

  1. Create Oracle user PERSEPHONE, assigning PERSEPHONE_TS as its default tablespace as shown in the example below.

Note

Your Oracle Database Administrator (DBA) may alter user persephone's privileges as appropriate to comply with relevant security and privacy policies.

create user persephone identified by somePassword
   default tablespace PERSEPHONE_TS temporary tablespace TEMP ;
grant connect, resource to persephone ;
grant create session to persephone;
grant alter  session to persephone ;
grant create table   to persephone;
grant create view    to persephone;
grant create synonym to persephone;
grant create any materialized view to persephone ;
grant select any dictionary to persephone ;
grant execute on sys.dbms_lock  to persephone ;
grant execute on ctxsys.ctx_ddl to persephone ;
grant execute on ctxsys.ctx_ddl to persephone ;
grant execute on dbms_datapump  to persephone ;
grant execute on DBMS_FILE_TRANSFER to persephone;
grant read, write on directory DATA_PUMP_DIR to persephone ;
grant unlimited tablespace to persephone ;

  1. The PERSEPHONE Oracle database server must have the CTXSYS package (which is part of the Oracle Text Utility) installed, and the permission to execute the ctxsys.ctx_ddl procedure must be granted to the user/schema (e.g., PERSEPHONE) associated with the PERSEPHONE database. To verify if the CTXSYS package is installed, enter the following:

SQL> select username, user_id, profile, EXPIRY_DATE, account_status, default_tablespace, temporary_tablespace from dba_users where username like '%CTX%';
USERNAME                          USER_ID PROFILE    EXPIRY_DATE         ACCOUNT_STATUS       DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE
------------------------------ ---------- ---------- ------------------- -------------------- -------------------- --------------------
CTXSYS                                 43 DEFAULT                        LOCKED               SYSAUX       TEMP

  1. Grant to user PERSEPHONE the permission to execute ctxsys.ctx_ddl by entering the following:

SQL>  grant execute on ctxsys.ctx_ddl to PERSEPHONE;

  1. Logoff from PERSDB (as Oracle user PERSEPHONE).
  2. The rest of the schema initialization can be done by running PersephoneShell with the command init. It will create all the necessary database tables, indices, constraints, triggers, etc., and will pre-populate a few look-up tables.

Optional. Send Us Your Database Login Credential File to Set Up ClickOnce Distribution

If you want to deploy the Persephone application to the users with Microsoft's ClickOnce technology (see Set Up ClickOnce User Distribution) you will need to send us your database login credential file in the form of an Oracle EZCONNECT string.

Please note Persephone does not ask for user names or passwords from users. Instead, it reads the database login credentials from a configuration file where the connection string is stored in an encrypted form. We (Persephone Software, LLC.) will encrypt your real connection string and put it into the configuration file.

To create this configuration file we need you to send us your database login credential file with the following:

  • Your server name (e.g., "oracle_server.com")
  • The port number (e.g., 1521)
  • The service name (e.g., "service_name")
  • The username (e.g., "PERSEPHONE" but please note it can be any name)
  • The password (e.g., "somepassword").

We then run a program that encrypts an Oracle EZCONNECT string like the following

persephone/somepassword@oracle_server1:1521/service_name

into an encrypted, unreadable string. This string is inserted into the "web.config" configuration file of the API server (Cerberus).

Optional. Oracle Server Maintenance Utilities

Refer to Oracle Maintenance Utilities for SQL commands to view and modify your Oracle database.