Create databases and users for Oracle

 

+
Search Tips   |   Advanced Search

 

Contents

  1. Before you begin
  2. Database considerations
  3. Set up databases
  4. Create Users
  5. Granting privileges
  6. Next steps

 

You are here

  1. Plan for Oracle
  2. Install Oracle
  3. Create databases and users for Oracle (Current task)
  4. Transfer Oracle manually or Transfer between databases manually
  5. Verify database connections

This section includes information on setting up Oracle Enterprise Edition databases to work with IBM WebSphere Portal.

 

Before you begin

Note the following information:

  • If WebSphere Portal V6.0 and an earlier version of WebSphere Portal coexist, the database user IDs for WebSphere Portal Version 6.0 must be different than earlier versions to avoid conflicts during installation.

  • The tablespace LMTABLESPACE is reserved for LikeMinds. LikeMinds and Portal Personalization will drop this tablespace during uninstallation of LikeMinds and certain database transfer tasks.

 

Database considerations

When creating Oracle databases for use with WebSphere Portal, you should consider the following information:

  • The Oracle databases must be created manually before configuring WebSphere Portal.

  • 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 are configured in Dedicated Server Mode.

  • Determine if the Oracle server will be remote or local to the WebSphere Portal 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, set the buffer pools allocated to the Oracle database in order for WebSphere Portal to communicate with the Oracle. 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 DB2Content 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 we can configure WebSphere Portal to use the database. After you finish setting up the databases, you should continue to the Configuring WebSphere Portal 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 create, see the Plan for Oracle topic. Be sure that all databases to be used with WebSphere Portal 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 machine. The typical location is the oracle_home/jdbc/lib directory. Record the copy location on the local machine for future reference.

 

Create Users

Ensure that you create users and grant the appropriate privileges in Oracle before configuring WebSphere Portal to work with Oracle.

To create the users in the WebSphere Portal databases:

  1. Log in to the database in which you want to create the new users.

  2. Using the architecture information in Planning for Oracle, create the users that we need in the appropriate databases.

    The simplest scenario is to create an Oracle user that would be used as the DbUser for each DbSchema

    For example, in SQLPlus....

    SQL> create user oraadmin 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.

See dbdomain.properties for example user, DB, and schema names.

 

Advanced User Setup

To implement the architecture based on the recommendations in the planning section, follow the steps below.

Note that for a simple configuration, you do not have to configure all these users, we can use one user for all schemas.

  1. Start SQL*Plus

    $ sqlplus

    ...and enter...

    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.

  2. Create the WebSphere Portal 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 wmmdbusr identified by password default tablespace users temporary tablespace temp;

    For more info, see the config files:

    All names must be in lower case.

    The users being created are dbdomain.dbuser, where dbdomain is replaced by release, community, customization, or wmm.

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

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

  5. Go to the Oracle database directory and create a data directory named data and an index directory named index.

    In the following command examples, the database directory is...

    D:\oracle\oradata\jcrdb

  6. Create the tablespaces:

    1. Find and edit the SQL script...

      portal_server_root/config/work/db/oracle/jcr_ora_tablespaces.sql

    2. In the define section, replace the following variables with the values from the environment:

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

    3. The size, autoextend, and maxsize values are recommendations and may need to be changed according to the environment. For example, you may want to change the maxsize to a set value rather than UNLIMITED. Consult the DBA for more info.

    4. Execute the SQL script.

      # sqlplus
      SQL > @jcr_ora_tablespaces.sql
      The DBA or a user with sufficient credentials (CREATE tablespace) must execute the script.

      The script will prompt for the database username and password.

  7. Connect to the Feedback database:

    SQL> connect

    Press Enter.

    Enter user-name:

    username/password@dbname

    For example:

    system/manager@fdbkdb

    ...will log the administrative user system with a password of manager into the fdbkdb database.

  8. Create the Feedback user:

    SQL> create user feedback identified by password default tablespace users temporary tablespace temp;

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

  10. Create the Likeminds user:

    SQL> create user lmdbusr identified by password default tablespace users temporary tablespace temp;

  11. 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 users the appropriate privileges, perform the following steps:

  1. Log in to the database in which you want to grant privileges to users.

  2. Log in to SQLPlus as system/manager or another administrative user.

  3. When granting privileges on dba_pending_transations, be logged in as sysdba.

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

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

  2. Connect to the WebSphere Portal 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.

  3. Grant WebSphere Portal users for each dbdomain the required privileges:

    SQL> grant select on dba_pending_transactions to dbdomain.dbusr;
    SQL> grant connect, resource to dbdomain.dbusr;

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

  5. Grant the Oracle user with the necessary authority on the database, where icmadmin is replaced with the user name:

    SQL> GRANT create session, alter session, create table, create view, create procedure, create trigger, create library, create tablespace, alter tablespace, drop table space, execute any procedure, unlimited tablespace, create public synonym, drop public synonym, create sequence to icmadmin;

  6. Grant Oracle user the required privileges, where icmadmin is replaced with the 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 configuration. After configuring WebSphere Portal, this privilege can be revoked, if required.

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

  8. Grant the Feedback user the required privileges:

    SQL> grant select on dba_pending_transactions to feedback;
    SQL> grant connect, resource, create session to feedback;

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

  10. 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 configuration. After configuring WebSphere Portal, this privilege can be revoked, if desired.

  11. If you are using Oracle 10g with WebSphere Application Server V6.0 or higher, see theOracle 10g technote

  12. Log out of the SQL command line tool:

    SQL> exit

 

Next steps

 

Parent topic:

Create databases and users