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.
Copyalter system set open_cursors=3000 scope=both;
-
Set processes to 1000.
Copyalter 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>/BI2900/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.
Copyexport 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/BI2900/Spatial-Metadata to the database directory <dump_dir> location.
Example: </u01/dumps> -
Execute the impdp command to import the USER_SDO* tables data.
Copyimpdp directory=dump_dir dumpfile=user_sdo.dmp logfile=impdp_user_sdo.log schemas=DWADM remap_tablespace=cists_01:<tablespace_name>
-
Provide DWADM username 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>/BI2900/Spatial-Metadata directory.
Copysqlplus 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.
Copyexport 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/BI2900/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.
Copyimpdp 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.
CopyORA-31684: Object type USER:”MDADM” already exists
ORA-31684: Object type USER:”RELADM” already exists
ORA-31684: Object type USER:”DWADM” already exists
ORA-39082: Object type FUNCTION:"MDADM"."B1_DECODE_STRING_FN" created with compilation warnings
ORA-39082: Object type PROCEDURE:"MDADM"."B1_ENCRYPT_STRING" created with compilation warnings
Post-Import Tasks
-
Connect to the database with SYS schema. Example:
Copysqlplus sys/<sys password>@<DATABASE SERVICE NAME> as sysdba
-
Execute the following commands:
CopyGRANT CREATE SYNONYM TO DW_USER;
GRANT CREATE SYNONYM TO DW_READ; -
Connect to the database with MDADM schema. Example:
Copysqlplus MDADM/<MDADM>@<DATABASE SERVICE NAME>
-
Execute the following commands.
Copycreate 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 must 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/BI2900/DWADM/Install-Upgrade.
-
Set the Java Home and Classpath, and run OraGenSec.
Copyexport JAVA_HOME=<JAVA HOME>
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=<TEMPDIR>/BI2900/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