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:
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
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
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
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;
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.
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
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.
References:
WebSphere Portal 6.1 Info Center
Technote - Exception occurs during recovery of Oracle database transactions (http://www-01.ibm.com/support/docview.wss?uid=swg21196663)