2.3.2 Create OHI Components database

Now create the OHI Components database. For this activity the following requirements and restrictions apply.

2.3.2.1 Character Set

The character set of an OHI Components database must be AL32UTF8.

2.3.2.2 Block size

Use an 8K block size.

2.3.2.3 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).

2.3.2.3.1 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.

2.3.2.3.2 Undo

For a production system, create an undo tablespace with an initial size of 16GB, auto-extendable to 64GB.

2.3.2.3.3 Redo log

For a production system, create 16 online redo log files of 3GB each.

2.3.2.4 Parameters

• NLS_LENGTH_SEMANTICS = CHAR
• DB_SECUREFILE = ALWAYS
• COMPATIBLE = 12.1.0.2
• DB_BLOCK_SIZE (BYTES) = 8
• SGA_TARGET = 48GB
• PGA_AGGREGATE_TARGET = 12GB
• PGA_AGGREGATE_LIMIT = 0
• NUMBER AND SIZE OF REDO LOG FILES =
• PROCESSES = 500
• OPEN_CURSORS = 300
• SESSION_CACHED_CURSORS = 100
• USE_LARGE_PAGES = ONLY
• FILESYSTEMIO_OPTIONS = 'SETALL'

Unless specified otherwise, keep all parameters default.

2.3.2.5 Required privileges

OHI Components 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 OHI Components 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;

2.3.2.6 Creating additional schemas in the database

Oracle recommends that the Oracle database instance that is used by OHI Components is used solely for the purpose of running OHI Components.

In the case that additional database schemas or roles are created in the Oracle database instance, make sure that these are NOT prefixed with OHI.

2.3.2.7 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

2.3.2.8 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 CAR_ARRANGEMENTS_B flashback archive fda1;

Configuring which tables to archive and how is considered a responsibility of the database administrator. Oracle recommends to enable archiving for the tables that result from the following query (execute as OHI Components application owner or as OHI Components application user):

select base_table
from   ohi_tables_b
where  ind_transportable = 'Y'
union
select translations_table
from   ohi_tables_b
where  ind_transportable = 'Y'
and    translations_table is not null
union
select dynamic_fields_table
from   ohi_tables_b
where  ind_transportable = 'Y'
and    dynamic_fields_table is not null
union
select dynamic_records_table
from   ohi_tables_b
where  ind_transportable = 'Y'
and    dynamic_records_table is not null
order by 1;

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;
/

2.3.2.9 Advanced Compression

OHI Components is certified to work with advanced compression: table compression for OLTP is supported. Note that this database option is licensed separately

2.3.2.10 Index Compression

OHI Components 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.