Skip Headers

Oracle Ultra Search User's Guide
Release 9.0.3

Part Number B10043-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

3
Post-Installation and Upgrade Information

This chapter contains the following topics:

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 the following:

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 protect the database in the event of an instance failure. The size of redo log files determines the frequency of redo log file switches. This, in turn, significantly impacts text indexing speed. To reduce the frequency of log file switches, ensure that the redo log files are each 10Mb or more.

The following section lists some quick tips on how to increase the redo log file sizes, if necessary. Enter the statements in the following section with the appropriate Oracle administrator privileges.

See Also:
  1. Locate redo log files and determine 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#
    
    
  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;
    
    

    A production database should have more log members for each log group, and different storage devices should be used to increase performance and reliability.

  3. Drop the old log files. For each old redo log file, enter the ALTER SYSTEM SWITCH LOGFILE statement until that log file's status is INACTIVE. This is necessary to ensure that Oracle is not using that log file when you try to drop it.

    Then, drop the old redo log file with the following statement:

    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';
    
    
  4. Manually delete the old log files from the file system For each old redo log file, use the appropriate operating system statement to delete the unwanted log file from the file system.

Increase the Size of the Undo Space

Every Oracle database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. Oracle refers to these records collectively as undo. The undo space created by the Oracle Installer is likely to be too small.

Historically, Oracle has used rollback segments to store undo. Oracle now offers another method of storing undo that eliminates the complexities of managing rollback segment space, and enables DBAs to exert control over how long undo is retained before being overwritten. This method uses an undo tablespace.

Oracle Corporation recommends that you use automatic undo management and increase the undo space using an UNDO_TABLESPACE.

See Also:

Oracle9i Database Administrator's Guide for details on using automatic undo management

Tune the Oracle Initialization Parameters in the init<SID>.ora File

PROCESSES: Increase this to 50 or more.

SORT_AREA_SIZE: Increase this to 5MB or more.

SORT_AREA_RETAINED_SIZE: Increase this to 5MB or more.

JOB_QUEUE_PROCESSES: Increase this to three. (Set it to at least one.) This is needed because the Ultra Search crawler is launched by scheduling a database job. If this is zero, then no database jobs are run. As a result, any attempts to launch the Ultra Search crawler will fail.

For the latest information on initialization parameters, see 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 want greater read and write performance, create a raw 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. Assign the temporary tablespace to the CTXSYS user with the following statement:

ALTER USER CTXSYS TEMPORARY TABLESPACE <NEW_TEMPORARY_TABLESPACE>
See Also:

Oracle9i Database Administrator's Guide for information on how to create a 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 want to crawl.

If you cannot estimate the size, then try to allocate as much space as possible. If you run out of disk space later, Ultra Search is able to resume crawling after you have added more datafiles to the instance tablespace.

Pay attention to the STORAGE clause in your CREATE TABLESPACE statement. 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 (that is, the PCTINCREASE setting is nonzero), then you could 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 halts until new extents can be added to the tablespace.

To 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 
                (initial extent size 5M, next extent size 50M, PCTINCEASE 1)
DR$WK$DOC_PATH_IDX$K 
                (initial extent size 5M, next extent size 50M, PCTINCEASE 1)

If you want greater read and write performance, create raw tablespaces.

Be sure to create a new large tablespace for each Ultra Search instance user.

See Also:

Step 4: Create and Configure New Database Users for Each Ultra Search Instance

The Ultra Search system uses Oracle'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 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:

If you intend to create more than one database instance, you should also create multiple user tablespaces - one for each user.


You must grant certain roles and privileges to each Ultra Search user. For convenience, the WKUSER role has all necessary privileges. The instance owner must have been granted the WKUSER role.

See Also:

"Users Page"

Enter the following statements to create and configure a new user. Run these statements as the WKSYS, SYSTEM, or SYS database user.

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>;

After these steps are completed, WKSYS or an Ultra Search superuser can create an Ultra Search instance on this user schema.

If you want this user to have the general administrative privilege or the superuser privilege of Ultra Search, log in as an Ultra Search superuser or WKSYS and click the Users tab to grant the appropriate privilege.

For the database release: After the database is installed, all the user schema accounts are locked. To login as user WKSYS, unlock WKSYS by running the following statements as the SYSTEM or SYS database user:

ALTER USER WKSYS ACCOUNT UNLOCK; 
ALTER USER WKSYS IDENTIFIED BY <password>;

For the iAS release: After the infrastructure database is installed, all the user schema passwords are randomized. To login as user WKSYS, change the WKSYS schema password by running the following statement as the SYSTEM or SYS database user:

ALTER USER WKSYS IDENTIFIED BY <password>;

Step 5: Gather Statistics for the Tables

If you notice performance degradation on the crawler, it might be because statistics have not been gathered for the tables. You should gather statistics for the following tables: WK$URL, WK$DOC, and DR$WK$DOC_PATH_IDX$I. Statistics for the WK$URL table are the most important. You must regularly gather statistics, because statistics are used by the cost-based optimizer to generate the best execution plan. Make sure that the crawler is not running during the performance tuning period to avoid interference.

