Skip Headers

Oracle® Audit Vault Administrator's Guide Release 10.2.3 Part Number E11059-03 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# E REDO Collector Database Reference

This appendix describes recommendations for setting initialization parameters for participating source sites for Oracle Database audit sources for the following releases: Oracle9i Database release 2 (9.2), Oracle Database 10g release 1 (10.1), Oracle Database 10g release 2 (10.2), and Oracle Database 11g release 1 (11.1). It is divided into the following sections:

- Initialization Parameter Recommendations for Audit Sources on Oracle9i Database Release 2 (9.2)

- Initialization Parameter Recommendations for Audit Sources on Oracle Database 10g Release 1 (10.1)

- Initialization Parameter Recommendations for Audit Sources on Oracle Database 10g Release 2 (10.2)

- Initialization Parameter Recommendations for Audit Sources on Oracle Database 11g Release 1 (11.1)

After changing these initialization parameters described in these sections, the DBA must restart the source database before an Oracle Redo Log Collector is set up to collect audit data.

## E.1 Initialization Parameter Recommendations for Audit Sources on Oracle9i Database Release 2 (9.2)

At each participating source site, configure the initialization parameters for each database to include the following hidden parameters (see Table E-1).

Table E-1 Hidden Initialization Parameters to Be Configured for the Database Source

Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description
--- | --- | --- | ---
_first_spare_parameter=200M/(current_shared_pool_size+200M) | Mandatory | 10 | The threshold (percent) of shared_pool_size memory at which spillover to disk is triggered for captured messages
_kghdsidx_count=1 | Recommended | Range: 10 to 80 | This parameter prevents the shared_pool from being divided among cpus.
_job_queue_interval=1 | Recommended | 5 | Scan rate interval (seconds) of job queue
_spin_count=5000 | Recommended | 2000 | See the Oracle Magazine Tuning article from March 2003 for a discussion of this parameter. Set this parameter if Memory Queue and Memory Queue Subscriber latch sleeps are high.

At each participating source site, confirm that the following required initialization parameters are set appropriately for each database (see Table E-2). The SHARED_POOL_SIZE parameter is of particular importance for REDO collectors.

