
	Table of Contents

Configuring the Oracle Server for Ultra Search

The operations described in this document are database administration operations. They can be performed using Oracle Enterprise Manager or SQL*Plus.

This document lists the necessary steps for:

Configuring the Oracle Server for Ultra Search.
Creating tablespaces and users for each Ultra Search instance.

	Related Topics
Installing Oracle Ultra Search Managing Stoplists

Configuration steps

Step 1: Tune the Oracle database

Increase the size of the Oracle redo logs (if necessary).
- Every instance of an Oracle database has an associated online redo log, which is a set of two or more online log files that record all committed changes made to the database. Online redo logs serve to protect the database in the event of an instance failure. The size of Oracle redo log files determines the frequency of redo log file switches. This, in turn, significantly impacts text indexing speed. To reduce the frequency of logfile switches we recommend ensuring that the redo log files are each 10Mb or more. Refer to the Oracle9i Designing and Tuning for Performance manual or the or the Oracle 9i Administrator's Guide for more details on tuning your production system. However, here are some quick tips on how to increase the redo log file sizes (if deemed necessary). Issue the commands described below using the appropriate Oracle administrator privileges.
- Step 1: Locate redo log files and determining their sizes:
  - select v$logfile.member, v$logfile.group#, v$log.status, v$log.bytes
    from v$log, v$logfile
    where v$log.group# = v$logfile.group#
- Step 2: Add larger redo log files:
  - alter database add logfile '<redo log directory>/newredo1.log' size 10m;
  - alter database add logfile '<redo log directory>/newredo2.log' size 10m;
  - alter database add logfile '<redo log directory>/newredo3.log' size 10m;
  - Note that a production database should have more log members per log group. Furthermore, different storage devices should be used to increase performance and reliability. Consult the Oracle9i Designing and Tuning for Performance manual for further details.
- Step 3: Drop the old log files:
  - For each old redo log file, issue the the "alter system switch logfile" command until that logfile's status is "INACTIVE". This is necessary to ensure that Oracle is not using that logfile when you attempt to drop it.
  - Then, drop the old redo log file using the following command:
    alter database drop logfile '<redo log directory>/redo01.log';
    alter database drop logfile '<redo log directory>/redo02.log';
    alter database drop logfile '<redo log directory>/redo03.log';
- Step 4: Manually delete the old log files from the file system
  - For each old redo log file, use the appropriate operating system command to delete the unwanted log file from the file system.
Increase the size of the rollback segments.
- The rollback segments created by the Oracle Installer are likely to be too small.
- It is recommended that you replace the small rollback segments (and corresponding datafiles) with larger datafiles and rollback segments. Refer to the Oracle9i Designing and Tuning for Performance manual or the Oracle 9i Administrator's Guide for more details on sizing the rollback segments for your production system.
Tune the Oracle initialization parameters in the init<SID>.ora file.
- processes - Increase this number to 50 or more.
- sort_area_size - increase this parameter to 5MB or more.
- sort_area_retained_size - increase this parameter to 5MB or more.
- job_queue_processes - set this to at least 1. We recommend that you increase this to 3. This is needed because the Ultra Search Crawler is launched by scheduling a dbms job. If the job_queue_processes parameter is 0, no dbms jobs are run. As a result, any attempts to launch the Ultra Search Crawler will fail.
- For the latest information on initialization parameters, please refer to the Ultra Search Readme.

Step 2: Create and assign the temporary tablespace to the CTXSYS user.

The starter database created by the Oracle Installer most likely creates a temporary tablespace that is too small. Oracle Ultra Search uses the Oracle Text engine intensively. Therefore, a large temporary tablespace must be created for the Oracle Text system user CTXSYS.
If you desire greater read and write performance, you can create a raw tablespace.
Consult your Oracle 9i documentation on how to create a temporary tablespace.
When you have created the temporary tablespace, assign it as the temporary tablespace for the CTXSYS user. To do so, you must log on as the SYSTEM or SYS user. You can assign the temporary tablespace to the CTXSYS user by typing the following command:

alter user CTXSYS temporary tablespace <NEW_TEMPORARY_TABLESPACE>.

Step 3: Create a large tablespace for each Ultra Search instance user.

