ETL Validator support Oracle database or PostgreSQL as repository/workschema. This post focuses on the Oracle database requirements and configuration recommendations for ETL Validator. 

ETL Validator requires two database schemas - one for the repository and one for workschema. The repository holds the test case and execution metadata while workschema holds the query results pulled from the source and target connections. While ETL Validator supports Oracle or Postgresql as repository and workschema, the focus of this solution is provide the database requirements and grants for the Oracle repository/workschema. 

Oracle database version supported : Oracle 11g, Oracle 12c, Oracle 18c & Oracle 19c

Note: In case of oracle 18c or 19c:

• 64 bit oracle client should be installed where ever ETL Validator client is installed.

Sample Repository user creation script (Ex: qprobe) :
CREATE USER qProbe IDENTIFIED BY qprobe;
GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TYPE, CREATE SYNONYM TO qprobe;
ALTER USER qprobe DEFAULT ROLE NONE;


ALTER USER qprobe quota unlimited on USERS;


Sample Workschema user creation script (Ex: qprobews) :

CREATE USER qProbews IDENTIFIED BY qprobews; GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TYPE, CREATE SYNONYM TO qprobews;
ALTER USER qprobews DEFAULT ROLE NONE;

ALTER USER qprobews quota unlimited on USERS;


The following queries can be used to check the privileges for a database users created for Repository and Workschema in Oracle database: 

SELECT * FROM USER_SYS_PRIVS; 

SELECT * FROM USER_ROLE_PRIVS;

Additional Privileges

ETL Validator makes use of the following system packages for test creating and execution in Oracle. So the repository and workschema database users will need 'Execute' privilege on them:

DBMS_LOB
DBMS_XMLGEN
DBMS_SQL
DBMS_OUTPUT
DBMS_STATS
UTL_RAW
DBMS_OBFUSCATION_TOOLKIT
EXECUTE IMMEDIATE

Table Space Recommendation: 

Repository : 50 GB

Workschema : 500 GB (varies based on the volume of data being tested)


A test case comparing 1 million records with 30 columns requires about 0.5 GB table space. The table space sizing can be estimated using this a guideline. ETL Validator also provides a 'PURGE' option (Admin Settings) that can be used to purge data related to test cases that are taking up large space. 

Recommendations for Oracle Database init.ora parameters: 

ETL Validator consumes database resources similar to a data warehouse usage. So it is recommended that the Oracle init.ora parameters be configured to the recommended 'Data Warehouse' template.

While the parameter values are heavily depended on the available physical memory and the size of data being compared, below is a general guidance for the key init.ora parameters assuming  Automatic Memory Management is enabled in the Oracle database

SGA_TARGET : 4 GB

PGA_AGGREGATE_TARGET : 4 GB (same as sga_target for data warehouse)

MEMORY_TARGET : 10 GB

Note: When Automatic Memory Management is enabled and memory_target is set to a positive value, the PGA_AGGREGATE_TARGET and SGA_TARGET values act as the minimum value for the size of the instance PGA and SGA.

Oracle DBA can monitor the maximum memory consumed by the PGA by using the below query

select value from v$pgastat where name='maximum PGA allocated';

The MEMORY_TARGET parameter can be adjusted accordingly. 

memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)

Encoding

In general, the encoding (NLS_CHARACTERSET) should be same as your source and target database. We recommend using UTF8 for the ETL Validator repository/workschema even if your current source is ASCII.