Install and Configure an OHI Database
Create Oracle Health Insurance database
Now create the Oracle Health Insurance database. For this activity the following requirements and restrictions apply.
Tablespaces and Redo log files
All tablespaces must be created using 12c default style (locally managed, system/uniform managed extent allocation, Automatic Segment Space Management).
Temporary tablespace
For a production system, create a default temporary tablespace TEMP (this name is mandatory) with an initial size of 32GB, auto-extendable to 128GB.
Parameters
-
NLS_LENGTH_SEMANTICS = CHAR
-
_like_with_bind_as_equality=TRUE
-
DB_SECUREFILE = ALWAYS
Unless specified otherwise, keep all parameters default.
Required privileges
Oracle Health Insurance use queues in the Oracle database. The owner of the queue objects, the applications owner schema, requires execute privileges on the SYS.DBMS_AQIN package.
For installing Oracle Health Insurance database artifacts the SYSTEM account is used. In this process also database grants are given by the system database user. To be able to do that, SYSTEM user needs GRANT ANY OBJECT PRIVILEGE (without grant option).
Thus, following commands should be executed as SYS:
connect sys as sysdba
GRANT EXECUTE ON sys.dbms_aqin TO system WITH GRANT OPTION;
GRANT EXECUTE ON sys.dbms_lock TO system WITH GRANT OPTION;
GRANT GRANT ANY OBJECT PRIVILEGE TO system;
GRANT EXECUTE on sys.dbms_system TO system WITH GRANT OPTION;
GRANT SELECT on sys.v_$parameter to system WITH GRANT OPTION;Access to the Oracle Health Insurance Specific Database Schemas
The Oracle Health Insurance specific database schemas and all database objects in it are an integral part of Oracle Health Insurance Applications. The data may be queried but should only be updated via published APIs like the application’s user interface and its HTTP APIs.
| Updating the data in the Oracle Health Insurance schemas in any other way is not supported. |
| Oracle may change the physical data model as it sees fit, for example in order to change the implementation of existing functionality or for adding new functionality to the application. As a result, queries executed directly against the OHI specific database schemas may fail after an upgrade of the application. |
If ad hoc queries against the data model are required, make sure to use the reporting views in Oracle Health Insurance applications that feature these.
Note that ad hoc queries impact the performance of Oracle Health Insurance applications. If database workload reports are required for diagnostic purposes, Oracle may request temporarily suspending the use of any ad hoc queries that do not originate from the Oracle Health Insurance application.
Creating additional schemas in the database
Oracle recommends that the Oracle database instance that is used by Oracle Health Insurance is used solely for the purpose of running Oracle Health Insurance.
In the case that additional database schemas or roles are created in the Oracle database instance, make sure these are NOT prefixed with OHI.
Schema privileges
Through the installation utility, database schemas will be created for the installed applications. These schemas will be granted privileges on the following SYS -owned objects:
Application Owner Schema:
-
AQ_ADMINISTRATOR_ROLE
-
EXECUTE ON CTX_DDL
-
EXECUTE ON DBMS_LOCK
-
EXECUTE ON DBMS_AQ
-
EXECUTE ON DBMS_AQADM
-
EXECUTE ON DBMS_AQ_BQVIEW
Application User Schema:
-
EXECUTE ON DBMS_AQIN
Set up Flashback Data Archive
The Flashback Data Archive (Total Recall) feature of Oracle Server is used to log changes to setup tables. Using this feature is important to trace set up or configuration changes, even at a later stage, to facilitate auditing and troubleshooting.
| Not using this feature severely limits auditing and traceability of setup or configuration changes. For example, determining the impact of certain configuration changes at a later stage without having Flashback enabled would require database backups to be restored. |
In order to use Flashback Data Archive the following settings need to be made:
-
The user that will be used to switch archiving on tables on and off should be granted the "FLASHBACK ARCHIVE ADMINISTER" privilege (grant FLASHBACK ARCHIVE ADMINISTER to <user>).
-
This user should be granted "ALTER TABLE" rights on the tables that need to be archived (or stopped being archived).
-
Create a separate tablespace for the Flashback Archive. Define this tablespace using "extent management local uniform size 40K". This 40K size (for 12c) has proven to be the optimal size for this tablespace.
-
Create a Flashback Archive, for example:
CREATE FLASHBACK ARCHIVE [DEFAULT] fda1 TABLESPACE tbs1 QUOTA 10G RETENTION 5 YEAR;
-
Subsequently, enable flashback on a per table basis, for example:
ALTER TABLE OHI_DYNAMIC_LOGIC_B flashback archive fda1;
Configuring which tables to archive and how is considered a responsibility of the database administrator.
After enabling Flashback Data Archive, it is recommended that the following script is run on a regular basis as a SYSDBA to reclaim storage in the flashback tablespace that is allocated by empty partition segments:
begin
for r_dfat in (select dfat.owner_name
, dfat.archive_table_name
from dba_flashback_archive_tables dfat
)
loop
dbms_space_admin.drop_empty_segments(r_dfat.owner_name, r_dfat.archive_table_name);
end loop;
end;
/Advanced Compression
Oracle Health Insurance is certified to work with advanced compression: table compression for OLTP is supported. Note that this database option is licensed separately
Index Compression
Oracle Health Insurance is certified to work with index compression. For index compression to work optimally, each index should be evaluated (periodically). When executing a 'validate index' statement Oracle will update the INDEX_STATS, showing the estimated optimal compression factor in the opt_cmpr_count column.
The value of this optimal compression factor is dependent on data distribution and can change over time.