Table E-2 Initialization Parameters to Be Configured for the Database Source

Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description
--- | --- | --- | ---
AQ_TM_PROCESSES=4 | Mandatory | Default: 0 Range: 0 to 10 | Establishes queue monitor processes. Setting the parameter to 1 or more starts the specified number of queue monitor processes. These queue monitor processes are responsible for managing time-based operations of messages such as delay and expiration, cleaning up retained messages after the specified retention time, and cleaning up consumed messages if the retention time is zero. This parameter is required for both Streams captured messages and user-enqueued messages.
COMPATIBLE=9.2.0 | Mandatory | Default: `8.1.0` Range: `8.1.0` to Current Release Number | This parameter specifies the release with which the Oracle server must maintain compatibility. Oracle servers with different compatibility levels can interoperate. To use Streams, this parameter must be set to `9.2.0` or higher.
GLOBAL_NAMES=true | Recommended | Default: `false` Range: `true` or `false` | Specifies whether a database link is required to have the same name as the database to which it connects. If you want to use Streams to share information between databases, then set this parameter to true at each database that is participating in your Streams environment.
JOB_QUEUE_PROCESSES=4 | Mandatory | Default: 0 Range: 0 to 1000 | Specifies the number of Jnnn job queue processes for each instance (J000 ... J999). Job queue processes handle requests created by `DBMS_JOB`. You can change the setting for `JOB_QUEUE_PROCESSES` dynamically by using the `ALTER SYSTEM` statement. This parameter must be set to at least 2 at each database that is propagating events in your Streams environment, and should be set to the same value as the maximum number of jobs that can run simultaneously plus two.
LOG_PALALLELISM=1 This parameter has to be set to 1. Note that the default value is 1. | Mandatory | Default: 1 Range: 1 to 255 | Specifies the level of concurrency for redo allocation within Oracle. If you plan to run one or more capture processes on a database, then this parameter must be set to 1. Setting this parameter to 1 does not affect the parallelism of capture. You can set parallelism for a capture process using the `SET_PARAMETER` procedure in the `DBMS_CAPTURE_ADM` package.
LOGMNR_MAX_PERSISTENT_SESSIONS=3 This parameter must be set to at least 1 which is also the default value. | Mandatory | Default: 1 Range: 1 to `LICENSE_MAX_SESSIONS` | Specifies the maximum number of persistent LogMiner mining sessions that are concurrently active when all sessions are mining redo logs generated by instances. If you plan to run multiple Streams capture processes on a single database, then set this parameter equal to or higher than the number of planned capture processes.
OPEN_LINKS=4 | Recommended | Default: 4 Range: 0 to 255 | Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process. In a Streams environment, ensure that this parameter is set to the default value of 4 or higher.
PARALLEL_MAX_SERVERS=20 | Mandatory | Default: Derived from the values of the following parameters: `CPU_COUNT` `PARALLEL_ADAPTIVE_MULTI_USER` `PARALLEL_AUTOMATIC_TUNING` Range: 0 to 3599 | Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle will increase the number of processes from the number created at instance startup up to this value. In a Streams environment, each capture process and apply process can use multiple parallel execution servers. Set this initialization parameter to an appropriate value to ensure that there are enough parallel execution servers.
PROCESSES | Recommended | Default: Derived from `PARALLEL_MAX_SERVERS` Range: 6 to operating system dependent limit | Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Ensure that the value of this parameter allows for all background processes, such as locks, job queue processes, and parallel execution processes. In Streams, capture processes and apply processes use background processes and parallel execution processes, and propagation jobs use job queue processes.
SESSIONS | Recommended | Default: Derived from: (1.1 * `PROCESSES`) + 5 Range: 1 to 231 | Specifies the maximum number of sessions that can be created in the system. If you plan to run one or more capture processes or apply processes in a database, then you may need to increase the size of this parameter. Each background process in a database requires a session.
SGA_MAX_SIZE Increase by at least 200M | Mandatory | Default: Initial size of SGA at startup Range: 0 to operating system dependent limit | Specifies the maximum size of SGA for the lifetime of a database instance. If you plan to run multiple capture processes on a single database, then you may need to increase the size of this parameter.
SHARED_POOL_SIZE= (Increase by at least 200M) | Mandatory | Default: 32-bit platforms: 8 MB, rounded up to the nearest granule size 64-bit platforms: 64 MB, rounded up to the nearest granule size Range: Minimum: the granule size Maximum: operating system-dependent | Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. You should increase the size of the shared pool by 10 MB for each capture process on a database. Additional memory is required from the shared_pool for storing logical change records (LCRs) in the buffer queue. This parameter should be sized so that LCRs remain in memory as much as possible. Use the formula shared_pool_size*_first_spare_parameter/100 to calculate the point at which LCRs will spill to disk.
TIMED_STATISTICS | Recommended | Default: If `STATISTICS_LEVEL` is set to `TYPICAL` or `ALL`, then `true` If `STATISTICS_LEVEL` is set to `BASIC`, then `false` The default for `STATISTICS_LEVEL` is `TYPICAL`. Range: `true` or `false` | Specifies whether or not statistics related to time are collected. If you want to collect elapsed time statistics in the data dictionary views related to Streams, then set this parameter to true. The views that include elapsed time statistics include: `V$STREAMS_CAPTURE` `V$STREAMS_APPLY_COORDINATOR` `V$STREAMS_APPLY_READER` `V$STREAMS_APPLY_SERVER`
TRANSACTION_AUDITING=TRUE | Mandatory | Default: TRUE Range: `true` or `false` | If `TRANSACTION_AUDITING` is `true`, Oracle generates a special redo record that contains the user logon name, username, the session ID, some operating system information, and client information. For each successive transaction, Oracle generates a record that contains only the session ID. These subsequent records link back to the first record, which also contains the session ID. These records might be useful if you are using a redo log analysis tool. You can access the records by dumping the redo log. If `TRANSACTION_AUDITING` is `false`, no redo record will be generated. TRANSACTION_AUDITING must be set to TRUE for databases with a Streams capture process configured

An additional initialization parameter must be configured at each instance involved in the Oracle Real Application Clusters (Oracle RAC) configuration. In addition to the parameters referenced previously, the parameter Table E-3 should be included.

Table E-3 An Additional Initialization Parameter to Be Configured at Each Instance Involved in the Oracle RAC Configuration at the Database Source

Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description
--- | --- | --- | ---
ARCHIVE_LAG_TARGET=1800 | Recommended | Default: 0 Range: 0 or any integer in [60, 7200] | Limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after a user-specified time period elapses. If you are using Streams in a Real Application Clusters environment, then set this parameter to a value greater than zero to switch the log files automatically. See Also: The section titled "Streams Capture Processes and Oracle Real Application Clusters" in Oracle9i Streams release 2 (9.2)

