Pre-Installation Validations

Before installing the Oracle Utilities Analytics Warehouse, you must ensure that the following tasks have been completed in the target system:

  • All pre-requisite software for Oracle Utilities Analytics Warehouse is installed
  • Target database users and roles have been created
  • Target database parameters are set

This section covers all pre-installation checks and provides a list of input parameters required for the installation of Oracle Utilities Analytics Warehouse. Validate each scenario in this section and be sure that you have the values for these parameters readily available before running the Oracle Utilities Analytics Warehouse installer.

Platform Versions (Target Database and Application Server)

After the installation of all pre-requisite software and required technologies needed for Oracle Utilities Analytics Warehouse, you must validate each of the following scenarios. For information about all pre-requisite software and source applications supported by Oracle Utilities Analytics Warehouse, see the Oracle Utilities Analytics Quick Install Guide.

# Validation Script or Steps to Validate
A01 Validate installed JDK version is same or higher in both database and application server.

Run this command after setting the java home path in both the target application and the database server. Make sure that the JDK version is the same or higher.

./java -version
A02 Validate the target database version.

Run this query in the target database as a SYS user:

select version_full from v$instance;
A03 Validate Oracle Fusion Middleware Infrastructure version for Oracle Data Integrator in the application server.

To validate the Oracle Fusion Middleware Infrastructure version:

  1. Login to Enterprise Manager console for the Oracle Fusion Middleware Infrastructure for Oracle Data Integrator.
  2. Navigate to <username> → About Fusion Middleware Control.
A04 Validate Oracle Data Integrator version in the application server.

To validate the Oracle Data Integrator version:

  1. Login to ODI Studio client.
  2. Navigate to Help→About.
A05 Validate Oracle GoldenGate version in the database server.

Oracle recommends installing the Oracle GoldenGate in the database server for better performance.

Open the GoldenGate Microservices command line interface (adminclient) and validate that the version displayed in the command line interface is the same as what is included in the product support matrix.
A06 Validate all GoldenGate services of the Target server are running.

To validate that all GoldenGate services are running:

  1. Log in to the Target Service Manager URL of GoldenGate Microservices.
  2. On the Home screen, verify that the status of all services are "Running".
A07 Validate Oracle Fusion Middleware Infrastructure version for Oracle Analytics Server in the application server.

To validate Oracle Fusion Middleware Infrastructure version:

  1. Login to Enterprise Manager console for the Oracle Fusion Middleware Infrastructure for Oracle Analytics Server.
  2. Navigate to <username> → About Fusion Middleware Control.
A08 Validate Oracle Analytics Server version in the application server.

To validate Oracle Analytics Server version:

  1. Login to Oracle Analytics Server using the DV url. For Example: http://<server name>:<port number>/dv/.
  2. Navigate to <username> → About option on the home page.
  3. Check the version number that appears.
A09 Validate Oracle Application Express version in the database server.

Run the following query in the target database as a SYS user:

select version from dba_registry where comp_name = 'Oracle Application Express';
A10 Validate Oracle Fusion Middleware Infrastructure version for Oracle Application Express in the application server.

To validate the Oracle Fusion Middleware Infrastructure version for APEX:

  1. Login to Enterprise Manager console for the Oracle Fusion Middleware Infrastructure for Oracle Application Express.
  2. Navigate to <username> → About Fusion Middleware Control.
A11 Validate all mandatory patches for all pre-requisite software has been applied to their respective servers.

To validate that all required patches have been applied:

  1. Find the list of patches for Oracle Data Integrator, Oracle GoldenGate, Oracle Analytics server, and Oracle Database from the product support matrix.
  2. Navigate to the OPatch directory of the installed software.
  3. Run the 'opatch lsinventory' command.
  4. Validate that all required patches have been applied.
A12 Validate system parameter enable_goldengate_replication is set to true for data replication using Oracle GoldenGate.

Run the following query in the target database as a SYS user and ensure that the value TRUE is displayed:

select value from v$parameter where name ='enable_goldengate_replication';
A13 Validate the Database Client is installed in the application server. If the application server and database server are separate, then the Database Client must be installed on the application server because it holds binaries that are required for the applications on the application server (ODI, OAS, APEX) to connect to the database. Make sure that Oracle Database Client is installed in the application server.
A14 Validate the Oracle Utilities Analytics Warehouse package that you download from eDelivery is placed into a temporary directory and unzipped.

To validate:

  1. Verify that the directory named /.../OUA/tmp is present.
  2. Verify that Oracle Utilities Analytics Warehouse product software is present under /.../OUA/tmp directory and unzipped.
  3. /.../OUA will be the home path for Oracle Utilities Analytics Warehouse product.

Note:

Oracle Utilities Analytics database and administration tool component will be deployed in the target database server. ETL and analytics component will be deployed in the target application server. Hence, this path should be present in both target database and application server.

Database Users and Parameters (Target Database)

Oracle Utilities Analytics Warehouse deploys its components into a target data warehouse. It requires predefined users and roles to deploy its components. These scenarios will ensure all required users and roles are present before running the installer to install the product.

# Validation Script or Steps to Validate
A15

Validate that these required roles have been created in the target database:

DW_USER: All grants for read/write access to objects in the DWADM schema.

DW_READ: All grants for read only access to objects in the DWADM schema.

DW_PRIVS_ROLE: Contains the privileges necessary for the proper functioning of Oracle Utilities Analytics Warehouse.

DW_REPLICATE: The privileges necessary for this role are automatically assigned by Oracle Utilities Analytics Warehouse at the time of source configuration.

Run the following query in the target database as a SYS user and validate that for each role one record should be present:

select role from dba_roles where role in ('DW_READ','DW_USER', 'DW_PRIVS_ROLE', 'DW_REPLICATE');
A16

Validate that these required users have been created in the target database. Also validate their privileges.

RELADM: Administration metadata schema for storing the product version and component installation information.

MDADM: Metadata schema for storing metadata for ETL.

DWADM: Target schema for dimensions and facts.

DWSTAGE: Intermediate schema for data transformation.

OUA_MASTER: Repository schema for ODI topology and security.

OUA_WORK: Repository schema for ODI ELT code.

DWUSER: User to read/write DWADM Schema objects.

DWREAD: User to read DWADM Schema objects.

Run the following query in the target database as a SYS user and validate the following:

select grantee, granted_role from dba_ role_privs where grantee in ('RELADM','MDADM','DWADM', 'DWSTAGE','OUA_WORK','OUA_MASTER', 'DWUSER','DWREAD') Order by grantee;
  1. Except DWREAD user, all other users and schemas should have RESOURCE and CONNECT roles.
  2. In addition, MDADM user should have the following roles:
    • IMP_FULL_DATABAS
    • EXP_FULL_DATABASE
    • SELECT_CATALOG_ROLE
    • DW_PRIVS_ROLE
  3. In addition, DWADM user should have DW_READ role.
  4. In addition, DWUSER user should have DW_READ, and DW_USER roles.
  5. DWREAD user should have CONNECT and DW_READ roles.
A17 Validate that default tablespace should exist for MDADM, DWADM, RELADM, OUA_MASTER, OUA_WORK, and DWSTAGE schema.

Run the following query in the target database as a SYS user and validate that a default tablespace exists for each schema:

select u.username,p.privilege, p.admin_option,u.default_tablespace from dba_sys_privs p ,dba_users u where p.privilege ='UNLIMITED TABLESPACE' and p.admin_option='YES' and p.grantee=u.username order by grantee;

Use this query result to validate scenarios A22, A23, and A24.
A18 Validate that the tablespace mentioned in the storage.xml file is the same as the default tablespace for RELADM. The Oracle Utilities Analytics Warehouse package that you download from eDelivery will include the file storage.xml.

Follow the below steps to validate this scenario:

  1. Open the storage.xml file available in the installer package (Path: /../OUA/tmp/BI<Product Version>/RELADM/Install-Upgrade).
  2. Ensure that the default tablespace for RELADM is present in the file.
A19 Validate that the tablespace mentioned in the storage.xml file is the same as the default tablespace for MDADM.

To validate this scenario:

  1. Open the storage.xml file available in the installer package (Path: /../OUA/tmp/BI<Product Version>/MDADM/Install-Upgrade).
  2. Ensure that the default tablespace for MDADM is present in the file.
A20 Validate that the tablespace mentioned in the storage.xml file is the same as the default tablespace for DWADM.

To validate this scenario:

  1. Open the storage.xml file available in the installer package (Path: /../OUA/tmp/BI<Product Version>/DWADM/Install-Upgrade).
  2. Ensure that the default tablespace retrieved for DWADM is present in the file.
A21

Validate that the following DB parameters are configured properly:

Open cursor limit to 3000

Processes to 1000

Target database character set and time zone must be the same as source database character set and time zones.

Run the following query in the target database as a SYS user and validate that the parameter value for processes is greater than 1000, and the valid parameter value for open_cursors is greater than 3000.

select name,value from v$parameter where name in ('processes', 'open_cursors')

Run the following query in both source and target database as a SYS user and validate that database character set and time zone must be the same:

select current_timestamp from dual; select * from nls_database_parameters where parameter in ('NLS_NCHAR_CHARACTERSET','NLS_CHARACTERSET');

After completing the pre-installation checks above, you can install Oracle Utilities Analytics Warehouse using the installer.