9.1 Design and Planning

Basic install recommendations during the design and planning phase include:

  • Estimating the processors, and sessions according to the number of Primary Servers.

  • Updating the database server with the latest service packs.

  • Planning the Memory and Disk I/O requirements.

  • Eliminating database contention if the database supports other applications

  • Tuning the operating system

9.1.1 Virtualizing the ZENworks Database

Micro Focus does not recommend virtualization of the ZENworks Database server. However, if you do, you should ensure that you follow the database vendor's best practice. Refer to your vendor’s documentation as well as the following documentation:

9.1.2 Shared vs Dedicated Server Modes

Most customers use the Dedicated Server Mode as default. However, in some cases the Shared Connection Mode can be used. At any given point in time a ZENworks Primary Server can have up to approximately 150 connections to the ZENworks database. Each connection with Oracle utilizes a certain amount of RAM. If the number of Primary Servers multiplied by the number of connections causes the required RAM to exceed what can be addressed by the operating system, such as with a 32-bit OS which is limited to 4GB, the Shared Server Mode can be used to handle such scenarios. The Shared Server Mode allows connections to be pooled and shared across a single memory allocation.

If you notice a large number of dedicated connections being rejected, use the following command to determine the status of connection rejection for a particular listener:

lsnrctl status

For more information, see Dedicated vs. shared servers.

9.1.3 Character Encoding

Oracle supports some hybrid versions of UTF-8, but ZENworks requires true UTF-8 or UTF-16. The following commands report on the character encoding supported by the database:

  • select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

  • select value from nls_database_parameters where parameter = 'NLS_NCHAR_CHARACTERSET';

9.1.4 Disk Size and RAM Size Requirements

For ZENworks, the recommended minimum hard disk size is 10 GB for every 1,000 devices. We need to maintain separate disks for the database to avoid issues and for the slowing down of simultaneous access to the disk. Different disks refer to different physical disks, possibly using different controllers.

For the initial 3000 devices, a minimum of 4 GB RAM is recommended, beyond which, for every subsequent 3000 devices, 1 GB of additional RAM would be required.

For more information on disk size and type information, see, Disk Management for Oracle,

9.1.5 Memory Management

Oracle Automatic Memory Management is a reactive tool to re-size the RAM regions, which is fine for smaller ZENworks systems. For large ZENworks systems, Micro Focus strongly recommends a Manual Memory Management configuration because Automatic Memory Management will not anticipate high transaction time and will not allocate additional data buffers. In the Micro Focus lab, we have observed better results with manual memory management when testing a large ZENworks system.

After the initial configuration of a database, monitoring and tuning an instance regularly is important, to eliminate any potential performance bottlenecks in the database. Oracle provides V$ views to identify these bottlenecks and provide recommendations.

Reserving RAM for Database Connections

The Oracle database administrator needs to determine the optimal RAM allocation based on the operating system on the database server and the number of database connections. The total RAM demands for Oracle are as follows:

  • OS RAM: 20 percent of the total RAM for Microsoft Windows, 10% of RAM for UNIX.

  • Oracle SGA RAM: Determined with the show sga command.

  • Oracle database connections RAM: Each Oracle connection (when not using the Oracle multi-threaded server) will use approximately two megabytes of RAM and Sort_Area_Size and Hash_Area_Size. (or pga_aggregate_target alocation).

Oracle PGA

Determining the PGA size is a critical part of Oracle RAM tuning. A PGA RAM region is allocated for every dedicated connection. The size is determined as follows:

  • OS Overhead: Program Global Area (PGA): OS Overhead – 2 MB of RAM has been reserved for Windows and 1 MB for UNIX.

  • Sort_area_size parameter value: Program Global Area (PGA): Sort_Area_Size – This RAM is used for data row sorting inside the PGA.

  • Hash_area_size parameter value: Program Global Area (PGA):Hash_Area_Size – This RAM defaults to 1.5 times the Sort_Area_Size value and is used for performing hash joins of Oracle tables.

Select <number of connections>*(2048576+a.value+b.value) pga_size from v$parameter a, v$parameter b, where a.name = 'sort_area_size', and b.name = 'hash_area_size';

Oracle SGA

The size of an Oracle SGA is based on the following parameter settings:

  • shared_pool_size: Sizes the administrative RAM for Oracle and the library cache.

  • db_cache_size: Determines the size of the RAM for the data buffers.

  • large_pool_size: The size used for shared servers (MTS, not recommended) and parallel queries. Parallel execution allocates buffers out of the large pool only. whenparallel_automatic_tuning parameter is true.

  • log_buffer: The size of the RAM buffer for redo logs.

For more information, see:

Optimize your Oracle PGA RAM

Oracle PGA Memory Allocation for Dedicated Connections

Optimizing Oracle RAM for SGA & PGA

9.1.6 Storage

This section lists important information related to the storage aspect of Oracle, including:

Oracle Automatic Storage Management

