4 Creating a TMS Database

This chapter includes:

4.1 Install the TMS Database Server

The Oracle Thesaurus Management System Database Server code must be installed on Windows on the same computer as the TMS Application Tier. You must install the server first, then create one or more databases, and then install the Application Tier.

4.1.1 Gather Required Information

Make sure you have the information below, which is arranged in the order it is prompted for by the Installer during a fresh installation. The order for upgrades is somewhat different.

Note:

You must install the Oracle Thesaurus Management System database server before you install or upgrade the Oracle Thesaurus Management System database.

  1. OPA Home: the directory where Oracle Thesaurus Management System will be installed; by default:

    C:\opapps52

4.1.2 Start the TMS Server Installer

To install the TMS Server:

  1. Log in as a user with system administrator privileges.

  2. In the staging area, locate the directory where you downloaded and extracted Oracle Thesaurus Management System (see Section 1.6, "Download the Software").

  3. Run the following file as an administrator:

    Disk1\install\setup.exe

    The Installer opens to the Welcome screen.

    Note:

    See Section 1.7, "Use the Silent Installer (Optional)" for instructions for running the Installer as a file with pre-entered parameter values.

    Note:

    Although the Welcome screen also shows a button for deinstalling products, Oracle does not support using the Installer to deinstall Oracle Thesaurus Management System.

  4. In the Select a Product to Install page, select Oracle Thesaurus Management System Server 5.2.1.0.x.

4.1.3 Attend to the TMS Database Server Code Installation Screens

The Installer guides you through the installation and configuration of the TMS database server. Provide the information you assembled in "Gather Required Information."

4.2 Review Database Requirements and Recommendations

A TMS database can be installed on Windows, UNIX, Solaris, or Itanium. You must install the TMS Database Server (always on Windows) before installing a TMS database.

Before you install the TMS database component, review the following requirements and recommendations for each TMS database you plan to install.

4.2.1 Start with a New Database Instance

Oracle recommends that you set up a new database instance so that neither TMS nor its installation process interferes with other applications. However, you can install TMS on an existing database instance.

4.2.2 Accept Connections as a Service

Choose to configure the database to accept connections as a service instead of a SID. Service name requirements for Oracle Thesaurus Management System include:

  • The service name must be less than 15 characters long.

  • It must not include the domain.

  • It must be all lowercase.

Note:

If you are installing the Oracle Thesaurus Management System database on a pluggable Oracle 12.1.0.2 database (PDB), see My Oracle Support article ID 1910177.1, How To Configure TNS / SQLNET using the local_listener parameter for Pluggable Database In Oracle 12c, Allowing a SQLNET Connection to the PDB.

4.2.3 Check Required Tablespaces

Table 4-1 lists the tablespaces, along with their minimum size, required for TMS. Make sure the database contains these tablespaces. The best practice is to create them with the Autoextend On option, to avoid running out of space.

In addition, you may need to increase the minimum sizes for your installation.

Table 4-1 Required Tablespaces and Sizes

Tablespace Minimum Size

SYSTEM

900 MB

TEMP

100 MB

UNDOTBS1

700 MB

USERS

500 MB

SYSAUX

600 MB

4.2.4 Use the Database Configuration Assistant

To create a new database, use the Oracle Database Configuration Assistant. For instructions about the Database Configuration Assistant, see the Oracle Database 12c Release 1 (12.1.0.2) documentation, including online help and the Oracle Database 12.1.0.2 Installation Guide for the appropriate operating system.

Notes:

  • Use the Custom Database installation option.

  • The following parameters are not listed under All Initialization Parameters. Click Show Advanced Parameters to set the following parameters as described in Section 4.2.7, "Set Initialization Parameters":

    • Db_cache_size

    • Db_files

    • Java_pool_size

    • Job_queue_process

    • Large_pool_size

  • The Custom installation option allows access to the Database Storage screen, where you can expand the Tablespaces folder in the tree on the left side of the screen and edit the following tablespaces as required; see Section 4.2.3, "Check Required Tablespaces"

4.2.5 Select Required Components

