Creating the Oracle database

To create a WSRR database manually, you must edit and run the scripts that come with WebSphere® Service Registry and Repository, this topic tells you how to edit and run scripts associated with the Oracle database.

About this task

attention You must create the database using the UTF-8 code set. No other code sets will function correctly.

Before you can run scripts to create an Oracle database manually, you must customize them for WSRR. You can create the following databases:

Business Space
WSRR
Activity logging
Service integration bus.
Note: Using WSRR without a service integration bus is not supported.

You can create these databases separately, or alternatively you can create the WSRR, activity logging, and service integration bus tables in the Business Space database. These instructions assume that you are using a single database.

Use the following values:

Table 1. Values used to create Oracle database in WSRR
Value	Description
$ACTDB_NAME	Activity logging database name.
$ACTDB_SCHEMA	Activity logging database schema. This must be the same as the value of $ACTDB_USER.
$ACTDB_USER	Activity logging database user ID.
$ACTDB_PASS	The password for $ACTDB_USER.
$DB_NAME	WSRR database name.
$DB_SCHEMA	WSRR database schema. This must be the same as the value of $DB_USER.
$DB_USER	User in the Oracle database (default is SYSTEM).
$DB_PASS	The password for $DB_USER
$ORACLEPATH	The Oracle installation directory.
$ORAUSER	The operating system ID owning the oracle installation (usually "oracle").
$ORASYSPASS	The password for the oracle SYSTEM user.
$SIBDB_NAME	Service integration bus database name.
$SIBDB_SCHEMA	Service integration bus database schema. This must be the same as the value of $SIBDB_USER.
$SIBDB_USER	Service integration bus database user ID.
$SIBDB_PASS	The password for $SIBDB_USER
$SQLPATH	Path to the SQL files, for example: install_root/WSRR/dbscripts/oracle or install_media_root/dbscripts/oracle.

Note: When using or creating databases, you must be aware of the following restrictions.

You must use the ojdbc6.jar file, which is the Java™ version 1.6 JDBC driver. If you are using Oracle10g, this is a separate download from the oracle.com website.
Databases, whether existing or newly created, must use the UTF-8 code set. No other code sets will function correctly. To create new databases in UTF-8, set the following parameters:
- CHARACTERSET = "AL32UTF8"
- NATIONALCHARACTERSET= "UTF8"
Oracle truncates database names to eight characters, so you must avoid names longer than eight characters.
Ensure that a listener is defined to accept TCP connections by using the netca tool. For more information, see the Oracle documentation.

Procedure

Copy the script files to your database server.
Create an empty database by completing the following steps (omit these steps if you are using an existing database):
1. Edit $SQLPATH/dbca.rsp.
  - Replace all instances of __VARFILEPATH__ with $SQLPATH
  - Replace all instances of __DBNAME__ with $DB_NAME
2. As $ORAUSER, run:
```
$ORACLEPATH/bin/dbca -silent -responseFile $SQLPATH/dbca.rsp 
```
Configure the Business Space tables in the database:
1. Edit the files $SQLPATH/configBusinessSpaceDB.sh, $SQLPATH/createSchema_BusinessSpace.sql, and $SQLPATH/createTable_BusinessSpace.sqland substitute the following values:
  - Replace all instances of @DB_NAME@ with $DB_NAME
  - Replace all instances of @SCHEMA@ with $BS_SCHEMA
  - Replace all instances of @DB_PASSWORD@ with $DB_PASS
2. Before you run the script, replace all instances of @SCHEMA@ in the following files with your chosen Business Space schema name: $BS_SQLPATH/createSchema_BusinessSpace.sql, $BS_SQLPATH/createTablespace_BusinessSpace.sql, $BS_SQLPATH/createTable_BusinessSpace.sql and $BS_SQLPATH/createGrant_BusinessSpace.sql.
3. As $ORAUSER, run:
```
$SQLPATH/configBusinessSpaceDB.sh
```
Configure the database for WSRR:
1. Edit the file $SQLPATH/createWsrrDb.sql
  - Uncomment the line beginning -- schema --
  - Replace __DBNAME__ with $DB_NAME
  - Replace __DBSYSPASSWORD__ with $ORASYSPASS
  - Replace __DBSCHEMA__ with $DB_SCHEMA
2. As $ORAUSER, run:
```
$ORACLEPATH/bin/sqlplus /nolog @$SQLPATH/createWsrrDb.sql
```
Configure the service integration bus database:
1. Edit the file $SQLPATH/createSibDb.sql
  - Uncomment the line beginning -- schema --
  - Replace __DBNAME__ with $DB_NAME
  - Replace __DBSYSPASSWORD__ with $ORASYSPASS
  - Replace __DBSCHEMA__ with $SIBDB_SCHEMA
2. As $ORAUSER run:
```
$ORACLEPATH/bin/sqlplus /nolog @$SQLPATH/createSibDb.sql
```

Create the tables for WSRR and activity logging:

Edit the files $SQLPATH/createWsrrTables1.sql, $SQLPATH/createWsrrTables2.sql, $SQLPATH/createWsrrProcs.sql, and $SQLPATH/createActTables.sql
- Replace __DBNAME__ with $DB_NAME
- Replace __DBUSER__ with $DB_USER
- Replace __DBSCHEMA__ with $DB_SCHEMA

As $ORAUSER run:

$ORACLEPATH/bin/sqlplus SYSTEM/$ORASYSPASS@$DB_NAME  @$SQLPATH/createWsrrTables1.sql

$ORACLEPATH/bin/sqlplus SYSTEM/$ORASYSPASS@$DB_NAME  @$SQLPATH/createWsrrProcs.sql

$ORACLEPATH/bin/sqlplus SYSTEM/$ORASYSPASS@$DB_NAME  @$SQLPATH/createWsrrTables2.sql

$ORACLEPATH/bin/sqlplus SYSTEM/$ORASYSPASS@$DB_NAME  @$SQLPATH/createActTables.sql

Create the database tables for the service integration bus database:
1. Edit the files $SQLPATH/createSibTables.sql:
  - Replace __DBNAME__ with $DB_NAME
  - Replace __DBUSER__ with $DB_USER
  - Replace __DBSCHEMA__ with $SIBDB_SCHEMA
2. As $ORAUSER run:
```
$ORACLEPATH/bin/sqlplus SYSTEM/$ORASYSPASS@$DB_NAME  @$SQLPATH/createSibTables.sql
```

Results

The Oracle database is created.