Steps to configure Portal v6.1.x with Oracle 

This guide will detail how to configure WebSphere Portal v6.1.0.x and 6.1.5.x with Oracle databases. These steps apply to all Oracle versions supported by WebSphere Portal. If you are unsure if the version of Oracle you wish to use is supported, please review the detailed system requirements for WebSphere Portal:

WebSphere Portal Detailed System Requirements

Before You Begin

1. Ensure that the database software is installed and upgraded to the supported version.
2. Copy the database driver jar files from the Oracle server to the WebSphere Portal server in some directory. The jar files you will need, depending on your setup, are:

• Oracle 9i and 10g: <Oracle root>/lib/java/ojdbc14.jar
• Oracle 11g (and using Portal with WAS v6.1): <Oracle root>/lib/java/ojdbc5.jar
• Oracle 11g (and using Portal with WAS v7): <Oracle root>/lib/java/ojdbc6.jar

In this guide, Oracle 10g will be used so we will copy the ojdbc14.jar file to the WebSphere Portal server.

Creating Databases

In this section, you will create the databases in the Oracle environment to be used by WebSphere Portal. In Portal v6.1.x, there are six Portal database domains (where "domain" refers to a logical grouping of data):

Release
Community
Customization
JCR
Likeminds
Feedback

You can organize this data in however many databases you would like. The recommendation is to use six separate databases. If this is not possible though and you need to combine these domains into a single database, ensure that you use unique values for the domain.DbSchema values in wkplc_comp.properties (this will be covered in the Modifying the ConfigEngine properties section).

In this guide, six databases will be created:

REL61DB
COM61DB
CUST61DB
JCR61DB
LM61DB
FB61DB

Each with the following bufferpools:

db_block_size = 8192
db_cache_size = 300M
db_files = 1024
log_buffer = 65536
open_cursors = 1500
pga_aggregate_target = 200M
pre_page_sga = true
processes = 300
shared_pool_size = 200M

IMPORTANT: All databases must be created with a UNICODE codeset: UTF8, AL32UTF8, or AL16UTF16. Failure to do so we result in an inoperable Portal environment. If you are unsure if your databases are created with a UNICODE codeset, you can check this by executing the following SQL statements against the databases:

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

If this returns anything other than UTF8, AL32UTF8, or AL16UTF16, then you must recreate your databases using the proper codeset.

Modifying the ConfigEngine properties

In this section, you will prepare the wkplc_comp.properties and wkplc_dbtype.properties files with the Oracle information. Complete the following steps:

1. Backup the following files from the <wp_profile>/ConfigEngine/properties directory:

wkplc_comp.properties
wkplc_dbtype.properties

2. Edit the <wp_profile>/ConfigEngine/properties/wkplc_comp.properties and update the following properties:

domain.DbType - The type of database you are configuring. This value should be 'oracle'.
domain.DbName - The name of the database for this domain.
NOTE: This value should be present in the DbUrl.
domain.DbSchema - The schema identifier for objects within the database for this domain.
NOTE: If you are using a single database or have combined domains into a single database, this value MUST be unique for each domain.
NOTE: This value must equal DbUser
domain.DataSourceName - The name of the datasource that will be created in the WebSphere Application Server configuration.
NOTE: Do NOT use any of these values: releaseDS, communityDS, customizationDS, jcrDS, likemindsDS, feedbackDS
domain.DbUrl - The url used to access your database.
NOTE: The URL must use this exact syntax for Oracle: jdbc:oracle:thin:@<oracle_hostname>:<port>:<DbName>. Any other syntax will cause the database transfer to fail.
NOTE: The value you set for DbName must be used in this url.
domain.DbUser - The name of the user that will access this database. This user does not exist yet and will be created in the Setting Up Databases section.
NOTE: The value you set for DbUser must equal DbSchema.
domain.DbPassword - The password of the DbUser.

For this guide, the following values were used:

feedback.DbType=oracle
feedback.DbName=FB61DB
feedback.DbSchema=fbdbusr
feedback.DataSourceName=wpdbDS_fdbk
feedback.DbUrl=jdbc:oracle:thin:@myoracle.ibm.com:1521:FB61DB
feedback.DbUser=fbdbusr
feedback.DbPassword=password

likeminds.DbType=oracle
likeminds.DbName=LM61DB
likeminds.DbSchema=lmdbusr
likeminds.DataSourceName=wpdbDS_lmdb
likeminds.DbUrl=jdbc:oracle:thin:@myoracle.ibm.com:1521:LM61DB
likeminds.DbUser=lmdbusr
likeminds.DbPassword=password

release.DbType=oracle
release.DbName=REL61DB
release.DbSchema=reldbusr
release.DataSourceName=wpdbDS_reldb
release.DbUrl=jdbc:oracle:thin:@myoracle.ibm.com:1521:REL61DB
release.DbUser=reldbusr
release.DbPassword=password

community.DbType=oracle
community.DbName=COM61DB
community.DbSchema=comdbusr
community.DataSourceName=wpdbDS_comdb
community.DbUrl=jdbc:oracle:thin:@myoracle.ibm.com:1521:COM61DB
community.DbUser=comdbusr
community.DbPassword=password

customization.DbType=oracle
customization.DbName=CUST61DB
customization.DbSchema=cusdbusr
customization.DataSourceName=wpdbDS_cusdb
customization.DbUrl=jdbc:oracle:thin:@myoracle.ibm.com:1521:CUST61DB
customization.DbUser=cusdbusr
customization.DbPassword=password
 
jcr.DbType=oracle
jcr.DbName=JCR61DB
jcr.DbSchema=icmadmin
jcr.DataSourceName=wpdbDS_jcrdb
jcr.DbUrl=jdbc:oracle:thin:@myoracle.ibm.com:1521:JCR61DB
jcr.DbUser=icmadmin
jcr.DbPassword=password 

3. (Optional) Edit the following properties in <wp_profile>/ConfigEngine/properties/wkplc_comp.properties if you intend to transfer the Portal databases from any database other than Derby.

NOTE: These values by default are set for the out-of-the-box derby configuration. If you are configuring Portal for an external database for the first time, you should NOT modify these properties. However if you have already configured Portal for another database, such as DB2, and you want to transfer to Oracle, then update the source properties to reflect your current database settings.

source.domain.DbType - The type of database you are currently configured to. This is 'derby' by default.
source.domain.DbName - The name of the database this domain is currently using.
source.domain.DbSchema - The current schema identifier for objects within the database for this domain.
source.domain.DataSourceName - The name of the datasource that is currently in use in the WebSphere Application Server configuration.
source.domain.DbUrl - The url currently used to access your database.
source.domain.DbUser - The name of the user that currently accesses this database.
source.domain.DbPassword - The password of the source DbUser.

In this guide, we will not be editing these properties.

4. Save the wkplc_comp.properties file.

5. Edit the wkplc_dbtype.properties file and modify the following property to point to the location that you copied the jar files from the Before You Begin Steps (example is from a Linux environment):

oracle.DbLibrary=/opt/WebSphere/OracleDriver/ojdbc14.jar

6. Save the wkplc_dbtype.properties file.

Setting up the databases

In this section we will prepare the databases for configuration with WebSphere Portal. This can be done either automatically or manually.

Automatically set up the databases

WebSphere Portal provides a ConfigEngine script to automatically setup the databases called 'setup-database'. This script does all of the following:

• Creates the users defined for domain.DbUser for each domain
• Assigns the appropriate grants and permissions for each user
• Creates the following tablespaces for the JCR domain: ICMLFQ32, ICMLNF32, ICMVFQ04, ICMSFQ04, ICMLSNDX. The tablespaces are created in the file structure.

This script requires DBA access to the databases.

1. On the Oracle server, create directories called "data" and "index" in the JCRDB directory. For example:

<Oracle database path>/JCR61DB/data
<Oracle database path>/JCR61DB/index

NOTE: Failure to do so will cause these steps to fail. If you are unable to create these directories, or have business standards that prevent you from doing so, please follow the Manual instructions instead.

2. Edit the <wp_profile>/ConfigEngine/properties/wkplc_comp.properties file and set the following properties for each domain:

domain.DBA.DbUser - The DBA user. This user MUST be able to connect to the Oracle Server "AS SYSDBA".
domain.DBA.DbPassword - The DBA password
domain.DbHome - The root of your Oracle database file structure, for example C:/oracle/product/10.2.0/db_1

3. Change directories to the /ConfigEngine directory, and execute the following command:

Windows: ConfigEngine.bat setup-database
UNIX: ./ConfigEngine.sh setup-database

4. Grant these additional permissions to each Portal domain user:

grant select on pending_trans$ to <domain.DbUser>;
grant select on dba_2pc_pending to <domain.DbUser>;
grant execute on dbms_system to <domain.DbUser>;

Manually set up the databases

Complete the following steps to manually set up the databases.

1. Connect to the release database as the DBA
2. Create the release user you specified for release.DbUser:

SQL> create user reldbusr identified by password;
default tablespace user_tablespace
temporary tablespace temp_tablespace;

3. Repeat Steps 1 and 2 for the Community, Customization, JCR, Likeminds, and Feedback domains.

4. Grant the following permissions for each user:

Release:
grant select on pending_trans$ to reldbusr;
grant select on dba_2pc_pending to reldbusr;
grant execute on dbms_system to reldbusr;
grant select on dba_pending_transactions to reldbusr;
grant connect, resource to reldbusr;

Community:
grant select on pending_trans$ to comdbusr;
grant select on dba_2pc_pending to comdbusr;
grant execute on dbms_system to comdbusr;
grant select on dba_pending_transactions to comdbusr;
grant connect, resource to comdbusr;

Customization:
grant select on pending_trans$ to custdbusr;
grant select on dba_2pc_pending to custdbusr;
grant execute on dbms_system to custdbusr;
grant select on dba_pending_transactions to custdbusr;
grant connect, resource to custdbusr;

JCR:
grant select on pending_trans$ to icmadmin;
grant select on dba_2pc_pending to icmadmin;
grant execute on dbms_system to icmadmin;
grant select on dba_pending_transactions to icmadmin;
grant create session, alter session, create table, create view, create trigger, create library, create tablespace, alter tablespace, drop tablespace, execute any procedure, unlimited tablespace, create sequence to icmadmin;
grant connect, resource to icmadmin;
grant insert any table to icmadmin;

Feedback:
grant select on pending_trans$ to fbdbusr;
grant select on dba_2pc_pending to fbdbusr;
grant execute on dbms_system to fbdbusr;
grant select on dba_pending_transactions to fbdbusr;
grant connect, resource to fbdbusr;

Likeminds:
grant select on pending_trans$ to lmdbusr;
grant select on dba_2pc_pending to lmdbusr;
grant execute on dbms_system to lmdbusr;
grant select on dba_pending_transactions to lmdbusr;
grant connect, resource, create session to lmdbusr;
grant insert any table to lmdbusr;

5. Connect to the JCR database as the JCR user or as the DBA.

6. Create the following tablespaces for the JCR domain with recommended size, autoextend and maxsize. The tablespace names CANNOT be changed, but you can adjust the sizes as you see fit:

ICMLFQ32 - size 300M; autoextend 10M; maxsize unlimited
ICMLNF32 - size 25M; autoextend 10M; maxsize unilimited
ICMVFQ04 - size 25M; autoextend 10M; maxsize unlimited
ICMSFQ04 - size 150M; autoextend 10M; maxsize unlimited
ICMLSNDX - size 10M; autoextend 10M; maxsize unlimited

Assigning Custom Tablespaces

This section is optional.