## E.2 Initialization Parameter Recommendations for Audit Sources on Oracle Database 10g Release 1 (10.1)

At each participating source site, configure the initialization parameters for each database to include the following hidden parameters (see Table E-4).

Table E-4 Hidden Initialization Parameters to Be Configured for the Database Source

Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description
--- | --- | --- | ---
_job_queue_interval=1 | Recommended | 5 | Scan rate interval (seconds) of job queue
_spin_count=5000 | Recommended | 2000 | See the Oracle Magazine Tuning article from March 2003 for a discussion of this parameter. Set this parameter if Memory Queue and Memory Queue Subscriber latch sleeps are high.

At each participating source site, confirm that the following required initialization parameters are set appropriately for each database (see Table E-5).

Table E-5 Initialization Parameters to Be Configured for the Database Source

Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description
--- | --- | --- | ---
COMPATIBLE= 10.1.0 | Mandatory | Default: `9.2.0` Range: `9.2.0` to Current Release Number Modifiable?: No | This parameter specifies the release with which the Oracle server must maintain compatibility. Oracle servers with different compatibility levels can interoperate. To use the new Streams features introduced in Oracle Database 10g, this parameter must be set to `10.1.0` or higher. To use downstream capture, this parameter must be set to `10.1.0` or higher at both the source database and the downstream database.
Cursor_space_for_time= FALSE This parameter has to be set to FALSE. Note that FALSE is the default value for this parameter. | Mandatory | Default: `FALSE` Range: `FALSE` or `TRUE` | Do not change this parameter when using Streams or Logical Standby.
GLOBAL_NAMES=true | Recommended | Default: `false` Range: `true` or `false` Modifiable?: Yes | Specifies whether a database link is required to have the same name as the database to which it connects. To use Streams to share information between databases, set this parameter to `true` at each database that is participating in your Streams environment.
JOB_QUEUE_PROCESSES=4 | Mandatory | Default: 0 Range: 0 to 1000 Modifiable?: Yes | Specifies the number of Jnnn job queue processes for each instance (J000 ... J999). Job queue processes handle requests created by DBMS_JOB. This parameter must be set to at least 2 at each database that is propagating events in your Streams environment, and should be set to the same value as the maximum number of jobs that can run simultaneously plus two.
LOG_ARCHIVE_DEST_n | Recommended | Default: None Range: None Modifiable?: Yes | Defines up to ten log archive destinations, where n is 1, 2, 3, ... 10. To use downstream capture and copy the redo log files to the downstream database using log transport services, at least one log archive destination must be at the site running the downstream capture process. See Also: Oracle Data Guard Concepts and Administration
LOG_ARCHIVE_DEST_STATE_n | Recommended | Default: `enable` Range: One of the following: `alternate` `reset` `defer` `enable` Modifiable?: Yes | Specifies the availability state of the corresponding destination. The parameter suffix (1 through 10) specifies one of the ten corresponding LOG_ARCHIVE_DEST_n destination parameters. To use downstream capture and copy the redo log files to the downstream database using log transport services, ensure that the destination that corresponds to the LOG_ARCHIVE_DEST_n destination for the downstream database is set to enable.
OPEN_LINKS | Recommended | Default: 4 Range: 0 to 255 Modifiable?: No | Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process. In a Streams environment, ensure that this parameter is set to the default value of 4 or higher.
PARALLEL_MAX_SERVERS Set this parameter to at least 20. | Mandatory | Default: Derived from the values of the following parameters: `CPU_COUNT` `PARALLEL_ADAPTIVE_MULTI_USER` `PARALLEL_AUTOMATIC_TUNING` Range: 0 to 3599 Modifiable?: Yes | Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle will increase the number of processes from the number created at instance startup up to this value. In a Streams environment, each capture process and apply process can use multiple parallel execution servers. Set this initialization parameter to an appropriate value to ensure that there are enough parallel execution servers.
PROCESSES | Recommended | Default: Derived from PARALLEL_MAX_SERVERS Range: 6 to operating system dependent limit Modifiable?: No | Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Ensure that the value of this parameter allows for all background processes, such as locks, job queue processes, and parallel execution processes. In Streams, capture processes and apply processes use background processes and parallel execution processes, and propagation jobs use job queue processes.
SESSIONS | Recommended | Default: Derived from: (1.1 * PROCESSES) + 5 Range: 1 to 231 Modifiable?: No | Specifies the maximum number of sessions that can be created in the system. To run one or more capture processes or apply processes in a database, then you may need to increase the size of this parameter. Each background process in a database requires a session.
SGA_MAX_SIZE Increase by at least 200M | Mandatory | Default: Initial size of SGA at startup Range: 0 to operating system dependent limit Modifiable?: No | Specifies the maximum size of SGA for the lifetime of a database instance. To run multiple capture processes on a single database, you may need to increase the size of this parameter.
SHARED_POOL_SIZE | Recommended | Default: 32-bit platforms: 32 MB, rounded up to the nearest granule size 64-bit platforms: 84 MB, rounded up to the nearest granule size Range: Minimum: the granule size Maximum: operating system dependent Modifiable?: Yes | Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. If the `STREAMS_POOL_SIZE` initialization parameter is set to zero, then Streams can use up to 10% of the shared pool.
STREAMS_POOL_SIZE>200M If using sga_target, also increase this value by at least 200M. | Mandatory | Default: 0 Range: Minimum: 0 Maximum: operating system dependent Modifiable?: Yes | Specifies (in bytes) the size of the Streams pool. The Streams pool contains captured events. In addition, the Streams pool is used for internal communications during parallel capture and apply. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the Streams pool size is set to zero, then SGA memory used by Streams is allocated from the shared pool and can use up to 10% of the shared pool. This parameter is modifiable. However, if this parameter is set to zero when an instance starts, then increasing it beyond zero has no effect on the current instance because it is using the shared pool for Streams allocations. Also, if this parameter is set to a value greater than zero when an instance starts and is then reduced to zero when the instance is running, then Streams processes and jobs will not run. You should increase the size of the Streams pool for each of the following factors: 10 MB for each capture process parallelism 1 MB for each apply process parallelism 10 MB or more for each queue staging captured events For example, if parallelism is set to 3 for a capture process, then increase the Streams pool by 30 MB. If parallelism is set to 5 for an apply process, then increase the Streams pool by 5 MB.
TIMED_STATISTICS | Recommended | Default: If `STATISTICS_LEVEL` is set to `TYPICAL` or `ALL`, then `true` If `STATISTICS_LEVEL` is set to `BASIC`, then `false` The default for `STATISTICS_LEVEL` is `TYPICAL`. Range: `true` or `false` Modifiable?: Yes | Specifies whether or not statistics related to time are collected. To collect elapsed time statistics in the data dictionary views related to Streams, set this parameter to true. The views that include elapsed time statistics include: `V$STREAMS_CAPTURE` `V$STREAMS_APPLY_COORDINATOR` `V$STREAMS_APPLY_READER` `V$STREAMS_APPLY_SERVER`
UNDO_RETENTION=3600 | Mandatory | Default: 900 Range: 0 to 2^32-1 (max value represented by 32 bits) Modifiable?: Yes | Specifies (in seconds) the amount of committed undo information to retain in the database. For a database running one or more capture processes, ensure that this parameter is set to specify an adequate undo retention period. If you are running one or more capture processes and you are unsure about the proper setting, then try setting this parameter to at least `3600`. If you encounter "snapshot too old" errors, then increase the setting for this parameter until these errors cease. Ensure that the undo tablespace has enough space to accommodate the `UNDO_RETENTION` setting. See Also: Oracle Database Administrator's Guide for more information about the retention period and the undo tablespace

