Configuration with Oracle

This section helps you configure the Oracle-based PMDB after you install Precise. This is not a replacement of the content within Installation and Administration. The configuration recommendations help you to fine-tune the PMDB, which then results in faster and more efficient performance.

Prerequisites

Before applying the information in this section, refer to the content within Installation and Administration . We recommend that the PMDB is an Oracle-dedicated instance.

Make sure that you are creating the database with the proper NLS_CHARACTERSET. When the monitored Oracle instances use a multiple byte character set, use UTF8.

The following table specifies the database block size prerequisite.

Table 1 Database block size prerequisite

Required INIT.IRA changes

To improve performance, change some parameter values and make sure the values match those listed in the appropriate table.

You can change the parameter values on a new and on an existing Oracle instance. Tables 2 through 5 describe the general and Oracle version-specific parameters.

Table 2 General parameters

Table 3 10g parameters

init.ora example

The following is an example of the init.ora file for an Oracle server with 2GB RAM and two disks:

db_block_size=16384 # 16K
db_cache_size = 268435456 # 256MB
log_buffer=5242880 # 5 MB
shared_pool_size=471859200 # 450MB
log_checkpoint_timeout=900 # 15 minutes
pga_aggregate_target=262144000 # 256MB
session_cached_cursors=300
open_cursors=2000 processes=300
undo_management=AUTOundo_tablespace=UNDOTBS1
undo_retention=900 # 15 minutes

Additional DBA settings

To improve performance, the DBA on site must modify the Rollback segment settings and the Redo log file settings. The following table describes those settings.

Table 4 Rollback segment and Redo log file settings

Additional Rollback segment and Redo log file information

Each Rollback segment should contain a minimum of five extents. The size of each extent within a Rollback segment should be at least 1MB. Their optimal size should be set to the summed size of those extents. For example, if creating rollback segment with five extents of 1MB each, they should set the optimal size to 5MB. This is to prevent the changing size of the rollback segment to exhaust the tablespace's free space.

If possible, keep data tablespace indexes and temporary tablespace on different disk drives. Make sure that you disable the recycle bin in Oracle. See How to disable the recycle bin in Oracle version 10g. For more information, refer to the Using Flashback Drop and Managing the Recycle Bin section in the Oracle Administration Guide.

Four Redo logs should be created, each with 1 mirror (2 members for each group). Remember that hardware mirroring is better than software mirroring.

In Oracle version 9i and higher, it is recommended to configure the Undo space management, otherwise you must configure the rollback segments according to the rules in Table 3-6.

If you are using an archive (log_archive_start set to true) and you enable parallel archiving writers, then the log_archive_max_processes should be set to more than 1 (same consideration as in the db_writer_process).

How to disable the recycle bin in Oracle 10g

Our recommendation is to disable the recycle bin and this section describes the background and the action on how to disable the recycle bin.

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

The following information is taken from the Oracle Database Administration Guide.

In version 10.0 and 10.1, use the following command:

alter system set
"_recyclebin"=FALSE scope=BOTH;

In version 10.2, use the following command:

alter system set recyclebin=off scope=BOTH

Enabling Automatic Undo Management (AUM)

To enable Automatic Undo Management

1. Create the undo tablespace by running the following command:
     CREATE UNDO TABLESPACE tablespace_name DATAFILE 'data file name' SIZE 32M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL
2. Set the following init.ora parameters:
     undo_management = AUTO
  undo_tablespace = tablespace_name
  undo_retention = 1800
   If the undo tablespace size is too large you can decrease the number of seconds.

For more information on how to enable AUM, see the Managing Undo Space section in the Oracle Database Administrator's Guide.

Backing up the database and creating archiving

The PMDB holds information for up to three years, by default. If you want to back up this information, you should enable the Redo log archiving, and then create a backup using RMAN or a backup utility. For more information, refer to the official Oracle Backup and Recovery documentation.

If you do not create a backup, information is lost when you have a database crash and you cannot recover data. If you create a backup without using the archive mode, you can recover data to the time of the backup. All new operations (after the logs were archived) are lost.

Maintenance operations

Every week the maintenance process analysis the PMDB, but to improve the database performance, the DBA must check and perform a rebuild to the indexes using the alter index {name} rebuild command.

Changing the size of tables and index extents

The PMDB uses the products\dbms\files\tables_definition\ps_00_db_entities.xml file to create the tablespaces for the tables and the indexes. To change the default sizes, you can change the file before the PMDB installation or alter the tablespaces after the installation.

The following table shows the default extents sizes.

Table 5 Default extents sizes

IDERA |  Products | Purchase | Support |  Community |  Resources |  About Us  | Legal