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 = FALSE
(This parameter must be set to FALSE for Argus Insight to be able to create the database links.)
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, "Creating 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>/<SID> | Yes | db_connect_string=abc.in.oracle.com:1521/811DB |
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_mart_hist_user=apr_hist/pwd |
appschema_rls_user | Argus Insight VPD schema Owner | Yes | appschema_ai_link_user=apr_link/pwd |
appschema_mart_app_user | Argus Insight Application schema Owner | Yes | appschema_rls_user=rls_user/pwd |
appschema_mart_hist_user | Argus Insight History schema Owner | Yes | appschema_mart_app_user=apr_app/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_db=AS_DBA_USER/pwd@SAF811DB |
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/811DB> |
#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 Parameters | Specifies the details of the tablespace | Yes |
|
LOB Storage | Location of Argus Insight LOB storage.
Possible Values:
|
Yes | lob_storage=basicfile |
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 |
GLOBAL_NAME |
FALSE |
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.
loadjava should be working on the machine.
From the command prompt, execute loadjava.
Java 1.8 or higher must be installed and Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files 8 is applied.
Note that the Liquibase installer supports both JRE 32 and 64 bit.
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 and JDK installation security folder with the local_policy.jar and US_export_policy.jar shipped in jce_policy-8.zip.
For example:
Location of Java JRE and JDK 32-bit.
C:\Program Files (x86)\Java\jre1.8.0_121\lib\security
C:\Program Files (x86)\Java\jdk1.8.0_121\jre\lib\security
Or, you may install both JRE and JDK 64-bit. To do so, perform the same steps.
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 DBInstaller\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
Role to be associated with 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.
C:\Program Files (x86)\Oracle\ArgusInsight\Database\DBInstaller\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 DBInstaller\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, SYSTEM or 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:
C:\Program Files (x86)\Oracle\ArgusInsight\Database\DBInstaller\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 Web Server.
Navigate to Programs > Oracle > Argus Insight > Database > DBInstaller.
Open Dbinstaller\dbinstaller.properties file.
Modify the following parameters:
Insight Database:
db_connect_string—connects to the Argus Insight database.
Syntax: db_connect_string=<host>:<port>/<SID>
For example, server.us.xx.com:1521/AI811MT
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, "Modify of the 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 Database > DBInstaller > 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 (without the .CTL extension)
For example, VLDN_APR_AI_8.1.1
Enter the destination where the log file is to be placed:
<C:\AI_811\Database\DBInstaller\ValidateSchema>
Enter the log file name for recording the schema differences:
<VLDN_APR_AI_8.1.1_diff.log>
Enter Validation Output File Name: The validation output file name to record the validation progress:
<VLDN_APR_AI_8.1.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:
C:\Program Files (x86)\Oracle\ArgusInsight\Database\DBInstaller\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 DBInstaller\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:
DBInstaller\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 DBInstaller\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 Role in Safety Database: <Insight Read-only role, created in Argus Safety>
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.
Verify the log files for status from:
DBInstaller\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 DBInstaller\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 DB_LINK_ARGUS appears for each Stage, Mart, and App user.
Press Exit to close the Insight to Argus Database Link Creation window.
Verify the log files for status from:
DBInstaller\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, "Extracting, Transforming, and Loading Data."
If Argus Insight and Argus Mart are running on the same database, provide additional grants for this database instance.
Navigate to DBInstaller\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:
C:\Program Files (x86)\Oracle\ArgusInsight\Database\DBInstaller\Utilities\am_grants\AM_GRANTS_ MMDDYYYY_HH24MISS.log
Note:
After execution of the utility, Argus Insight schema validation file will reflect additional privileges.