Install Database Objects

Change Installation Configuration

In <OHI_ROOT>/util/install, make a copy of ohi_install.cfg.template and name it ohi_install.cfg.
Edit ohi_install.cfg to contain a specific database connection data and other configuration settings. The settings are explained in the file itself.

By default, the schema passwords will be similar to the schema usernames. The ohi_install.cfg files allows the specification of different passwords. Alternatively, specify empty string passwords to have the option of entering the passwords at the command prompt. In the latter case, the passwords will not end up in a configuration file.

Default schema passwords should not be used.

Oracle recommends that schema passwords are entered at the command prompt. Never store passwords in configuration files.

Copy Fresh Install Dump File

Open a terminal window and change the working directory to <OHI_ROOT>/database/install
Unzip the <application>_fresh_install.zip file located in that directory
Move the <application>_fresh_install.dmp file to the directory defined as OHI_DPDUMP_DIR in the database

Run Installer

Open a terminal window and change the working directory to <OHI_ROOT>/util/install.
Run the installer.

Table 1. Run Installer
Option		Argument	Description
Short	Long
-c	--cfg	config file path	The location of the configuration file. Default is ohi_install.cfg
-e	--env	environment name	The name that specifies which of the environment settings from the config file to use

Install Seed Data

Part of the database objects installation is the installation of Seed Data.

Types of Seed Data

Generic Seed Data is maintained by Oracle. Customers should not change this data. It is delivered as part of a release and may be updated by software upgrades.

Localization Seed Data covers specific data that is required by localization. The data is maintained by Oracle. Examples:

Sample Data

Sample data is provided by Oracle to give a headstart during configuration. Choose to install this data during a fresh install or upgrade. It is not modified during future upgrades. To load sample data, adjust the appropriate parameter in the ohi_install.cfg configuration file. To not load sample data for a certain table, remove the unwanted file from the database/sample_data directory prior to running the install/upgrade process.

Restrictions on Using Seed Data

Because Seed Data is maintained by Oracle, it may be modified or even deleted as part of an upgrade. Customers should therefore exercise caution when using seed data in their configuration by abiding these rules.

Violations of the rules above (especially rule 3) may lead to failures during the installation of upgrades.

An overview of seeded data is provided in Appendix - Seed Data.

Populating Installation Metadata in `OHI$DEPLOYMENT_METADATA`

The Oracle Health Insurance (OHI) installer automatically creates or updates deployment metadata in the OHI$DEPLOYMENT_METADATA database table during installation and upgrade operations. This process ensures consistent tracking of installation details across environments and deployments.

Deployment Metadata Capture

During OHI installation, the installer records the following key information in the OHI$DEPLOYMENT_METADATA table:

application_name: Identifier of the deployed application.
application_version: Version of the application being installed or upgraded.
deployment_type: Indicates type of deployment environment, that is, CLOUD or ON-PREM.
ind_prod: Indicates whether the environment is production (Y) or nonproduction (N).
last_installation_date: Timestamp of the most recent installation or upgrade.
customer_timezone: Time zone context used during installation.

During an OHI upgrade, the installer updates only the following fields:

application_version
last_installation_date

This metadata provides visibility into deployment characteristics and supports auditing, troubleshooting, and environment management.

Configuration Parameters

The following parameters let you customize deployment metadata and installation behavior.

timeZoneOverride

Description

Specifies the time zone that the installer uses during execution.

If you do not set this parameter, the installer uses the time zone of the underlying Java process or operating system. During a fresh installation, the configured value is stored in OHI$DEPLOYMENT_METADATA.

After installation is complete, you cannot change the time zone value stored in OHI$DEPLOYMENT_METADATA.

This setting can affect timestamps generated during the installation or upgrade process. For example, the setting can affect the creation_date value of newly created seed data.

Format

The value must follow the Java ZoneId format.

Example

timeZoneOverride = 'America/New_York'

For more information, see Java ZoneId documentation

deploymentType

Description

Defines the deployment environment type.

Supported Values

CLOUD
ON-PREM

Default Value

