Oracle Database 12c Release 2 Considerations

This document will cover storage tier issues for Oracle12c Release 2.

Contents

• Oracle Standard Edition2 vs. Oracle Enterprise Edition
• Optimal Flexible Architecture
• File System
• Locally Managed vs. Dictionary Managed Tablespaces
• Segment Space Management Auto
• Automatic Undo Management
• Automatic Storage Management
• Automatic Shared Memory Management
• Manual Shared Memory management
• Automatic PGA Memory Management
• Oracle Enterprise Manager
• Flash Recovery Area
• Character Set
• Oracle Database Configuration Assistant (DBCA)
• DBCA Templates
• Security
• Oracle Server Parameter File
• Cost Based Optimizer
• Oracle Statistics
• Audit trail

Oracle Standard Edition vs. Oracle Enterprise Edition

IFS recommends to run IFS Applications on Oracle Enterprise Edition using the Oracle DB ASFU License agreement. It is still possible to run IFS Applications using a Standard Edition2 license from Oracle, since IFS Applications is developed using Standard Edition.
Oracle DB ASFU License comes as a pure Enterprise Edition without any extra cost options. When installing Enterprise Edition lots of extra cost options is installed by default. 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 Applications 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 and 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\12.2.0\db_home1\...
            UNIX:       /u01/app/product/12.2.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 Oracle12c Release 2. 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 Oracle12c Release 2 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 Oracle12c 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.

For Oracle12c Release 2 it is possible to configure a database using Oracle Real Application Cluster (RAC) functionality on two nodes using server with maximum two CPUs each using the Oracle Standard Edition2 license with the requirement to use Oracle Automatic Storage Management. More powerful hardware or more instances require Oracle Enterprise Edition license and in this installations traditional file systems can be used.  

More information about Oracle Automatic Storage Management can be found in the Oracle12c Release 2 documentation.

Benchmark tests performed on IFS Applications running on Oracle 11g R2 shows no performance difference for a database using ASM compared to a database using a normal file system.

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

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 is 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 can be found in the Oracle12c documentation.

IFS recommends not using Automatic Shared 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 this for the SGA 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 Applications can imply decreased performance. Therefore, we recommend IFS Applications to use manual memory shared management so you have more control over the memory.   

Databases created using the DBCA template for IFS Applications will use manual 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 recommend using Automatic PGA Memory Management in all Oracle12c Release 2 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 Applications 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 Oracle12c Release 2 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 Applications shall always use the Unicode character set AL32UTF8. All IFS Applications clients and the IFS middle-tier use Unicode natively.
If the database doesn't also handle Unicode characters there is a risk that the database will convert a Unicode character which cannot be represented in the used non-Unicode character set into a non-printable/control character.

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 Applications with DBCA templates for both Windows and Unix and recommends using DBCA when creating an Oracle12c Release 2 database for IFS Applications.

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

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

Security

After installing Oracle make sure that you make your database server secure.

See security checklist on:

• Security checklist (IFS internal)

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 Applications 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
  

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');

Audit trail

In IFS Applications the audit trail is disabled by default. The reason behind this is that the Audit trail often requires a lot of disk. Since Oracle has changed Audit trail very much in Oracle12c it is hard to be sure to disable the audit trail. The following tasks has been made to the Audit trail during an installation of IFS Applications:

• DBCA templates
  In the DBCA templates we set initialization parameter audit_trail=none.
• Audit policy ORA_SECURECONFIG
  Oracle has created a new Audit policy called ORA_SECURECONFIG in Oracle12c and it is enabled by default. This audit policy is disabled during installation of IFS Applications. Instead a similar policy called IFS_SECURECONFIG is created and disabled by default. This policy is exactly the same as ORA_SECURECONFIG except that it doesn't audit login's and logoff's. Use IFS_SECURECONFIG instead of ORA_SECURECONFIG if Auditing should be enabled.

Customers that want to have auditing must make sure:

• Enable Audit trail by themselves
• Create their own policy on how to purge the audit trail

After an installation of IFS Applications it is important to manually check how the Audit trail is setup and that it behaves as expected. The behavior of the Audit trail is under the customer project responsibility.

In order to disable all Unified auditing, run Prepare_SYS.Disable_All_Audit_Policies.