Configuration Guidelines

This section describes the general recommendations for configuring various database objects and includes a brief syntax overview. It covers the general aspects of the database objects and does not cover any specific implementation requirements.

• Index

• Table Partitioning Recommendations

• Transparent Data Encryption Recommendations

• Data Compression Recommendations

• Database Vault Recommendations

• Oracle Fuzzy Search Support

• Storage Recommendations

• Database Configuration Recommendations

• Database Syntax

• Database Initialization Parameters

Index

Index recommendations specify points that need to be considered when creating indexes on a table.

1. Indexes on a table should be created according to the functional requirements of the table and not in order to perform SQL tuning.

2. The foreign keys on a table should be indexes.

Note: If the implementation creates a CM index on table-columns where the product already provides an index, then the CM index will be overridden by the base index.

Table Partitioning Recommendations

Oracle Utilities recommends using a minimum of 'n' partitions for selective database objects, where 'n' is number of RAC nodes.

Interval Partitioning F1_MO_UPD and F1_MO_UPD_BACKLOG tables

Performance tests conducted on GDE batch showed that Interval partitioning of tables, F1_MO_UPD and F1_MO_UPD_BACKLOG improved performance. Hence, it is recommended by Product Development to convert the tables F1_MO_UPD and F1_MO_UPD_BACKLOG into partitioned tables using Oracle's Interval partition scheme.

To implement interval partitioning on F1_MO_UPD and F1_MO_UPD_BACKLOG tables follow the steps below:

Note that the steps mentioned below are for F1_MO_UPD table. However, the same can be applied for partitioning the F1_MO_UPD_BACKLOG table.

1. Login to the database either with a schema owner account or as SYS.

2. Make sure that the GDE export related batches are not running.

3. Check the record count for the F1_MO_UPD table.

4. Execute the F1_MO_UPD_INT_PART.sql SQL script to create an interval-partitioned table.

5. Check the definition of F1_MO_UPD to verify if it has the partition clause “PARTITION BY RANGE (“ENT_KEY_HASH”) INTERVAL (1)”. Execute the following:

select dbms_metadata.get_ddl('TABLE','F1_MO_UPD') from dual;

6. Drop the backup table <table_name>_OLD created by the script after verifying the data is correct.

Transparent Data Encryption Recommendations

Oracle Utilities supports Oracle Transparent Data Encryption (TDE). Oracle 19c supports tablespace level encryption. The application supports tablespace level encryption for all application data. Make sure that the hardware resources are sufficiently sized for this as TDE uses additional hardware resources. The Oracle Advanced Security license is a prerequisite for using TDE.

Please consider the following when implementing TDE:

• Create a wallet folder to store the master key. By default, the wallet folder should be created under $ORACLE_BASE/admin/<sid>.

• The wallet containing the master key can be created using the following command:

alter system set encryption key authenticated by "keypasswd"

• The wallet can be closed or opened using the following commands:

alter system set wallet open identified by "keypasswd";
alter system set wallet close;

• Column level encryption can be achieved using the following commands:

create table <table_name>
(name varchar2(200) default ' ' not null,
bo_data_area CLOB encrypt using 'AES128',
bo_status_cd char(12) encrypt using 'AES128')
lob (bo_data_area) store as securefile (cache compress)
tablespace <tablespace_name>;

• AES128 is the default encryption algorithm.

• Tablespace level encryption is also supported using the following command:

Create tablespace <tablespace_name> logging datafile '<datafile
location>' size <initial size> reuse autoextend on next <next size>
maxsize unlimited extent management local uniform size
<uniform size> encryption using 'AES128' default storage(encrypt);

• Indexed columns can only be encrypted using the NO SALT Option. Salt is a way to strengthen the security of encrypted data. It is a random string added to the data before it is encrypted, causing repetition of text in the clear to appear different when encrypted.

Data Compression Recommendations

Oracle Utilities supports Advanced Data Compression, available with Oracle 19c onwards, to reduce the database storage footprint. Make sure that your resources are sufficiently sized for this as it uses additional system resources. Compression can be enabled at the Tablespace level or at the Table level.

Exadata Hardware

For Exadata hardware the compression recommendations are:

• Load data into the uncompressed table partitions using a conventional load and then, once data is loaded using a CTAS operation, load into a temporary heap table. Then truncate the original partition. Alter the original partition into HCC compressed and then partition exchange this with the temporary heap table.

