4 Upgrading and Creating Databases for Oracle WebCenter Interaction

This chapter describes how to upgrade the portal database and create the new database necessary for the Tagging Engine.

It includes the following sections:

• Upgrading the Portal Database

• Running the Database Upgrade Tool

• Creating and Configuring the Tagging Engine Database

• Creating and Configuring the ALUI Security Database

Note:

Perform the steps in the order presented. If you run the Database Upgrade Tool before running the upgrade scripts, you receive a password seed error when you run the scripts.

Upgrading the Portal Database

This section describes how to upgrade the portal database. It includes the following sections:

• Upgrading the Portal Database on Oracle Database for Microsoft Windows

• Upgrading the Portal Database on Microsoft SQL Server

Upgrading the Portal Database on Oracle Database for Microsoft Windows

This section describes how to upgrade the Oracle WebCenter Interaction portal database on Oracle Database for Microsoft Windows.

Notes:

• When running Oracle WebCenter Interaction with Oracle Database 11g with the provided initPLUM10.ora file, make the following modification: change compatible = 10.2.0.0.0 to compatible = 11.0.0.

• To prevent problems with “group by” optimizations when using Oracle WebCenter Interaction with Oracle Database 11g you must add the following configuration to the bottom of your init$ORACLE_SID.ora file: _optimizer_group_by_placement=false.

1. Verify that the Oracle environment variables are properly set.

   For details, see Setting Oracle Environment Variables.

2. Copy the SQL scripts from the Oracle WebCenter Interaction installation directory to your Oracle server.

   • For Oracle9i, the Oracle WebCenter Interaction installer creates the SQL scripts in the following directories:

     install_dir\ptportal\10.3.3\sql\oracle_nt9.2
     

   • For Oracle Database 10g, the Oracle WebCenter Interaction installer creates the SQL scripts in the following directories:

     install_dir\ptportal\10.3.3\sql\oracle_nt10
     

   • For Oracle Database 11g, the Oracle WebCenter Interaction installer creates the SQL scripts in the following directories:

     install_dir\ptportal\10.3.3\sql\oracle_nt11
     

3. Run the upgrade10.3.0to10.3.3_oracle.sql script to upgrade the database.

4. Start the Oracle Listener for the portal database.

Upgrading the Portal Database on Microsoft SQL Server

This section describes how to upgrade the Oracle WebCenter Interaction portal database on Microsoft SQL Server.

1. Log in to the portal database host computer as the sa user so that the tables are owned by dbo.

2. Run the upgrade10.3.0to10.3.3_mssql.sql script to upgrade the database. The script is located in install_dir\ptportal\10.3.3\sql\mssql.

Running the Database Upgrade Tool

Note:

Run the Database Upgrade Tool only after running the upgrade scripts.

The command line Database Upgrade Tool is used to upgrade a portal database to 10.3.3 specifications. You must run the tool twice. The first time you run the Database Upgrade Tool, it creates a text file to hold the data required by the upgrade tool. You edit the file (entering parameters such as the location of various files), then run the upgrade tool again to perform the upgrade.

Note:

Your system must be properly configured to run the portal in order to use this application, as it relies on your portal configuration to know how to connect to the database and complete the upgrade.

1. Run the Database Upgrade Tool from $PORTAL_HOME\bin\dbupgradetool.bat.

   If you have a .NET portal, a GUI upgrade utility will be launched. If you have a Java portal, a command line utility will be launched.

2. Provide the Admin user name and password in the fields in the GUI tool or as parameters in the command line utility:

   • Admin User Name - type the name of the Administrator user that you created when you installed your 10.3.0.1 portal (not another user in the Administrators group). The default name is “Administrator,” but you may have changed the name for security purposes after installation.

     Note:

     The Admin User Name is case sensitive.

   • Password - type the password for the Administrator user. If this user has an empty password, do not type anything.

   The upgrade tool creates the upgradedata.properties file in install_dir\settings\portal.

3. When prompted to edit the property settings (in the GUI tool) or the properties file (in the command line utility), you can skip this step. There is no new data required for this release.

4. Run the Database Upgrade Tool again to begin upgrading the database. The upgrade can run for a few seconds or a few hours, depending on the size of your database. If the Database Upgrade Tool encounters errors or data inconsistencies, it does not stop. Instead it logs the errors to the file specified in Step 3.

5. When the database upgrade completes, you are notified of the status. If the upgrade completed successfully (without errors), skip to Step 7.

   Note:

   If PTGROUPMEMBERSHIP has a materialized view you may see an error regarding inability to drop a view. This can be ignored.

6. If there were errors, you should examine the log file, identify solutions, restore the database to its previous state, fix the problems, and re-run the Database Upgrade Tool.

   Note:

   You must restore the database to its original 10.3.0.1 state before you re-run the Database Upgrade Tool. The Database Upgrade Tool may modify the database even if it fails. Therefore, even if the upgrade did not complete successfully, the database is at least partially upgraded to 10.3.3 specifications.

7. If you changed the database credentials, change them back to use the Oracle WebCenter Interaction database user.

Creating and Configuring the Tagging Engine Database

This section describes how to create and configure a database for the Tagging Engine. It includes the following sections:

