4 Creating and Configuring Databases for Oracle WebCenter Interaction

This chapter describes how to create and configure the portal, Unitization Service, and Tagging Engine databases for Oracle WebCenter Interaction.

It includes the following sections:

• Creating and Configuring the Portal Database

• Creating and Configuring the Notification Service Database

• Creating and Configuring the Tagging Engine Database

Creating and Configuring the Portal Database

This section describes how to create and configure the portal database. It includes the following sections:

• Creating and Configuring the Portal Database on Oracle Database for Microsoft Windows

• Creating and Configuring the Portal Database on Microsoft SQL Server

Creating and Configuring the Portal Database on Oracle Database for Microsoft Windows

This section describes how to create and configure 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
     install_dir\aluidirectory\1.1\sql\oracle
     

   • 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
     install_dir\aluidirectory\1.1\sql\oracle
     

   • 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
     install_dir\aluidirectory\1.1\sql\oracle
     

3. Configure the portal database, tablespace, and user.

   • If you are creating a new Oracle9i database for the Oracle WebCenter Interaction schema, see Creating the Portal Database on Oracle9i for Microsoft Windows.

   • If you are creating a new Oracle Database 10g or 11g for the Oracle WebCenter Interaction schema, see Creating the Portal Database on Oracle Database 10g or 11g for Microsoft Windows.

   • If you are creating the Oracle WebCenter Interaction tablespace and schema within an existing Oracle database, see Creating the Portal Tablespace on Oracle Database for Microsoft Windows.

4. Create the portal schema and initialize the portal database.

   For details, see Creating the Portal Schema on Oracle Database for Microsoft Windows.

5. Start the Oracle Listener for the portal database.

Creating the Portal Database on Oracle9i for Microsoft Windows

This section describes how to create and configure the portal database, tablespace, and user on Oracle9i.

Note:

These steps create a new, dedicated portal database. If you are creating the portal tablespace within an existing database, see Creating the Portal Tablespace on Oracle Database for Microsoft Windows.

The following must be done before scripting the database:

• Log in to the portal database host computer.

• Verify that ORACLE_BASE, ORACLE_HOME, and ORACLE_SID are set appropriately. For details, see Setting Oracle Environment Variables.

• If this is a re-creation of a database or a retry of a prior failed attempt, delete the old database file.

1. Register the portal SID in the registry by running RegisterSIDPLUM.reg.

   RegisterSIDPLUM.reg is in the directory on the Oracle Database server to which you copied the scripts.

2. Create and configure the portal database:

   1. Create the PLUM directory under %ORACLE_BASE%\oradata.

   2. Copy initPLUM.ora to %ORACLE_HOME%\database.

3. Run CreateService.bat with the argument PLUMpassword.

4. Create the portal database instance:

   1. From %ORACLE_BASE%\admin\%ORACLE_SID%\plumtreescripts, start sqlplus using the /nolog parameter.

   2. Run the crdb1_oracle_nt.sql script to create and start the new database instance.

      This script should generate no errors. Output from the script is saved in the file crdb1.lst in the plumtree scripts directory. The database should now be running.

   3. Verify that the correct data files have been created.

      In %ORACLE_BASE%\database you should see the following:

      systPLUM.dbf
      undo1A.dbf
      temp1A.dbf (single disk installation only.)
      

5. Create the portal tablespace and user:

   1. Run the crdb2_oracle_nt.sql script to create tablespaces, create the portal database user, and perform low level database tuning.

      This script can take a significant amount of time to complete. The process may generate the following errors:

      ORA-00942 table or view does not exist
      ORA-1432/ORA-1434 public synonym to be dropped does not exist
      

      These errors are acceptable. Any other errors are not acceptable. Output from the script is saved in the file crdb1.lst in the plumtree scripts directory.

   2. Verify that the correct data files have been created.

      In %ORACLE_BASE%\database you should see the following:

      PLUMtbl1.dbf
      PLUMtmp1.dbf
      PLUMidx1.dbf (single disk installation only.)
      

Creating the Portal Database on Oracle Database 10g or 11g for Microsoft Windows

This section describes how to create and configure the portal database, tablespace, and user on Oracle Database 10g or 11g.

Note:

These steps create a new, dedicated portal database. If you are creating the portal tablespace within an existing database, see Creating the Portal Tablespace on Oracle Database for Microsoft Windows.

The following must be done before scripting the database:

• Log in to the portal database host computer.

• Verify that ORACLE_BASE, ORACLE_HOME, and ORACLE_SID are set appropriately. For details, see Setting Oracle Environment Variables.

• If this is a re-creation of a database or a retry of a prior failed attempt, delete the old database file.

1. Register the portal SID in the registry by running RegisterSIDPLUM10.reg.

   RegisterSIDPLUM10.reg is in the directory on the Oracle Database server to which you copied the scripts.