## E.3 Initialization Parameter Recommendations for Audit Sources on Oracle Database 10g Release 2 (10.2)

For best results in a REDO collector environment, set the following initialization parameters at each participating database: `compatible`, `global_names`, `_job_queue_interval`, `sga_target`, `streams_pool_size`.

At each participating source site, configure the initialization parameters for each database to include the following hidden parameters (see Table E-6).

Table E-6 Hidden Initialization Parameters to Be Configured for the Database Source

Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description
--- | --- | --- | ---
_job_queue_interval=1 | Recommended | 5 | Scan rate interval (seconds) of job queue
_spin_count=5000 | Recommended | 2000 | See the Oracle Magazine Tuning article from March 2003 for a discussion of this parameter. Set this parameter if Memory Queue and Memory Queue Subscriber latch sleeps are high.

At each participating source site, confirm that the following required initialization parameters are set appropriately for each database (see Table E-7). Enable autotuning of the various pools within the SGA, by setting `SGA_TARGET` to a large nonzero value. Leave the `STREAMS_POOL_SIZE` value set to 0. The combination of these to parameters enables autotuning of the SGA and the Streams Pool size will be automatically adjusted to meet the workload requirements.

Table E-7 Initialization Parameters to Be Configured for the Database Source

Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description
--- | --- | --- | ---
COMPATIBLE= 10.2.0 | Mandatory | Default: `10.0.0` Range: `9.2.0` to Current Release Number Modifiable?: No | This parameter specifies the release with which the Oracle server must maintain compatibility. Oracle servers with different compatibility levels can interoperate. To use the new Streams features introduced in Oracle Database 10g release 1, this parameter must be set to `10.1.0` or higher. To use downstream capture, this parameter must be set to `10.1.0` or higher at both the source database and the downstream database. To use the new Streams features introduced in Oracle Database 10g release 2, this parameter must be set to `10.2.0` or higher.
GLOBAL_NAMES=true | Recommended | Default: false Range: true or false Modifiable?: Yes | Specifies whether a database link is required to have the same name as the database to which it connects. To use Streams to share information between databases, set this parameter to `true` at each database that is participating in your Streams environment.
JOB_QUEUE_PROCESSES=4 | Mandatory | Default: 0 Range: 0 to 1000 Modifiable?: Yes | Specifies the number of Jnnn job queue processes for each instance (J000 ... J999). Job queue processes handle requests created by `DBMS_JOB`. This parameter must be set to at least 2 at each database that is propagating events in your Streams environment, and should be set to the same value as the maximum number of jobs that can run simultaneously plus two.
LOG_ARCHIVE_DEST_n | Recommended | Default: None Range: None Modifiable?: Yes | Defines up to ten log archive destinations, where n is 1, 2, 3, ... 10. To use downstream capture and copy the redo log files to the downstream database using log transport services, at least one log archive destination must be at the site running the downstream capture process. See Also: Oracle Data Guard Concepts and Administration
LOG_ARCHIVE_DEST_STATE_n | Recommended | Default: enable Range: One of the following: `alternate` `reset` `defer` `enable` Modifiable?: Yes | Specifies the availability state of the corresponding destination. The parameter suffix (1 through 10) specifies one of the ten corresponding `LOG_ARCHIVE_DEST_n` destination parameters. To use downstream capture and copy the redo log files to the downstream database using log transport services, ensure that the destination that corresponds to the `LOG_ARCHIVE_DEST_n` destination for the downstream database is set to `enable`.
OPEN_LINKS | Recommended | Default: 4 Range: 0 to 255 Modifiable?: No | Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process. In a Streams environment, ensure that this parameter is set to the default value of 4 or higher.
PARALLEL_MAX_SERVERS Set this parameter to at least 20. | Mandatory | Default: Derived from the values of the following parameters: CPU_COUNT PARALLEL_ADAPTIVE_MULTI_USER PARALLEL_AUTOMATIC_TUNING Range: 0 to 3599 Modifiable?: Yes | Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle will increase the number of processes from the number created at instance startup up to this value. In a Streams environment, each capture process and apply process can use multiple parallel execution servers. Set this initialization parameter to an appropriate value to ensure that there are enough parallel execution servers.
PROCESSES | Recommended | Default: Derived from PARALLEL_MAX_SERVERS Range: 6 to operating system dependent limit Modifiable?: No | Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Ensure that the value of this parameter allows for all background processes, such as locks, job queue processes, and parallel execution processes. In Streams, capture processes and apply processes use background processes and parallel execution processes, and propagation jobs use job queue processes.
SESSIONS | Recommended | Default: Derived from: (1.1 * PROCESSES) + 5 Range: 1 to 231 Modifiable?: No | Specifies the maximum number of sessions that can be created in the system. To run one or more capture processes or apply processes in a database, then you may need to increase the size of this parameter. Each background process in a database requires a session.
SGA_MAX_SIZE Increase by at least 200M | Mandatory | Default: Initial size of SGA at startup Range: 0 to operating system dependent limit Modifiable?: No | Specifies the maximum size of SGA for the lifetime of a database instance. To run multiple capture processes on a single database, you may need to increase the size of this parameter. See the `STREAMS_POOL_SIZE` initialization parameter for more specific recommendations.
SGA_TARGET >0 Increase this parameter by at least 200M. | Mandatory | Default: 0 (SGA autotuning is disabled) Range: 64 to operating system-dependent Modifiable?: Yes | Specifies the total size of all System Global Area (SGA) components. If this parameter is set to a nonzero value, then the size of the Streams pool is managed by Automatic Shared Memory Management. See the `STREAMS_POOL_SIZE` initialization parameter for more specific recommendations.
SHARED_POOL_SIZE=0 | Recommended | Default: 32-bit platforms: 32 MB, rounded up to the nearest granule size 64-bit platforms: 84 MB, rounded up to the nearest granule size Range: Minimum: the granule size Maximum: operating system-dependent Modifiable?: Yes | Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. If the `SGA_TARGET` and `STREAMS_POOL_SIZE` initialization parameters are set to zero, then Streams transfers an amount equal to 10% of the shared pool from the buffer cache to the Streams pool.
STREAMS_POOL_SIZE=200 | Mandatory | Default: 0 Range: Minimum: 0 Maximum: operating system-dependent Modifiable?: Yes | Specifies (in bytes) the size of the Streams pool. The Streams pool contains captured events. In addition, the Streams pool is used for internal communications during parallel capture and apply. If the `SGA_TARGET` initialization parameter is set to a nonzero value, then the Streams pool size is set by Automatic Shared memory management, and `STREAMS_POOL_SIZE` specifies the minimum size. The `STREAMS_POOL_SIZE` initialization parameter should be set to 200 MB and, if necessary, increment the `SGA_TARGET` and `SGA_MAX` initialization parameters appropriately. For example, if the `SGA_TARGET` initialization parameter is already set to 2 GB, setting `STREAMS_POOL_SIZE`=200 MB would not require that the `SGA_TARGET` initialization parameter be increased. However, if the `SGA_TARGET` initialization parameter is set to 600 MB and the `STREAMS_POOL_SIZE` initialization parameter is increased to 200 MB, then it is recommended that the `SGA_TARGET` initialization parameter value be increased similarly. This parameter is modifiable. If this parameter is reduced to zero when an instance is running, then Streams processes and jobs will not run. You should increase the size of the Streams pool for each of the following factors: 10 MB for each capture process parallelism 10 MB or more for each buffered queue. The buffered queue is where the Logical Change Records(LCRs) are stored. 1 MB for each apply process parallelism You can use the `V$STREAMS_POOL_ADVICE` dynamic performance view to determine an appropriate setting for this parameter. For example, if parallelism is set to 3 for a capture process, then increase the Streams pool by 30 MB. If parallelism is set to 5 for an apply process, then increase the Streams pool by 5 MB.
TIMED_STATISTICS | Recommended | Default: If `STATISTICS_LEVEL` is set to `TYPICAL` or `ALL`, then `true` If `STATISTICS_LEVEL` is set to `BASIC`, then `false` The default for `STATISTICS_LEVEL` is `TYPICAL`. Range: `true` or `false` Modifiable?: Yes | Specifies whether or not statistics related to time are collected. To collect elapsed time statistics in the data dictionary views related to Stream, set this parameter to true. The views that include elapsed time statistics include: `V$STREAMS_CAPTURE` `V$STREAMS_APPLY_COORDINATOR` `V$STREAMS_APPLY_READER` `V$STREAMS_APPLY_SERVER`
UNDO_RETENTION=3600 | Mandatory | Default: 900 Range: 0 to 2^32-1 (max value represented by 32 bits) Modifiable?: Yes | Specifies (in seconds) the amount of committed undo information to retain in the database. For a database running one or more capture processes, ensure that this parameter is set to specify an adequate undo retention period. If you are running one or more capture processes and you are unsure about the proper setting, then try setting this parameter to at least `3600`. If you encounter "snapshot too old" errors, then increase the setting for this parameter until these errors cease. Ensure that the undo tablespace has enough space to accommodate the `UNDO_RETENTION` setting. See Also: Oracle Database Administrator's Guide for more information about the `UNDO_RETENTION` parameter