• Creating and Configuring the Tagging Engine Database on Microsoft SQL Server

• Creating and Configuring the Tagging Engine Database on Oracle Database

You only must perform this procedure if you installed the Tagging Engine.

Creating and Configuring the Tagging Engine Database on Microsoft SQL Server

To create the Tagging Engine database on Microsoft SQL Server:

1. Copy the scripts from install_dir\pathways\10.3.3\sql\mssql to the database host computer.

2. In SQL Server Management Studio, access the database engine's properties.

3. Configure the database engine to use SQL Server and Windows Authentication mode.

4. Restart the database engine.

5. Create the Tagging Engine database user, configuring the Tagging Engine database user to use SQL Server Authentication.

6. Create the Tagging Engine database.

   Configure the size of the Tagging Engine database. The growth of the database is directly correlated to the number of objects present in the system. Objects include such things as tags, user preferences, and saved searches. Estimate 10 MB of growth per 100,000 objects. For example, if the Tagging Engine database stores roughly 100,000 new objects per day, you should anticipate growth of 3.65 GB per year.

7. Change the default database for the Tagging Engine database user to the Tagging Engine database.

8. Grant the Tagging Engine database user the db_owner role for the Tagging Engine database.

9. As the Tagging Engine database user, run the create_pathways_schema.sql script on the Tagging Engine database.

10. Run the install_pathways_seeddata.sql script on the Tagging Engine database.

Creating and Configuring the Tagging Engine Database on Oracle Database

To create and configure the Tagging Engine database on Oracle Database:

1. Copy the oracle directory from install_dir\pathways\10.3.3\sql\oracle\windows to the Tagging Engine database's host computer. This folder includes the scripts that you will use to set up and configure the Tagging Engine database.

2. Log on to the host computer for the Tagging Engine database as owner of the Oracle system files.

3. Execute the following steps as the system user in your Oracle Database:

   1. Run the script create_pathways_tablespaces.sql for your platform. This file is located in a platform specific subdirectory within the oracle directory that you copied in step 1.

      Note:

      Before running the script, determine the name of the SID used in your portal database. If necessary, edit the script so that all SID name instances in the script match the SID name used for the portal database.

   2. Run the script create_pathways_user.sql.

4. Execute the following steps as the Tagging Engine user that you just created:

   1. Run the script create_pathways_schema.sql. This script creates all of the tables and indexes that are necessary to run the Tagging Engine. The create_pathways_schema.sql script is located in the oracle directory that you copied in step 1.

   2. Run the script install_pathways_seeddata.sql. This script adds all of the initial seed data that are necessary to run the Tagging Engine. The install_pathways_seeddata.sql script is located in the oracle directory that you copied in step 1.

5. Run your database's analysis tool on the portal database to increase the efficiency of the database.

Creating and Configuring the ALUI Security Database

This section describes how to set up the ALUI Security database. It includes the following sections:

• Creating and Configuring the ALUI Security Database on Microsoft SQL Server

• Creating and Configuring the ALUI Security Database on Oracle Database

Note:

You do not need to perform this procedure if Oracle WebCenter Analytics is installed. Installing Oracle WebCenter Analytics requires creating the ALUI Security database.

Creating and Configuring the ALUI Security Database on Microsoft SQL Server

This section describes how to create and configure the ALUI Security database on Microsoft SQL Server.

1. On the computer on which you installed the Tagging Engine, copy the scripts from install_dir\pathways\10.3.3\sql\mssql to the ALUI Security database host computer.

   These scripts include the script that you will use to configure the ALUI Security database.

2. In SQL Server Management Studio, access the database engine's properties.

3. Configure the database engine to use SQL Server and Windows Authentication mode.

4. Restart the database engine.

5. Create the ALUI Security database user:

   1. Create the ALUI Security database user.

   2. Configure the ALUI Security database user to use SQL Server Authentication.

   3. Set the ALUI Security database user password to the password you designated when you planned your deployment.

6. Create the ALUI Security database.

7. Change the default database for the ALUI Security database user to the ALUI Security database.

8. Grant the ALUI Security database user the db_owner role for the ALUI Security database.

9. Create the ALUI Security database schema. Specify the ALUI Security database user as the schema owner.

10. Grant the ALUI Security database user the sysadmin server role.

11. Connect to the ALUI Security database as the ALUI Security database user, using SQL Server Authentication.

12. Run the create_security_tables.sql script, located in the folder that you copied in step 1.

Creating and Configuring the ALUI Security Database on Oracle Database

This section describes how to create and configure the ALUI Security database on Oracle Database.

1. On the computer on which you installed the Tagging Engine, copy the oracle directory from install_dir\pathways\10.3.3\sql\oracle\windows to the ALUI Security database's host computer.

   This directory contains the script that you will use to configure the ALUI Security database.

2. Log on to the host computer for the ALUI Security database as owner of the Oracle system files.

3. Create the ALUI Security database tablespace.

4. Create the ALUI Security database user.

5. Connect to the ALUI Security database as the ALUI Security database user.

6. Run the create_security_tables.sql script, located in the folder that you copied in step 1.

7. Run your database's analysis tool on the ALUI Security database to the efficiency of the database.