Monday, December 10, 2012

IBM B2B Sterling Integrator - Oracle Database User and Table Space creation Scripts

Below process explains how to create Oracle database for Sterling Integrator with all required user permissions.

1. Create a new database using Oracle Database Configuration Assist for Sterling Integrator with all required Initilation perameters. Initilation perameters are very critical so refer installation document very carefully while creating new database.

2. Log on to Database as a Power User (system or sys as sysdba) and create new table space,
       CREATE TABLESPACE SI_TABLESPACE DATAFILE
       '/u01/app/sterling/SI52.DBF' SIZE 2000M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED
        LOGGING
        ONLINE
        PERMANENT
        EXTENT MANAGEMENT LOCAL AUTOALLOCATE
        BLOCKSIZE 8K
        SEGMENT SPACE MANAGEMENT AUTO;

3. Log on to Database as a Power User (system or sys as sysdba) and create new user for Sterling Integrator Database in this case the user is "Sterling". Excute below scripts one by one.

    DROP USER STERLING CASCADE; --Not Required This Statement.
    CREATE USER STERLING IDENTIFIED BY STERLING DEFAULT TABLESPACE SI_TABLESPACE TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;
    GRANT EXP_FULL_DATABASE TO STERLING;
    GRANT IMP_FULL_DATABASE TO STERLING;
    GRANT RESOURCE TO STERLING;
    GRANT CONNECT TO STERLING;
    ALTER USER STERLING DEFAULT ROLE ALL;
    GRANT UNLIMITED TABLESPACE TO STERLING;
    GRANT DBA TO STERLING;
    GRANT ALTER SESSION TO STERLING;
    GRANT CREATE PROCEDURE TO STERLING;
    GRANT CREATE SEQUENCE TO STERLING;
    GRANT CREATE SESSION TO STERLING;
    GRANT CREATE SYNONYM TO STERLING;
    GRANT CREATE TABLE TO STERLING;
    GRANT CREATE VIEW TO STERLING;
    GRANT EXECUTE ANY PROCEDURE TO STERLING;
    GRANT INSERT ANY TABLE TO STERLING;
    GRANT UPDATE ANY TABLE TO STERLING;
    GRANT SELECT ANY TABLE TO STERLING;
    GRANT SELECT_CATALOG_ROLE TO STERLING;
    ALTER USER STERLING DEFAULT ROLE "CONNECT", "RESOURCE",SELECT_CATALOG_ROLE;
    GRANT CREATE TRIGGER TO STERLING;
    GRANT CREATE TYPE TO STERLING;
    GRANT EXECUTE ANY TYPE TO STERLING;
    GRANT SELECT ANY TABLE TO STERLING;
    GRANT SELECT ANY DICTIONARY TO STERLING;

4. Connect to Database as new user Sterling just to make sure you don't have any issues while connecting or accessing Sterling Integrator database.

5. You can proceed with the SI installations :)

3 comments:

  1. awsome piece of information, I had come to know about your website from my friend vinod, indore,i have read atleast seven posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, Unlock Table

    ReplyDelete
  2. Hi Sirish
    Thank you for the useful details.

    I have downloaded the SI software from partner world for Windows environment. When I tried to install the same, it is saying that "Error Validating Database". I am able to login to the database with same credentials through SQL plus.

    Below are the version details:
    SI version= SI 5.2.3
    DB version= Oralce 11g
    ojdbc.jar version = I tried with ojdbc5.jar and ojdbc6.jar
    Catalog name = tried with STERLING, SI, my DB name (ORCL)

    I have given all the parameters, I don't know what is the exact reason for failure. Please help me out.

    Thanks & Regards,
    -rajasekhar

    ReplyDelete
  3. Rajasekhar, What is the database name you created? ORCL is default database and it's not for SI.

    ReplyDelete