## E.4 Initialization Parameter Recommendations for Audit Sources on Oracle Database 11g Release 1 (11.1)

For best results in a REDO collector environment, set the following initialization parameters at each participating database: `compatible`, `global_names`, `_job_queue_interval`, `sga_target`, `streams_pool_size`.

At each participating source site, configure the initialization parameters for each database to include the following hidden parameters (see Table E-6).

Table E-8 Hidden Initialization Parameters to Be Configured for the Database Source

Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description
--- | --- | --- | ---
_job_queue_interval=1 | Recommended | 5 | Scan rate interval (seconds) of job queue
_spin_count=5000 | Recommended | 2000 | See the Oracle Magazine Tuning article from March 2003 for a discussion of this parameter. Set this parameter if Memory Queue and Memory Queue Subscriber latch sleeps are high.

At each participating source site, confirm that the following required initialization parameters are set appropriately for each database (see Table E-7). Enable autotuning of the various pools within the SGA, by setting `SGA_TARGET` to a large nonzero value. Leave the `STREAMS_POOL_SIZE` value set to 0. The combination of these to parameters enables autotuning of the SGA and the Streams Pool size will be automatically adjusted to meet the workload requirements.

Table E-9 Initialization Parameters to Be Configured for the Database Source

Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description
--- | --- | --- | ---
COMPATIBLE= 11.1.0 | Mandatory | Default: `11.1.0` Range: `10.1.0` to Current Release Number Modifiable?: No | This parameter specifies the release with which the Oracle server must maintain compatibility. Oracle servers with different compatibility levels can interoperate. To use the new Streams features introduced in Oracle Database 10g release 1, this parameter must be set to `10.1.0` or higher. To use downstream capture, this parameter must be set to `10.1.0` or higher at both the source database and the downstream database. To use the new Streams features introduced in Oracle Database 10g release 2, this parameter must be set to `10.2.0` or higher. To use the new Streams features introduced in Oracle Database 11g release 1, this parameter must be set to `11.1.0` or higher.
GLOBAL_NAMES=true | Recommended | Default: false Range: true or false Modifiable?: Yes | Specifies whether a database link is required to have the same name as the database to which it connects. To use Streams to share information between databases, set this parameter to `true` at each database that is participating in your Streams environment.
JOB_QUEUE_PROCESSES=4 | Mandatory | Default: 0 Range: 0 to 1000 Modifiable?: Yes | Specifies the number of Jnnn job queue processes for each instance (J000 ... J999). Job queue processes handle requests created by `DBMS_JOB`. This parameter must be set to at least 2 at each database that is propagating events in your Streams environment, and should be set to the same value as the maximum number of jobs that can run simultaneously plus two.
LOG_ARCHIVE_DEST_n | Recommended | Default: None Range: None Modifiable?: Yes | Defines up to ten log archive destinations, where n is 1, 2, 3, ... 10. To use downstream capture and copy the redo log files to the downstream database using log transport services, at least one log archive destination must be at the site running the downstream capture process. See Also: Oracle Data Guard Concepts and Administration
LOG_ARCHIVE_DEST_STATE_n | Recommended | Default: enable Range: One of the following: `alternate` `reset` `defer` `enable` Modifiable?: Yes | Specifies the availability state of the corresponding destination. The parameter suffix (1 through 10) specifies one of the ten corresponding `LOG_ARCHIVE_DEST_n` destination parameters. To use downstream capture and copy the redo log files to the downstream database using log transport services, ensure that the destination that corresponds to the `LOG_ARCHIVE_DEST_n` destination for the downstream database is set to `enable`.
OPEN_LINKS | Recommended | Default: 4 Range: 0 to 255 Modifiable?: No | Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process. In a Streams environment, ensure that this parameter is set to the default value of 4 or higher.
PROCESSES | Recommended | Default: Derived from PARALLEL_MAX_SERVERS Range: 6 to operating system dependent limit Modifiable?: No | Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Ensure that the value of this parameter allows for all background processes, such as locks, job queue processes, and parallel execution processes. In Streams, capture processes and apply processes use background processes and parallel execution processes, and propagation jobs use job queue processes.
SESSIONS | Recommended | Default: Derived from: (1.1 * PROCESSES) + 5 Range: 1 to 231 Modifiable?: No | Specifies the maximum number of sessions that can be created in the system. To run one or more capture processes or apply processes in a database, then you may need to increase the size of this parameter. Each background process in a database requires a session.
SGA_MAX_SIZE Increase by at least 200M | Mandatory | Default: Initial size of SGA at startup Range: 0 to operating system dependent limit Modifiable?: No | Specifies the maximum size of SGA for the lifetime of a database instance. To run multiple capture processes on a single database, you may need to increase the size of this parameter. See the `STREAMS_POOL_SIZE` initialization parameter for more specific recommendations.
SGA_TARGET >0 Increase this parameter by at least 200M. | Mandatory | Default: 0 (SGA autotuning is disabled) Range: 64 to operating system-dependent Modifiable?: Yes | Specifies the total size of all System Global Area (SGA) components. If this parameter is set to a nonzero value, then the size of the Streams pool is managed by Automatic Shared Memory Management. See the `STREAMS_POOL_SIZE` initialization parameter for more specific recommendations.
SHARED_POOL_SIZE=0 | Recommended | Default: 32-bit platforms: 32 MB, rounded up to the nearest granule size 64-bit platforms: 84 MB, rounded up to the nearest granule size Range: Minimum: the granule size Maximum: operating system-dependent Modifiable?: Yes | Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. If the `SGA_TARGET` and `STREAMS_POOL_SIZE` initialization parameters are set to zero, then Streams transfers an amount equal to 10% of the shared pool from the buffer cache to the Streams pool. The `STREAMS_POOL_SIZE` initialization parameter should be set to 200 MB and, if necessary, increment the `SGA_TARGET` and `SGA_MAX` initialization parameters appropriately. For example, if the `SGA_TARGET` initialization parameter is already set to 2 GB, setting `STREAMS_POOL_SIZE`=200 MB would not require that the `SGA_TARGET` initialization parameter be increased. However, if the `SGA_TARGET` initialization parameter is set to 600 MB and the `STREAMS_POOL_SIZE` initialization parameter is increased to 200 MB, then it is recommended that the `SGA_TARGET` initialization parameter value be increased similarly.
STREAMS_POOL_SIZE=200 | Mandatory | Default: 0 Range: Minimum: 0 Maximum: operating system-dependent Modifiable?: Yes | Specifies (in bytes) the size of the Streams pool. The Streams pool contains captured events. In addition, the Streams pool is used for internal communications during parallel capture and apply. If the `SGA_TARGET` initialization parameter is set to a nonzero value, then the Streams pool size is set by Automatic Shared memory management, and `STREAMS_POOL_SIZE` specifies the minimum size. This parameter is modifiable. If this parameter is reduced to zero when an instance is running, then Streams processes and jobs will not run. You should increase the size of the Streams pool for each of the following factors: 10 MB for each capture process parallelism 10 MB or more for each buffered queue. The buffered queue is where the Logical Change Records(LCRs) are stored. 1 MB for each apply process parallelism You can use the `V$STREAMS_POOL_ADVICE` dynamic performance view to determine an appropriate setting for this parameter. For example, if parallelism is set to 3 for a capture process, then increase the Streams pool by 30 MB. If parallelism is set to 5 for an apply process, then increase the Streams pool by 5 MB.
TIMED_STATISTICS | Recommended | Default: If `STATISTICS_LEVEL` is set to `TYPICAL` or `ALL`, then `true` If `STATISTICS_LEVEL` is set to `BASIC`, then `false` The default for `STATISTICS_LEVEL` is `TYPICAL`. Range: `true` or `false` Modifiable?: Yes | Specifies whether or not statistics related to time are collected. To collect elapsed time statistics in the data dictionary views related to Stream, set this parameter to true. The views that include elapsed time statistics include: `V$STREAMS_CAPTURE` `V$STREAMS_APPLY_COORDINATOR` `V$STREAMS_APPLY_READER` `V$STREAMS_APPLY_SERVER`
UNDO_RETENTION=3600 | Mandatory | Default: 900 Range: 0 to 2^32-1 (max value represented by 32 bits) Modifiable?: Yes | Specifies (in seconds) the amount of committed undo information to retain in the database. For a database running one or more capture processes, ensure that this parameter is set to specify an adequate undo retention period. If you are running one or more capture processes and you are unsure about the proper setting, then try setting this parameter to at least `3600`. If you encounter "snapshot too old" errors, then increase the setting for this parameter until these errors cease. Ensure that the undo tablespace has enough space to accommodate the `UNDO_RETENTION` setting. See Also: Oracle Database Administrator's Guide for more information about the `UNDO_RETENTION` parameter