ON-PREM

Example

deploymentType = 'CLOUD'

isEnvironmentProduction

Description

Indicates whether the target environment is a production environment.

Supported Values

Y: Production environment
N: Non-production environment

Default Value

Example

isEnvironmentProduction = 'Y'

Behavior Summary

During every installation or upgrade operation, the installer performs the following actions:

Reads configuration values from the ohi_install.cfg file.
Determines applicable values, either default or user-provided.
Inserts or updates a record in the OHI$DEPLOYMENT_METADATA table.

Configure node affinity for Oracle AQ

If Oracle Real Application Clusters (RAC) database option is used to achieve high-availability, then configure node affinity for Oracle AQ queue table OHI_TASK_QUEUE_TABLE.

To identify the owner instance of the AQ queue table, run the following query as application owner

select owner_instance
,      primary_instance
,      secondary_instance
from   user_queue_tables
where  queue_table = 'OHI_TASK_QUEUE_TABLE'
;

Execute the following as user with DBA privileges:

execute dbms_aqadm.alter_queue_table (
   queue_table => 'OHI_CLAIMS_OWNER.OHI_TASK_QUEUE_TABLE',
   primary_instance => 1,
   secondary_instance => 2
);

Change OHI_CLAIMS_OWNER (in queue_table parameter) to suit the application.
Change the values of primary_instance and secondary_instance parameters to suit the environment/RAC configuration. Use the value of the column owner_instance from the above query for primary_instance parameter

Enable Total Recall (Optional)

When Total Recall Option is activated, decide on whether one or more of the new tables should be added to a Flashback Data Archive.

Syntax to enable history tracking for a table is:

ALTER TABLE <tablename> FLASHBACK ARCHIVE [<Flashback Data Archive name>];

Note that the FDA name is required only when adding the table to a non-default FDA.

To disable history tracking for a table use:

ALTER TABLE <tablename> NO FLASHBACK ARCHIVE;

For convenience, the example script helps configure archived tables:

import groovy.sql.Sql
import java.util.logging.*

def logger = Logger.getLogger(this.class.getName())

def config = new Config(args, 'setFlashbackArchive.cfg')
// Override the default level for the top-level logger
Logger.getLogger("").setLevel(config.log.level)
config.log()

def db = Sql.newInstance( config.db.url
                        , config.db.user
                        , config.db.passwd
                        , 'oracle.jdbc.driver.OracleDriver')
this.db = db

// Get the set of tables For which FBA needs to be switched off
def getTablesToSwitchOff(tables) {
    def tableList = tables.join("', '")
    switch_off = db.rows("""select fba.owner_name as owner
                            ,      fba.table_name
                            from   dba_flashback_archive_tables fba
                            where  fba.status = 'ENABLED'
                            and    fba.owner_name||'.'||fba.table_name not in ('""" + tableList + """')
                        """)
}

for (table in config.flashback.tables) {
    switch_on = db.firstRow("""select fba.owner_name as owner
                               ,      fba.table_name
                               from   dba_flashback_archive_tables fba
                               where  fba.owner_name||'.'||fba.table_name = $table
                               and    (  fba.status is null
                                      or fba.status != 'ENABLED'
                                      )
                            """)
    if (switch_on) {
        logger.info "Switching Flashback Archiving on for table ${table}"
        def stmt = "alter table " + table + " flashback archive ${config.flashback.archive}"
        try {
            println stmt
            db.execute(stmt)
        } catch (java.sql.SQLException e) {
            logger.warning "Error occurred while executing SQL " + stmt
            logger.warning "Error was " + e.getMessage()
            println "Unable to set Flashback Archive to ${config.flashback.archive} for table ${table} " + e.getMessage()
            println "Press enter to continue"
            new InputStreamReader(System.in).readLine()
        }
    }
}