Automatic Storage Management (ASM) is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Cluster (Oracle RAC) configurations. ASM is Oracle's recommended storage management solution that provides an alternative to conventional volume managers, file systems, and raw devices.

ASM uses disk groups to store data files. An ASM disk group is a collection of disks that ASM manages as a unit. Within a disk group, ASM exposes a file system interface for Oracle database files. The content of files that are stored in a disk group are evenly distributed, or striped, to eliminate hot spots and to provide uniform performance across the disks. The performance is comparable to the performance of raw devices.

You can add or remove disks from a disk group while a database continues to access files from the disk group. When you add or remove disks from a disk group, ASM automatically redistributes the file contents and eliminates the need for downtime when redistributing the content.

The ASM volume manager functionality provides flexible, server-based mirroring options. The ASM normal and high redundancy disk groups enable two-way and three-way mirroring respectively. You can use external redundancy to enable a Redundant Array of Inexpensive Disks (RAID) storage subsystem to perform the mirroring protection function.

ASM also uses the Oracle Managed Files (OMF) feature to simplify database file management. OMF automatically creates files in designated locations. OMF also names files and removes them while relinquishing space when tablespaces or files are deleted.

ASM reduces the administrative overhead for managing database storage by consolidating data storage into a small number of disk groups. This enables you to consolidate the storage for multiple databases and to provide for improved I/O performance.

ASM files can coexist with other storage management options such as raw disks and third-party file systems. This capability simplifies the integration of ASM into pre-existing environments.

Oracle Enterprise Manager includes a wizard that enables you to migrate non-ASM database files to ASM. ASM also has easy to use management interfaces such as SQL*Plus, the ASMCMD command-line interface, and Oracle Enterprise Manager.

For more information, see http://docs.oracle.com/cd/B28359_01/server.111/b31107/toc.htm.

ZENworks Tablespaces

In the ZENworks install or upgrade wizard, you have the option to segregate the tables and indexes into two separate Tablespaces. This helps to balance disk I/O usage and improve the performance. It is recommended that you host the data files from both of the tablespaces in separate hard disks.

Spreading objects to different disks helps obtain better performance. To do so, use multiple tablespaces, allocating them to different disks. Move objects among different tablespaces, or add multiple data files spread among different disks to the same tablespace, and allocate extents for the database objects to these data files.

Use the DBA_HIST_SEG_STAT view to identify the most-accessed segments from the instance startup.For more information, see:

http://docs.oracle.com/cd/E18283_01/server.112/e17120/tspaces007.htm

RAID

RAID is the acronym for Redundant Arrays of Inexpensive Disks, a common configuration in a storage subsystem. It is used to obtain low-cost, fault-tolerant configurations for high performance in the non-mainframe market by using multiple inexpensive disks in different configurations.

A RAID can be software-based at the operating system and firmware level or hardware-based. The latter offers guaranteed performance and no overhead on the CPU.

The following steps will demonstrate the various RAID levels; you can chose the right RAID level based on the following:

  • RAID 0+1 is preferable for your Oracle database installations.

  • RAID 5 has a significant write penalty, so do not use it for storing write-intensive data files (if RAID 0+1 is available), redo log files, archived redo log files, and undo segments. You can use it for control files and for data files with moderate write activity.

LGWR writes online redo logs sequentially using RAID 5 on the disks, where online redo logs are stored. This can lead to poor performance due to the slower write times that characterize this type of disk array. Using RAID 0+1 is preferable.

For more information, see http://www.dba-oracle.com/oracle_tips_raid_usage.htm.

Asynchronous I/O

The Oracle database can use synchronous or asynchronous I/O calls. With synchronous I/O, the write process will block until the operation is completed.

Using asynchronous I/O, while the I/O request is still executing, the calling process continues its work without blocking. This is why asynchronous I/O can lead to performance gain in processing writes to Oracle database files.

Enable asynchronous I/O if it is not enabled:

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

Restart the database to set the new parameters.

On platforms that don't support asynchronous I/O, you can enable multiple database writer-slave processes. A single DBWR process will use multiple slave processes to write data on disks, simulating something similar to asynchronous I/O.

For more information, see http://www.dba-oracle.com/art_dbazine_disk.htm.

9.1.7 Oracle Parameters

The following parameters are provided based on the scale test performed in the Micro Focus lab on a dedicated Oracle database server with all the ZENworks processes enabled.

These parameters might differ if the Oracle server is shared for applications other than ZENworks and the hardware configuration.

Recommended Parameter Settings:

DB_BLOCK_SIZE: 8KB, which is the default value.

Number of Process: 200 * Number of Primary Servers

Memory Management: Manual memory management

PGA: Number of processes * 6 MB

SGA: Minimum 2 GB or SGA = Total RAM size – OS allotted RAM – PGA

OPEN_CURSORS: Number of opened sessions * 1.5

9.1.8 LOBs Storage Parameters

Large Objects (LOBs) are a particular data type, used to store large binary or character objects inside or outside the database when using BFILEs.