2. Create and configure the portal database:

   1. Create the PLUM directory under %ORACLE_BASE%\oradata.

   2. Copy initPLUM10.ora to %ORACLE_HOME%\database.

3. Run CreateService.bat with the argument PLUM10 password.

4. Create the portal database instance.

   1. From %ORACLE_BASE%\admin\%ORACLE_SID%\plumtreescripts, start sqlplus using the /nolog parameter.

   2. Run the crdb1_oracle_nt.sql script to create and start the new database instance.

      This script should generate no errors. Output from the script is saved in the file crdb1.lst in the plumtree scripts directory. The database should now be running.

   3. Verify that the correct data files have been created.

      In %ORACLE_BASE%\database you should see the following:

      systPLUM.dbf
      undo1A.dbf
      temp1A.dbf (single disk installation only.)
      

5. Create the portal tablespace and user:

   1. Run the crdb2_oracle_nt.sql script to create tablespaces, create the portal database user, and perform low level database tuning.

      This script can take a significant amount of time to complete. The process may generate the following errors:

      ORA-00942 table or view does not exist
      ORA-1432/ORA-1434 public synonym to be dropped does not exist
      

      These errors are acceptable. Any other errors are not acceptable. Output from the script is saved in the file crdb1.lst in the plumtree scripts directory.

   2. Verify that the correct data files have been created.

      In %ORACLE_BASE%\database you should see the following:

      PLUMtbl1.dbf
      PLUMtmp1.dbf
      PLUMidx1.dbf (single disk installation only.)
      

Creating the Portal Tablespace on Oracle Database for Microsoft Windows

Note:

These steps create the portal tablespace within an existing database. If you are creating a new, dedicated portal database, see Creating the Portal Schema on Oracle Database for Microsoft Windows.

• Log in to the portal database host computer as the owner of the Oracle system files.

• Verify that ORACLE_BASE, ORACLE_HOME, and ORACLE_SID are set appropriately. For details, see Setting Oracle Environment Variables.

1. Connect to your database as a user with sysdba rights.

2. Create the portal tablespace and DB user:

   1. From %ORACLE_BASE%\admin\$ORACLE_SID\plumtreescripts, start sqlplus using the /nolog parameter.

   2. Run the create_ali_tablespace_nt.sql script to create the portal tablespace.

   3. Run the create_ali_user_oracle.sql script to create the portal schema user

Creating the Portal Schema on Oracle Database for Microsoft Windows

before creating the portal schema you must configure the database, tablespace, and database user. For details on Oracle9i, see Creating the Portal Database on Oracle9i for Microsoft Windows. For details onOracle Database 10g, see Creating the Portal Database on Oracle Database 10g or 11g for Microsoft Windows.

This section describes how to create the portal schema.

1. If your imageserver is located on a computer than the one hosting your portal server, use a text editor to edit the postinst_oracle.sql file to correctly reflect the imageserver location. Find the following setting, and replace server with the new location:

   UPDATE PTOBJECTPROPERTIES SET PROPERTIES2 = '<S N="URL">http://server/imageserver/</S></PTBAG>'
   WHERE OBJECTID = 30 AND CLASSID = 48 AND PAGENUMBER = 0
   

2. Create the Oracle WebCenter Interaction tables, indexes, and stored procedures.

   Create the Oracle WebCenter Interaction tables, indexes, and stored procedures by running the init_ali_db_oracle.sql script. This script must be run as the portal database user you created.

   Output from the script is saved in the following files in the scripts directory:

   • create_tables_oracle.lst

   • stored_procs_oracle.lst

   • load_seed_info.lst

   • postinst.lst

3. (Optional) Create an Oracle SPFILE.

   For the benefits of using an SPFILE, refer to Oracle documentation. To create the SPFILE, run the create_spfile_oracle_nt.sql script.

4. Create the ALUI Directory tables.

   Run the following scripts in order:

   1. create_tables.sql

   2. create_functions.sql

   3. map_wcidb_103.sql

Creating and Configuring the Portal Database on Microsoft SQL Server

This section provides an overview of how to create and configure the Oracle WebCenter Interaction portal database on Microsoft SQL Server.

1. Create and configure the portal database.

   For details, see Creating and Configuring the Portal Database on Microsoft SQL Server.

2. Script the portal database.

   For details, see Scripting the Portal Database on Microsoft SQL Server

Creating and Configuring the Portal Database on Microsoft SQL Server

This section describes how to create and configure the portal database on Microsoft SQL Server 2005.

1. Configure the SQL Server instance to use SQL Server and Windows Authentication mode.

2. Create the portal database.

   1. Set the portal database name to the name you specified in the Configuration Manager while completing installation for Oracle WebCenter Interaction.

   2. Verify that the initial size of the portal database is sufficient for your Oracle WebCenter Interaction deployment.

      For a relatively small installation, configure a database that is at least 100 MB. For a large enterprise with as many as 20,000 users, configure a database that is as large as 1 GB.

   3. Choose a case-insensitive collation, such as SQL_Latin1_General_CP1_CI_AS.

