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,
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'
- alter database add logfile '<redo log directory>/newredo2.log'
- alter database add logfile '<redo log directory>/newredo3.log'
- 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
- 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
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
- 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
- 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
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)
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
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
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
- Revoke the policy directly from the user.
- Revoke the policy from all roles granted to
- 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
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
- create user <username> identified by <password> default
tablespace <default_tbs> temporary tablespace <temporary_tbs>
quota unlimited on <default_tbs>;
[ where <username> = name of the Ultra Search instance owner ]
[ and <password> = password of the Ultra Search
instance owner ]
[ and <default_tbs> = default tablespace for the Ultra
Search instance created in step 3]
[ and <temporary_tbs> = temporary tablespace
created in step 2]
- 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.]