Skip to content

Oracle Database 19c Considerations

This document will cover storage tier issues for Oracle 19c.

Oracle Standard Edition vs. Oracle Enterprise Edition

IFS recommends to run IFS Cloud on Oracle Enterprise Edition using the Oracle DB ASFU License agreement. It is still possible to run IFS Cloud using a Standard Edition2 license from Oracle.
Oracle DB ASFU License comes as a pure Enterprise Edition license without any extra cost options. When installing Enterprise Edition lots of extra cost options are installed by default. Further information about Oracle Database offerings, features and management packs can be found here. If you don't want to break Oracle's licensing by mistake IFS recommends you to disable these options. Some options can be disabled by using the Oracle chopt tool. More details about disabling these options can be found here.

Oracle Optimal Flexible Architecture

Oracle Optimal Flexible Architecture (OFA) is a set of file naming and placement guidelines for Oracle software and databases. It can also be thought of as a set of good habits to adopt when organizing Oracle directories and files on your servers. All Oracle products are OFA-compliant; that is, Oracle Universal Installer places Oracle products in directory locations that follow OFA guidelines.

The aim of OFA is to prevent an entire class of problems that can occur when you have different releases of Oracle software and multiple, growing databases on your computer. OFA is designed to provide significant benefits:

  • Easier maintenance of Oracle software using standard file organization.
  • Greater reliability through data spanning multiple physical drives.
  • Higher performance and decreased I/O contention for disks.

IFS recommends following OFA guidelines when installing Oracle software and creating the database. The Oracle Database Configuration Assistant (DBCA) templates delivered with IFS Cloud all follow OFA guidelines.

File System

Oracle Corporation recommends configuring the file system for data files, control files and redo log files using SAME technique (Stripe All Mirror Everything) for best performance. Data files should, if possible, be placed on a striped file system where RAID 1+0 or RAID 5 can be used.

In a Windows or UNIX environment installation of Oracle software and database the configuration could look like this (the examples are for non-striped disks).

Oracle Software
Oracle software shall be installed on a file system where you do not place data files, redo log files and control files. On most UNIX environments the Oracle software is installed on the system disk.

Examples:

            Windows: C:\app\administrator\product\19.0.0\db_home1...
            UNIX:       /u01/app/product/19.0.0/db_home1/....

Data Files
Oracle data files should be placed so the load is spread equally among the disks.

Examples:

            Windows: D:\oradata\<SID>\system01.dbf,
                            E:\oradata\<SID>\undotbs01.dbf,
                            F:\oradata\<SID>\temp01.dbf …
            UNIX:       /u02/oradata/<SID>/system01.dbf,
                            /u03/oradata/<SID>/undotbs01.dbf
                            /u04/oradata/<SID>/temp01.dbf …

Control Files
For best performance and availability control files should be doubled/mirrored.

Examples:
            Windows: D:\oradata\<SID>\control01.ctl,
                            E:\oradata\<SID>\control02.ctl,
                            F:\oradata\<SID>\control03.ctl
            UNIX:       /u02/oradata/<SID>/control01.ctl,
                            /u03/oradata/<SID>/control02.ctl,
                            /u04/oradata/<SID>/control03.ctl

Online Redo Log Files
For best performance and availability redo log files should be mirrored. If possible, redo log files should be placed on Solid State Disks (SSD), which are faster compared to conventional disks.

Examples:
            Windows: D:\oradata\<SID>\redo11.log, redo12.log
                            E:\oradata\<SID>\redo21.log, redo22.log
                            F:\oradata\<SID>\redo31.log, redo32.log
            UNIX:       /u02/oradata/<SID>/redo11.log, redo12.log
                            /u03/oradata/<SID>/redo21.log, redo22.log
                            /u04/oradata/<SID>/redo31.log, redo32.log

Locally Managed vs. Dictionary Managed Tablespaces

Locally managed tablespaces manages its own extents and maintains a bitmap in each data file to keep track of the free or used status of blocks in that data file. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks which does not generate rollback information.

Locally managed tablespaces can have either uniform extent sizes or variable extent sizes which are determined automatically by the system. When the tablespace is created either UNIFORM or AUTOALLOCATE (system-managed) options specifies the type of allocation.

For system-managed extents, Oracle determines the optimal size of extents, with a minimum extent size of 64K. This is the default for permanent tablespaces. For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.

The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for extents that are managed locally.

Locally managed temporary tablespaces must be of type TEMPORARY (not PERMANENT).

Benefits using locally managed tablespaces:

  • Improved concurrency and speed of space operations, because space allocations and deallocations predominantly modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues.
  • Improved performance, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated.
  • Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.
  • Simplified space allocation when the AUTOALLOCATE clause is specified, appropriate extent size is automatically selected.
  • Reduced user reliance on the data dictionary because necessary information is stored in file headers and bitmap blocks.

Locally managed tablespaces are default for Oracle19c. IFS recommends keeping to this and create all tablespaces locally managed with AUTOALLOCATE option.

Oracle Segment Space Management Auto

