Skip Headers
Oracle® Thesaurus Management System Installation Guide
Release 5.1

Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

7 Upgrading Oracle Thesaurus Management System to Release 5.1

This chapter describes the recommended approach to upgrading an existing Oracle Thesaurus Management System installation to Release 5.1. Supported upgrade paths include:


If you are upgrading from Release 4.6 or earlier you must upgrade to Release 4.6.2 first. You must open a Service Request (SR) on My Oracle Support at to download the Release 4.6.2 media.

A revised version of the TMS 4.6.2 Installation Guide is posted at

Be sure you are using the latest documentation:

Review Chapter 1, "Preparing to Install Oracle Thesaurus Management System" for system requirements and planning information.

This chapter includes:

Critical Patch Updates See Section 1.8, "Applying Oracle Critical Patch Updates."

Oracle Database  includes many new features including multitenancy, which allows you to create one or more pluggable databases (PDBs) contained in a single root or container database (CDB). You can install Oracle Thesaurus Management System on either a pluggable database or a non-CDB database (as in 11g). For more information on multitenancy, see

For information about upgrading and migrating your existing database, see "Upgrading to Oracle Database 12c (" at

For more information about new features see Oracle White Paper Plug into the Cloud with Oracle Database 12c at and, for more details, the Oracle® Database New Features Guide 12c Release 1 (12.1) at

7.1 Upgrade Oracle Clinical (If Integrated with Oracle Clinical)

If you are upgrading both Oracle Clinical and TMS in an integrated installation, upgrade Oracle Clinical first. See the Oracle Clinical Installation Guide.

7.2 Install Oracle Database