For each Ultra Search instance, you must create a tablespace large enough for containing all data obtained during the crawling and indexing processes. This amount is naturally subject to the amount of data you intend to crawl and index. However, it is often not possible to know in advance how much data you intend to collect. Try to obtain an estimate of the cumulative size of all data you wish to crawl.
If you do not have any idea whatsoever, try to allocate as much space as possible. Nevertheless, even if you run out of disk space later, the Ultra Search system is able to resume crawling after you have added more datafiles to the instance tablespace.
Please pay attention to the storage clause in your create tablespace statement. For example, the amount of data to be stored in the tablespace can potentially be very large. This can cause the Oracle Server to progressively allocate many new extents when more storage space is needed. If the extent management clause specifies that each new extent is to be larger than the previous extent (i.e. the PCTINCREASE setting is non-zero), you may potentially encounter the situation where the next extent that the Oracle Server wants to allocate is larger than what is available. In such a situation, indexing will be halted until new extents can be added to the tablespace.

To help mitigate this problem, certain instance specific tables have explicit storage parameter settings. The initial extent size, next extent size and PCTINCREASE setting are defined for these tables. These tables are created when a new instance is created. The tables and their storage clause settings are as follows:
- DR$WK$DOC_PATH_IDX$I (inital extent size 5M, next extent size 50M, PCTINCEASE 1)
- DR$WK$DOC_PATH_IDX$K (inital extent size 5M, next extent size 50M, PCTINCEASE 1)
For more information on creating tablespaces and managing storage settings, please refer to the Oracle 9i SQL Reference.
If you desire greater read and write performance, you can to create raw tablespaces. Consult your Oracle 9i documentation on how to create a tablespace.
Be sure to create a new large tablespace for each Ultra Search instance user.

Step 4: Create and configure new database users for each Ultra Search instance

The Ultra Search system makes use of Oracle 9i's Fine Grained Access Control feature to support multiple Ultra Search instances within one physical database. This feature is especially useful for large organizations or Application Service Providers (ASPs) that want to host multiple disjoint search indexes within one physical installation of Oracle.
Important:
- The exempt access policy must not be granted to Ultra Search instance users. Doing so will result in a security compromise where an Ultra Search instance user will be able to view and inadvertently modify information in other Ultra Search instances. Therefore, it is very important that the exempt access policy privilege be granted only to database users that absolutely require this policy (such as a user performing an administrative task in the database).
- The following steps should be used to check if a user has the exempt access policy granted to it:
  - Login as the user you want to investigate.
  - Run the following SQL statement: select * from session_privs where privilege = 'exempt access policy'.
  - If the above SQL statement returns 1 or more rows, then the user does have the exempt access policy granted to it. You must revoke this policy.
- To revoke the policy from the user, you must do the following:-
  1. Revoke the policy directly from the user.
  2. Revoke the policy from all roles granted to the user.
- To revoke the policy directly from the user, type revoke exempt access policy from <username>.
- To revoke the policy directly from all roles assigned to the user, first find out all roles that are both assigned to the user and have the policy granted to it. Do that with the following SQL statement:
  select grantee, granted_role from dba_role_privs where grantee = '<username>' and granted_role in (select grantee from dba_sys_privs where privilege = 'exempt access policy').
  Then, for each role returned, type revoke exempt access policy from <role>.
- As a safety precaution, the Ultra Search Administration Tool will perform a check every time an administrator attempts to select an instance to administer. The user will not be allowed to proceed if the exempt access policy is granted.
The Ultra Search system requires that each Ultra Search virtual instance belong to a unique database user. Therefore, as part of the installation process, you must create one or more new database users to own all data for your Ultra Search instance. (Note that if you intend to create more than one database instance, you should also create multiple user tablespaces - one for each user).

You will need to grant certain roles and privileges to each Ultra Search user. For convenience, the WKUSER role has been created with all necessary privileges.
Issue the following commands to create and configure a new user (you can run these commands as the WKSYS, SYSTEM or SYS database users):

create user <username> identified by <password> default tablespace <default_tbs> temporary tablespace <temporary_tbs> quota unlimited on <default_tbs>;

grant WKUSER to <username>;
grant WKADMIN to <username>;
[Note: This step is optional. Granting the WKADMIN role to this user will allow the user to create and drop Ultra Search instances.]