Step by Step Instructions for Configuring WebSphere Portal with Oracle

This article shows you how to transfer from default database to Oracle database in WebSphere Portal 6.1.

The article explains the various steps that are required to completely configure the WebSphere Portal to Oracle. These instructions can be used to perform the database transfer process for a stand-alone WebSphere Portal instance or can also be applied for a Clustered environment.

In WebSphere Portal 6.1, the Portal databases can be configured to DB2, Oracle or SQL Server. The portal databases can be configured to a local database server installed on the same machine or databases can be distributed to multiple database servers, located on the multiple remote database machines. This document explains how to configure the WebSphere Portal with Oracle database.

Assumptions:

WebSphere Portal 6.1 installed on a machine

Oracle 10g Server is running on a separate machine.

Steps:

1. The Oracle databases must be created manually before configuring WebSphere Portal. Make sure that all databases to be used with WebSphere Portal are created as UNICODE character set such as UTF8, AL32UTF8, or AL16UTF16.

In this scenario, I am considering creating 6 different oracle databases that will be used by WebSphere Portal. However multiple domains can share a single database, depending on your environment.

Domain                        Database

---

                      

---

Release                        RELDB

Community                   COMDB

Customization               CUSDB

Likeminds                     LMDB

Feedback                     FDBKDB

Jcr                                JCRDB

2. Make sure that the created databases are configured with the following values; the values for all databases will remain same except JCRDB that should have a value for open_cursors as 2500.

        db_block_size = 8192
        db_cache_size = 300M
        db_files = 1024
      & nbsp; log_buffer = 65536
        open_cursors = 1500  (2500 for JCR database)
        pga_aggregate_target = 200M
        pre_page_sga = true
        processes = 300
        shared_pool_size = 200M

3. If you are using remote Oracle databases, you must also copy the ojdbc14.jar file from the remote Oracle server to the WebSphere Portal/jdbc/lib
   machine. For example, the location of the jar file on the Portal machine can be C:/IBM/WebSphere/PortalServer/drivers

4. The database users should be created with certain privileges so that these users can access the Oracle databases used by WebSphere Portal.

        
create user release identified by passw0rd default tablespace ts_release temporary tablespace ts_release;
grant connect, resource to release;

create user community identified by passw0rd default tablespace ts_community temporary tablespace ts_community;
grant connect, resource to community;

create user customization identified by passw0rd default tablespace ts_customization temporary tablespace ts_customization;
grant connect, resource to customization;

create user feedback identified by passw0rd default tablespace ts_feedback temporary tablespace ts_feedback;
grant connect, resource, create session to feedback;

create user likeminds identified by passw0rd default tablespace ts_likeminds temporary tablespace ts_likeminds;
grant connect, resource, create session to likeminds;

create user jcr identified by passw0rd default tablespace ts_jcr temporary tablespace ts_jcr;
grant connect, reso urce, create session, alter session, create table, create view, create trigger, create library, execute any procedure, create sequence, insert any table to jcr;

create user wsadmin identified by passw0rd default tablespace ts_wsadmin temporary tablespace ts_wsadmin;
grant dba to feedback;

5. Use a text editor to open the properties file wp_profile_root/ConfigEngine/properties/wkplc_comp.properties and modify the values to correspond to your environment.

feedback.DbType=oracle

feedback.DbName=FDBKDB

feedback.DbSchema=feedback

feedback.DataSourceName=wpfdbkDS

feedback.DbUrl=jdbc:oracle:thin:@oracle.test.ibm.com:1521:FDBKDB

feedback.DbUser=feedback

feedback.DbPassword=passw0rd

feedback.DBA.DbUser=wsadmin

feedback.DBA.DbPassword=passw0rd

likeminds.DbType=oracle

likeminds.DbName=LMDB

likeminds.DbSchema=likeminds

likeminds.DataSourceName=wplmDS

likeminds.DbUrl=jdbc:oracle:thin:@oracle.test.ibm.com:1522:LMDB

likeminds.DbUser=likeminds

likeminds.DbPassword=passw0rd

likeminds.DBA.DbUser=wsadmin

likeminds.DBA.DbPassword=passw0rd

release.DbType=oracle

release.DbName=RELDB

release.DbSchema=release

release.DataSourceName=wprelDS

release.DbUrl=jdbc:oracle:thin:@oracle.test.ibm.com:1523:RELDB

release.DbUser=release

release.DbPassword=passw0rd

release.DBA.DbUser=wsadmin

release.DBA.DbPassword=passw0rd

community.DbType=oracle

community.DbName=COMDB

community.DbSchema=community

community.DataSourceName=wpcomDS

community.DbUrl=jdbc:oracle:thin:@oracle.test.ibm.com:1524:COMDB

