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.
AL32UTF8 is not supported by the PMDB.
The following table specifies the database block size prerequisite.
Table 1 Database block size prerequisite
| Parameter | Value | Improvement |
|---|---|---|
db_block_size | Minimum 8KB (16KB recommended) | This value defines the Oracle block size. The block size is set when creating the database and cannot be changed afterwards. A value of at least 8 KB ensures that the PMDB can perform smoothly. A lower value may cause serious performance problems.
To review the default block size value, check the |
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
| Parameter | Value | Improvement / Remarks |
|---|---|---|
log_buffer | If the version < 9.2.0.7, then the log_buffer=3MB, else the log_buffer=16MB | |
shared_pool_size | 1/4 of the physical memory | The shared pool should not be too small (<250MB) or too large (>2GB) |
log_checkpoint_timeout | 900 | |
open_cursors | 2000 | Make sure you have enough open cursors for the application to execute. |
Processes | 300 | Make sure you have enough open processes for the application to execute. |
db_writer_processes | Should be set according to the number of I/O devices you have for the database | Improves I/O performance in cases of large I/O. Each writer is writing to a different disk (improves loads, for example). |
Table 3 10g parameters
| Parameter | Value | Improvement / Remarks |
|---|---|---|
sga_target | Automatic memory management. 1/4th of the physical memory. | Replaces the following parameters:
Make sure to initialize them with 0 or the minimum value you need. |
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
| Setting | Description | Value |
|---|---|---|
| Rollback segments (For 8i) | Minimum tablespace size | 2GB* *4GB recommended in larger installations. |
| Rollback segments (For 8i) | Minimum number of segments | 20 |
| Rollback segments (For 8i) | Minimum initial segment size | 5MB* *Each Rollback segment should be able to grow to the size of the tablespace. |
| Enable AUM (For 9i and above) | Automatic Undo Management See Enabling Automatic Undo Management (AUM) | For 9i and higher |
| Temporary tablespace | Minimum temporary tablespace size | 2GB* *The temporary tablespace is most likely to grow according to the PMDB load. If the Redo file is switching every 20 minutes, enlarge the file. |
| Redo log file | Minimum redo log file size | 1GB* *4GB recommended in larger installations If the Redo file is switching every 20 minutes, enlarge the file. |
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
- 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 - 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
| Tablespace | Size |
|---|---|
| Large table | 2MB |
| Medium table | 512KB |
| Small table | 80KB |
| Large index | 1MB |
| Medium index | 256KB |
| Small index | 80KB |