Install Database Objects

Change Installation Configuration

  1. In <OHI_ROOT>/util/install, make a copy of ohi_install.cfg.template and name it ohi_install.cfg.

  2. Edit ohi_install.cfg to contain your 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 user names. 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 your 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 your database

Run Installer

  • Open a terminal window and change your working directory to <OHI_ROOT>/util/install.

  • Run the 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

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 localizations. The data is maintained by Oracle. Examples:

Sample Data

Sample data is provided by Oracle to give you a headstart during configuration. You can opt 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.

Enable Total Recall (optional)

When Total Recall Option is activated, you should decide if 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, we provide an example script that can help configuring 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 = '<your hostname>'
  port     = '<your port>'
  sid      = '<your sid>'
  user     = '<your dbuser>'
  passwd   = '<your 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.
// If you remove a table from this list, archiving will be disabled.
flashback {
    archive = '<name of your FlashBack Archive>'
    tables = ['<owner.table1>', '<owner.table2>']
}

The script can be run using Groovy.