Install Database Objects
Change Installation Configuration
-
In
<OHI_ROOT>/util/install
, make a copy ofohi_install.cfg.template
and name itohi_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.
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.
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 );
|
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.