Setting up an Oracle database

Set up an Oracle database to work with Jazz® Team Server, Engineering Lifecycle Management applications, and Data Warehouse.

Before you begin

Important: In version 6.0.6.1 and later, you can partition a non-partitioned REPOSITORY_VERSION table in a configuration-enabled system. Database table partitioning helps manage the performance, availability, and scalability of large amounts of data (millions of artifacts) in a repository. To use the partitioning features, you must install an Enterprise edition of an Oracle database. Standard, Personal, or Express editions of the database do not support partitioning.

To create and set up an Oracle database, the user who is preparing the database must have database administration authority over the database and that the database, tablespace storage, and appropriate storage configuration is created by a user with system administration authority.
The required Java Database Connectivity (JDBC) driver is ojdbc8.jar.
For a list of supported database versions and JDBC drivers, see Hardware and software requirements for IBM Engineering Lifecycle Management.
The configuration parameters and tablespace options used in this topic 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 Jazz Team Server database user, then make sure to allow enough space for rapid growth.
If you install the IBM® Engineering Lifecycle Management applications on the same computer or distributed platforms, you can either create separate databases for each application or you can use the same database for all applications. However, you must create a separate tablespace and a database user who is associated with that tablespace for each application.
The examples in this topic are for one Oracle database with separate database users who are dedicated to each application.
The database that you create for the Jazz Team Server and all Engineering Lifecycle Management applications 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.
Change the open cursors limit:
- If you use spfile to start the database, open a SQL *Plus window and enter the following command:
```
alter system set open_cursors = 10000 scope=both;
```
- If you use pfile to start the database, open a SQL *Plus window and enter the following command:
```
alter system set open_cursors = 10000;
```

Procedure

As a system administrator, create a database with UTF-8 encoding for the Jazz Team Server and all applications.
1. Start the Database Configuration Assistant wizard and on the Welcome page click Next.
2. On the Operations page, select Create a Database and then click Next.
3. On the Templates page, select Custom Database and click Next.
4. In both the Global Database Name and SID fields, enter a name for the content storage database; for example, ELMDB. Click Next.
5. To configure the database control, enter the required information for alert notification and optionally enable the daily disk backup schedule. To continue without database control configuration, clear the check box next to Configure Enterprise Manager and then click Next.
6. On the Database Credentials page, choose an option for your user account, and then click Next.
  
  Note: Oracle password complexity policy requires that passwords have minimum of 12 characters in length. In addition, the password must contain at least one uppercase character, one lowercase character, and one digit. If your password contains special characters, multibyte characters, or starts with numbers or special characters, you must enclose it in double quotation marks. For details, see the Oracle documentation.
7. On the next page, select a storage type and location for database files. Click Next.
8. On the next page, choose a recovery option for the database, and then click Next.
9. On the next page, specify whether or not to add the Sample Schemas to your database. Click Next.
10. On the next page, click the Character Sets tab and select Use Unicode (AL32UTF8). Click Next.
  
  Note: Setting the character set to Unicode (AL32UTF8) enables you to store multiple language groups.
11. On the Database Storage page verify or change the database objects and click Next.
12. On the next page, click Finish to create the database.
13. In the Confirmation window that opens, optionally save the page as an HTML file, and then click OK.
  A progress bar is displayed to show the database creation progress.