Use the DBMS_STATS PL/SQL package or the ANALYZE procedure to gather statistics. The DBMS_STATS package can be run on either the table level or the schema level. Running on the schema level computes the statistics for the all the objects in the schema including the tables and indexes. Oracle Corporation recommends using the DBMS_STATS package.

Connect to the schema owning the Ultra Search instance. For example:

EXEC DBMS_STATS.GATHER_TABLE_STATS(`<schema_name>', `<table_name>', null, DBMS_
STATS.AUTO_SAMPLE_SIZE); 

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(`<schema_name>', DBMS_STATS.AUTO_SAMPLE_
SIZE); 

or

ANALYZE TABLE <table_name> ESTIMATE STATISTICS SAMPLE 20 percent; 

where <schema_name> is the owner of the Ultra Search instance and <table_name> is the table you want to gather statistics for (for example, wk$url).

Occasionally rebuilding the B-tree indexes could also improve performance by freeing disk space. For example:

ALTER INDEX <index_name> REBUILD;

where <index_name> with the index that you want to rebuild.

To get a list of the indexes, run the following statement:

SELECT index_name FROM user_indexes WHEREindex_type='NORMAL'; 

Step 6: Alter the Index Preferences

This step is optional.

An empty index is created when an Ultra Search instance is created. The existing index preferences, such as language-specific parameters, are defined in the $ORACLE_HOME/ultrasearch/admin/wk0pref.sql file.

You can modify these preferences so that all new Ultra Search instances use the modified preferences, or you can alter the index using your own preferences immediately after an instance is created. Alter the index using SQL.


Note:

The crawler transforms all documents into HTML files with binary document filtering before indexing begins.


See Also:

Managing Stoplists

Every Ultra Search instance has a stoplist associated with it. A stoplist is a list of words that are ignored during the indexing process. These words are known as stopwords. Stopwords are not indexed because they are deemed not useful, or even disruptive, to the performance and accuracy of indexing.

Default Ultra Search Stoplist

During the installation process, a default stoplist is created for the Ultra Search product. Subsequently, when an Ultra Search instance is created, a copy of the default stoplist is created for the Ultra Search instance.

The default stoplist is created under the WKSYS schema. The default stoplist name is wk_stoplist. (This list is defined in the file $ORACLE_HOME/ultrasearch/admin/wk0pref.sql, which is run at installation).

Modify the default stoplist by adding or removing stopwords from it. However, remember that these modifications do not affect existing Ultra Search instances. They only affect Ultra Search instances that are created after the modifications are made.

Modifying Instance Stoplists

Modifying instance stoplists should be done as a last resort. Use one of the following methods:

Modifications made to the default stoplist are reflected in all other instance stoplists created after the time of modification.

Replacing the instance stoplist immediately after creating the instance affects only that instance. You first need to create a user-defined stoplist.

In both cases, the result is that the Ultra Search instance stoplist is modified and defined before initial crawling. This means that all documents collected by the Ultra Search crawler are evaluated against the correct stoplist. It is important to modify the stoplist before initial crawling to avoid having to recrawl all documents again.

Modifying Instance Stoplists Before Initial Crawling

  1. Modifying the default stoplist before creating the instance:

    To add the stopword "web" to the default stoplist, log in as user WKSYS in SQL*Plus, and run the following statement:

    EXEC ctx_ddl.add_stopword('wk_stoplist','web');
    
    

    To remove the stopword "web" from the default stoplist, log in as user WKSYS in SQL*Plus, and run the following statement:

    EXEC ctx_ddl.remove_stopword('wk_stoplist','web');
    
    

    Subsequently, the stoplists of all new instances reflect the modifications made to the default stoplist.

  2. Replace the instance stoplist immediately after creating the instance:

    You must create a new user-defined stoplist. Log in as the owner of the instance in SQL*Plus, and run the following statements:

    BEGIN   ctx_ddl.create_stoplist('example_stoplist'); 
            ctx_ddl.add_stopword('example_stoplist','example_stopword'); 
            ... (add more stopwords by repeated the previous 
            line with new stopwords) ... 
    END; 
    /
    
    

    To replace an instance stoplist with this new stoplist, log in as the owner of the instance in SQL*Plus, and run the following statement:

    ALTER INDEX wk$doc_path_idx rebuild parameters('replace stoplist example_
    stoplist');
    

Modifying Instance Stoplists After Initial Crawling

If necessary, alter an instance stoplist after initial crawling with one of the following methods:

  1. Add stopwords to the instance stoplist:

    Choosing to add stopwords to the instance stoplist does not affect any documents already crawled or indexed. This operation is not an expensive operation.

    To add the stopword "web" to the instance stoplist, log in as the owner of the instance in SQL*Plus, and run the following statement:

    ALTER INDEX wk$doc_path_idx rebuild parameters('add stopword web');
    
    
  2. Replace the instance stoplist after initial crawling:

    Defining a new stoplist and replacing the instance stoplist with it invalidates the entire index. If you choose this method, you must force the Ultra Search crawler to recrawl all documents in the index. To do this, click "Process all documents" in the Edit Schedule page. This is a very expensive operation. Therefore, this option should be the last resort.

Upgrading to the Most Recent Ultra Search Release

Ultra Search supports the following upgrades:

Upgrade is based on the server component only. Upgrade on the middle tier is not supported. Install the 9.0.3 middle tier in a separate Oracle Home.

See Also:

"What's New in Ultra Search?"describes the Ultra Search release numbering

Upgrade from Ultra Search 1.0.3 (Oracle9i Database 9.0.1) to 9.0.3

To upgrade Ultra Search 1.0.3 to 9.0.3, perform the following steps:

  1. Launch Oracle Portal Configuration Assistant (OPCA) in 'ALL' mode, and it invokes Ultra Search Configuration Assistant (USCA). USCA checks the Ultra Search release in the existing database.
  2. If USCA detects that the Ultra Search release is 1.0.3, then it asks you to select one of the following three options:
    • Deinstall/reinstall
    • Migrate
    • Abort
  3. Select the option "Migrate". You must run the upgrade script and perform some manual steps.

    The Ultra Search upgrade script first verifies the version of the current system, then upgrades the system and migrates user data. User data includes all dictionary and table data, such as information about the metadata, data sources, mappings, crawler schedules, authentication, and query statistics.

    All crawler schedules and jobs created in the older version are disabled before data and system migration. When migration is complete, the system administrator should re-activate the crawling schedule to re-index the document. You do not need to reconfigure the system or re-enter any data. Users can still query documents that were crawled and indexed by the previous version.

    See Also:

    "Installing the Server Component on an Existing Database or Metadata Repository"

Ultra Search Migration Approaches

There are two approaches to migrate user data: the in-place approach and the ETL (extract-transform-load) approach. With the in-place approach, the current ORACLE_HOME is used. With the ETL approach, a new ORACLE_HOME is created.

Ultra Search In-Place Migration

In-place migration upgrades existing configurations and user data to the latest Ultra Search release. Upgraded files are left in place, and the source installation is modified. The benefit to this approach is that it might conserve disk space. With the in-place approach, data migration involves the following six steps:

  1. Back up user data
  2. Deinstall previous database objects
  3. Install new database objects
  4. Re-create user instances
  5. Restore data
  6. Rebuild index

Use the SQL script wk0upgrade.sql to run the in-place migration steps one through five, listed in the preceding section. The script is located in the %ULTRASEARCH_HOME%/admin/ directory. It requires the following input parameters:

The sixth step requires the system administrator to re-activate all crawling schedules through the Ultra Search administration tool.

Ultra Search Extract-Transform-Load Migration

Extract-transform-load (ETL) migration extracts the useful subset of configuration data from the source installation, transforms necessary data, and loads or merges this data into a new installation of Ultra Search. This approach might require more disk space, but it offers the following benefits:

With the ETL approach, data migration involves the following five steps:

  1. Install the new system (for example, 9.0.3) in a new ORACLE_HOME
  2. Re-create user instance schemas and related database objects
  3. Re-create user instances
  4. Restore data
  5. Rebuild index

The first two steps in the ETL approach must be done manually:

Use the SQL script wk0migrate.sql to run the ETL migration steps three and four. The script is located in the %ULTRASEARCH_HOME%/admin/ directory. It requires the following input parameters:

The fifth step requires the system administrator to re-activate all crawling schedules through the Ultra Search administration tool.


Note:

The upgrade script does not roll back the Ultra Search system to the old version if an unexpected error occurs, such as a power failure or system failure. For in-place migration, back up the database before starting migration. For ETL migration, because all previous data is kept, you can switch back to the previous (for example, 9.0.1) system


Ultra Search Migration Logs

The upgrade script provides log files to show which actions the migration has taken. The upgrade script writes the following contents to the log file:

For in-place migration, the wk0upgrade.sql script writes the execution logs to the file wk0upgrade.log in the %ULTRASEARCH_HOME%/admin/ directory.

For ETL migration, the wk0migrate.sql script writes the execution logs to the file wk0migrate.log in the %ULTRASEARCH_HOME%/admin/ directory.

Upgrade from Ultra Search 9.0.2 to 9.0.3

To upgrade Ultra Search 9.0.2 to 9.0.3, perform the following steps:

  1. Launch Oracle Portal Configuration Assistant (OPCA) in 'ALL' mode, and it invokes Ultra Search Configuration Assistant (USCA). USCA checks the Ultra Search release in the existing database.
  2. If USCA detects that the Ultra Search release is 9.0.2, then it asks you to select one of the following three options:
    • Deinstall/reinstall
    • Upgrade to 9.0.3
    • Abort
  3. Select the option "Upgrade to 9.0.3". USCA upgrades the existing Ultra Search server components to release 9.0.3 automatically. There is no manual step required, and all data collected with the existing Ultra Search remains intact.
  4. After USCA finishes the upgrade, the Ultra Search 9.0.3 server components are installed into the existing database.

Upgrade from Ultra Search 9.2 to 9.0.3

Because Ultra Search 9.2 uses the same database schema as Ultra Search 9.0.2, the upgrade procedure is the same.

See Also:

"Upgrade from Ultra Search 9.0.2 to 9.0.3"