// For every table that is not in the config-list but has FBA turned on, a confirmation is asked before actually
// turning off FBA
getTablesToSwitchOff(config.flashback.tables).each { it ->
    def tableName = "${it.owner}.${it.table_name}"
    println "Switching Flashback Archiving OFF for table ${tableName}"
    println "Are you sure you want to do that? This will purge all history for this table!! (Y/N)"
    response = new InputStreamReader(System.in).readLine()
    while (response.toUpperCase() != "Y" && response.toUpperCase() != "N") {
        println "Please respond with \"Y\" or \"N\""
        response = new InputStreamReader(System.in).readLine()
    }
    if (response.toUpperCase() == "Y") {
        def stmt = "alter table " + tableName + " no flashback archive"
        try {
            println stmt
            db.execute(stmt)
            logger.warning "Flashback Archiving was switched off for table ${tableName} as per users request"
        } catch (java.sql.SQLException e) {
            logger.warning "Error occurred while executing SQL " + stmt
            logger.warning "Error was " + e.getMessage()
            println "Unable to switch off Flashback Archive for table ${tableName} " + e.getMessage()
            println "Press enter to continue"
            new InputStreamReader(System.in).readLine()
        }
    } else {
        println "Flashback Archiving was NOT switched off for table ${tableName}."
        logger.warning "Flashback Archiving was NOT switched off for table ${tableName} as per users request"
    }
}

class Config {
  private static Logger logger = Logger.getLogger(Config.class.getName())
  def configFile
  def db = [:]
  def log = [:]
  def flashback

  def Config(args, configFile) {
    this.configFile = configFile
    def parsedConfig
    def cl = new CliBuilder(usage:
             'setFlashbackArchive.groovy [-c configFile]')

    cl.h(longOpt:'help', 'Show usage information and quit')
    cl.c(argName:'configFile', longOpt:'cfg', args:1, required:false, 'Config file, default is ' + configFile)

    def opt = cl.parse(args)

    if (!opt) {
      // the parse failed, the usage will be shown automatically
      println "\nInvalid command line, exiting..."
      System.exit(-1)
    } else if (opt.h) {
      cl.usage()
      System.exit(0)
    }

    if (opt.c) {
      this.configFile = opt.c
    }
    try {
      parsedConfig = new ConfigSlurper().parse(new File(this.configFile).toURL())
    } catch (FileNotFoundException e) {
      logger.severe "Config file ${this.configFile} not found"
      System.exit(-1)
    }
    db = parsedConfig.db
    db.url = db.protocol + "//" + db.hostname + ":"  + db.port + "/"  + db.sid
    log = parsedConfig.log
    flashback = parsedConfig.flashback
    if (!flashback) {
      println("\nflashback is not set in Config file")
      System.exit(-1)
    }
  }

  def log() {
    logger.config "db=" + db.toString()
  }
}

This script works in conjunction with a configuration file that can be created by copying the following to a file named setFlashbackArchive.cfg and changing its settings to appropriate values:

import java.util.logging.Level
// Allowable values: SEVERE | WARNING | INFO | CONFIG | FINE | FINER | FINEST
log.level = Level.INFO

// user has to be a database user with the FLASHBACK ARCHIVE object privilege granted
// on the Archive used.
db {
  protocol = 'jdbc:oracle:thin:@'
  hostname = '<hostname>'
  port     = '<port>'
  sid      = '<sid>'
  user     = '<dbuser>'
  passwd   = '<dbpassword>'
}

// This Flashback Archive has to exist.
// It can be created by a SYSDBA or a user with
// The tables argument contains a list of tables that should be archived.
// Removing a table from this list, disables archive.
flashback {
    archive = '<name of FlashBack Archive>'
    tables = ['<owner.table1>', '<owner.table2>']
}

The script can be run using Groovy.

Install Database Objects

Change Installation Configuration

Copy Fresh Install Dump File

Run Installer

Install Seed Data

Types of Seed Data

Sample Data

Restrictions on Using Seed Data

Populating Installation Metadata in OHI$DEPLOYMENT_METADATA

Deployment Metadata Capture

Configuration Parameters

Behavior Summary

Configure node affinity for Oracle AQ

Enable Total Recall (Optional)

Populating Installation Metadata in `OHI$DEPLOYMENT_METADATA`