Oracle Automatic Segment Space Management simplifies the management of free space within an object such as tables or indexes, improves space utilization, and provides significantly better out of box performance and scalability compared with Manual Space Management.

Oracle Automatic Segment Space Management implementation eliminates the necessity to tune space management related controls such as FREELISTS, FREELIST GROUPS and PCTUSED and thereby freeing database administrators from manually managing the space within a database object.

IFS recommend using automatic Oracle Automatic Segment Space Management for all Oracle19c databases.

Oracle Automatic Undo Management

Oracle provides a fully automated mechanism for managing undo information and space called Oracle Automatic Undo management. With Automatic Undo Management the database manages undo segments in an UNDO tablespace.

Beginning with Oracle 11g, Oracle Automatic Undo Management is the default mode when you create a database using Oracle Database Configuration Assistant (DBCA) and is implemented by setting the following two parameters:

  • UNDO_MANAGEMENT=AUTO
  • UNDO_TABLESPACE=<your locally managed undo tablespace>

Oracle Manual Undo Management is still present, but Oracle Automatic Undo Management is much more efficient and both Oracle and IFS recommends using this functionality for all Oracle19c databases.

Automatic Storage Management (ASM)

Oracle Automatic Storage Management can be described as an Oracle internal file system in the database. Disks are configured into disk groups and database files (data-, online redo log-, archive logs, control, backup files) are then striped across all available disks. ASM handles striping and mirroring to optimize performance, utilization and to protect from disk failure.

Benchmark tests performed on IFS Cloud shows no performance difference for a database using ASM compared to a database using a normal file system.

More information about Oracle Automatic Storage Management can be found in the Oracle19c documentation.

Oracle Automatic Storage Management is certified to use for IFS Cloud.

Automatic Shared Memory Management

Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You only designate the total memory size to be used by the instance and Oracle dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. It is also possible to exclude PGA from this and let Oracle handle the memory automatically for all memory pools within the SGA.

Oracle Automatic Shared Memory Management and Oracle Automatic Memory Management are controlled by the following parameters:

  • MEMEORY_TARGET - define memory the memory target for both SGA and PGA
  • MEMORY_MAX_TARGET - define the maximal memory size for both SGA and PGA
  • SGA_TARGET - define the memory target for SGA
  • SGA_MAX_TARGET - define the maximal memory size for SGA

More information about Oracle Automatic Shared Memory Management and Oracle Automatic Memory Managerment can be found in the Oracle19c documentation.

IFS recommends not using Automatic Shared Memory Management or Automatic Memory Management, instead one shall use Manual Shared Memory Management to be able to control the memory within the SGA and PGA. However, Automatic Shared Memory Management can be used, but in that case it is vital only to use this for the SGA and not for the PGA e.g. use SGA_TARGET and SGA_MAX_TARGET and set MEMORY_TARGET=0.

Manual Shared Memory Management

To have more control over the individual memory components in the SGA you can disable automatic memory management and configure the database for manual memory management. You configure and tune each memory pool of the SGA individual and thereby determine the overall SGA size.

IFS has done tests using both automatic and manual shared memory management and our findings are that Oracle, over time, favors memory for the shared pool compared to the buffer cache, which for a system like IFS Cloud can imply decreased performance. Therefore, we recommend IFS Cloud to use Manual Shared Memory Management so you have more control over the memory.

Databases created using the DBCA template for IFS Cloud will use Oracle Manual Shared Memory Management.

Automatic PGA Memory Management

For automatic PGA memory management mode, the main goal of Oracle is to honor the PGA_AGGREGATE_TARGET limit set by the DBA, by controlling dynamically the amount of PGA memory allotted to SQL work areas. At the same time, Oracle tries to maximize the performance of all the memory-intensive SQL operators, by maximizing the number of work areas that are using an optimal amount of PGA memory (cache memory).

The PGA memory management is controlled by three parameters:

  • PGA_AGGREGATE_TARGET
  • PGA_AGGREGATE_LIMIT
  • WORKAREA_SIZE_POLICY

IFS recommends using Automatic PGA Memory Management in all Oracle19c development and production databases.

Oracle Enterprise Manager Express

When Oracle Database is installed one can choose also to install the web-based management tool Enterprise Manager Express (EM Express), which offers support for basic administrative tasks such as storage and database management. If using Oracle Enterprise Edition with Diagnostic + Tuning option pack it also provides comprehensive solutions for performance diagnostics and tuning.

IFS recommends installing and configuring Oracle EM Express when a database is created. If using the DBCA templates for IFS Cloud Oracle EM Express will be installed and configured by default.

Flash Recovery Area

Oracle Flash Recovery Area is a location on the filesystem or on an ASM disk group that holds files related to recovery including:

  • multiplexed control files
  • multiplexed online redo log files
  • archived redo log files
  • flashback logs
  • RMAN disk backups
  • files created by RESTORE and RECOVERY commands.

Space within the flash recovery area is managed by the database. If there is not enough space to complete an operation obsolete, backed up or redundant files are removed to free up some space, but if this isn't enough the database will halt until enough space is available, therefore IFS recommends not using this functionality in production environments.