Micro Focus recommends that you store the LOB data as SECUREFILEs by setting the following parameter. We recommend that you set this parameters before creating the ZENworks zone or before creating the ZENworks schema.

Oracle 12C: ALTER SYSTEM SET db_securefile = 'ALWAYS';

While creating the LOB field, by default, the ENABLE STORAGE IN ROW clause will be enabled, which means store the data in the same DB block in which other fields of the row are stored. When the size of the LOB field is greater than 4000 bytes it is always stored off-line. The same behavior occurs when the DB block size is large enough to accommodate the LOB field. By default an 8 KB block size is good enough to support most of the ZENworks cases.

For more information, see the Oracle website.

9.1.9 Checkpoints and Redo Log Files

CKPT process signals the DBWn processes to write the dirty (modified) buffers from the database buffer cache in memory to the data files.

SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE 'background check%';

If the number of started checkpoints is greater than the value of completed checkpoints by more than one, in the first query you need to enlarge the Redo Log File size. In this situation, checkpoints are not completed between log file switches. This is because the log file switches occur too often and log files are very small. Increasing the Redo Log File size will limit the number of log switches required, allowing checkpoints to complete between them.

A redo log switch should occur every 15 to 30 minutes. Switching too often leads to performance issues, while not switching often enough can cause a recovery operation to take longer.

SELECT * FROM V$LOGFILE;

Query V$LOGFILE to know the redo log files in our database and some information on their status.

In a production database, you need at least two members for each group, and, according to the transaction load on the database, more redo log groups could be required.

For more information, see http://docs.oracle.com/cd/E18283_01/server.112/e17120/onlineredo002.htm.

9.1.10 Important Log Locations

The alert.log is located at the path specified by the SQL command:

Show parameter BACKGROUND_DUMP_DEST

The Alert log will display all the deadlock errors, I/O issues, and memory issues.

Oracle will generate a specific trace file for each error and the file location will be available in the Alert log.

The Oracle instance restart/ shutdown timings and instance abnormal termination details will be logged in this file. This file should be sent to Micro Focus Support for better understanding about the issue.

The listener.log file is found by checking the Listener Log File path, after running lsnrctl status from a command prompt.

See the following links for more information:

http://download.oracle.com/docs/cd/B14117_01/network.101/b10775/listenercfg.htm

http://www.orafaq.com/wiki/Alert_log

9.1.11 Oracle RAC

THe Oracle RAC database system involves the configuration of multiple hosts or servers joined together with clustering software and accessing the shared disk storage structures. On each of the hosts in the cluster, an Oracle database instance is launched that uses the shared storage structures to provide the logical database objects. Thus, multiple database instances provide a common database access for the users. Users can access the same database from any of the instances.

Basic features include:

  • Multiple instances accessing the same database.

  • One set of data files and control files, but separate Redo Log files and Undo segments for each instance.

  • Locking and Concurrency Maintenance is extended to multiple instances.

  • Multiple instances access the same shared storage structures.

  • Provides HA and Scalability Solution.

Advanced features of Oracle Net include failover and load balancing. They are mostly used in a RAC environment.

Failover

In the context of Oracle Net, failover refers to the mechanism of switching over to an alternate resource when the connection to the primary resource is terminated for any reason. Connection failure can be broadly categorized as follows:

  • Those that occur while making the initial connection.

  • Those that occur after a connection has been successfully established.

Load Balancing

Load balancing can be defined as distributing a job or piece of work to multiple resources. RAC is an ideal environment for distributing a load among multiple instances accessing the same physical database.

  • Client Load Balancing: You can configure load balancing either at the client end or at the server end.

  • Connection Load Balancing: This feature improves connection performance by allowing the listener to distribute new connections to different dispatchers and instances.

For more information, see the following links:

http://docs.oracle.com/cd/E11882_01/rac.112/e41960/admcon.htm

http://www.oracle.com/technetwork/database/options/clustering/rac-wp-12c-1896129.pdf?ssSourceSiteId=ocomen

9.1.12 Oracle RAC One Node

This option is available with the Enterprise edition only. It provides a cold failover solution for Oracle databases. It is a single instance of Oracle RAC running on one node of the cluster while the second node is in a cold standby mode. If the instance fails for some reason, then RAC One Node detects it and first tries to restart the instance on the same node. The instance is relocated to the second node in case there is a failure or fault in the first node and the instance cannot be restarted on the same node. The benefit of this feature is that it automates the instance relocation without any down time and does not need manual intervention. It uses a technology called Omotion, which facilitates the instance migration/relocation. Additional benefits include:

  • Built-in cluster fail-over for HA but not to load balance, unlike regular RAC.

  • It is useful for some maintenance tasks, such as rolling upgrade or proactive upgrade.

  • It is capable of online upgrade to real RAC.

For more information about Oracle one node, see:

http://www.oracle.com/technetwork/database/options/clustering/rac-one-node-wp-12c-1896130.pdf?ssSourceSiteId=ocomen

http://www.dba-oracle.com/t_rac_one_node.htm