community.DbUser=community

community.DbPassword=passw0rd

community.DBA.DbUser=wsadmin

community.DBA.DbPassword=passw0rd

customization.DbType=oracle

customization.DbName=CUSDB

customization.DbSchema=customization

customization.DataSourceName=wpcusDS

customization.DbUrljdbc:oracle:thin:@oracle.test.ibm.com:1525:CUSDB

customization.DbUser=customization

customization.DbPassword=passw0rd

customization.DBA.DbUser=wsadmin

customization.DBA.DbPassword=passw0rd

jcr.DbType=oracle

jcr.DbName=JCRDB

jcr.DbSchema=jcr

jcr.DataSourceName=wpjcrDS

jcr.DbUrl= jdbc:oracle:thin:@oracle.test.ibm.com:1526:JCRDB

jcr.DbUser=jcr

jcr.DbPassword=passw0rd

jcr.DBA.DbUser=wsadmin

jcr.DBA.DbPassword=passw0rd

Note that these values might be different in your case and should be changed according to your environment. For example, Oracle database host, instance port, database name, database names, user, passwords, schema names.

6. Use a text editor to open the properties file wp_profile_root/ConfigEngine/properties/wkplc_dbtype.properties and modify the following values to correspond to your environment. The value of oracle.DbLibrary must be the path where ojdbc14.jar was copied on the WebSphere Portal box.

oracle.DbDriver=oracle.jdbc.driver.OracleDriver

oracle.DbLibrary=C:/IBM/WebSphere/PortalServer/drivers/jdbc/lib/ojdbc14.jar

oracle.JdbcProviderName=wpdbJDBC_oracle

7. Open the following mapping files under wp_profile_root/PortalServer/config/tablespaces that specify the table space and index space property pairs for each database table:

community.space_mapping.properties

customization.space_mapping.properties

feedback.space_mapping.properties

jcr.space_mapping.properties

likeminds.space_mapping.properties

release.space_mapping.properties

Assign a table space to each entry in the mapping files. The table space name must be prepended by the keyword TABLESPACE and a space. For example: community.COMP_INST.tablespace=TABLESPACE TS_COMMUNITY Repeat this step for each domain that you are transferring.

8.   Create the tablespaces that are required for JCR domain using the following commands;           

Before using the following commands, replace &dbpath. with the actual database path on oracle jcr db. You also need to replace JCRDB with the name of jcr database if this is different in your case.

create tablespace ICMLFQ32 datafile '&dbpath./JCRDB/data/JCRDB_ICMLFQ32_01.dbf' size 300M reuse autoextend on next 10M maxsize UNLIMITED extent management local autoallocate;

create tablespace ICMLNF32 datafile '&dbpath./JCRDB/data/JCRDB_ICMLNF32_01.dbf' size 25M reuse autoextend on next 10M maxsize UNLIMITED extent management local autoallocate;

create tablespace ICMVFQ04 datafile '&dbpath./JCRDB/data/JCRDB_ICMVFQ04_01.dbf' size 25M reuse autoextend on next 10M maxsize UNLIMITED extent management local autoallocate;

create tablespace ICMSFQ04 datafile '&dbpath./JCRDB/data/JCRDB_ICMSFQ04_01.dbf' size 150M reuse autoextend on next 10M maxsize UNLIMITED extent management local autoallocate;

create tablespace ICMLSNDX datafile '&dbpath./JCRDB/index/JCRDB_ICMLSNDX_01.dbf' size 10M reuse autoextend on next 10M maxsize UNLIMITED extent management local autoallocate;

 

9.  Run the following commands as user SYS:

grant select on pending_trans$ to ; grant select on dba_2pc_pending to ; grant select on dba_pending_transactions to ; grant execute on dbms_system to ;

Where is the username configured in the Oracle datasource's authentication alias.

 

10. Open a command prompt and change to the directory wp_profile_root\ConfigEngine. Enter the following commands to validate configuration properties.
ConfigEngine.bat validate-database-driver
-DTransferDomainList=release,customization,community,jcr,feedback,likeminds

ConfigEngine.bat validate-database-connection
-DTransferDomainList=release,customization,community,jcr,feedback,likeminds

11. Stop both WebSphere Application Server and the WebSphere Portal server:

 

12.  Open a command prompt and change to the directory wp_profile_root\ConfigEngine. Enter the following commands to transfer the database to Oracle.
ConfigEngine.bat database-transfer
-DTransferDomainList=release,customization,community,jcr,feedback,likeminds -DuseCustomTablespaceMapping=true
 
13. Start the Portal Server and test if you can login to the Portal Server.

Source : http://www-10.lotus.com/ldd/portalwiki.nsf/dx/06152009050451pmwebsuc.htm