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