You must install Oracle Database 12c Release 1 ( You cannot upgrade your existing Oracle Database installation.


If you are using TMS with DMW or LSH 2.4.x, do not install Oracle Database 12c. You must remain on Oracle Database 11g.

7.2.1 Installing the Oracle Database Server on UNIX

To install on UNIX, follow instructions in Chapter 2, "Installing Oracle Database on UNIX"

7.2.2 Installing the Oracle Database Server on Windows

To install on Windows, follow instructions in Chapter 3, "Installing Oracle Database on Windows".

7.3 Install the TMS Database Server Code

Follow instructions in Section 4.2, "Installing the TMS Database Server Code".

7.4 Upgrade a Single or Master TMS Database

Follow the instructions in this section when you are upgrading a single TMS database or, if you have a distributed environment, to upgrade the master TMS database. You must perform the tasks described in previous sections first.


Even if you have Oracle Clinical integrated with TMS, re-registering databases is not required during an upgrade.

7.4.1 Suspend Symmetric Replication (Distributed Environment Only)

If TMS is running in a distributed environment, suspend symmetric replication and drop the replication group before you patch Oracle Database or the TMS databases by following the instructions in this section. Use the suspend scripts that are in your TMS server code area.

Table 7-1 Symmetric Replication Suspend Scripts and Instructions

Run Script Where? For Where? Parameters Purpose and Comments


Master instance

Master instance


Purpose: Suspends replication activity for all sites.

Comment: Wait until the dba_repgroup view has a status of QUIESCED on the master instance and all slave instances.


Master instance

Master instance


Purpose: Drops replication group for all sites.

Comment: Wait until the dba_repcatlog view is empty on the master instance and on all slave instances.

To suspend replication:

  1. Log in to the server using an account with system administrator privileges.

  2. Open a MS-DOS command window.

  3. Change to the OPA_HOME directory where the install scripts are located, for example:

    cd c:\opapps51\tms\51\install
  4. Set the local name for your master site:

    set local=Master_Site


    Ensure that you explicitly set the local name so that the system does not use the Oracle local registry variable by mistake. For this reason, it is best to run the upgrade scripts from the Command window, rather than from SQL*Plus.
  5. Issue these commands:

    sqlplus repsys/password
    start tmsmupgsrsuspend
  6. Unschedule the push operation from each slave site to the master site and from the master to each slave. Unschedule as many jobs as necessary to ensure that no scheduled push jobs remain.

    Connect to each site as REPSYS and issue this command:

    exec dbms_defer_sys.unschedule_push('remote_site');
  7. Connect as REPSYS at each slave site and run the following script. On each updateable snapshot site (slave site), this script drops the TMS Snapshot Replication and Refresh Groups.

    start tmsssrdrop
  8. Connect as TMS at each slave site and run the following script. This step drops the TMS replicated tables on all sites.

    start tmsssrdroptables
  9. Connect as TMS at the master site and run the following script, which drops the TMS Snapshot Logs at the master site.

    start tmsmsrdropmvlog
  10. Connect as REPSYS on the master site and issue the following command, which drops the TMS Master Replication Group.

    start tmsmupgsrdrop


    You must follow steps in Section 7.4 to upgrade the master database before proceeding to the next step.

7.4.2 Prevent Access to Oracle Thesaurus Management System Databases

You must ensure that no data entry is performed, and no jobs that update data (such as batch validation) run during the upgrade process.

To prevent users from accessing the data, place the database in restricted mode. Provide restricted session access to the following accounts:


  • SYS

  • OPA

  • RXC

  • TMS




  • TDX

After you complete the upgrade, remove the restricted access from the databases and user accounts.

7.4.3 Upgrade to Oracle Database

Oracle recommends backing up your database(s) at this point.

Do one of the following:


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.

The Oracle Thesaurus Management System Installer no longer works if you set up connections using the SID.


If you are installing the Oracle Thesaurus Management System database on a pluggable Oracle 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. See "Finding Information on My Oracle Support". Upgrade in Place

  1. Install Oracle Database binaries in the Oracle_Home. See Section 4.3, "Review Database Requirements and Recommendations (All Databases)."

  2. To use one or more pluggable databases, create an Oracle Database Container Database (CDB). (If you are upgrading multiple databases, you still need only one CDB.)

    Update the listener and tnsnames.ora either before or after the next step.

  3. Upgrade your database to Oracle Database using the Database Upgrade Assistant (DBUA) following Method 1 described in "Upgrading to Oracle Database 12c (" at Clone Your Database

  1. Install Oracle Database binaries in the Oracle_Home. See Section 4.3, "Review Database Requirements and Recommendations (All Databases)."

  2. To use one or more pluggable databases, create an Oracle Database Container Database (CDB). (If you are upgrading multiple databases, you still need only one CDB.)

    Update the listener and tnsnames.ora either before or after the next step.

  3. Clone your database. See Oracle White Paper Cloning Oracle Clinical and TMS Databases, article ID 883213.1 on My Oracle Support. See "Finding Information on My Oracle Support".

7.4.4 Gather Required Information

Before you start the Installer, be sure you have the information it prompts for:

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


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

  3. Database Connect String: Enter the database name. It must be a valid TNS entry in your tnsnames.ora file.

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

    • American_America.UTF8


  5. 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\

  6. Enter and confirm passwords for the following accounts:

    • SYS

    • SYSTEM

    • RXC

    • OPA

    • TMS

    • CTXSYS

7.4.5 Stop 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.

7.4.6 Start the Installer

To upgrade a TMS database:

  1. Log in to the application 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.4, "Downloading and Extracting the Software").

  3. Run the following file as an administrator:


    The Installer opens to the Welcome screen.


    See Section 1.5, "Using the Silent Installer" for instructions for running the Installer as a file with pre-entered parameter values.


    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 Upgrade

  5. What type of installation do you want?

    • Select Master if you are planning to use only one database or if you have a distributed environment (using data replication) and this is the master database.

    • Select Slave only if you have a distributed environment and this is a slave, or local, database.

7.4.7 Attend to the TMS Database Upgrade Installer Screens

The Oracle Universal Installer guides you through the database upgrade. Provide the information you assembled in "Gather Required Information."

Ignore tablespace creation errors:

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

  • Select No if the TMS tablespaces do not exist.

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

7.4.8 Set Initialization Parameters

After the upgrade completes, set the init.ora parameters according to the instructions in Section 4.3.7, "Set Initialization Parameters."