You configure the flash recovery area size and location using DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_SIZE initialization parameters. It is essential to allocate enough space for flash recovery area to host all files resides in this area.

IFS recommends not using the Flash Recovery Area in Oracle19c production databases.

Character Set

When creating a database you have to specify which character set the database shall use, once the database is created you can’t change this unless recreating the whole database. For example, if you create a database with the default character set (US7ASCII) you can’t store European characters in it. You will get no error when you try to do this; Oracle converts them to characters the database can handle.

Please note that databases for IFS Cloud shall always use the Unicode character set AL32UTF8.

Oracle Database Configuration Assistant (DBCA)

Oracle Database Configuration Assistant (DBCA) is a client tool that helps the administrator to create, configure, and maintain a database/instance. The Database Configuration Assistant can use predefined templates containing all necessary data to create a database.

IFS supports IFS Cloud with DBCA templates for both Windows and Unix and recommends using DBCA when creating an Oracle19c database for IFS Cloud.

DBCA Templates

Templates are used by the DBCA to create new databases or to make clones of existing databases. Templates must be copied from the IFS location to the Oracle location in order to be used by DBCA.
DBCA Templates are XML files containing information required to create a database. The templates include:

  • database options
  • init.ora parameters
  • storage attributes (datafiles, tablespaces, controlfiles and redolog files)

These templates reside in the $ORACLE_HOME/assistants/dbca/templates directory. By default you found following files in this directory.

  • Data_Warehouse.dbc
  • General_Purpose.dbc
  • Transaction_Processing.dbc
  • New_Database.dbt

IFS provides DBCA templates located at <build_home>\template\fndbas\, there exists templates for

  • Unix, Windows, Multitenant, standard filesystem structure
  • Unix, Windows, Multitenant, with OMF (Oracle Managed Files)

The templates includes two initialization parameters that stops from misusing database options that require extra licenses. Theses parameters are:

  • In-memory option
    inmemory_query=DISABLE
  • Diagnostics and Tuning pack option
    control_management_pack_access=NONE

Oracle Server Parameter File

The SPFILE (server parameter file) is Oracle's new method of maintaining database parameters. The old method of editing a text based parameter file (INIT.ORA) has given way to the new method of maintaining persistent parameters. By this Oracle means that you can change a system parameter and have its value be maintained across shutdown and startup. The only way of changing parameter values is by using the ALTER SYSTEM SET/RESET command.

Both IFS and Oracle recommends using a SPFILE instead of the old PFILE.

Cost Based Optimizer

IFS Cloud only supports Oracle’s Cost Based Optimizer and to be able to reach full effect of the optimizer the following database parameters need to be set:

  • OPTIMIZER_MODE=ALL_ROWS
  • OPTIMIZER_INDEX_COST_ADJ=10

Oracle Cost Based Optimizer uses statistic information for all objects involved in a query and statistics for the database and host where the query runs to choose the best and most effective execution plan. Therefore, it is vital to analyze database objects and generate statistic information. IFS recommends using the, by default configured and activated, Oracle Statistics Gathering task to do this.

Oracle Statistics

The preferred method for gathering statistics in Oracle Database is to use the automatic Oracle Statistics Gathering task. By default, Oracle Database collects statistics for database objects that are missing statistics or have stale (out of date) statistics. This is done by an automatic task that executes during the predefine maintenance window. Oracle internally prioritizes the database objects that require statistics, so that those objects, which most need updated statistics are processed first.

The automatic Oracle Statistics Gathering task eliminates many manual tasks associated with managing the optimizer and significantly reduces the risk of generating suboptimal execution plans. Therefore, IFS strongly recommends using the automatic Oracle Statistics Gathering task. If the Oracle Automatic Statistics Gathering task needs to be disabled for any special reason you can use IFS Gather Statistics database task. Please refer the IFS technical documentation how to configure and schedule IFS Gather Statistics database task.

Oracle recommends to re-gather statistics about objects in the SYS schema, fixed objects and data dictionary tables after major application upgrades or changes to the database configuration (updated SGA). Oracle’s Automatic Statistics Gathering task will eventually gather these statistics, but it still good practice to gather these statistics when there is representative workload running in the database. Please, see Oracle documentation for further details.To manually gather statics for Oracle fixed objects, data dictionary tables and SYS objects, the following commands can be used.

  • EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
  • EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
  • EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

JAVA_POOL_SIZE

When exporting IFS Cloud Permission Sets and ACPs, Oracle’s internal Java function is used and depending on the number of Permission Sets and/or ACPs being exported the amount of required memory may increase, which will influence the required setting of JAVA_POOL_SIZE.

JAVA_POOL_SIZE is set to 500M in the IFS Cloud DBCA templates (Oracle default value is 24M), but might need to be increased if there are many Permission Sets or ACPs in the database which will be exported.

NOLOGGING

In earlier IFS releases it was possible to create indexes in NOLOGGING mode, this must be considered when it comes to backup method and to what extent it will be possible to recover a database.