In this section, you have the option to assign custom tablespaces to the domains (other than the JCR tablespaces created in the previous section). By default, the tables that are created by the configuration are created within the default tablespace of the user. For example, if your release domain user had a default tablespace of 'Users', then all of the release tables will be created in the 'Users' tablespace. If you would like to customize where these tables are created, complete the following steps:

1. Create all of the tablespaces you would like to use

2. In the <wp_profile>/PortalServer/config/tablespaces directory, edit each of these files to assign a custom tablespace to each individual table and index:

release.space_mapping.properties
community.space_mapping.properties
customization.space_mapping.properties
jcr.space_mapping.properties
likeminds.space_mapping.properties
feedback.space_mapping.properties

3. For each entry in these properties files, at your tablespace name. IMPORTANT: For Oracle, prepend "TABLESPACE" to each entry. For example if I wanted my release tables in a tablespace called RELSPACE, my entries would look like:

release.ACTION_DESC.tablespace=TABLESPACE RELSPACE
release.ACTION_DESC_LOD.tablespace=TABLESPACE RELSPACE
release.ACTION_SET.tablespace=TABLESPACE RELSPACE
.
.
.

4. Save the files when you have completed updating all of them.

Configuring WebSphere Portal to use Oracle

In this section, you will transfer the WebSphere Portal server data that exists in the current database (Derby by default) to the Oracle server. This is accomplished by a ConfigEngine script called 'database-transfer'. This script does all of the following:

• Connects to the target database (Oracle in this case) and drops all objects within them to ensure we have a clean database to transfer to.
• Creates the necessary tables, indexes, etc in the target databases for each domain.
• Transfers the data from the source database (Derby by default) to the target database (Oracle) for each domain.
• Creates a JDBC Provider and datasources in the WebSphere Application Server configuration
• Maps the datasources to the appropriate applications. For example the release, customization, community and jcr datasources are mapped to wps.ear.

Complete the following steps:

1. Stop the WebSphere_Portal server by running the following command from the <wp_profile>/bin directory:

Windows: stopServer.bat WebSphere_Portal -user wasadmin -password password
UNIX: ./stopServer.sh WebSphere_Portal -user wasadmin -password password

2. Change directories to the <wp_profile>/ConfigEngine directory

3. Enter the following command to validate the database driver, where WasPassword is your WebSphere Application Server administrator password:

Windows: ConfigEngine.bat validate-database-driver -DWasPassword=password
UNIX: ./ConfigEngine.sh validate-database-driver -DWasPassword=password

4. Enter the following command to validate the database connection, where WasPassword is your WebSphere Application Server administrator password:

Windows: ConfigEngine.bat validate-database-connection -DWasPassword=password
UNIX: ./ConfigEngine.sh validate-database-connection -DWasPassword=password

5. Enter the following command to transfer the database configuration to Oracle, where WasPassword is your WebSphere Application Server administrator password:

IMPORTANT: Do not run this script as a background process. The action-validate-database sub-task may hang if executed as a background process.

Windows: ConfigEngine.bat database-transfer -DWasPassword=password
UNIX: ./ConfigEngine.sh database-transfer -DWasPassword=password

NOTE: This command by default transfers all six domains. If you would like to transfer individual domains only or a subset of the six domains, you can specify the -DTransferDomainList parameter in the command with a list of domains you want to transfer. For example if you only wanted to transfer release, community, and customization:

ConfigEngine.bat database-transfer -DWasPassword=password -DTransferDomainList=release,community,customization

6. Start the WebSphere Portal server from the <wp_profile>/bin directory:

Windows: startServer.bat WebSphere_Portal
UNIX: ./startServer.sh WebSphere_Portal

7. Clustered environments only. If you have performed these steps in a clustered environment, you must also complete the following steps:

• Copy the wp_profile_root/PortalServer/jcr/lib/com/ibm/icm/icm.properties file from the primary node and replace the icm.properties file on each secondary node with the new file from the primary node.
• Restart the secondary cluster members.