You must stop and then start the database to activate the changed init.ora parameters.


Do NOT SET the DB_DOMAIN parameter. In Release 5.1 and above this causes problems.

If you set up the EVENT parameter in the init.ora file to trace unique key constraints before upgrading, you should set the event parameter back to its required value.

7.4.9 Check Installation Log for Errors

When you upgrade, the installation log file may report the following error:

BEGIN tms_dsi_xml_schema.InstallSchemas; END  

ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents 
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 3
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 14 
ORA-06512: at "TMS.TMS_DSI_XML_SCHEMA", line 35 
ORA-06512: at line 1
finished TMSUPG 
TMS will now attempt to validate any invalidate objects... 

This error occurs because the tmsupg.sql upgrade script executes tms_dsi_xml_schema.InstallSchemas before attempting to validate any invalid objects. Execution of tms_dsi_xml_schema.InstallSchemas fails if the database has invalid objects.

If this error occurs, you need to:

  1. Run compile invalid until all database objects have been made valid.

  2. Run tms_dsi_xml_schema.InstallSchemas manually. See Section 7.4.10, "Configure DSI Import" for details.

7.4.10 Configure DSI Import

If you plan to use disconnected system integration (DSI), enter following commands:

set LOCAL=databasename
sqlplus tms/password
exec tms_dsi_xml_schema.InstallSchemas;

7.4.11 Load Dictionaries

If you need to upgrade any dictionaries or load new dictionaries and are installing a distributed environment, this is a good time to do it since you have suspended replication and will next do an export/import. However, you can load dictionaries at any time.

If you are installing TMS with AERS in a distributed environment, you must load the AERS-TMS dictionaries onto the master database before creating an export file and importing it to the slave databases.

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

7.4.12 Create an Export File (Distributed Environments Only)

If you are upgrading a distributed environment, follow instructions in Section 4.8.1, "Export Data from the Master Database."

7.5 Upgrade Slave TMS Databases (Distributed Environments Only)

Follow the instructions in this section to upgrade existing slave databases in a distributed environment.

  1. Follow these instructions:

    1. Section 7.4.1, "Suspend Symmetric Replication (Distributed Environment Only)"

    2. Section 7.4.3, "Upgrade to Oracle Database"

    3. Section 7.4.4, "Gather Required Information"

    4. Section 7.4.5, "Stop PSUB Process (If Integrated with Oracle Clinical)"

    5. Section 7.4.6, "Start the Installer"

    6. Section 7.4.7, "Attend to the TMS Database Upgrade Installer Screens"

    7. Section 7.4.8, "Set Initialization Parameters"

    8. Section 7.4.9, "Check Installation Log for Errors"

    9. Section 7.4.10, "Configure DSI Import"

  2. Import data from the master instance. Perform this task on the database server. At every slave database, import the export file you created in the previous task.

    See Section 4.8.2, "Import Data to the Slave Database" for more information.


    You must first create an export file of the master database; see Section 4.8.1, "Export Data from the Master Database."
  3. On the TMS application server, set the LOCAL variable and run the tmsupgslave.sql script by entering the following commands in a MS-DOS window:

    cd OPA_HOME\tms\51\install
    sqlplus system/password
    start tmsupgslave.sql

    The script generates a log file in the install directory named tmsupgslave_database_name.log.


    When upgrading a slave database to Release 5.1, the following types of errors occur in the tmsupg_database_name.sql log file:
    Warning: Package created with compilation errors. (and appended PLSQL errors with line numbers) 
    Warning: Package Body created with compilation errors. (and appended PLSQL errors with line numbers) ORA-00942: table or view does not exist 
    ORA-01775: looping chain of synonyms 
    ORA-04063: view "TMS.view_name" has errors 

    As long as there are no errors in the log file, tmsupgslave_database_name.sql, and there are no invalid OPA/TMS objects in the database after the upgrade, the upgrade can be considered successful and these errors can be ignored.

  4. Start symmetric replication, following instructions in Section, "Start Replication on the Master Database" and Section, "Start Replication on Each Slave Instance."

