Setting up an Oracle database

Set up an Oracle database to work with Jazz™ Team Server, CLM applications, Data Warehouse.

Before you begin

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.
Depending on the version of Java that you use, the required Java Database Connectivity (JDBC) driver is ojdbc7.jar for Java 7 or ojdbc8.jar for Java 8.
Restriction: Because of a defect in Oracle JDBC driver 12.1.0.2.0, this version of the driver cannot be used. For details, see repotools -createTables command fails with ORA-01000 on Oracle 12 on the IBM Support portal page.
For a list of supported database versions and JDBC drivers, see Hardware and software requirements.
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 Rational solution for Collaborative Lifecycle Management (CLM) 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 CLM 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, CLMDB. 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 8 characters in length. In addition, the password must contain at least one uppercase character, one lowercase character, and one digit.
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, CLMDB with the database name you created in step 1, and JTS.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE JTS 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/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, CLMDB 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/CLMDB/JTS_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Change and Configuration Management application, enter the following command. Replace CCM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step 1, and CCM.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE CCM 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/CCM.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Change and Configuration Management application, enter the following command. Replace CCM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB 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/CLMDB/CCM_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Quality Management application, enter the following command. Replace QM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step 1, and QM.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE QM 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/QM.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Quality Management application, enter the following command. Replace QM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB 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/CLMDB/QM_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Requirements Management application, enter the following command. Replace RM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step 1, and RM.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE RM 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/RM.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Requirements Management application, enter the following command. Replace RM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB 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/CLMDB/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, CLMDB with the database name you created in step 1, and DCC.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE DCC 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/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, CLMDB 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/CLMDB/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, CLMDB with the database name you created in step 1, and LQE.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE LQE 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/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, CLMDB 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/CLMDB/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, CLMDB with the database name you created in step 1, and LDX.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE LDX 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/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, CLMDB 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/CLMDB/LDX_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Rational Engineering Lifecycle Manager application, enter the following command. Replace RELM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step 1, and RELM.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE RELM 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/RELM.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Rational Engineering Lifecycle Manager application, enter the following command. Replace RELM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step 1, and RELM_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE RELM_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/CLMDB/RELM_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Design Management application, enter the following command. Replace DM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step 1, and DM.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE DM 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/DM.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Design Management application, enter the following command. Replace DM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step 1, and DM_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE DM_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/CLMDB/DM_TEMP.DBF' 
SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
```
To create a tablespace for the Architecture Management application, enter the following command. Replace AM with the tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step 1, and AM.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE AM 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/AM.DBF' 
SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
```
To create a temporary tablespace for the Architecture Management application, enter the following command. Replace AM_TEMP with the temporary tablespace name, ORACLE_BASE with the absolute path where Oracle is installed, CLMDB with the database name you created in step 1, and AM_TEMP.DBF with the temporary file name that you want to create.
```
CREATE TEMPORARY TABLESPACE AM_TEMP 
TEMPFILE 'ORACLE_BASE/oradata/CLMDB/AM_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, CLMDB with the database name you created in step 1, and GC.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE GC 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/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, CLMDB 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/CLMDB/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, CLMDB with the database name you created in step 1, and DW.DBF with the datafile name that you want to create.
```
CREATE TABLESPACE DW 
DATAFILE 'ORACLE_BASE/oradata/CLMDB/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, CLMDB 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/CLMDB/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 Change and Configuration 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 Quality 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 Requirements Management 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 Rational Engineering Lifecycle Manager application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER RELM_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE RELM QUOTA UNLIMITED ON RELM TEMPORARY TABLESPACE RELM_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO RELM_DB_USER;
```
To create a user for Design Management application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER DM_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE DM QUOTA UNLIMITED ON DM TEMPORARY TABLESPACE DM_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO DM_DB_USER;
```
To create a user for Architecture Management application tablespace and to grant the required permissions, enter the following commands:
```
CREATE USER AM_DB_USER IDENTIFIED BY Passw0rd DEFAULT TABLESPACE AM QUOTA UNLIMITED ON AM TEMPORARY TABLESPACE AM_TEMP;
```
```
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO AM_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 this Deployment wiki article.
Create an environment variable named ORACLE_JDBC_DRIVER_FILE and point to the ojdbc7.jar or 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_dirver\ojdbc7.jar or C:\Path_to_JDBC_dirver\ojdbc8.jar depending on the version of Java you are using.
1. On UNIX systems, add the following environment variable declaration:
```
export ORACLE_JDBC_DRIVER_FILE =/Path_to_JDBC_dirver/ojdbc7.jar
```
  or
```
export ORACLE_JDBC_DRIVER_FILE =/Path_to_JDBC_dirver/ojdbc8.jar
```
  depending on the version of Java you are using.
  Note: For a list of supported versions of Java Runtime Environment and Oracle Java Database Connectivity (JDBC), see Hardware and software requirements.
  If you are using IBM WebSphere® Application Server, you must also add a custom property that points to the JDBC driver. For more information, see Setting up WebSphere Application Server.
To configure your database connection and create database tables complete one of these steps:
1. If you are using Liberty or Tomcat, start the server and continue with Running the setup by using Custom setup in the setup wizard.
2. If you are deploying WebSphere Application Server, see Deploying and starting WebSphere Application Server, and then continue with Running the setup by using Custom setup in the setup wizard.
3. 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.
4. If you are upgrading from a previous version, continue to run the appropriate upgrade scripts, which will migrate your existing database connections. For the data warehouse, you can modify the information to connect to the database while configuring the server after the upgrade.

video icon Watch videos

CLM playlist
Jazz.net channel
User Education channel

learn icon Learn more

CLM learning circle
Agile learning circle
Learning circles

ask icon Ask questions

Jazz.net forum
developerWorks forums

support icon Get support

Support Portal
Deployment wiki
Support blog