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:

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 activity logging and service integration bus tables in the WSRR database. The most common scenario is to create activity logging, and service integration bus tables in the WSRR database, and this topic describes that scenario. If you create users by running the createWsrrSchema.sql, createActSchema.sql, or createSibSchema.sql scripts, then you need to unlock the accounts before starting WSRR.

Use the following values:

Table 1. Values used to create Oracle database in WSRR
Value	Description
$ACTDB_SCHEMA	Activity logging database schema. This must be the same as the value of $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_SCHEMA	Service integration bus database schema. This must be the same as the value of $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.
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 
```
3. Set ORACLE_SID with the database name by running the following command:
```
ORACLE_SID=$DB_NAME
export ORACLE_SID
```
Configure the database for WSRR:
1. Edit the file $SQLPATH/createWsrrDb.sql
  - Replace __DBNAME__ with $DB_NAME
  - Replace __DBSYSPASSWORD__ with $ORASYSPASS
2. As $ORAUSER, run:
```
$ORACLEPATH/bin/sqlplus /nolog @$SQLPATH/createWsrrDb.sql
```
Create the WSRR schema and tables:
1. Edit the file $SQLPATH/createWsrrSchema.sql:
  - Uncomment the line starting -- schema --
  - Replace __DBSCHEMA__ with $DB_SCHEMA
2. As $ORAUSER run:
```
$ORACLEPATH/bin/sqlplus SYSTEM/$ORASYSPASS@$DB_NAME  @$SQLPATH/createWsrrSchema.sql
```
3. Edit the files $SQLPATH/createWsrrTables1.sql, $SQLPATH/createWsrrTables2.sql, and $SQLPATH/createWsrrProcs.sql.
  - Replace __DBUSER__ with $DB_USER
  - Replace __DBSCHEMA__ with $DB_SCHEMA
4. 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
```
Create the schema and database tables for the activity logging database:
1. Edit the file $SQLPATH/createActSchema.sql:
  - Uncomment the line starting -- schema --
  - Replace __DBSCHEMA__ with $ACTDB_SCHEMA
2. As $ORAUSER run:
```
$ORACLEPATH/bin/sqlplus SYSTEM/$ORASYSPASS@$DB_NAME  @$SQLPATH/createActSchema.sql
```
3. Edit the file $SQLPATH/createActTables.sql:
  - Replace __DBUSER__ with $DB_USER
  - Replace __DBSCHEMA__ with $ACTDB_SCHEMA
4. As $ORAUSER run:
```
$ORACLEPATH/bin/sqlplus SYSTEM/$ORASYSPASS@$DB_NAME  @$SQLPATH/createActTables.sql
```
Create the schema and database tables for the service integration bus database:
1. Edit the file $SQLPATH/createSibSchema.sql:
  - Uncomment the line starting -- schema --
  - Replace __DBSCHEMA__ with $SIBDB_SCHEMA
2. As $ORAUSER run:
```
$ORACLEPATH/bin/sqlplus SYSTEM/$ORASYSPASS@$DB_NAME  @$SQLPATH/createSibSchema.sql
```
3. Edit the file $SQLPATH/createSibTables.sql:
  - Replace __DBUSER__ with $DB_USER
  - Replace __DBSCHEMA__ with $SIBDB_SCHEMA
4. As $ORAUSER run:
```
$ORACLEPATH/bin/sqlplus SYSTEM/$ORASYSPASS@$DB_NAME  @$SQLPATH/createSibTables.sql
```

Results

The Oracle database is created.