Creating Oracle BI Scheduler Databases and Tables
Creating a Scheduler Database and Tables for Oracle Database Server
Databases: Oracle only.
Use the following procedure to create a Scheduler database and tables for Oracle.
NOTE: For usage statistics, create the table in SAACCT.Oracle.sql.
To create a database and tables for Oracle
Proceed in one of the two following ways:
Create a new database named S_NQ_SCHED, and create a user named S_NQ_SCHED.
In one of your existing databases, create a user named S_NQ_SCHED.
Provide your own password to the user S_NQ_SCHED.
Using the Oracle Net configuration tool, create an Oracle Service to the current S_NQ_SCHED database with this user ID and password.
Using the SQL*Plus Worksheet or the SQL*Plus tool, open the file SAJOBS.Oracle.sql and execute it to create Oracle Business Intelligence Scheduler tables.
Configuring Oracle Server Databases for the Oracle BI Scheduler
To configure the Oracle database using Job Manager
From the Windows Start menu, select Programs > Oracle Business Intelligence > Job Manager.
In Job Manager, select File > Configuration Options.
In the Connection Pool section, enter the Oracle database service alias for the Data Source Name.
Select the appropriate Database Type from the drop down list (for example, Oracle 10g R1).
-
The Call Interface is updated automatically according to the Database Type chosen.
Exit the Job Manager.
Start the Oracle BI Scheduler Service from Windows Start Menu.
Changing Oracle BI Scheduler Table Names(OPTIONAL)
You can change the names of the tables that Oracle BI Scheduler uses by adding settings to the Oracle BI Scheduler configuration file, instanceconfig.xml, located in the directory OracleBIData_HOME\scheduler\config.
The following procedure shows how to change Oracle BI Scheduler table names. For this procedure, a new tag, DB Column Names, has been created as an example.
To change Oracle Business Intelligence Scheduler table names
Using an XML editor, open the instanceconfig.xml file.
Create a new tag named DB Column Names.
For each of the entries under the tag DB Column Names, add the parameter and string values shown in the following table. The values created in the data string become the values used for Oracle BI Scheduler table names.
Parameter Name
|
Type
|
String Value
|
TABLE_JOBS
|
REG_SA
|
S_NQ_JOB
|
TABLE_INSTANCES
|
REG_SA
|
S_NQ_INSTANCE
|
TABLE_PARAMS
|
REG_SA
|
S_NQ_JOB_PARAM
|
TABLE_ERRMSGS
|
REG_SA
|
S_NQ_ERR_MSG
|
NOTE: The data types for each column should remain true to the intent of the schema. For example, if the job ID is defined as an integer type, do not change it to a varchar type. However, increasing the number of characters in a varchar column is an acceptable change.
Restart the Oracle BI Scheduler server.
Setting Oracle BI Scheduler Configuration Options
About the Oracle BI Scheduler Administrator
As part of the process of setting Oracle BI Scheduler configuration options, you will need to specify a username and password for the Scheduler Administrator. The Scheduler administrator must be a user in the Oracle BI repository (.rpd file) and have the Administrator group membership assigned.
If you do not want to create a new administrator for Scheduler, you can set the Scheduler administrator credentials to those of the Oracle BI user, Administrator, that exists in the repository.
NOTE: The Scheduler Administrator is referred to in the following topics as the SchedulerAdmin.
For more information on the Administrator user in the repository, and on creating users and granting Group membership, see the Oracle Business Intelligence Server Administration Guide.
Section or Tab
|
Field
|
Input
|
Connection Pool
|
Database Type
|
From the drop down list, select the appropriate Database Type for the S_NQ_SCHED database.
The Call Interface is automatically updated according to the Database Type chosen.
|
|
Data Source Name
|
DSN created for the S_NQ_SCHED database.
|
|
Username
|
User name for the S_NQ_SCHED database.
|
|
Password
|
|
General
|
Administrator Name
|
User name for the Scheduler administrator.
|
|
Administrator Password
|
User password for the Scheduler administrator. Confirm the password entry.
|
|
(Other parameters)
|
The other parameters are set to defaults. The default port for the Scheduler service is 9705.
To change the Scheduler port, select and set Port Number.
|
The Scheduler host name and port are specified in the instanceconfig.xml file for Oracle BI Presentation Services. The Presentation Services instanceconfig.xml file is located in the directory OracleBIData_HOME\web\config on Windows, and in OracleBIData_HOME/web/config on Linux.
When Oracle BI Scheduler and Oracle BI Presentation Services components are installed together on the same machine, the installer sets the instanceconfig.xml setting to the local host and default Scheduler port of 9705.
-
When Oracle BI Scheduler and Oracle BI Presentation Services are not installed on the same machine, or if you have changed the Scheduler port from the default port of 9705, you must modify the instanceconfig.xml file for Presentation Services.
NOTE: Make a back-up copy of this file before editing it.
Use the following procedure to modify the instanceconfig.xml configuration file with an entry that points the Oracle BI Presentation Services to the Oracle BI Scheduler machine and port number.
To change the ScheduleServer configuration setting
On the BI Presentation Services machine, navigate to the Oracle BI data directory at the location shown in the following table:
Operating System
|
Data Directory Location
|
Windows
|
OracleBIData_HOME\Web\config
|
Linux
|
OracleBIData_HOME/web/config
|
Locate the file instanceconfig.xml and make a backup copy.
Open the instanceconfig.xml file for editing.
Between the <ServerInstance></ServerInstance> tags, locate the tag pair <Alerts> and </Alerts>. (If they do not exist, create them.)
Between the Alerts tags, create the tag pair <ScheduleServer> and </ScheduleServer>.
Between the ScheduleServer tags, insert the machine name of the Scheduler machine.
For example:
<ServerInstance>
. . .
<Alerts>
<ScheduleServer>Schedulermachine</ScheduleServer>
</Alerts>
If the Scheduler port has been changed from the default of 9705, specify the Scheduler port number.
For example:
<ServerInstance>
. . .
<Alerts>
<ScheduleServer>Schedulermachine:Port</ScheduleServer>
</Alerts>
Save the file when you are done.
Your changes take effect when the Oracle BI Presentation Services service is restarted.
Adding Scheduler Administrator Credentials to Oracle BI Presentation Services Credential Store
Oracle BI Presentation Services must be able to identify the Scheduler administrator and obtain the credentials to establish a connection with the Scheduler. Presentation Services stores the credentials that it uses in a Presentation Services Credential Store. The Scheduler administrator credentials must be added to the credential store, under the alias admin. To obtain the Scheduler Administrator credentials, Oracle BI Presentation Services searches the credential store for a username-password credential with the alias admin. For more information on the Presentation Services Credential Store, refer to the Oracle Business Intelligence Enterprise Edition Deployment Guide.
Use the following procedure to add the Scheduler administrator credentials to the Presentation Services Credential Store with the admin alias. This procedure adds the Scheduler administrator credentials to a proprietary XML file credential store called credentialstore.xml. The default location of this file is OracleBIData_HOME\web\config on Windows and OracleBIData_HOME/web/config on Linux.
To add Scheduler administrator credentials to the credential store
Open a command prompt window or command shell on the machine where Oracle BI Presentation Services has been installed.
Navigate to the directory OracleBI_HOME\web\bin on Windows. On Linux, navigate to OracleBI_HOME/web/bin.
Execute the CryptoTools utility to add the Scheduler Administrator credentials to the Presentation Services Credential Store:
cryptotools credstore -add -infile OracleBIData_HOME/web/config/credentialstore.xml
For more information on the CryptoTools utility, its syntax and supported sub-commands, refer to the Oracle Business Intelligence Enterprise Edition Deployment Guide.
Supply values for the prompted parameters, as shown in the following table.
CryptoTools Prompt
|
Value or Input
|
Description
|
Credential Alias
|
admin
|
Specify the value admin. Presentation Services uses this alias to identify the username-password credential for the Scheduler administrator.
|
Username
|
(Username of Scheduler administrator)
|
Username of the Scheduler administrator.
-
|
Password
|
(Password for the Scheduler administrator)
|
Password of the Scheduler administrator.
For example, SchedulerAdmin.
|
Do you want to encrypt the password?
|
y or n
|
Choosing Y encrypts the above password.
|
Passphrase for encryption
|
(passphrase)
|
Provide a passphrase that is used to encrypt the password. For example, secret.
|
Do you want to write the passphrase to the xml?
|
y or n
|
Choosing Y writes the passphrase that is needed to decode the password in the xml file.
For enhanced security, choose N. The passphrase is not written to the xml file. Instead, you must specify the passphrase in the instanceconfig.xml file.
|
The following is an example of the procedure to add the Scheduler administrator credentials to the Presentation Services Credential Store.
cryptotools credstore -add -infile OracleBIData_HOME/web/config/credentialstore.xml
>Credential Alias: admin
>Username: SchedulerAdmin
>Password: SchedulerAdmin
>Do you want to encrypt the password? y/n (y):
>Passphrase for encryption: secret
>Do you want to write the passphrase to the xml? y/n (n):
>File "OracleBIData_HOME/web/config/credentialstore.xml" exists. Do you want to overwrite it? y/n (y):
The CryptoTools utility updates the credentialstore.xml file, located in the directory OracleBIData_HOME\web\config. (The directory structure is the same on Linux systems.)
After executing the CryptoTools utility with the example inputs, the credentialstore.xml file contains entries similar to the following example:
<sawcs:credential type="usernamePassword" alias="admin">
<sawcs:username>SchedulerAdmin</sawcs:username>
<sawcs:password passphrase="secret">
<xenc:EncryptedData>
<xenc:EncryptionMethod Algorithm="http://www.rsasecurity.com/rsalabs/pkcs/schemas/pkcs-5#pbes2">
<pkcs-5:PBES2-params Algorithm="http://www.rsasecurity.com/rsalabs/pkcs/schemas/pkcs-5#pbkdf2">
<pkcs-5:KeyDerivationFunc>
<pkcs-5:Parameters>
<pkcs-5:IterationCount>1024</pkcs-5:IterationCount>
</pkcs-5:Parameters>
</pkcs-5:KeyDerivationFunc>
<pkcs-5:EncryptionScheme Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc"/>
</pkcs-5:PBES2-params>
</xenc:EncryptionMethod>
<xenc:CipherData>
<xenc:CipherValue>jeThdk8ZklnTlyKIat8Dkw</xenc:CipherValue>
</xenc:CipherData>
</xenc:EncryptedData>
</sawcs:password>
</sawcs:credential>
Configuring Oracle BI Presentation Services to Identify the Credential Store
Oracle BI Presentation Services must be directed to the credential store that contains the Scheduler administrator credentials. This is done by setting parameters in the Oracle BI Presentation Services configuration file, instanceconfig.xml. In addition, if you have not stored the passphrase in the credential store, then the passphrase to decrypt the password credential must also be specified. In the above example, the passphrase was not stored in the credential store and needs to be specified in the instanceconfig.xml file.
To identify the credential store to be used by Oracle BI Presentation Services
Open the instanceconfig.xml file for editing. This file is located in the OracleBI_Data\web\config directory. This directory structure is the same on Linux platform.
Locate the <CredentialStore> node within this file.
Specify attribute values as shown in the following example. If the <CredentialStore> node does not exist, create this element with sub-elements and attributes.
<WebConfig>
<ServerInstance>
<!-- other settings ... -->
<CredentialStore>
<CredentialStorage type="file" path="<path to credentialstore.xml>" passphrase="<passphrase>"/>
<!-- other settings ... -->
</CredentialStore>
<!-- other settings ... -->
</ServerInstance>
</WebConfig>
Restart Presentation Services to reflect the configuration changes.
After modification, the instanceconfig.xml contains entries as shown in the following example:
<?xml version="1.0"?>
<WebConfig>
<ServerInstance>
<!-- other settings ... -->
<CredentialStore>
<CredentialStorage type="file" path=""OracleBIData_HOME/web/config/credentialstore.xml" passphrase="secret"/>
<!-- other settings ... -->
</CredentialStore>
<!-- other settings ... -->
</ServerInstance>
</WebConfig>
CAUTION: Both the credentialstore.xml and instanceconfig.xml file must be protected. Their combination can reveal a privileged user password. (Neither file by itself has enough information to expose the password.)
