Setting up an Oracle database

Set up an Oracle database to work with the Rational® Connector for SAP Solution Manager.

Before you begin

To create and set up an Oracle database, it is assumed that the user who is preparing the database must have database administration authority over the database and that the database, table space storage, and appropriate storage configuration is created by a user with system administration authority.
Also, one of the following supported Oracle editions must be installed on a computer to be used as the database server. This computer can be different from the one that the Jazz™ Team Server runs on:
  • Oracle Database 11g Standard or Enterprise Edition Release 2
  • Oracle Database 10g Standard or Enterprise Edition Release 2
Important:
  • The required Java™ Database Connectivity (JDBC) driver for both Oracle 10g and 11g is ojdbc5.jar 11.2.0.2.0 or higher. Also, always update the database to the latest database version. For example, if the database server is 11.2, then you must be running 11.2.0.2.0 or later.
  • The following configuration parameters and table space options are examples and might not entirely apply to your environment. For example, if you cannot use the AUTOEXTEND setting, then the files must be large enough to allow for growth. Or, if you cannot use the UNLIMITED size quota for the IBM® Rational Connector for SAP Solution Manager database user, then make sure to allow enough space for rapid growth.

About this task

These examples are for one Oracle database with separate database users who are dedicated to each application.
Note: The database that you create for the Rational Connector must be formatted to use UTF-8 as the character set. By default, Oracle does not create a database with the UTF-8 character set. When you create a new database, ensure that you select AL32UTF8 in the Oracle DB setup.

Procedure

  1. As a system administrator, create a database with UTF-8 encoding for the Rational Connector. For scalability, select the Shared Server Mode option as your database connection. With shared server mode, several client connections share an allocated pool of resources.
  2. Open an SQL *Plus window, and create the database table spaces:
    CREATE TABLESPACE <sap user tablespace>
    DATAFILE '<path and name of .dbf file>' 
    SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
    
    				CREATE TEMPORARY TABLESPACE <sap user temp tablespace> 
    TEMPFILE '<path and name of the temp .dbf file>' 
    SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
  3. Create an Oracle database user for each new table space.

    After the table spaces are created, a special Oracle user must be created for each application to use the Oracle database. For multiple Jazz servers that run on one Oracle database, an Oracle database user must be created for each application. The Oracle database user must be able to create database objects in its table space (either unlimited or with a space quota) with privileges to create session, table, procedure, and views.

    In these examples, replace the phrases in the angle brackets.
    CREATE USER <sap db user> IDENTIFIED BY <password> DEFAULT TABLESPACE <sap user tablespace>
    QUOTA UNLIMITED ON <sap user tablespace> TEMPORARY TABLESPACE <sap user temp tablespace>;
    After the user is created, give the user the appropriate permissions to initialize the connector database. The following SQL command can be used to grant the appropriate permissions:
    GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO <sap db user>;

    For more information about Oracle data warehouse setup, see this wiki.

  4. You must specify the JAR file location for JDBC as follows:
    1. Locate the Oracle JDBC driver ojdbc6.jar JAR file. The location can vary depending on the Oracle product and operating system. You can download the supported Oracle JDBC driver (ojdbc6.jar 11.2.0.3) from www.oracle.com.
    2. Copy this file to a directory of your choice on the application server.
    3. If you are connecting to your Oracle database by using WebSphere® Application Server with JRE 6.0, set the ORACLE_JDBC_DRIVER_FILE environment variable to point to the JRE 6.0 version of the JDBC driver named ojdbc6.jar. This driver can be installed in a directory of your choice on the application server. For Tomcat 7.x, put ojdbc6.jar into the SAPCInstallDir/server/Tomcat/lib directory.
    4. If you are using WebSphere Application Server, you must also add a custom property that points to the ojdbc6.jar JDBC driver. For more information, see Setting up WebSphere Application Server.
  5. To configure your database connections and create database tables complete this step. There is a DDL SQL script at SAPCInstallDir/Oracle_Tables.ddl. Run that script in the SQL *Plus window.
  6. Edit the <SAPCInstallDir/server/conf/sapconnector.properties file. Comment out the default Derby DB lines, uncomment out the Oracle lines and change the host name, instance, user, and password fields.
  7. Save the file and close it.

Feedback