Page History
...
- Prerequisites for configuring the PMDB in an SQL Server
- Prerequisites for configuring the PMDB in an Oracle Server
- Additional DBA settings
- How to disable the recycle bin in Oracle version 10g
- About backing up the database and creating archiving
- Enabling Automatic Undo Management (AUM)
- About backing up the database and creating archiving
- About maintenance operations
- About changing the size of tables and index extents
...
| Parameter | Value | Improvement / Remarks | log_buffer|||
|---|---|---|---|---|---|
If the version <9.2.0.7, then the log_buffer = 3 MB, else the log_buffer= 16 MB. shared_ pool_size | 1/4 of the physical memory. | The shared pool should not be too small (< 250 MB) or too large (>2 GB). | log_checkpoint_timeout | 900 | |
open_cursors | 300 | Verify that you have enough open cursors for the application to execute. | |||
Processes | 300 | Verify that you have enough 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 different disks (improves loads, for example). |
Table 4 Oracle 8i parameters
...
sort_area_size
...
(1/8th of the physical memory) / (concurrent users + 10), up to 6 MB.
...
For example: If you have 2 GB of Physical Memory and 90 users, then the calculation will be:
(2 GB / 8) / 100 ~= 2.5 MB
...
db_block_buffers
...
1/3rd of the physical memory divided by the size of the db_block_size.
...
The given values help to keep the I/O wait of the PMDB queries low.
Table 5 Oracle Table 4 Oracle 9i parameters
| Parameter | Value | Improvements / Remarks | db_cache_size | Replaces parameter | pga_aggregate_target | 1/3rd of the physical memory. | workarea_size_policy | Auto | This parameter should be set to auto when we use the|
|---|---|---|---|---|---|---|---|---|---|
| session_cached_cursors | 300 | ||||||||
DB-cache_advice | Verify that this parameter is set to | This parameter checks the work on the database and helps determine the recommended size of the Buffer pool. But this damages performance, so verify that it is on only for a short period. |
Table 6 Oracle 5 Oracle 10g parameter
| Parameter | Value | Improvement / Remarks |
|---|---|---|
sga_target | Automatic memory management. 1/4th of the physical memory. | Replaces the following parameters:
Verify that they are initialized with either 0 or the minimum value you need. |
...
The following is an example of the init.ora file for a an Oracle 912.2 server with 2 GB RAM and 2 disksinstance.
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 name='ORCL' memory_target=1G processes=300 db_block_size=8192db_domain='' db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G diagnostic_dest='<ORACLE_BASE>'dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
processes=300
undo_management=AUTO remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
undo_retention=900 # 15 minutes
...
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.
...
Rollback segments (For Oracle 8i)
...
Minimum tablespace size
...
Rollback segments (For Oracle 8i)
...
Rollback segments (For Oracle 8i)
...
5 MB
| Info |
|---|
Each rollback segment should be able to grow to the size of the tablespace. |
...
Enable AUM (For Oracle 9i and later)
...
Automatic Undo Management
...
Temporary tablespace
...
Minimum temporary tablespace size
...
2 GB
| Info |
|---|
The temporary tablespace is most likely to grow (according to the PMDB load). |
...
Redo log file
...
Minimum redo log file size
...
1 GB (4 GB in large installations)
| Info |
|---|
If the redo file is switching every 20 minutes, enlarge the file. |
...
Each Rollback segment should contain a minimum of 5 extents. The size of each extent within a Rollback segment should be at least 1MB each. Their optimal size should be set to the summed size of those extents. For example, if creating a rollback segment with 5 extents of 1MB each, you 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. Verify that the recycle bin in Oracle version 10 is disabled. 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 6-7.
...
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='12.0.0'
| Anchor | ||||
|---|---|---|---|---|
|
...
Our recommendation is to disable the recycle bin and this section describes the background and the action on how to disable the recycle bin.
...
alter system set recyclebin=off scope=BOTH
| Anchor | ||||
|---|---|---|---|---|
|
The PMDB holds information for up to 3 years by default. If you want to backup this information, you should enable the Redo log archiving, and create a backup using RMAN. For more information, refer to the official Oracle Backup and Recovery documentation. If you do not create a backup, information will be lost when you have a database crash and you will not be able to recover data. If you create a backup without using the archive mode, you will be able to recover data to the time of the backup. All new operations (after the logs were archived) will be lost.
| Anchor | ||||
|---|---|---|---|---|
|
...
The PMDB holds information for up to 3 three years by default. If you want to backup back up this information, you should enable the Redo log archiving, and create a backup using RMAN or the Precise backup utility. For more information, refer to the official Oracle Backup and Recovery documentation.
If you do not create a backup, information will may be lost when you have a database crash and you will not be able to cannot recover data. If you create a backup without using the archive mode, you will be able to can recover data to the time of the backup. All new operations ( after the logs were archived ) will be are lost.
| Anchor | ||||
|---|---|---|---|---|
|
...
The following table shows the default extents sizes.
Table 8 Default 6 Default extents sizes
| Tablespace | Size |
|---|---|
| Large table | 2 MB |
| Medium table | 512 KB |
| Small table | 80 KB |
| Large index | 1 MB |
| Medium index | 256 KB |
| Small index | 80 KB |