When you create a TMS database, select the following mandatory components:

  • Oracle Text

  • Oracle JVM

  • Oracle XML DB

4.2.6 Use Automatic Memory Management

Oracle recommends that you use Oracle Database 12c Release 1 (12.1.0.2)'s Automatic Memory Management feature for a new or an upgraded TMS database.

4.2.7 Set Initialization Parameters

Table 4-2 lists the required and recommended initialization parameters in the init.ora file for TMS. For those parameters that accept a value from within a range, the values in the table are minimum values.

Tip:

Table 4-2 arranges the parameters in alphabetical order. In the Database Configuration Assistant, you can select the Parameter column to sequence the parameters in the same order.

Note:

If you make any changes to the initialization parameters, be sure to stop and restart the database in order to acquire the new parameter settings.

Table 4-2 Required and Recommended Initialization Values in the init.ora File

Parameter Value Comments

COMPATIBLE

12.1.0.2

Specifies the release with which the Oracle server must maintain compatibility.

DB_BLOCK_SIZE

16384 bytes

You cannot change this value after you create the database.

DB_CACHE_SIZE

150 MB

Recommended value for 50 to 60 concurrent users. Adjust this value according to your organization's needs.

DB_DOMAIN

null

DO NOT set this value. In Release 5.1 onward, setting this value causes problems.

DB_FILES

200

Oracle adds needed space to the control files up to the number specified in the DB_FILES parameter.

EVENT

31151 trace name context forever, level 0x100

Required for HTML generation.

NOTE: Do not include the EVENT parameter when you create the database. Once the database is created, you can add the EVENT parameter to the init.ora file.

JAVA_POOL_SIZE

50 MB

Recommended value for 50 to 60 concurrent users. You can change the value of this parameter after installation.

JOB_QUEUE_PROCESSES

10

Developer-specific parameter. You can change the value of this parameter after installation.

LARGE_POOL_SIZE

50 MB

Recommended value for 50 to 60 concurrent users.

MEMORY_MAX_TARGET

1000 MB (minimum)

Adjust this value according to your organization's needs.

MEMORY_TARGET

1000 MB (minimum)

Adjust this value according to your organization's needs.

NLS_DATE_FORMAT

DD-MON-RRRR (default value)

Determines the format in which client applications running on the Windows server transfer date information to and from the database. The format must specify the year as RRRR.

NLS_LENGTH_SEMANTICS

BYTE

The CHAR value for this parameter is not supported.

OPEN_CURSORS

800 or greater

You can change the value of this parameter after installation.

OPTIMIZER_FEATURES_ENABLE

12.1.0.2

Acts as an umbrella for enabling a series of optimizer features based on an Oracle release number.

NOTE: Oracle Thesaurus Management System 5.2.1 is certified on Oracle Database 11g optimizer features; see Section 6.6, "Run Scripts to Gather Schema Statistics for the 12c Optimizer."

OPTIMIZER_MODE

CHOOSE

If you run TMS's statistics-gathering scripts, the CHOOSE value sets Oracle Optimizer to apply the execution plan that best minimizes response time. See Oracle Database documentation for more information. (CHOOSE is the default value when you specify 11.2.0.4 as the value of OPTIMIZER_FEATURES_ENABLE.)

PGA_AGGREGATE_TARGET

200 MB

Recommended value for 50 to 60 concurrent users. You can change the value of this parameter after installation.

REMOTE_LOGIN_PASSWORDFILE

EXCLUSIVE

The database must be set up to use password file authentication.

REMOTE_OS_AUTHENT

FALSE

NOTE: Do not include the REMOTE_OS_AUTHENT parameter when you create the database. After the database is created, you can add the REMOTE_OS_AUTHENT parameter to the init.ora file.

REMOTE_OS_AUTHENT is an obsolete parameter. When you start up a database that has this setting, TMS displays the following warning:

ORA-32004: obsolete and/or deprecated parameter(s) specified. ORACLE instance started.

You can safely ignore this warning.

SEC_CASE_SENSITIVE_LOGON