3. Create the portal database user.

   1. Create the portal database user with the user name and password you specified when you ran the Oracle WebCenter Interaction installer.

   2. Configure the portal database user to use SQL Server Authentication.

   3. Set the portal database user's default database to the portal database.

4. Grant the portal database user the db_owner role for the portal database.

Scripting the Portal Database on Microsoft SQL Server

This section describes how to create and populate SQL Server tables necessary for the Oracle WebCenter Interaction portal.

We recommend that you run the scripts as the sa user so that the tables are owned by dbo.

Note:

See Knowledge Base article 778539.1 for a discussion of the benefits of dbo object ownership.

1. Delete previous tables (if they exist) and create the tables required for the new portal components by running the following script: install_dir\ptportal\10.3.3\sql\mssql\create_tables_mssql.sql.

2. Create the portal objects required by the portal by running the following script: install_dir\ptportal\10.3.3\sql\mssql\load_seed_info_mssql.sql

3. Create the stored procedures required by the portal by running the following script: install_dir\ptportal\10.3.3\sql\mssql\stored_procs_mssql.sql

4. If your imageserver is located on a computer other than the one hosting your portal server, use a text editor to edit the postinst_mssql.sql file to correctly reflect the imageserver location. Find the following setting, and replace server with the new location:

   UPDATE PTOBJECTPROPERTIES SET PROPERTIES2 = '<S N="URL">http://server/imageserver/</S></PTBAG>'
   WHERE OBJECTID = 30 AND CLASSID = 48 AND PAGENUMBER = 0
   

5. Set configuration information required by the portal by running the following script: install_dir\ptportal\10.3.3\sql\mssql\postinst_mssql.sql.

6. Run the SQL scripts for ALUI Directory.

   The scripts are located in install_dir\aluidirectory\1.1\sql\mssql. Run the scripts in the following order:

   1. create_tables.sql

   2. create_functions.sql

   3. map_alidb_65.sql

   Note:

   The create_functions.sql and map_alidb_65.sql scripts assume that all portal tables are under DBO. If your portal tables are under a different schema, you must manually edit create_functions.sql and map_alidb_65.sql, making the following replacements:

   • DBO.PTAUTHSOURCES must be changed to your_schema.PTAUTHSOURCES

   • DBO.LDAP_ORG_UNITS must be changed to your_schema.LDAP_ORG_UNITS

   For more information on handling portal objects that are not in the DBO schema, refer to install_dir\aluidirectory\1.1\sql\mssql\README.txt.

Creating and Configuring the Notification Service Database

This section describes the database configuration options for the Notification service.

By default, the Notification service uses an internal database. If your deployment requires a more robust database, you can configure Notification to use an external database.

To configure an external database:

1. Script your database.

   • For details on scripting an Oracle database, see Creating an External Notification Database on Oracle Database.

   • For details on scripting a Microsoft SQL Server database, see Creating an External Notification Database on Microsoft SQL Server.

2. Update Notification database configuration information in Configuration Manager.

   The Notification database configuration is located in Configuration Manager under Notification Service | External Database. Details of the necessary settings are provided as inline documentation in the Configuration Manager.

Creating an External Notification Database on Oracle Database

This section describes how to create and configure a database for the Notification service on all supported versions of Oracle Database.

• Log in to the portal database host computer as the owner of the Oracle system files. Unless otherwise noted, scripts must be run as the system user.

• Verify that ORACLE_BASE, ORACLE_HOME, and ORACLE_SID are set appropriately. For details, see Setting Oracle Environment Variables.

The script files referred to in the following steps are found in install_dir\cns\1.1\sql\oracle\windows. In this directory there are two subdirectories:

1. Edit references to the PLUM10 SID in cns-server-create-table-space.sql, if necessary.

   The cns-server-create-table-space.sql script assumes your SID to be PLUM10. If your SID is different, replace all occurrences of PLUM10 in the script file with your SID.

2. Run cns-server-create-table-space.sql.

3. Set user and password values in cns-server-create-user.sql.

   In the cns-server-create-user.sql script replace the tokens @CNSDB_LOGIN@ and @CNSDB_PASSWORD_UNENCRYPTED@ with the user name and password, respectively, for the user you are creating.

4. Run cns-server-create-user.sql.

5. As the user you just created, run cns-createTables.sql.

6. As the user you just created, run cns-data.sql.

Creating an External Notification Database on Microsoft SQL Server

This section describes how to create and configure a Microsoft SQL Server database for the Notification service.

The script files referred to in the following steps are found in install_dir\cns\1.1\sql\mssql.

1. Create a new database for the Notification service.

2. Give a user the db_owner role on the new database.

   Create a new database user for the Notification service, or use an existing user.

3. Run cns-createTables.sql.

4. Run cns-data.sql.

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. You were required to create the ALUI Security database when you installed Oracle WebCenter Analytics.

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.

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.