Install Database Component

This page will guide you in installing the Database component for Oracle Utilities Analytics Warehouse (OUAW).

Note: The tasks below must be followed in sequential order.

On this page:

Prerequisites

Before installing the OUAW Database component, verify the following:

Ensure the same OS user installs the required software.
Ensure Java is installed on the database server.
Ensure Oracle Database Server Enterprise Edition is installed on the database server.
Ensure the database is created.
Ensure the storage.xml file (included in the package in <TEMPDIR>/BI2800/<DB Schema>/Install-Upgrade) allocates all the base tables and indexes to the default tablespace CISTS_01. If a few tables or indexes outside of the default tablespace need to be allocated, it should be reflected in the storage.xml file by changing the tablespace name from the default value to a custom value.

See Perform Pre-Installation Steps for OUAW for more information.

Create OUAW Users and Tablespaces

The following configurations are required for the OUAW database server.

Set the Database Parameters

Connect to the database server with system administrator credentials. Example:
Copy
```
sqlplus sys/<sys password>@<database service name> as sysdba
```

Check the current values of the parameters below:

Copy

 show parameter cursor    
 show parameter Processes

If the open_cursor value and processes values are less than 3000 and 1000, then change the values as follows:
Copy
```
alter system set open_cursors=3000 scope=both;
alter system set processes=1000 scope=both;
```

Create Users and Tablespaces

Create the required tablespaces and proceed with user creation. The default name of the application tablespace is CISTS_01. Assign the tablespaces created in this step as the default tablespaces while creating the users mentioned in next step.

Note: If you are not creating and assigning the CISTS_01 tablespace as the default tablespace to DWADM, MDADM, and RELADM users (or if DWADM, MDADM, and RELADM users do not have a quota on the CISTS_01 tablespace), then edit the Storage.xml file to indicate the correct tablespace name on which ADM schemas have quota. The Storage.xml file is located under the Install-Upgrade directory.
Create users in the database with the names below (without giving specific roles):
- DWADM: Contains star schema objects (such as facts and dimensions) containing the data of the data warehouse.
- DWUSER: User with read/write access to objects in DWADM schema.
- DWREAD: User with read only access to objects in DWADM schema.
- MDADM: A metadata schema consisting of database objects used for storing the Oracle Utilities Analytics metadata.
- RELADM: A metadata schema consisting of database objects used to store the product version and component installation information.
- OUA_MASTER: ODI Master repository schema.
- OUA_WORK: ODI Work repository schema.
- DWSTAGE: The staging schema of the data warehouse.
Create roles with the following names:
- DW_USER: Assigned to DWUSER. This contains all grants for READ/WRITE access to objects in the DWADM schema.
- DW_READ: Assigned to DWREAD. This contains all grants for read-only access to objects in the DWADM schema.
- DW_PRIVS_ROLE: Assigned to MDADM. This contains the privileges necessary for the proper functioning of Oracle Utilities Analytics.
- DW_REPLICATE: Assigned to various replication users that get created automatically during the source configuration. The privileges necessary for this role are automatically assigned by Oracle Utilities Analytics.
Connect to the database as a sys user.
Execute the SQL below. This provides the required permissions to the users created in step 1.
Copy
```
Usersgrants.sql in <TEMPDIR>/BI2800/Scripts/Usersgrants.sql
```

Install RELADM Schema

Log in to the Database server.
Navigate to the temporary directory where the Oracle Utilities Analytics Warehouse v2.8.0.0 Oracle Database Multiplatform.zip file is unzipped.
Copy
```
cd database/BI2800/RELADM/Install-Upgrade
```
Review Storage.xml to ensure the RELADM user has quota on mentioned tablespace in file. If not, edit the file with correct tablespace name.
Add JAVA_HOME/bin to the PATH variable.
Set CLASSPATH pointing to the location where OraDBI.jar and all dependency jars exist.
Copy
```
export CLASSPATH=<TEMPDIR>/BI2800/RELADM/Jarfiles/*
```
Execute the MDADM installation command below. When it is completed, the utility creates the RELADM schema and system data definitions. If an error occurs while executing a SQL or another utility, it logs and displays the error message and allows you to re-execute the current step.
Copy
```
java com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DATABASE SERVER>:<DATABASE PORT>/<ORACLE SERVICENAME>,RELADM,<Password of RELADM User>,,,,,RELADM -l 1,2 -o -q true
```
To generate database statistics after connecting to sys user, run the statement below:
Copy
```
exec DBMS_STATS.GATHER_SCHEMA_STATS('RELADM')
```

Install MDADM Schema

Log in to the Database server.
Navigate to the temporary directory where Oracle Utilities Analytics Warehouse v2.8.0.0 Oracle Database Multiplatform.zip file is unzipped.
Copy
```
cd database/BI2800/MDADM/Install-Upgrade
```
Review Storage.xml to ensure the MDAM user has quota on mentioned tablespace in file. If not, edit the file with correct tablespace name.
Add JAVA_HOME/bin to the PATH variable.
Set CLASSPATH pointing to the location where OraDBI.jar and all dependency jars exist.
Copy
```
export CLASSPATH=<TEMPDIR>/BI2800/MDADM/Jarfiles/*
```
Execute the MDADM installation command below. When it is completed, the utility creates the MDADM schema and system data definitions. If an error occurs while executing a SQL or another utility, it logs and displays the error message and allows you to re-execute the current step.
Copy
```
java com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DATABASE SERVER>:<DATABASE PORT>/<ORACLE SERVICENAME>,MDADM,<Password of MDADM User>,,,,,MDADM -l 1,2 -o -q true
```
To generate database statistics after connecting to sys user, run the statement below:
Copy
```
exec DBMS_STATS.GATHER_SCHEMA_STATS('MDADM')
```

Install DWADM Schema

Log in to the Database server.
Navigate to the temporary directory where Oracle Utilities Analytics Warehouse v2.8.0.0 Oracle Database Multiplatform.zip file is unzipped.
Copy
```
cd database/BI2800/DWADM/Install-Upgrade
```
Review Storage.xml to ensure the DWADM user has quota on mentioned tablespace in file. If not, edit the file with correct tablespace name.
Add JAVA_HOME/bin to the PATH variable.
Set CLASSPATH pointing to the location where OraDBI.jar and all dependency jars exist.
Copy
```
export CLASSPATH=<TEMPDIR>/BI2800/DWADM/Jarfiles/*
```
Execute DWADM installation command below. When it is completed, the utility creates the DWADM schema and system data definitions. If an error occurs while executing a SQL or another utility, it logs and displays the error message and allows you to re-execute the current step.
Copy
```
java com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DATABASE SERVER>:<DATABASE PORT>/<ORACLE SERVICENAME>,DWADM,<Password of DWADM User>,DWUSER,DWREAD,DW_USER,DW_READ,DWADM -p <Password of  DWUSER>,<Password of DWREAD> -l 1,2 -o -q true
```
To generate database statistics after connecting to sys user, run the statement below:
Copy
```
exec DBMS_STATS.GATHER_SCHEMA_STATS('DWADM')
```
Back to Top