Oracle Ultra Search User's Guide Release 9.0.3 Part Number B10043-01 |
|
|
View PDF |
This chapter contains the following topics:
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:
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.
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#
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.
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';
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 |
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.
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 |
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:
|
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.
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>;
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';
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. |
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.
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 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.
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.
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');
If necessary, alter an instance stoplist after initial crawling with one of the following methods:
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');
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.
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 |
To upgrade Ultra Search 1.0.3 to 9.0.3, perform the following 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.
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.
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:
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:
SYSPW
: password of the user SYS
WKSYSPW
: password of the user WKSYS
HOST
: database host machinePORT
: database port numberORACLE_SID
: database SIDWK_TABLESPACE
: tablespace for Ultra SearchWK_TEMPTABLESPACE
: temporary tablespaceCONN_STRING
: database connect stringORACLE_HOME
: the path of Oracle homeJAVA_EXE_PATH
: Java executable file pathPATH_SEPARATOR
: Java classpath separator; use ':' for UNIX or ';' for Windows NTThe sixth step requires the system administrator to re-activate all crawling schedules through the Ultra Search administration tool.
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:
ORACLE_HOME
The first two steps in the ETL approach must be done manually:
ORACLE_HOME
, either on the same machine or on a different machine. If the new 9.0.3 system is installed in the same machine as the old 9.0.1 system, then the database listener port number should be configured to a different number than the old 9.0.1 database. This lets both the old and the new database run at the same time.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:
WKSYSPW
: password of the user WKSYS
CONN_STRING
: database connect stringSRC_WKSYSPW
: password of the source database (9.0.1 database) user WKSYS
SRC_CONN_STRING
: source database connect stringThe fifth step requires the system administrator to re-activate all crawling schedules through the Ultra Search administration tool.
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.
To upgrade Ultra Search 9.0.2 to 9.0.3, perform the following steps:
Because Ultra Search 9.2 uses the same database schema as Ultra Search 9.0.2, the upgrade procedure is the same.