4 Creating and Configuring Databases for Oracle WebCenter Interaction

This chapter describes how to create and configure the portal, Notification 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 UNIX

• Creating and Configuring the Portal Database on DB2 for UNIX

Creating and Configuring the Portal Database on Oracle Database for UNIX

This section describes how to create and configure the Oracle WebCenter Interaction portal database on Oracle Database for UNIX.

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_unix9.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_unix10
     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_unix11
     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 UNIX.

   • 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 UNIX.

   • 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 UNIX.

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

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

Creating the Portal Database on Oracle9i for UNIX

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 UNIX.

The following must be done before scripting the database:

• 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.

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

1. Create and configure the portal database:

   1. Create the sys password.

      For example: $ $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/database/orapwPLUM password=password

   2. Create the PLUM directory under $ORACLE_BASE/oradata.

   3. Create a link to initPLUM.ora in $ORACLE_HOME/database.

2. Create the portal database instance:

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

   2. Run the crdb1_oracle_UNIX.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/oradata/$ORACLE_SID you should see the following:

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

3. Create the portal tablespace and user:

   1. Run the crdb2_oracle_UNIX.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 following errors might be generated:

      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/oradata/$ORACLE_SID 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 UNIX

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 UNIX.

The following must be done before scripting the database:

• 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.

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

1. Create and configure the portal database:

   1. Create the sys password.

      For example: $ $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/database/orapwPLUM10 password=password

   2. Create the PLUM10 directory under $ORACLE_BASE/oradata.

   3. Create a link to initPLUM10.ora in $ORACLE_HOME/database.

2. Create the portal database instance:

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

   2. Run the crdb1_oracle_UNIX.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/oradata/$ORACLE_SID you should see the following:

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

3. Create the portal tablespace and user:

   1. Run the crdb2_oracle_UNIX.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 following errors may be generated:

      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/oradata/$ORACLE_SID you should see the following:

      PLUM10tbl1.dbf
      PLUM10tmp1.dbf
      PLUM10idx1.dbf (single disk installation only.)
      

Creating the Portal Tablespace on Oracle Database for UNIX

This section describes how to create and configure the portal tablespace and user.

Note:

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

The following must be done before scripting the database:

• 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_UNIX.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 UNIX

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 UNIX. For details on Oracle Database 10g or 11g, see Creating the Portal Database on Oracle Database 10g or 11g for UNIX.

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. You must run this script as the portal database user that 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_UNIX.sql script.

4. Create the ALUI Directory tables.

   Run the following scripts in order:

   1. create_tables.sql

   2. create_functions.sql

   3. map_alidb_65.sql

Creating and Configuring the Portal Database on DB2 for UNIX

This section describes how to create and configure the portal database on DB2.

You must do the following before you create and configure your DB2 database.

• Oracle WebCenter Interaction, Oracle WebCenter Collaboration, and ALUI Directory must share the same DB2 database.

• Have your DB2 DBA examine and, if necessary, customize the SQL scripts before you use them. Each SQL file contains comments that describe what customization might be necessary.

  Note:

  The DB2 SQL scripts use a hard-coded schema name, ALUI. If the schema name is changed in the scripts, you must use Configuration Manager to update the schema name setting for Portal Service, Automation Service, ALI API Service, and ALUI Directory.

1. Copy SQL scripts from the Oracle WebCenter Interaction install directory to your DB2 server.

   The Oracle WebCenter Interaction installer creates the scripts in the following directories:

   • For scripts pertaining to the main portal application, install_dir/ptportal/10.3.3/sql/db2

   • For scripts pertaining to ALUI Directory, install_dir/aluidirectory/1.1/sql/db2

2. Use the DB2 command line processor to run the portal SQL scripts against your DB2 database.

   The scripts must be run in this order:

   1. create_alui_schema.sql

   2. grant_alui_user.sql

   3. create_alui_tablespace_UNIX.sql

   4. create_tables_db2.sql

   5. load_seed_info_db2.sql

   Run create_alui_schema.sql, grant_alui_user.sql, and create_alui_tablespace_UNIX.sql as an operating system user with DBADMIN privileges. All other scripts may be run by a DBADMIN user or the ALI user.

   Note:

   Comments in the header of each SQL file contain recommended syntax for the DB2 command line processor.

3. Run the run_stored_procs.sh shell script.

4. Use the DB2 command line processor to run the postinst_db2.sql script.

5. If you have configured DB2 to skip automatic statistics collection, run statistics_build.sql.

6. Use the DB2 command line processor to run the ALUI Directory SQL scripts against your DB2 database.

   The scripts must be run in this order:

   1. create_tables.sql

   2. create_functions.sql

   3. map_alidb_103.sql

7. To use an Active - Standby DB2 backend database environment, open the configuration.xml file in a text editor and add the following line:

   <setting name="database-connection:dbname">
   <value
   xsi:type="xsd:string">DATABASENAME;AlternateServers=(HOSTNAME:PORT)</value>
   </setting>
   

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.

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/unix. 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 and Configuring the Tagging Engine Database

This section describes how to create and configure a database for the Tagging Engine.

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

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/unix 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.

Note:

You do not must 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 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/unix to the ALUI Security database's host computer.

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.