Oracle BI Scheduler Database Setup

Oracle Business Intelligence ( a component of oracle fusion middleware)

This post will describe the steps required to set up a Oracle BI Scheduler Database.

A schedule database is required for Oracle BI Schedule service to work.

Use following steps to configure Oracle BI Scheduler:

  • Identify a database
  • Create scheduler tables
  • Configure Job Manager
  • Configure XML files
  • Start Oracle BI Schedule Service

 

Identify a database

The schedule database would hold scheduler tables. You could choose any supported database to be scheduler database. Supported databases are: Oracle, DB2, MSSQL, Teradata, and SQLServer.
In this document we assume that Oracle database is used for Scheduler database.
Create a new schema to host the scheduler tables. For example, Sched_1@OBIEE
Create scheduler tables
Scripts to create scheduler tables are located under C:OBIEEOracleBIserverSchema folder.

For Oracle database, use SAJOBS.Oracle.sql to create the scheduler tables.
Launch Oracle SQL*Plus, login as Sched_1@OBIEE .
Next enter the following string at the SQL > prompt to run the batch sql file:
start C:OBIEEOracleBIserverschema SAJOBS.Oracle.sql;

In Linux files : /oracle/app/OracleBI/server/Schema

SAJOBS.xxx.sql
SAACCNT.xxx.sql

In my case :

For this we have to set the path :
ORACLE_HOME=/oracle/app/oracle/product/11.2.0/client
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin:.

then :
sqlplus / username@DBname
password : database password.

Username : SCHED_– USER

Password : Demo

Database name : SCHED
Port : 1522

sqlplus SCHED_– USER@SCHED

@SAJOBS.oracle.sql
@SAACCNT.oracle.sql

This will create scheduler tables.

Verify that the tables were created by entering the following string at the SQL > prompt:

select table_name from user_tables where table_name like ‘S_NQ%’;

 

Now we have run cryptotools to create credential in credentialstore.xml file .
Cryptotools utility is located under OracleBIwebbin folder

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/OracleBI/server/Bin64:/orarep/app/OracleBI/web/bin64:.

cryptotools64 credstore -add -infile /flexcube/app/OracleBIData/web/config/credentialstore.xml

>Credential Alias: Admin
>Credential “Admin” already exists. Do you want to overwrite it? y/n (y): y
>Username: Administrator
>Password: Administrator
>Do you want to encrypt the password? y/n (y): y
>Passphrase for encryption: Admin
>Do you want to write the passphrase to the xml? y/n (n): y

Add the below in Instanceconfig.xml
/OracleBIData/web/config

<CredentialStore>
<CredentialStorage type=”file” path=”OracleBIDatawebconfigcredentialstore.xml” />
</CredentialStore>

Run schconfig to configure the scheduler(unix) with the database schema connection.
/OracleBI/web/bin64

Add Scheduler Administrator Credentials to Oracle BI Presentation Services Credential Store (cryptotools).

Configure Oracle BI Presentation Services to Identify the Credential Store (instanceconfig.xml)

Datasource Name : SCHED
username :    SCHED_– USER
password : Demo

cd /OracleBI/setup
. ./sa-init64.sh

cd /OracleBI/server/Bin64
schconfig

Databse Configuration :
***** Scheduler Database Configuration *****
1 – Database Type                  : Oracle 10g R2
2 – Call Interface                    : OCI 10g
3 – Data Source Name          : SCHED
4 – User Name                        : SCHED_– USER
5 – Password                           : *****
6 – Timeout (Minutes)          : 60
7 – Maximum Connections     5
8 – Bulk Fetch Buffer Size (bytes)   : 33792
9 – Database Table for Jobs          : S_NQ_JOB
10 – Database Table for Instances     : S_NQ_INSTANCE
11 – Database Table for Parameters    : S_NQ_JOB_PARAM
12 – Database Table for Messages      : S_NQ_ERR_MSG
13 – DEFAULT VALUES
0 – Quit

General Configuration:

***** Scheduler General Configuration *****
1 – Scheduler Script Path            : /OracleBI/server/Scripts/Scheduler
2 – Default Script Path              :/OracleBI/server/Scripts/Common
3 – Temporary File Path              : /OracleBIData/tmp
4 – Port Number                      : 9705
5 – Purge Old instances after X days : 7
6 – Purge DB every X minutes         : 5
7 – Minimum Execution Threads        : 1
8 – Maximum Execution Threads        : 100
9 – Pause When Service Starts        : False
10 – Administrator Name               : Administrator
11 – Administrator Password           : *****
12 – DEFAULT VALUES
0 – Quit

Then we can try to start all the OBIEE related services .

oc4j start
run-sa.sh start
run-saw.sh start
run-ccs.sh start

 

Please follow and like us:

Please add your comments here