Create databases and users for Oracle

 

+
Search Tips   |   Advanced Search

 

---

Oracle DB Install and Setup

1. Install Oracle.

   If WebSphere Portal V5.1 and an earlier version of WebSphere Portal coexist, the database user IDs for WebSphere Portal V5.1 must be different than earlier versions to avoid conflicts during installation.

2. Create the databases that you are planning to use.

   The Oracle database(s) must be created manually before configuring WebSphere Portal. All databases to be used with WebSphere Portal must be created as UTF-8 character set databases.

3. If you are using remote Oracle databases, copy...

   oracle_home/jdbc/lib/ojdbc14.jar

   ...from the remote Oracle server to the WebSphere Portal machine. Record the copy location on the local machine for future reference.

4. Start SQL*Plus and log into the database. For example:

   $ sqlplus
   Enter user-name: system/manager@wpsdb

5. Create the users required. For example...

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

6. Connect to the content database:

   SQL> connect
   Enter user-name: system/manager@jcrdb

7. Create the DB2 Content Manager Runtime Edition users:

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

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

9. Create the table spaces:

   Replace jcrdb with the name of the database used to store user data, icmadmin with the user name used to access jcrdb, D:\oracle\oradata with the directory where you created the database, and icmjcr.log with the name of the log file.

   spool C:\Temp\icmjcr.log
   whenever sqlerror exit sql.sqlcode rollback
   whenever sqlerror exit sql.sqlcode rollback
   CONNECT icmadmin/password

   create tablespace ICMLFQ32 datafile 'D:\oracle\oradata\jcrdb\data\jcrdb_ICMLFQ32_01.dbf' size 300M reuse autoextend on next 10M maxsize UNLIMITED extent management local autoallocate;

   create tablespace ICMLNF32 datafile 'D:\oracle\oradata\jcrdb\data\jcrdb_ICMLNF32_01.dbf' size 25M reuse autoextend on next 10M maxsize UNLIMITED extent management local autoallocate;

   create tablespace ICMVFQ04 datafile 'D:\oracle\oradata\jcrdb\data\jcrdb_ICMVFQ04_01.dbf' size 25M reuse autoextend on next 10M maxsize UNLIMITED extent management local autoallocate;

   create tablespace ICMSFQ04 datafile 'D:\oracle\oradata\jcrdb\data\jcrdb_ICMSFQ04_01.dbf' size 150M reuse autoextend on next 10M maxsize UNLIMITED extent management local autoallocate;

   create tablespace ICMLSNDX datafile 'D:\oracle\oradata\jcrdb\index\jcrdb_ICMLSNDX_01.dbf' size 10M reuse autoextend on next 10M maxsize UNLIMITED extent management local autoallocate;

   create tablespace OBJINDEX datafile 'D:\oracle\oradata\jcrdb\index\jcrdb_objndx_01.dbf' size 30M reuse autoextend on next 30M maxsize 120M extent management local autoallocate;

   create tablespace OBJECTS datafile 'D:\oracle\oradata\jcrdb\data\jcrdb_objects_01.dbf' size 50M reuse autoextend on next 50M maxsize 2000M extent management local autoallocate;

   create tablespace SMS datafile 'D:\oracle\oradata\jcrdb\data\jcrdb_sms_01.dbf' size 2M reuse autoextend on next 2M maxsize 150M extent management local autoallocate;

   create tablespace BLOBS datafile 'D:\oracle\oradata\jcrdb\data\jcrdb_blobs_01.dbf' size 50M reuse autoextend on next 50M maxsize 1000M extent management local autoallocate;

   create tablespace REPLICAS datafile 'D:\oracle\oradata\jcrdb\data\jcrdb_replicas_01.dbf' size 10M reuse autoextend on next 10M maxsize 1000M extent management local autoallocate;

   create tablespace TRACKING datafile 'D:\oracle\oradata\jcrdb\data\jcrdb_tracking_01.dbf' size 2M reuse autoextend on next 2M maxsize 250M extent management local autoallocate;

   create tablespace VALIDATEITM datafile 'D:\oracle\oradata\jcrdb\data\jcrdb_validateitm_01.dbf' size 10M reuse autoextend on next 10M maxsize 250M extent management local autoallocate;

   spool off
   exit

10. Set the buffer pools allocated to the Oracle database. Recommended initial buffer pool sizes:

    db_block_size = 4096 db_cache_size = 300M
    shared_pool_size = 200M
    large_pool_size = 0
    java_pool_size = 0
    pre_page_sga = true
    pga_aggregate_target = 200M
    db_files = 1024
    open_cursors = 500
    processes = 300
    log_buffer = 65536

11. Connect to the Feedback database:

    SQL> connect
    Enter user-name: system/manager@fdbkdb

12. Create the Feedback user:

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

13. Connect to the Likeminds database:

    SQL> connect
    Enter user-name: system/manager@lmdb

14. Create the Likeminds user:

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

15. Connect to the WebSphere Portal database:

    SQL> connect
    Enter user-name: system/manager@wpsdb

16. Grant WebSphere Portal users the required privileges:

    SQL> grant select on dba_pending_transactions to wpsdbusr;
    SQL> grant select on dba_pending_transactions to wmmdbusr;
    SQL> grant connect, resource to wpsdbusr;
    SQL> grant connect, resource to wmmdbusr;

17. Connect to the content database:

    SQL> connect
    Enter user-name: system/manager@jcrdb

18. Grant the DB2 Content Manager Runtime Edition user with the necessary authority on the database, where icmadmin" 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 tablespace, execute any procedure, unlimited tablespace, create public synonym, drop public synonym, create sequence to icmadmin;

19. Grant DB2 Content Manager Runtime Edition user the required privileges, where icmadmin" 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 desired.

20. Connect to the Feedback database:

    SQL> connect
    Enter user-name: system/manager@fdbkdb

21. Grant the Feedback user the required privileges:

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

22. Connect to the Likeminds database:

    SQL> connect
    Enter user-name: system/manager@lmdb

23. Grant Likeminds user the required privileges:

    SQL> grant select on dba_pending_transactions to lmdbusr;
    SQL> grant connect, resource 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.

24. Log out of the SQL command line tool:

    SQL> exit

 

Next steps

• Database (Windows/UNIX)
• Plan for Oracle
• Install Oracle
• Configure Oracle
• Verifying database connections

• Configure WebSphere Portal for Oracle

 

WebSphere is a trademark of the IBM Corporation in the United States, other countries, or both.

 

IBM is a trademark of the IBM Corporation in the United States, other countries, or both.