7.6 Upgrade Cloned Databases

If you have cloned a database, see Cloning Oracle Clinical and Oracle Thesaurus Management System Databases (Article ID 883213.1) on My Oracle Support:

Be sure to follow all the instructions in the document, including running the tmschown.sql script, the opachown.sql script, and, if you are using Oracle Clinical with TMS, the chown.sql script, before doing the upgrade.

7.7 Run Scripts to Gather Schema Statistics for the 11g Optimizer

After upgrading to Release 5.1 and setting initialization parameter optimizer_features_enable to (see Section 4.3.7, "Set Initialization Parameters"), you must gather statistics required for the Oracle 11g Optimizer to be effective for accounts used internally by TMS.

Scripts opastats.sql and tmsstats.sql are available for this purpose. Failure to execute these scripts can negatively impact performance.

If your database contains large amounts of data, the scripts may take a long time to run. You may want to edit the scripts. For information the scripts' parameters see documentation of the Oracle DBMS_STATS package that they call:

tmsstats.sql Since TMS already has a procedure, AnalyzeTables in tmspb_user_analyze.sql, that runs as part of every activation job, you may prefer to edit tmspb_user_analyze.sql if required and call it from tmsstats.sql to keep the two synchronized.

The tmsstats.sql script prompts for the password of the account it processes: TMS.

To change how TMS analyzes tables and ensure that tmsstats and AnalyzeTable remain in synch:

  1. Modify the lAnalyzeTable procedure in tmspb_user_analyze.sql.

  2. Edit tmsstats.sql to call tms_user_analyze.AnalyzeTable. Replace the section that analyzes tables with:

    exec tms_user_analyze.AnalyzeTables

  3. Run tmsstats.sql.

opastats.sql captures new statistics in the OPA application account used by TMS. It prompts for the OPA password. It is also used by Oracle Clinical and RDC Onsite.

7.8 Install or Upgrade the Application Tier Technology Stack

Whether you need to install or upgrade depends on what release you are upgrading from.

7.8.1 Upgrading from a Release Earlier than 5.1

If you are upgrading from a release prior to 5.1, you cannot upgrade the application tier. You must install it fresh because Release 5.1 and later use a different technology stack:

  • Oracle Application Server 11gR2, which includes the Oracle Forms Server and the Oracle Reports Server. Both are required for Oracle Thesaurus Management System.

  • WebLogic Server 11gR1 (10.3.6) and Oracle Application Developer Framework 11g R1. These are required for TMS Browser.

You can install all components on the same machine, or install on multiple machines, but you must install all components on all machines and then disable services on the machine(s) where they are not needed.

To ensure high availability of the Reports Server, you can set up and use more than one Reports Server on a subnet and they can access each other. For more information see the Oracle Clinical Administrator's Guide.

Follow all instructions in Chapter 5, "Installing and Configuring the Application Tier."

7.9 Install the TMS Front End and Reports Server

7.9.1 Install the Oracle Thesaurus Management System Front End and Reports Server

If you are upgrading from a release prior to 5.1, you cannot upgrade the application tier.

Follow instructions in Chapter 6, "Installing Oracle Thesaurus Management System Application Components."


If you have already installed the Oracle Clinical Reports Server on this machine, you do not need to install the TMS Reports Server.

The Installer prevents you from installing two Reports Servers with the same name.

7.9.2 Set Remote Method Invocation (RMI) JDBC Security to Secure

  1. Log in to the WebLogic Server

  2. Go to Summary of Servers, select OpaServer1, select General, and click Advanced.

  3. Change the RMI JDBC Security setting to Secure.

  4. Click Save.

  5. Click Activate Changes.

7.10 Redefine the TMS Load User

During the upgrade process, the tables accessed by the TMS load user are dropped and recreated, so the load user loses the grants required to load dictionary data.

To re-grant the required privileges:

  1. In TMS, navigate to Security, then Define Users.

  2. Query for the load user your company created.

  3. Uncheck LoadUser?.

  4. Save.

  5. Check Load User?.

  6. Save.