Portal Express, Version 6.0
Operating systems: i5/OS, Linux, Windows
Create databases and users for Oracle
This section provides information on setting up Oracle to work with WebSphere Portal Express.
Before you begin
- You should have completed Installing Oracle.
Note the following information:
- If WebSphere Portal Express Version 6.0 and an earlier version of WebSphere Portal Express coexist, the database user IDs for WebSphere Portal Express Version 6.0 must be different than earlier versions to avoid conflicts during installation.
Database considerations
When creating Oracle databases for use with WebSphere Portal Express, you should consider the following information:
- The Oracle databases must be created manually before configuring WebSphere Portal Express.
- All databases must be created using UNICODE Database and National character sets such as UTF8, AL32UTF8, or AL16UTF16.
- IBM recommends that all databases to be used with WebSphere Portal Express are configured in Dedicated Server Mode.
- Determine if your Oracle server will be remote or local to the WebSphere Portal Express installation.
- If using an earlier version of Oracle (9i or earlier), ensure that Oracle JVM is also installed.
- After installing the database software for WebSphere Portal Express, you will need to set the buffer pools allocated to the Oracle database in order for WebSphere Portal Express to communicate with the DB2 Content Manager Runtime Edition database.
Use the following recommended values as a guide. Refer to Oracle product documentation for information on how to set the buffer pools. Recommended initial buffer pool sizes:
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
If you are using DB2® Content Manager Runtime Edition, the open_cursors value may need to be increased based on the table count in the DB2 Content Manager Runtime Edition schema.
Set up databases
You must create the databases that you are planning to use, create users, and grant privileges before you can configure WebSphere Portal Express to use the database. After you finish setting up the databases, you should continue to the Configuring WebSphere Portal Express for Oracle topic.
Create databases
For information about creating databases, refer to the Oracle product documentation. For information on the recommended database architecture and the databases you will need to create, see the Planning for Oracle topic. Be sure that all databases to be used with WebSphere Portal Express are created as UNICODE character set databases.
Notes:
- If you are using remote Oracle databases, also copy the ojdbc14.jar file from the remote Oracle server to the WebSphere Portal Express machine. The typical location is the oracle_home/jdbc/lib directory. Record the copy location on your local machine for future reference.
Create Users
Ensure that you create users and grant the appropriate privileges in Oracle before configuring WebSphere Portal Express to work with Oracle.
Take care to create users in an environment that has the same settings as the actual runtime environment. For example, avoid creating a user in an English environment if you plan to use that user in a Turkish environment.
Follow these steps to create the users in the WebSphere Portal Express databases:
- Log in to the database in which you want to create the new users.
- Using the architecture information in Planning for Oracle, create the users that you need in the appropriate databases by entering the following command in the SQL*Plus tool:
SQL> create user username identified by password default tablespace user_tablespace temporary tablespace temp_tablespace;
where user_tablespace is the default tablespace that is identified by the database administrator to store user objects and temp_tablespace is identified to store temporary objects.
To implement the architecture based on the recommendations in the planning section, follow these steps:
- Start SQL*Plus (the Oracle SQL
command line tool), and type the following commands to log in:
$ sqlplus
Press Enter.
Enter user-name: username/password@dbname
where username is an existing administrative user in the database. For example:
system/manager@wpsdb will log the administrative user system with a password of manager into the wpsdb database.
- Create the WebSphere Portal Express users:
SQL> create user releaseusr identified by password default tablespace users temporary tablespace temp; SQL> create user communityusr identified by password default tablespace users temporary tablespace temp; SQL> create user customizationusr identified by password default tablespace users temporary tablespace temp; SQL> create user icmadmin identified by password default tablespace users temporary tablespace temp; SQL> create user wmmdbusr identified by password default tablespace users temporary tablespace temp;
Notes:
- All names must be in lower case.
- The users being created are dbdomain.dbuser, where dbdomain is replaced by release, community, customization, JCR or WMM.
- Connect to the content database:
SQL> connect
Press Enter.
Enter user-name: username/password@dbname
where username is an existing administrative user in the database. For example:
system/manager@jcrdb will log the administrative user system with a password of manager into the Oracle database.
- Create the Oracle users:
SQL> create user icmadmin identified by password default tablespace users temporary tablespace temp;
When creating the icmadmin user, also grant all necessary privileges per the Granting privileges section of this document. If you do not grant privileges, you will receive the following error when you try to connect with the icmadmin user:
ICMADMIN lacks CREATE SESSION Privilege logon denied
- In the Oracle database directory, create a data directory named data and an index directory named index.
- Create the tablespaces:
- Find and edit the SQL script jcr_ora_tablespaces.sql under portal_server_root/config/work/db/oracle.
- In the define section, replace the following variables with the values from your environment:
Variable Description jcrdb Name of the database you created to store user data. logfile Location to store the log file. dbpath Directory where you created the database. - The size, autoextend, and maxsize values are recommendations and may need to be changed according to your environment. For example, you may want to change the maxsize to a set value rather than UNLIMITED. Consult your DBA for more info.
- Execute the SQL script.
# sqlplus SQL > @jcr_ora_tablespaces.sql
Notes:
- The DBA or a user with sufficient credentials (CREATE tablespace) must execute the script.
- The script will prompt for the database username and password.
- Connect to the Feedback database:
SQL> connect
Press Enter.
Enter user-name: username/password@dbname
where username is an existing administrative user in the database. For example:
system/manager@fdbkdb will log the administrative user system with a password of manager into the fdbkdb database.
- Create the Feedback user:
SQL> create user feedback identified by password default tablespace users temporary tablespace temp;
- Connect to the Likeminds database:
SQL> connect
Press Enter.
Enter user-name: username/password@dbname
where username is an existing administrative user in the database. For example:
system/manager@lmdb will log the administrative user system with a password of manager into the lmdb database.
- Create the Likeminds user:
SQL> create user lmdbusr identified by password default tablespace users temporary tablespace temp;
- Log out of the command line tool:
SQL> exit
Granting privileges
Grant these two default roles and their corresponding default privileges to each of the users that you created:
- CONNECT
- RESOURCE
To grant WebSphere Portal Express users the appropriate privileges...
- Log in to the database in which you want to grant privileges to users.
- Log in to SQLPlus as system/manager or another administrative user.
- When granting privileges on dba_pending_transactions, be logged in as sysdba.
- From the prompt, type the following command, where username represents the database user, to grant privileges:
SQL> grant connect, resource to username;
To implement the architecture based on the recommendations in the planning section, follow these steps:
- Start SQL*Plus (the Oracle SQL
command line tool), and type the following commands to log in:
$ sqlplus
Press Enter.
Enter user-name: username/password@dbname
where username is an existing administrative user in the database. For example:
system/manager@release will log the administrative user system with a password of manager into the release database.
- Connect to the WebSphere Portal Express database:
SQL> connect
Press Enter.
Enter user-name: username/password@dbname
where username is an existing administrative user in the database. For example:
system/manager@wpsdb will log you in to the wpsdb database.
- Grant WebSphere Portal Express users for each dbdomain the required privileges:
SQL> grant select on dba_pending_transactions to dbdomain.dbusr; SQL> grant connect, resource to dbdomain.dbusr;
- Connect to the content database:
SQL> connect
Press Enter.
Enter user-name: username/password@dbname
where username is an existing administrative user in the database. For example:
system/manager@jcrdb will log you into the Oracle database.
- Grant the Oracle user with the necessary authority on the database, where icmadmin is replaced with your user name:
SQL> GRANT create session, alter session, create table, create view, create procedure, create trigger, create library, create tablespace, alter tablespace, drop tablespace, execute any procedure, unlimited tablespace, create public synonym, drop public synonym, create sequence to icmadmin;
- Grant Oracle user the required privileges, where icmadmin is replaced with your user name:
SQL> grant select on dba_pending_transactions to icmadmin; SQL> grant connect, resource to icmadmin; SQL> grant insert any table to icmadmin;
The insert privilege is required only to import the database during WebSphere Portal Express configuration. After configuring WebSphere Portal Express, this privilege can be revoked, if required.
- Connect to the Feedback database:
SQL> connect
Press Enter.
Enter user-name: username/password@dbname
where username is an existing administrative user in the database. For example:
system/manager@fdbkdb will log you in to the fdbkdb database.
- Grant the Feedback user the required privileges:
SQL> grant select on dba_pending_transactions to feedback; SQL> grant connect, resource, create session to feedback;
- Connect to the Likeminds database:
SQL> connect
Press Enter.
Enter user-name: username/password@dbname
where username is an existing administrative user in the database. For example:
system/manager@lmdb will log you into the lmdb database.
- Grant Likeminds user the required privileges:
SQL> grant select on dba_pending_transactions to lmdbusr; SQL> grant connect, resource, create session to lmdbusr; SQL> grant insert any table to lmdbusr;
The insert privilege is required only to import the database during WebSphere Portal Express configuration. After configuring WebSphere Portal Express, this privilege can be revoked, if desired.
- If you are using Oracle 10g with WebSphere Application Server Version 6.0 or higher, refer to the following technote for additional information on Oracle database configuration: http://www-1.ibm.com/support/docview.wss?uid=swg21196663.
- Log out of the SQL command line tool:
SQL> exit
Next steps
You have completed this step. Continue to the next step.
Parent topic:
Configuring Oracle
Previous topic
Installing Oracle