FALSE

Lets you enter passwords without case sensitivity.

SESSIONS

500 or greater

You can change the value of this parameter after installation.

SGA_MAX_SIZE

600 MB (minimum)

Recommended value for 50 to 60 concurrent users. Adjust this value according to your organization's needs.

SGA_TARGET

600 MB (minimum)

Recommended value for 50 to 60 concurrent users. Adjust this value according to your organization's needs.

SHARED_POOL_SIZE

150 MB (minimum)

Recommended value for 50 to 60 concurrent users. You can change the value of this parameter after installation.

UNDO_MANAGEMENT

AUTO

Specifies which undo space management mode the system uses. When set to AUTO, the instance starts in Automatic Undo Management (AUM) mode.

UTL_FILE_DIR

opa_home\ xmltemp

Specifies each directory you access.

If this environment is exclusively an TMS environment, you do not have to set this parameter.

In a Windows environment, samples of the valid syntax are as follows:

UTL_FILE_DIR=c:\e2b\import

UTL_FILE_DIR=c:\opapps\xmltemp

In a UNIX environment, UTL_FILE_DIR requires an entry with two specified paths: one with and one without a trailing slash. Add these lines before any other UTL_FILE_DIR entries:

UTL_FILE_DIR=/usr/opapps/oc/xmltemp/

UTL_FILE_DIR=/usr/opapps/oc/xmltemp

4.3 Install TMS Database Objects

This section describes how to add TMS database objects to each Oracle database that you will use for TMS.

4.3.1 Unlock Accounts Before Installing the TMS Database

The Installer prompts you for passwords to several system accounts. In Oracle Database, some accounts are locked. Before you run the Installer, check these accounts. Unlock them if necessary. You will need to set their passwords when you run the Installer.

Make sure the following accounts are unlocked: SYSTEM, CTXSYS, and SYS.

When you have finished installing the database, relock any accounts that were locked except for SYSTEM and SYS, which should not be locked.

4.3.2 Gather Required Information

Before you start the installer, be sure you have the information it prompts for; see Section 4.3.5, "Attend to the TMS Database Installer Screens".

  1. Check that the value of the PATH system environment variable is set to database_client_home/bin;java_home\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32.

    For example, PATH=C:\app\gbuadmin\product\11.2.0\client_2\bin;C:\Java\jdk1.8.1_131\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32.

  2. Home Details: The full path to the OPA_HOME or similar application home name on your application server; by default:

    C:\opapps52

  3. Oracle Database Client Home: Full path to ORACLE_HOME was created when you installed the Oracle Database Client. (The Oracle Database Client Home is required for SQL Loader and LOADJAVA to work.)

    For example, C:\app\gbuadmin\product\12.1.0\client_2\bin.

  4. TMS Server Code Home Directory: The system detects the location and enters the value for you. By default, it is C:\opapps52\tms.

  5. Database Service name to connect: Enter the database name. It must be a valid TNS entry in your tnsnames.ora file.

  6. Database Details: Enter the database server name and database port number.

  7. NLS Settings: Enter the NLS settings for the database; see Section 1.8, "Choose a Character Set". Oracle strongly recommends that you use UTF8. The default values are:

    • American_America.UTF8

    • DD-MON_RRRR

  8. Directory for data tablespace data files: Enter the path to the directory on the database server where the data tablespace datafiles for the application should be created during the installation. The Installer does not validate the value and you must use the following syntax, including a trailing slash, depending on your operating system; for example:

    • UNIX: /u01/oradata/dbname/

    • Windows: drive:\oradata\dbname\

  9. Directory for index tablespace data files: Enter the path to the directory on the database server where the index tablespace datafiles for the application should be created during the installation. The Installer does not validate the value and you must use the following syntax, including a trailing slash, depending on your operating system; for example:

    • UNIX: /u01/oradata/dbname/

    • Windows: drive:\oradata\dbname\

  10. Enter and confirm passwords for the following accounts:

    • SYS

    • SYSTEM

    • CTXSYS

    • OPA

    • RXC

    • TMS

    • TMSPROXY

  11. Ignore tablespace creation errors:

    • Select Yes if the database already has the TMS tablespaces created.

    • Select No if the TMS tablespaces do not exist.

  12. The Installer gives you information that you should make a note of.

