Install Demo Database Component

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

On this page:

Prerequisites

Before installing the OUAW demo database component, verify the following:

Java is installed on the database server.
Oracle Database Enterprise Edition 19c is installed on the database server.

See Perform Pre-Installation Steps for OUAW for details.

Create OUAW Users and Tablespaces on Demo Database Server

The following configurations are required for the OUAW target demo database server.

Set the Database Parameters

Open the cursor limit to 3000.

Copy

alter system set open_cursors=3000 scope=both;

Set processes to 1000.

Copy

alter system set processes=1000 scope=both;

Create Users and Tablespaces

Create at least one tablespace for storing the application objects. The default name of the application tablespace is CISTS_01.
Run users.sql under <TEMPDIR>/BI2802/Demo directory after replacing tablespace name as required.

Import Spatial Data

This section details how to import spatial metadata in the USER_SDO* tables for OUAW. Log in to the OUAW database server and follow these steps:

Set the following environment variables.

Copy

export ORACLE_SID=<DATABASE SERVICENAME>
export ORACLE_HOME=<ORACLE HOME>
export PATH=$ORACLE_HOME/bin:$PATH

Create the database directory in the database.
Example: Create the directory <dump_dir> as '</u01/dumps>'.
Copy the user_sdo.dmp dumpfile from the temporary directory <TEMPDIR>/database/BI2802/Spatial-Metadata to the database directory <dump_dir> location.
Example: </u01/dumps>

Execute the impdp command to import the USER_SDO* tables data.

Copy

impdp directory=dump_dir dumpfile=user_sdo.dmp logfile=impdp_user_sdo.log schemas=DWADM remap_tablespace=cists_01:<tablespace_name>

Provide DWADM user name password when prompted.

Note: Ensure to change the tablespace_name to the name on which DWADM has the quota. If DWADM user have quota on cists_01 tablespace, then exclude whole remap_tablespace clause from command.
Review the impdp_user_sdo.log file to ensure the tables are imported successfully.
After importing the tables, run the following SQL scripts from the <TEMPDIR>/BI2802/Spatial-Metadata directory.
Copy
```
sqlplus DWADM/<DWADM>@<database service name>
@copy_spatial_metadata.sql
@clean_sdo_release_tbls.sql
```
Review the log files.

Import Demo Data

This section details how to import the demo data dump file into the database. Log in to the OUAW database server and follow these steps:

Set the following environment variables.

Copy

export ORACLE_SID=<DATABASE SERVICENAME>
export ORACLE_HOME=<ORACLE HOME>
export PATH=$ORACLE_HOME/bin:$PATH

Create the database directory in the database.
Example: Create directory <dump_dir> as '</u01/dumps>'.
Copy the exp_demo.dmp dumpfile from the temporary directory <TEMPDIR/BI2802/Demo> to the database directory <dump_dir> location.

Example: </u01/dumps>

Note: Gunzip the exp_demo.dmp.gz file to obtain exp_demo.dmp.

Execute the impdp command to import the demo data.

Copy

impdp directory=<dump_dir> dumpfile=exp_demo.dmp logfile=<exp_demo.log> full=y remap_tablespace=cists_01:<tablespace_name>

Provide the system user and its password when prompted.

Note: Make sure to change the tablespace_name to the tablespace name on which DWADM, RELADM, and MDADM have the quota. If DWADM, RELADM, and MDADM users have quota on cists_01 tablespace, then exclude the whole remap_tablespace clause from the command.

If the following error messages appear while importing the demo data, you can ignore them.
Copy
```
ORA-31684: Object type USER:”MDADM” already exists
ORA-31684: Object type USER:”RELADM” already exists
ORA-31684: Object type USER:”DWADM” already exists
```

Post-Import Tasks

Connect to the database with SYS schema. Example:

Copy

sqlplus sys/<sys password>@<DATABASE SERVICE NAME> as sysdba

Execute the following commands:

Copy

GRANT CREATE SYNONYM TO DW_USER;
GRANT CREATE SYNONYM TO DW_READ;

Connect to the database with MDADM schema. Example:
Copy
```
sqlplus MDADM/<MDADM>@<DATABASE SERVICE NAME>
```

Execute the following commands.

Copy

create public synonym B1_TARGET_ENTITY for B1_TARGET_ENTITY;
create public synonym B1_PROD_INSTANCE for B1_PROD_INSTANCE;
create public synonym B1_JOB_CONFIG for B1_JOB_CONFIG;
grant select on B1_TARGET_ENTITY to public;
grant select on B1_PROD_INSTANCE to public;
grant select on B1_JOB_CONFIG to public;

Configure Security

The security scripts have to be executed after the initial load and the materialized views are created for the given source product instance.

In the Oracle Utilities Analytics Warehouse database component installation package, navigate to the directory <TEMPDIR>/database/BI2802/DWADM/Install-Upgrade.

Set the Java Home and Classpath, and run OraGenSec. jerome

Copy

export JAVA_HOME=<JAVA HOME>
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=<TEMPDIR>/BI2802/DWADM/Jarfiles/*
java com.oracle.ouaf.oem.install.OraGenSec -d DWADM,DWADM,jdbc:oracle:thin:@<DATABASE SERVER>:<DATABASE PORT>/<ORACLE SERVICE NAME> -u DWUSER,DWREAD -r DW_USER,DW_READ  -p <DWUSER>,<DWREAD> -a A  CAPTION:Configuring Security LANGUAGE:ALL