Open an SQL *Plus window and log in as SYSTEM or SYSDBA.
To create a tablespace for Jazz Team Server, enter the following command. Replace JTS with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and JTS.DBF with the datafile name that you want to create.
```
CREATE BIGFILE TABLESPACE JTS 
DATAFILE 'ORACLE_BASE/oradata/ELMDB/JTS.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for Jazz Team Server, enter the following command. Replace JTS_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and JTS_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE JTS_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/ELMDB/JTS_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Engineering Workflow Management application, enter the following command. Replace CCM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and CCM.DBF with the datafile name that you want to create.
```
CREATE BIGFILE TABLESPACE CCM 
DATAFILE 'ORACLE_BASE/oradata/ELMDB/CCM.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Engineering Workflow Management application, enter the following command. Replace CCM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and CCM_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE CCM_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/ELMDB/CCM_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Engineering Test Management application, enter the following command. Replace QM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and QM.DBF with the datafile name that you want to create.
```
CREATE BIGFILE TABLESPACE QM 
DATAFILE 'ORACLE_BASE/oradata/ELMDB/QM.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Engineering Test Management application, enter the following command. Replace QM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and QM_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE QM_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/ELMDB/QM_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Engineering Requirements Management DOORS® Next application, enter the following command. Replace RM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and RM.DBF with the datafile name that you want to create.
```
CREATE BIGFILE TABLESPACE RM 
DATAFILE 'ORACLE_BASE/oradata/ELMDB/RM.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Engineering Requirements Management DOORS Next application, enter the following command. Replace RM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and RM_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE RM_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/ELMDB/RM_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Data Collection Component application, enter the following command. Replace DCC with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and DCC.DBF with the datafile name that you want to create.
```
CREATE BIGFILE TABLESPACE DCC 
DATAFILE 'ORACLE_BASE/oradata/ELMDB/DCC.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Data Collection Component application, enter the following command. Replace DCC_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and DCC_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE DCC_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/ELMDB/DCC_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Lifecycle Query Engine application, enter the following command. Replace LQE with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and LQE.DBF with the datafile name that you want to create.
```
CREATE BIGFILE TABLESPACE LQE 
DATAFILE 'ORACLE_BASE/oradata/ELMDB/LQE.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Lifecycle Query Engine application, enter the following command. Replace LQE_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and LQE_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE LQE_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/ELMDB/LQE_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Link Index Provider application, enter the following command. Replace LDX with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and LDX.DBF with the datafile name that you want to create.
```
CREATE BIGFILE TABLESPACE LDX 
DATAFILE 'ORACLE_BASE/oradata/ELMDB/LDX.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Link Index Provider application, enter the following command. Replace LDX_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and LDX_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE LDX_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/ELMDB/LDX_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the IBM Engineering Lifecycle Optimization Engineering Insights application, enter the following command. Replace ENI with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and ENI.DBF with the datafile name that you want to create.
```
CREATE BIGFILE TABLESPACE ENI 
DATAFILE 'ORACLE_BASE/oradata/ELMDB/ENI.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the IBM Engineering Lifecycle Optimization Engineering Insights application, enter the following command. Replace ENI_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and ENI_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE ENI_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/ELMDB/ENI_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Global Configuration Management application, enter the following command. Replace GC with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and GC.DBF with the datafile name that you want to create.
```
CREATE BIGFILE TABLESPACE GC 
DATAFILE 'ORACLE_BASE/oradata/ELMDB/GC.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Global Configuration Management application, enter the following command. Replace GC_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and GC_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE GC_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/ELMDB/GC_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the common data warehouse, enter the following command. Replace DW with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and DW.DBF with the datafile name that you want to create.
```
CREATE BIGFILE TABLESPACE DW 
DATAFILE 'ORACLE_BASE/oradata/ELMDB/DW.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the common data warehouse, enter the following command. Replace DW_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, ELMDB with the database name you created in step 1, and DW_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE DW_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/ELMDB/DW_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
In the Oracle Enterprise Manager Database Control, click Server > Tablespaces and verify that the tablespaces you created are displayed and accessible. Also ensure that the default Oracle tablespace named USERS already exist. If this tablespace does not exist in your Oracle database, create it.
Create an Oracle database user for each new tablespace.
After the tablespaces 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 tablespace (either unlimited or with a space quota) with privileges to create session, table, procedure, and views.
To create a user for Jazz Team Server tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER JTS_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE JTS QUOTA UNLIMITED ON JTS TEMPORARY TABLESPACE JTS_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO JTS_DB_USER;
```
To create a user for Engineering Workflow Management application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER CCM_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE CCM QUOTA UNLIMITED ON CCM TEMPORARY TABLESPACE CCM_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO CCM_DB_USER;
```
To create a user for Engineering Test Management application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER QM_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE QM QUOTA UNLIMITED ON QM TEMPORARY TABLESPACE QM_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO QM_DB_USER;
```
To create a user for Engineering Requirements Management DOORS Next application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER RM_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE RM QUOTA UNLIMITED ON RM TEMPORARY TABLESPACE RM_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO RM_DB_USER;
```
To create a user for Data Collection Component application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER DCC_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE DCC QUOTA UNLIMITED ON DCC TEMPORARY TABLESPACE DCC_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO DCC_DB_USER;
```
To create a user for Lifecycle Query Engine application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER LQE_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE LQE QUOTA UNLIMITED ON LQE TEMPORARY TABLESPACE LQE_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO LQE_DB_USER;
```
To create a user for Link Index Provider application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER LDX_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE LDX QUOTA UNLIMITED ON LDX TEMPORARY TABLESPACE LDX_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO LDX_DB_USER;
```
To create a user for IBM Engineering Lifecycle Optimization Engineering Insights application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER ENI_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE ENI QUOTA UNLIMITED ON ENI TEMPORARY TABLESPACE ENI_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO ENI_DB_USER;
```
To create a user for Global Configuration Management application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER GC_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE GC QUOTA UNLIMITED ON GC TEMPORARY TABLESPACE GC_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO GC_DB_USER;
```
To create a user for common data warehouse tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER DW_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE DW QUOTA UNLIMITED ON DW TEMPORARY TABLESPACE DW_TEMP;
```
```
GRANT DBA TO DW_USER;
```
Note: The creation of the data warehouse on Oracle requires more permissions as compared to other databases. When you specify the database user in the connection spec for data warehouse, ensure that the database user has DBA permissions. You can change this permission after the data warehouse is created. For more information, see Completing the installation.
To create the data warehouse without DBA permissions, see Create Oracle data warehouse without DBA permissions .
Create an environment variable named ORACLE_JDBC_DRIVER_FILE and point to the ojdbc8.jar JDBC driver. The location can vary depending on the Oracle product and operating system. You can download the supported Oracle JDBC driver from the Oracle website.
1. Click Start > Control Panel > System.
2. Click the Advanced tab, and then click Environment Variables.
3. In the System variables list, click New.
4. In the Variable name enter ORACLE_JDBC_DRIVER_FILE, and in the Variable value enter C:\<Path_to_JDBC_driver>\ojdbc8.jar, where <Path_to_JDBC_driver> is the file path to the computer that host the applications and not the database.
1. On UNIX systems, add the following environment variable declaration:
```
export ORACLE_JDBC_DRIVER_FILE =/Path_to_JDBC_driver/ojdbc8.jar
```
  Note: For a list of supported versions of Java Runtime Environment and Oracle Java Database Connectivity (JDBC), see Hardware and software requirements for IBM Engineering Lifecycle Management.
To configure your database connection and create database tables complete one of these steps:
1. If you are using IBM WebSphere® Liberty, start the server and continue with Running the setup by using Custom setup in the setup wizard.
2. If you prefer to manually modify the teamserver.properties file for the database connection and run the repotools commands to create database tables, see Manually configuring an Oracle database.
3. If you are upgrading from a previous version, continue to run the appropriate upgrade scripts, which will migrate your existing database connections.
Note: You can set value of the optimizer_use_sql_plan_baselines parameter to false to enhance the performance.