4.3.3 Stop the PSUB Process (If Integrated with Oracle Clinical)

If your TMS installation is integrated with Oracle Clinical, you must stop the PSUB process before upgrading the database. See the Oracle Clinical Administrator's Guide for instructions.

4.3.4 Start the Installer

To install a TMS database:

  1. Log in as a user with system administrator privileges in the application tier.

  2. In the staging area, locate the directory where you downloaded and extracted Oracle Thesaurus Management System (see Section 1.6, "Download the Software").

  3. Run the file as an administrator:

    Disk1\install\setup.exe

    The Installer opens to the Welcome screen.

    Note:

    See Section 1.7, "Use the Silent Installer (Optional)" for instructions for running the Installer as a file with pre-entered parameter values.

    Note:

    Although there is a button for deinstalling products on the Welcome screen, Oracle does not support using the Installer to deinstall Oracle Thesaurus Management System.

  4. In the Select a Product to Install page, select TMS Database Install 5.2.1.0.x.

4.3.5 Attend to the TMS Database Installer Screens

The Installer guides you through the installation and configuration of the TMS database.

4.3.6 Check the Database Log File

For each database installation you perform, the Installer creates the following log file in the INSTALL directory:

tmsinst_database_name.log

Always check the log file for status, messages, or errors.

4.4 Create a TMS Administrator User Account

This section describes creating an administrator-level TMS user, which is defined as a user with the OPA_ADMIN role. Users with this role have access to the Define Users window in TMS, which enables them to create additional users in the database.

To create a new Oracle account for a user:

  1. Connect to SQL*Plus as system and run the following script:

    C:\opapps52\tms\52\install\tmsadduser.sql

  2. Enter a user ID.

  3. Enter a password for this user. Do not use the identified externally clause; explicitly assign a password.

  4. Grant the OPA_ADMIN role to the user:

    grant OPA_ADMIN to user_id

4.5 Register Databases Integrated with Oracle Clinical

If you are installing TMS in the same database as Oracle Clinical, register the database by running the TMS Installer.

4.5.1 Gather Required Information

Before you start the installer, be sure you have the information it prompts for; see Section 4.5.3, "Attend to the TMS Database Registration Screens".

4.5.2 Start the Installer

To register your TMS database for use with Oracle Clinical:

  1. Log in to the application tier server as a user with system administrator privileges.

  2. In the staging area, locate the directory where you downloaded and extracted Oracle Thesaurus Management System (see Section 1.6, "Download the Software").

  3. Run the following file as an administrator:

    Disk1\install\setup.exe

    The Installer opens to the Welcome screen.

    Note:

    See Section 1.7, "Use the Silent Installer (Optional)" for instructions for running the Installer as a file with pre-entered parameter values.

    Note:

    Although there is a button for deinstalling products on the Welcome screen, Oracle does not support using the Installer to deinstall Oracle Thesaurus Management System.

4.5.3 Attend to the TMS Database Registration Screens

The Installer guides you through registering a TMS database.

  1. Product to install: TMS Database Registration 5.2.1.0.x.

  2. Home Details: The full path to the Oracle Home location on your application server; by default:

    C:\app\oracle\Middleware\Oracle_FRHome1

  3. TMS Server Code Home Directory: The system detects the location and enters the value for you. By default, it is C:\opapps52\tms\52.

  4. Enter and confirm passwords for the following accounts:

    • SYS

    • TMS

    • RXC

  5. Global name: Enter the global name of the TMS database; for example:

    service_name

    To find out the global name, log in to SQL*Plus and enter:

    select * from global_name

  6. Global name: Enter the global name of the OC database.

  7. The Installer gives you information that you should make a note of.

  8. When you are ready, install.

4.6 Load Dictionaries

See the Oracle Thesaurus Management System User's Guide for information about loading dictionaries.