5 Creating a TMS Database

This chapter describes how to create a new Oracle database for use with Oracle Thesaurus Management System (TMS).

Check My Oracle Support for an updated version of the TMS release notes (Article ID 859690.1), which may contain updated installation instructions.

Your Oracle Database 11g installation must be complete before you can install the TMS database. In addition, you must first install the TMS database server code in the application tier; see Installing the TMS Database Server Code.

This chapter includes the following topics:

Reviewing Database Requirements and Recommendations
Installing TMS Database Objects
Loading Reports
Registering Databases Integrated with Oracle Clinical
Creating a TMS Administrator-Level User Account on the Database

Reviewing Database Requirements and Recommendations

Before you install the Oracle TMS database component, review the requirements and recommendations — such as SID names, tablespace sizes, memory management, and initialization parameters — listed in this section.

Note:

Repeat these instructions for each new database you create.

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 4.6.1 on an existing database instance if it is configured according to the instructions in this book for UNIX or Windows.

Use Lowercase SID Name with Oracle Clinical

If you are integrating your TMS environment with Oracle Clinical, ensure that the SID name uses lowercase letters only. SIDs with uppercase letters cause conflicts in the Oracle Clinical Data Extract module.

Check Required Tablespaces

Table 5-1 lists the required tablespaces, along with their minimum size. 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 5-1 Required Tablespaces and Sizes

Tablespace	Minimum Size
SYSTEM	900 MB
TEMP	100 MB
UNDOTBS1	700 MB
USERS	500 MB
SYSAUX	500 MB

Select Required Components

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

Oracle Text
Oracle JVM
Oracle XML DB

Use the Database Configuration Assistant

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

Note:

The Database Configuration Assistant prompts you to "Keep the enhanced 11g default security settings (recommended)." Information on security settings is included in the Oracle Database 2 Day + Security Guide 11g Release 1 (11.1).

Use Automatic Memory Management

Oracle recommends that you use Oracle Database 11g's Automatic Memory Management feature for a new or an upgraded TMS database.

Set Initialization Parameters

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

The memory-related recommendations listed in Table 5-2 are based on the requirements of approximately 50 to 60 concurrent users. If you expect to have more or fewer concurrent users, calculate and adjust these settings accordingly. See the Oracle Database Reference for more information. However, regardless of your calculation result, Oracle recommends a minimum setting of 600 MB for MEMORY_TARGET and 400 MB for SGA_TARGET.

Tip:

Table 5-2 arranges the parameters in alphabetical order. In the 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 5-2 Required and Recommended init.ora Parameter Settings

