The Argus Insight data mart structure is created while installing Argus Insight through Liquibase in silent-mode.
Besides, it also creates a link between the source Argus database and the new Argus Insight data mart. The Extract Transform and Load (ETL) process uses this link to transfer data from Argus Safety database to the Argus Insight data mart for reporting purposes.
Liquibase is a refactoring tool that enables Argus Insight to be in synchronization with the closest major release, and subsequently upgrades the product to the required minor versions of the product.
Note:
The Argus Insight database must be created with the same character set as the Argus Safety database. Make sure you have installed the requisite software as per Section 1.2, "Software and Hardware Requirements".The GLOBAL_NAMES and NLS_LENGTH_SEMANTICS database parameters must be configured properly in order for the Argus Insight installation to run. You must check those settings before you run the Argus Insight Installation. If the parameters are not set properly, the installation will fail.
To review and modify these database settings:
Contact your database administrator (DBA).
Verify that the database configuration file for the Argus Insight database defines the following database parameter values:
GLOBAL_NAMES
Value of parameter GLOBAL_NAMES must be same in Argus Safety and Argus Insight (either TRUE for both or FALSE for both).
If GLOBAL_NAMES is set to TRUE, the Database Name reflected in the table GLOBAL_NAME, must have the same domain for both Argus Safety and Argus Insight databases (like, us.example.com).
NLS_LENGTH_SEMANTICS = CHAR
TNS Name for Argus Safety Database must be present in the Oracle Database Client > tnsnames.ora file at the following path:
...\network\admin\tnsnames.ora
Restart the database instance to reflect the changes.
To create a DBA user:
To use a different user than SYSTEM user to execute the installation, then create a DBA user by executing the DBA User Creation script:
ArgusInsight\Database\DBInstaller\Utilities\Create_Dba_User\ai_create_dba_user.bat
For more details on creating this user, see Section 3.4.3, "Create the DBA User."
Besides creating the DBA user, this batch file also provides minimum necessary privileges required for executing the installation.
Open the DBInstaller\dbinstaller.properties file, and view or modify the following parameters to make sure that Liquibase runs properly.
Parameter | Description | Modify | Default or Sample Value |
---|---|---|---|
db_connect_string | Database instance details in the form of <host>:<port>/<service name> | Yes | db_connect_string=abc.in.oracle.com:1521/AI821DB
Or, db_connect_string=abc.in.oracle.com:1521/AI821DB.IN.ORACLE.COM |
dba_user | Name of the Target Database DBA user | Yes | dba_user=ai_dba_user |
log_level | Log level setting related to liquibase for smooth run.
Possible values:
|
Yes | log_level=info |
argus_securekey_path | Complete directory path of Argus Secure Key ini file on the middle-tier. | Yes | argus_securekey_path=c:/windows |
appschema_mart_user | Argus Insight Mart schema Owner | No | appschema_mart_user=apr_mart/pwd |
appschema_stage_user | Argus Insight Staging schema Owner | Yes | appschema_stage_user=apr_stage/pwd |
appschema_ai_link_user | Argus Insight Link User | Yes | appschema_ai_link_user=apr_link/pwd |
appschema_rls_user | Argus Insight VPD schema Owner | Yes | appschema_rls_user=rls_user/pwd |
appschema_mart_app_user | Argus Insight Application schema Owner | Yes | appschema_mart_app_user=apr_app/pwd |
appschema_mart_hist_user | Argus Insight History schema Owner | Yes | appschema_mart_hist_user=apr_hist/pwd |
appschema_ai_apr_user | Argus Insight apr User Name | No | appschema_ai_apr_user=apr_user/pwd |
appschema_ai_login_user | Argus Insight Login User Name | Yes | appschema_mart_user=apr_mart/pwd |
ai_role | Argus Insight Mart Role | Yes | ai_role=MART_ROLE |
ai_app_role | Argus Insight Mart App Role | Yes | ai_app_role=APP_ROLE |
ai_link_role | Argus Insight Mart Link Role | Yes | ai_link_role=LINK_ROLE |
safety_db | Argus Safety database instance name | Yes |
|
safety_ro_user | Argus Insight read only user in Safety DB | Yes | safety_ro_user=insight_ro_user |
#Default Datafiles Directory | Default location of the datafiles. on the Database Server | Yes | default_datafile_destination
=<c:/app/oradata/821DB> |
#Insight Stage Normal Datafiles | Location of Argus Insight normal datafiles. | Yes |
|
#Insight Stage ESM Datafiles | Location of Argus Insight ESM datafiles. | Yes |
|
#Insight Stage WHO Datafiles | Location of Argus Insight WHO datafiles. | Yes |
|
#Insight Mart Case/Rpt Datafiles | Location of Insight Mart case and report datafiles. | Yes |
|
#Insight Mart Non-Case/Rpt Datafiles | Location of Insight Mart non-case and report datafiles. | Yes |
|
#Insight Mart EDM Datafiles | Location of Insight Mart EDM datafiles | Yes |
|
#Insight Mart WHO Datafiles | Location of Insight Mart WHO datafiles | Yes |
|
#Insight Hist Case/Rpt Datafiles | Location of Argus Insight case or report History datafiles | Yes |
|
#Insight Hist Non-Case/Rpt Datafiles | Location of Argus Insight non-case and report History datafiles. | Yes |
|
#Insight Apr Datafiles | Location of Argus Insight APR datafiles | Yes |
|
Default and Temporary tablespaces | Defines default and temporary tablespace name | Yes |
|
Tablespace Encryption | Specifies the logic used for default encryption | Yes | tablespace_encryption=<blank> or <text>, where
|
Tablespace Parameters | Specifies the details of the tablespace | Yes |
|
Table 3-1 lists the database parameters and the values that must be set for Argus Insight.
For those parameters that require a numeric value, Table 3-1 lists the minimum value recommended. You may need to increase the value depending on your system configuration and the number of cases. It is the responsibility of the database administrator to monitor the system and adjust the database parameters as necessary.
Table 3-1 Database Parameters for Argus Insight
Database Parameter | Required Value |
---|---|
COMPATIBLE (for Oracle 12c R1) |
12.1.0.2 or later |
CURSOR_SHARING |
EXACT |
JOB_QUEUE_PROCESSES |
10 (Minimum value recommended) |
NLS_LENGTH_SEMANTICS |
CHAR |
OPTIMIZER_MODE |
ALL_ROWS |
OPTIMIZER_SECURE_VIEW_MERGING |
TRUE |
PARALLEL_MAX_SERVERS |
Minimum value recommended based on the total number of cases:
|
PGA_AGGREGATE_TARGET |
Minimum value recommended based on the total number of cases:
|
QUERY_REWRITE_ENABLED |
TRUE (if computing statistics regularly) FALSE (if not computing statistics regularly) |
SGA_MAX_SIZE |
Greater than or equal to the value of the SGA_TARGET parameter. |
SGA_TARGET |
Minimum value recommended based on the total number of cases:
The 32-bit architecture allows for 4 GB of physical memory to be addressed. DBAs should verify the maximum addressable RAM for their respective architectures. |
UNDO_MANAGEMENT |
AUTO |
WORKAREA_SIZE_POLICY |
AUTO |
DB_BLOCK_BUFFERS (in MB) / |
Leave set to the Oracle default value |
DB_BLOCK_SIZE (in bytes) |
Leave set to the Oracle default value |
QUERY_REWRITE_INTEGRITY |
Leave set to the Oracle default value |
SHARED_POOL_SIZE |
Leave set to the Oracle default value |
Table 3-2 Recommended Database I/O Configuration for Argus Insight
Total Number of Cases | ||||
---|---|---|---|---|
Database I/O Configuration | Small (< 30,000) | Medium (30,000 to 200,000) | Large (200,000 to 1,000,000) | Extra Large (> 1,000,000) |
Number and Size of Redo Log Files |
Default |
3 X 500 MB |
5 X 500 MB |
5 X 500 MB |
The value depends on the characteristics of the I/O subsystem such as the I/O bandwidth, storage disks type, and RAID level. (Oracle recommends RAID 1+0 or similar.) |
||||
TEMP Tablespace Size |
32 GB |
32 GB |
64 GB |
128 GB |
UNDO Tablespace Size |
16 GB |
32 GB |
64 GB |
128 GB |
The recommended UNDO tablespace size is based on the projections with the following two parameter values: RETENTION=NOGUARANTEE UNDO_RETENTION=900 (seconds) |
Table 3-3 Recommended Configuration for the Argus Insight Database Server
Total Number of Cases | ||||
---|---|---|---|---|
Database Server Configuration | Small (< 30,000) | Medium (30,000 to 200,000) | Large (200,000 to 1,000,000) | Extra Large (> 1,000,000) |
RAM |
4–8 GB |
8–16 GB |
16–32 GB |
16–32 GB |
CPU |
Equivalent to 2–4 Dual Core, 3 GHz |
Equivalent to 4–8 Dual Core, 3 GHz |
Equivalent to 8–12 Dual Core, 3 GHz |
Equivalent to 8–12 Dual Core, 3 GHz |
Note:
The Argus Insight Database and Argus Safety Database TNS names entry must be available in both Argus Insight Database Server and Argus Safety Database Server. Argus Safety Database TNS should also be present in the Argus Insight Web Server.Make sure:
an Oracle client with Administrator option is installed on the server.
database TNS entry should be added in the TNSNAMES.ora file.
login machine user should have administrative privileges.
Download the jce_policy-8.zip file on your local machine from the following link:
.http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html (download jce_policy-8.zip).
Unzip the jce_policy-8.zip.
Replace local_policy.jar and US_export_policy.jar files present in all the Java JRE installation security folders with the local_policy.jar and US_export_policy.jar shipped in jce_policy-8.zip.
For example:
Location of Java JRE 64-bit.
C:\Program Files\Java\jre1.8.0_161\lib\security
From the command prompt verify that Java is properly installed by executing:
java -version
If no Java version appears, check the environment variables settings and path system variables have correct Java installation path set.
To set Java Installation Path:
Right-click the My Computer (or Computer) icon, and from the drop-down menu select Properties.
From the left-pane, select Advanced system settings.
The System Properties dialog box with Advanced tab appears.
In the Startup and Recovery section, click Environment Variables.
From the System variables section, scroll-down to Path variable, and double-click.
The Edit System Variable dialog box appears.
In the Variable value: field, enter the location where Java will be installed, and end it with a semi-colon (;).
Click OK to close the Edit System Variable dialog box.
Click OK to close the System Properties dialog box.
To create the Safety Read-only user for Insight, execute the following script from the folder <Argus Release Media>\Database\Argus Insight\Utilities\Create_Safety_Ro_User:
For Windows—Use the batch script ai_argus_read_only{user}.bat
For Linux—Use the shell script ai_argus_read_only{user}
When prompted, enter the following parameters:
Name of the Safety Database instance
Name of DBA User in Safety Database
DBA user password in Safety Database
Name of the Safety Read Only User to be created for Insight
Password of the Safety Read Only User
Default Tablespace for Safety Read-only User—For example, USERS
Temporary Tablespace for Safety Read-only User—For example, TEMP
The process of creating a Safety Read-only user begins.
Review the log file from the following path, and check for any errors, when a confirmation message appears.
<Argus Release Media>\Database\ArgusInsight\Utilities\Create_Safety_Ro_User\AI_AS_RO_User_MMDDYYYY_HH24MISS.log
Note:
You must execute this script to install Argus Insight database, even if you are using the SYSTEM user. This script provides additional grants to the existing user.To create a DBA user that has privileges same as the SYSTEM user for installation:
Execute the following script from the folder <Argus Release Media>\Database\ArgusInsight\Utilities\Create_Dba_User:
For Windows—Use the batch script ai_create_dba_user.bat
For Linux—Use the shell script ai_create_dba_user
When prompted, enter the following parameters:
Argus Insight Database instance name
SYS or an equivalent SYSDBA user on this database
SYSDBA user password
Name of the DBA User—For example, AI_DBA_USER
DBA user password
If you provide a non-existing user name, then the script creates this as a new user, and provides the necessary grants to this user.
If you provide an already existing user name, then the script provides the necessary additional grants to the existing user.
When done, a message appears as:
Created DBA user &dba_user
Press Exit.
Verify the log files for status information from:
<Argus Release Media>\Database\ArgusInsight\Utilities\Create_Dba_User\AI_DBA_User_ MMDDYYYY_HH24MISS.log
Recommendation:
For security reasons, Oracle recommends to drop the DBA user from the database after successful installation of Argus Insight as this user will have DBA privileges.To drop this user, connect to the respective database as a privileged user, and execute the following command:
DROP USER <INSTALL_USER> CASCADE;
Log in to the Argus Insight Transaction Server.
Navigate to <Argus Release Media>\Database\Argus Insight.
Open the dbinstaller.properties file.
Modify the following parameters:
Insight Database:
db_connect_string—connects to the Argus Insight database.
Syntax: db_connect_string=<host>:<port>/<service name>
For example, server.us.xx.com:1521/AI821MT
dba_user—specifies the name of the DBA user to run Argus Insight Liquibase Install.
default_datafile_directory—default location on the database server, where datafiles will be created.
Insight User of each schema, where password is optional:
appschema_mart_user
appschema_stage_user
appschema_mart_app_user
appschema_mart_hist_user
appschema_ai_link_user
appschema_rls_user
appschema_ai_apr_user
appschema_ai_login_user
Insight Roles:
ai_role—specifies Mart User roles
ai_app_role—specifies App User roles
ai_link_role—specifies Link User roles
Secure Key:
argus_securekey_path—path of the file insight.ini in the middle-tier machine from where the DBInstaller.bat is executed.
Safety Database:
safety_db—Argus Safety database instance name
safety_ro_user—Argus Insight Read-only user created in Argus Safety
Note:
For more information on these parameters, see Section 3.2, "Configure dbinstaller.properties file."It is recommended that you preserve the default names for tablespaces and roles.
From the command prompt, go to DBInstaller directory.
Type dbinstaller.bat, and press Enter.
The Liquibase install begins, and the parameters (as entered) appear on the command prompt screen with password in the hidden mode (****).
Keep monitoring the Liquibase progress by querying the Liquibase Log table insight_dbchangelog, created in APR_MART schema.
When the process is complete, a confirmation message appears with the latest version of Argus Insight.
The following is created as per the values specified in the dbinstaller.properties file:
Users
MART_USER
STAGE_USER
MART_APP_USER
MART_HIST_USER
AI_LINK_USER
RLS_USER
AI_APR_USER
AI_LOGIN_USER
Roles
AI_ROLE
AI_APP_ROLE
AI_LINK_ROLE
Factory Data
Out of the box Factory data is loaded into tables such as ETL_PROCEDURES, CMN_PROFILE_GLOBAL, etc.
Database Links, DB_LINK_ARGUS:
From STAGE_USER of Argus Insight to ARGUS_APP of Safety
From MART_USER of Argus Insight to ARGUS_APP of Safety
From MART_APP_USER of Insight to ARGUS_APP of Safety
Tablespaces
Note that the tablespace names begin with APR. The Argus Power Reports (APR) product was renamed to Argus Insight.
STAGE Schema Tablespaces | MART Schema Tablespaces | HIST Schema Tablespaces | APR User Tablespaces |
---|---|---|---|
APR_STAGE_DATA_01 | APR_MCAS_DATA_01 | APR_MCAS_HIST_DATA_01 | APR_USER_DATA_01 |
APR_STAGE_INDEX_01 | APR_MCAS_INDEX_01 | APR_MCAS_HIST_INDEX_01 | APR_USER_INDEX_01 |
APR_STAGE_LOB_01 | APR_MCAS_LOB_01 | APR_MCAS_HIST_LOB_01 | APR_USER_LOB_01 |
APR_SESM_DATA_01 | APR_MCFG_DATA_01 | APR_MFACT_HIST_DATA_01 | |
APR_SESM_INDEX_01 | APR_MCFG_INDEX_01 | APR_MFACT_HIST_INDEX_01 | |
APR_SESM_LOB_01 | APR_MCFG_LOB_01 | APR_MCFG_HIST_LOB_01 | |
APR_SWHOC_DATA_01 | APR_MEDM_DATA_01 | ||
APR_MEDM_INDEX_01 | |||
APR_MEDM_LOB_01 | |||
APR_MWHOC_DATA_01 |
Press Exit.
Navigate to <Argus Release Media>\Database\ArgusInsight\ValidateSchema.
Run the batch script validate_schema.bat.
When prompted, enter the following parameters:
Enter instance name: <Argus Insight Database Instance name>
Enter DBA User Name: <DBA user of Argus Insight>
Enter Password for DBA User: <DBA User password in Argus Insight>
Enter Validation Data File Name: The validation control file name:
<VLDN_APR_AI_8.2.1.CTL>
Enter the destination where the log file is to be placed:
<Argus Release Media\Database\ArgusInsight\ValidateSchema>
Enter the log file name for recording the schema differences:
<VLDN_APR_AI_8.2.1_diff.log>
Enter Validation Output File Name: The validation output file name to record the validation progress:
<VLDN_APR_AI_8.2.1.log>
Enter the password for the Argus Insight SYSTEM or DBA user, and press Enter.
When the validation process is complete, a confirmation message appears.
The log files are created at the following location:
<Argus Release Media>\Database\ArgusInsight\ValidateSchema\
To view any schema discrepancies, such as missing objects, use the Difference Log File.
To view the list of errors, which occurred during schema validation, use the Output Log File.
Note:
If Argus Insight read-only user is created, then ignore the schema validation differences, where
Objects are RO% views/columns
GRANTEE is Argus Insight Read-only user
If Argus Insight and Argus Mart are installed on the same Database server, ignore the differences due to grants from APR_MART/RLS_USER of Insight to AM_MART_USER/AM_APP_USER of Argus Mart.
Open <Argus Release Media>\Database\ArgusInsight\Utilities\ Create_Insight_Ro_User file.
For Windows—execute the batch script ai_ro_user.bat
For Linux—execute the shell script ai_ro_user
When prompted, enter the following parameters:
Enter TNSNAME Entry to connect to the ARGUS INSIGHT Database: < ARGUS INSIGHT Database name>
Enter the name of Custom DBA user in Insight Database: < Argus Insight Install user>
Enter password for install user in Insight Database: <Install user password>
Enter Mart schema owner name in Insight Database: <Mart User of Insight>
Enter Argus Insight History schema owner name in Insight Database: <Mart Hist User of Insight>
Enter Read Only user to be created in Insight Database: <Read-only user to be created in Insight>
Enter password for Read Only user of Insight Database: <Insight Read-only user password>
Press Exit.
Verify the log files for status information from:
<Argus Release Media>\Database\Argus Insight\Utilities\Create_Insight_Ro_User\AI_RO_User_MMDDYYYY_HH24MISS.log
This link allows real-time updates of some of the values from Argus Console to Argus Insight data mart.
Create the reverse DB Link by executing the script file from <Argus Release Media>\Database\Argus Insight\Utilities\Database_Links\argus_to_mart.
For Windows—execute the batch script ai_argus_db_link_setup.bat
For Linux—execute the shell script ai_argus_db_link_setup
When prompted, enter the following parameters:
Enter the Argus Safety Database Tns Name: <Safety Database Instance Name>
Enter the name of Argus Safety schema owner in Safety Database Instance: <user account that owns the Argus Safety schema>
Enter the password for Argus Safety schema owner: <password of Argus Schema owner>
Enter the name of Argus Safety Role: < Argus Safety role>
Enter the name of Read only user in Safety Database: <Insight Read-only role, created in Argus Safety>
For example, <INSIGHT_RO_USER>
Enter the Argus Insight Database Tns Name: <Argus Insight database>
Enter the name of link user in Insight Database.: <link user of Argus Insight>
Enter the password for user Link user: <link user password>
Verify that the script is successfully connected as <Safety schema owner/Safety schema password>@<Argus Safety Database Name>, and press Enter.
When the confirmation message appears, press Enter.
Wait until a message Created <Insight DB Global Name> @ARGUS_TO_MART appears. This link name gets stored in the key DB_LINK_ARGUS_TO_MART of CMN_PROFILE_GLOBAL of the Argus Safety database.
Press Exit to close the window.
Verify the log files for status from:
<Argus Release Media>\Database\Argus Insight\Utilities\Database_Links\argus_to_mart\ ARGUS_TO_MART_DB_LINK_MMDDYYYY_HH24MISS.log
During Fresh installation, the database link from Argus Insight to Argus Safety (DB_LINK_ARGUS) is created as a part of the installation process. However, you can manually recreate the link DB_LINK_ARGUS.
Navigate to <Argus Release Media>\Database\Argus Insight\Utilities\Database_Links\db_link_argus.
For Windows—execute the batch script ai_create_db_link_argus.bat
For Linux—execute the shell script ai_create_db_link_argus
When prompted, enter the following parameters:
Enter the name of the Safety Database instance: <Safety Database Instance Name>
Enter the name of the Insight Database instance: <Insight Database Instance Name>
Enter the name of DBA User in Insight DB: <Insight dba user>
Enter the password for user &dba_user: <Insight dba user pwd>
Enter the name of the Read Only User in Safety DB: <Safety Read Only user for Insight>
Enter the password for Read Only user in Safety: <Read-only user pwd>
Enter the name of the stage owner in Insight: <Stage user of Insight>
Enter the password for Stage user: <Stage user password>
Enter the name of the mart owner in Insight: <Mart user of Insight>
Enter the password for Mart owner: <Mart user password>
Enter the name of the mart app owner in &insight_db: <App user of Insight>
Enter the password for app owner: <App user password>
Verify that the script is successfully connected as <Insight DBA user/Insight DBA user pwd>@<Argus Insight>, and press Enter.
Wait until a message Created <Safety DB Global Name> @DB_LINK_ARGUS appears for each Stage, Mart, and App user. This link name is stored in the key DB_LINK_ARGUS of CMN_PROFILE_GLOBAL in the Argus Insight database.
For example, a typical DB link is:
SAF821DB@DB_LINK_ARGUS
SAF821DB.US.CORP.COM@DB_LINK_ARGUS
Press Exit to close the Insight to Argus Database Link Creation window.
Verify the log files for status from:
<Argus Release Media>\Database\Argus Insight\Utilities\Database_Links\db_link_argus\DB_LINK_ARGUS_MMDDYYYY_HH24MISS.log
Navigate to DBInstaller folder.
Double click dbinstaller.exe.
Click Initial ETL.
Enter the parameters, and click OK.
Click Start ETL.
To monitor the ETL progress, execute query on tables:
staging_data_insert_log
mart_data_insert_log
For more details on ETL, see Chapter 6, "Extract, Transform, and Load Data."
If Argus Insight and Argus Mart are running on the same database, provide additional grants for this database instance.
Navigate to <Argus Release Media>\Database\Argus Insight\Utilities\ am_grants.
For Windows—execute the batch script am_grants.bat
For Linux—execute the shell script am_grants
When prompted, enter the following parameters:
Enter the TNS name to connect to SDM database: <Argus Insight Database Instance name>
Enter the name of dba user in Insight Database: <DBA user of Insight Database>
Enter Password for User install user: <DBA user password>
Press Exit when a status message appears as:
Execution of grants from Insight to Mart User and Mart App User completed
Verify the log files for status from:
<Argus Release Media>\Database\Argus Insight\DBInstaller\Utilities\am_grants\AM_GRANTS_ MMDDYYYY_HH24MISS.log
Note:
After execution of the utility, Argus Insight schema validation file will reflect additional privileges.