• All multi column Indexes (primary as well as secondary) will be compressed using the default compression. HCC or OLTP compression is not applicable on the top of compressed Indexes.

Non- Exadata Hardware

For non-Exadata hardware the recommendations are the same as above, except that you cannot use HCC compression (it is only available in Exadata database machine). Instead of HCC you can use any other compression tool available to you for non-Exadata hardware.

CLOB Fields

All CLOB fields should be stored as SecureFiles and Medium compressed. This requires a separate license for Advanced Data Compression. As a part of the schema, we create the product-owned tables with compression turned OFF at the LOB level. If you have the license for Advanced Data Compression, you can enable compression by updating the storage.xml.

From Oracle 12c onwards:

• Admin and Master Data tables and their indexes will NOT be compressed.

• All Transactional Tables, including ILM enabled MOs shall be compressed.

• Compression will be done at the tablespace level.

• All multicolumn indexes on transactional/ILM tables will be compressed.

• Use 'compress advanced low'.

Compression Guidelines

• Admin and Metadata tables and their indexes will NOT be compressed.

• All Transactional Tables will be compressed.
This includes ILM enabled MOs where applicable.

• Compression will be done at the tablespace level.

• Different MOs will have different tablespaces.

• Partitioned MOs will have one tablespace per partition.

• Child tables will use reference partitioning with parent + children sharing the same tablespace. (parent and child will always be managed/archived together).

• All multicolumn indexes on transactional tables will be compressed.

• Use ‘compress advanced low’.

• Local partitioned indexes will reside in the same tablespace as the table.

• Each MO will have an index tablespace. All MO (Parent-Child Table) indexes will share this tablespace.

• Do NOT specify standard index compression.

• Securefile medium compression in row for LOBs and CLOBs.

Examples:

Create a Tablespace with Advanced Rowstore Compress

CREATE BIGFILE TABLESPACE CM_XT012_P2017JANDATAFILE '+DATA' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

Create Table with Subpartitions using Compressed Tablespaces & Securefiles Compression

CREATE TABLE CI_ADJ (

ADJ_ID CHAR(12) NOT NULL ENABLE,

SA_ID CHAR(10) DEFAULT ' ' NOT NULL ENABLE, ADJ_TYPE_CD CHAR(8) DEFAULT ' ' NOT NULL ENABLE, ADJ_STATUS_FLG CHAR(2) DEFAULT ' ' NOT NULL ENABLE, CRE_DT DATE,

CAN_RSN_CD CHAR(4) DEFAULT ' ' NOT NULL ENABLE,

ADJ_AMT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE, XFER_ADJ_ID CHAR(12) DEFAULT ' ' NOT NULL ENABLE, CURRENCY_CD CHAR(3) DEFAULT ' ' NOT NULL ENABLE, COMMENTS VARCHAR2(254) DEFAULT ' ' NOT NULL ENABLE, VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE, BEHALF_SA_ID CHAR(10) DEFAULT ' ' NOT NULL ENABLE, BASE_AMT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE, GEN_REF_DT DATE,

APPR_REQ_ID CHAR(12) DEFAULT ' ' NOT NULL ENABLE,

ADJ_DATA_AREA CLOB, ILM_DT DATE,

ILM_ARCH_SW CHAR(1),)

ENABLE ROW MOVEMENT

PARTITION BY RANGE (ILM_DT)

SUBPARTITION BY RANGE (ADJ_ID) SUBPARTITION TEMPLATE (

SUBPARTITION S01 VALUES LESS THAN ( '124999999999' ), SUBPARTITION S02 VALUES LESS THAN ( '249999999999' ), SUBPARTITION S03 VALUES LESS THAN ( '374999999999' ), SUBPARTITION S04 VALUES LESS THAN ( '499999999999' ), SUBPARTITION S05 VALUES LESS THAN ( '624999999999' ), SUBPARTITION S06 VALUES LESS THAN ( '749999999999' ), SUBPARTITION S07 VALUES LESS THAN ( '874999999999' ), SUBPARTITION S08 VALUES LESS THAN ( MAXVALUE )

) (

PARTITION "P2017JAN" VALUES LESS THAN (TO_DATE('2017-02-01 00:00:01',