Parameter	Value	Comments
COMPATIBLE	11.1.0.7.0	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	company.com	Make this value the same as your company domain name.
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.
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	9.2.0	Acts as an umbrella for enabling a series of optimizer features based on an Oracle release number. TMS uses the optimizing features of Oracle9i.
OPTIMIZER_MODE	CHOOSE	If you run Oracle Clinical's statistics-gathering scripts, the CHOOSE value sets Oracle9i's Optimizer to apply the execution plan that best minimizes response time. See the Oracle9i Concepts Guide and the Oracle9i Tuning Guide for more information. (CHOOSE is the default value when you specify 9.2.0 as the value of OPTIMIZER_FEATURES_ENABLE.)
OS_AUTHENT_PREFIX	"OPS$"	Enter the double quotes symbol (").
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	TRUE	If you intend to use Oracle Clinical and have SAS on a different computer, DX SAS jobs fail unless you set this parameter to TRUE. However, REMOTE_OS_AUTHENT is an obsolete parameter. When you start up a database that has this setting, Oracle Clinical 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	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 a TMS environment, you do not have to set this parameter. However, if you share this environment with Oracle Clinical or Oracle AERS, you must specify entries to support Oracle Clinical PDF layout generation and Oracle AERS. For Windows environments, samples of the valid syntax are as follows: `UTL_FILE_DIR=c:\e2b\import` `UTL_FILE_DIR=c:\opapps\xmltemp` For UNIX environments, 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`

Modify tnsnames.ora

Add an entry to the tnsnames.ora file for the database. Add the tnsnames entry to file tnsnames.ora on any existing Oracle Thesaurus Management System Forms Servers or Reports Servers. The tnsnames.ora entry must match the Oracle SID. In addition, if you are installing TMS with Oracle Clinical, the tnsnames.ora entry must be in lowercase.

Installing TMS Database Objects

Do the following tasks in this order:

Unlock Accounts

Start the Installer

Attend to the Installer

Log in to the Forms Server computer and follow these instructions to add TMS database objects to each Oracle database that you will use for TMS.

The TMS Installer creates a log file (tmsinst_database_name.log) for each database installation you perform, saving it under the install directory.

Unlock Accounts

The Installer prompts you for passwords to several system accounts. In Oracle 11g, 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, XDB, SYS.

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

Start the Installer

To begin the installation:

Log in to the server computer using an account with system administrator privileges.
Insert Disk V17174-01 of the Oracle Clinical 4.6 and Oracle Thesaurus Management System 4.6.1 Media Pack.
Locate and execute the setup.exe file:

tms\install\setup.exe

The Installer opens to the Welcome screen.
Follow the instructions on the installation screens. For additional information on each screen, see Attend to the Installer.

Attend to the Installer

Attend to the Installer screens:

Welcome

Click Next.

Specify File Locations

Enter values:

Source By default, the Installer displays the location in which you extracted the TMS patch. Accept this default value.

Destination Enter values for the Oracle Home you created when you installed Oracle AS10gR2. If the computer has multiple Oracle Homes, the Installer displays them all in the drop-down lists.

Name: Select the name of the correct Oracle Home; for example, FRHome1.
Path: Browse for the path to the correct Oracle Home; for example, D:\Oracle\FRHome1.

Click Next.

Select a Product to Install

Select TMS Database Install and click Next.

Select Installation Type

Select Master and click Next.

TMS Server Code Home Directory

Accept the default value for the directory path of the server code. Click Next.

Enter database connect string

Enter the database name. An alias must exist in tnsnames.ora for this database.

Note:

The Installer sets the LOCAL registry setting to default all connections to this database.

NLS Settings

NLS_LANG Oracle recommends the setting: AMERICAN_AMERICA.UTF8

See Choosing a Character Set.

NLS_DATE_FORMAT Enter a valid date value. A four-digit year is required. Oracle recommends DD-MON-RRRR.

Click Next.

Warning: Stop PSUB process

If you are integrating TMS with Oracle Clinical, stop the PSUB process on the database at this point, restarting it only after you have completely installed TMS.

Directory for data tablespace datafiles

Enter the directory for TMS data tablespace files in the appropriate syntax for your operating system, including a trailing slash. Use the same database name you entered as the connect string. For example:

UNIX: /u01/oradata/dbname/

Windows: Drive:\oradata\dbname\

Directory for index tablespace datafiles

Enter the directory for TMS index tablespace files in the appropriate syntax for your operating system, including a trailing slash. The Installer enters the path you entered for tablespace datafiles as the default.

Enter Password for schema

In a series of Installer screens, enter the passwords for SYS, SYSTEM, CTXSYS, OPA, RXC, TMS, TDX, BC4J_INTERNAL, OPS$TMS_BROWSER. You will need these passwords later.

Ignore Tablespace Creation Errors

You may want to ignore tablespace creation errors if you know that the necessary tablespaces already exist. Select Yes to ignore the errors; select No to view them if they occur.

Information

This screen informs you that the installer will start a SQL*Plus session during the installation and provides the location of the log file.

Summary

The Installer displays information about the installation, including space requirements and space available. Click Install.

Loading Reports

If you installed TMS in the database using the full Oracle AS10gR2 installation then you can skip this manual procedure. You may verify the reports have been loaded successfully.

If you have installed TMS in the database using the Oracle AS10gR2 Forms and Reports Services, you must perform this manual procedure:

Transfer directory OPA_HOME\tms\install\reports to the database server.
Point to the database by setting the environment variables.
Change directories to the directory above the report folder.
Load the reports by entering the following command:

sqlldr opa/opa_password control=reports/tmsreports.ctl log=reports/tmsreports_database.log

Registering Databases Integrated with Oracle Clinical

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

Start the Installer

To begin the installation:

Log in to the server computer using an account with system administrator privileges.
Insert Disk V17174-01 of the Oracle Clinical 4.6 and Oracle Thesaurus Management System 4.6.1 Media Pack.
Locate and execute the setup.exe file:

tms\install\setup.exe

The Installer opens to the Welcome screen.
Follow the instructions on the installation screens. For additional information on each screen, see Attend to the Installer.

Attend to the Installer

Attend to the Installer screens:

Welcome

Click Next.

Specify File Locations

Enter values:

Source By default, the Installer displays the location in which you extracted the TMS patch. Accept this default value.

Destination Enter values for the Oracle Home you created when you installed Oracle AS10gR2. If the computer has multiple Oracle Homes, the Installer displays them all in the drop-down lists.

Name: Select the name of the correct Oracle Home; for example, FRHome1.
Path: Browse for the path to the correct Oracle Home; for example, D:\Oracle\FRHome1.

Click Next.

Select a Product to Install

Select TMS Database Registration and click Next.

TMS Server Code Home Directory

Accept the default value for the directory path of the server code; for example, d:\opapps46\tms. Click Next.

Enter Password for schema

In a series of Installer screens, enter the passwords for SYSTEM, RXC, TMS. You will need these passwords later.

Enter the global name of TMS database

Enter the global name of the TMS database; for example, tms461.us.oracle.com. A SQL*Net TNS alias must exist with the same name; see Setting Up SQL*Net Connections to TMS Databases.

To find the global name, open a SQL*Plus session and enter:

select * from global_name

Information

This screen informs you that the installer will start a SQL*Plus session during the installation and provides the location of the log file.

Summary

The Installer displays information about the installation, including space requirements and space available. Click Install.

Creating a TMS Administrator-Level User Account on the Database

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.

Note:

If the person already has an Oracle Clinical OPS$ user account in the same database, perform step 4 only.

To create a new Oracle account for a user:

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

OPA_HOME\tms\install\tmsadduser.sql
Enter a user ID. If the user account also accesses Oracle Clinical, enter the string OPS$ at the beginning of the user ID.
Enter a password for this user. Do not use the identified externally clause; explicitly assign a password.
Grant the user the OPA_ADMIN role:

grant OPA_ADMIN to user_id