12 REDO Collector Database Reference

This chapter contains:

About the Recommended Settings for the REDO Collector
Recommended Oracle Streams Supplemental Logging Recommended Oracle Streams Supplemental Logging
Oracle Database 11g Release 2 (11.2) Audit Source Parameter Recommendations
Oracle Database 11g Release 1 (11.1) Audit Source Parameter Recommendations
Oracle Database 10g Release 2 (10.2) Audit Source Parameter Recommendations
Oracle Database 10g Release 1 (10.1) Audit Source Parameter Recommendations
Oracle9i Database Release 2 (9.2) Audit Source Parameter Recommendations

12.1 About the Recommended Settings for the REDO Collector

This chapter describes recommendations for setting initialization parameters if you plan to use the REDO collector to collect audit data. After you change the initialization parameters described in these sections, you must restart the source database before configuring the REDO collect to collect audit data.

12.2 Recommended Oracle Streams Supplemental Logging

Oracle recommends that you enable Oracle Streams supplemental logging for at minimum the primary key columns. This enables auditors to identify the row for which they see before and after values. In addition to logging these columns, you should log any other columns that your site requires.

12.3 Oracle Database 11g Release 2 (11.2) Audit Source Parameter Recommendations

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.

Table 12-4 lists the initialization parameters that you must configure for each source database that will use the REDO log collector.

Table 12-1 Initialization Parameters to Be Configured for the 11.2 Source Database

Parameter	Mandatory or Recommended Parameter	Default Value	Description
`COMPATIBLE`	Mandatory	Default: `11.2.0` Range: `10.0.0` to default release 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 Oracle Streams features introduced in Oracle Database 11g Release 2, this parameter must be set to `11.2.0` or higher.
`GLOBAL_NAMES`	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 Oracle Streams to share information between databases, set this parameter to `true` at each database that is participating in your Oracle Streams environment.
`LOG_ARCHIVE_CONFIG`	Recommended	Default: `'SEND, RECEIVE, NODG_CONFIG'` Range: Values: `SEND` `NOSEND` `RECEIVE` `NORECEIVE` `DG_CONFIG` `NODG_CONFIG` Modifiable? Yes	Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique database names (`DB_UNIQUE_NAME`) for each database in the Data Guard configuration To use downstream capture and copy the redo data to the downstream database using redo transport services, specify the `DB_UNIQUE_NAME` of the source database and the downstream database using the `DG_CONFIG` attribute. This parameter must be set at both the source database and the downstream database.
`LOG_ARCHIVE_DEST_n`	Recommended	Default: None Range: None Modifiable? Yes	Defines up to 31 log archive destinations, where `n` is `1`, `2`, `3`, ... `31`. To use downstream capture and copy the redo data to the downstream database using redo transport services, at least one log archive destination must be set at the site running the downstream capture process.
`LOG_ARCHIVE_DEST_STATE_n`	Recommended	Default: `enable` Range: One of the following: `alternate` `defer` `enable` Modifiable? Yes	Specifies the availability state of the corresponding destination. The parameter suffix (`1` through `31`) specifies one of the corresponding `LOG_ARCHIVE_DEST_n` destination parameters. To use downstream capture and copy the redo data to the downstream database using redo transport services, ensure that the destination that corresponds to the `LOG_ARCHIVE_DEST_n` destination for the downstream database is set to `enable`.
`LOG_BUFFER`	Recommended	Default: `5` `MB` to `32` `MB` depending on configuration Range: Operating system-dependent Modifiable? No	Specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. If an Oracle Streams capture process is running on the database, then set this parameter properly so that the capture process reads redo log records from the redo log buffer rather than from the hard disk.
`MEMORY_MAX_TARGET`	Recommended	Default: `0` Range: `0` to the physical memory size available to Oracle Database Modifiable? No	Specifies the maximum systemwide usable memory for an Oracle database. If the `MEMORY_TARGET` parameter is set to a nonzero value, then set this parameter to a large nonzero value if you must specify the maximum memory usage of the Oracle database.
`MEMORY_TARGET`	Recommended	Default: `0` Range: `152` `MB` to `MEMORY_MAX_TARGET` setting Modifiable? Yes	Specifies the systemwide usable memory for an Oracle database. Oracle recommends enabling the autotuning of the memory usage of an Oracle database by setting `MEMORY_TARGET` to a large nonzero value (if this parameter is supported on your platform).
`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 an Oracle Streams environment, ensure that this parameter is set to the default value of `4` or higher.
`PROCESSES`	Recommended	Default: `100` Range: `6` to operating system-dependent 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 and slave processes. In Oracle Streams, capture processes, apply processes, XStream inbound servers, and XStream outbound servers use background processes. Propagations use background processes in combined capture and apply configurations. Propagations use Oracle Scheduler slave processes in configurations that do not use combined capture and apply.
`SESSIONS`	Recommended	Default: Derived from: `(1.1 * PROCESSES) + 5` Range: `1` to `2`³¹ Modifiable? No	Specifies the maximum number of sessions that can be created in the system. To run one or more capture processes, apply processes, XStream outbound servers, or XStream inbound servers in a database, you might need to increase the size of this parameter. Each background process in a database requires a session.
`SGA_MAX_SIZE`	Mandatory	Default: Initial size of SGA at startup Range: `0` to operating system-dependent Modifiable? No	Specifies the maximum size of System Global Area (SGA) for the lifetime of a database instance. If the `SGA_TARGET` parameter is set to a nonzero value, then set this parameter to a large nonzero value if you must specify the SGA size.
`SGA_TARGET`	Mandatory	Default: `0` (SGA autotuning is disabled) Range: `64` `MB` to operating system-dependent Modifiable? Yes	Specifies the total size of all System Global Area (SGA) components. If `MEMORY_MAX_TARGET` and `MEMORY_TARGET` are set to `0` (zero), then Oracle recommends enabling the autotuning of SGA memory by setting `SGA_TARGET` to a large nonzero value. If this parameter is set to a nonzero value, then the size of the Oracle Streams pool is managed by Automatic Shared Memory Management.
`SHARED_POOL_SIZE`	Recommended	Default: When `SGA_TARGET` is set to a nonzero value: If the parameter is not specified, then the default is `0` (internally determined by Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the shared memory pool. When `SGA_TARGET` is not set (32-bit platforms): 64 MB, rounded up to the nearest granule size. When `SGA_TARGET` is not set (64-bit platforms): 128 MB, rounded up to the nearest granule size. Range: The granule size to 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 `MEMORY_MAX_TARGET`, `MEMORY_TARGET`, `SGA_TARGET`, and `STREAMS_POOL_SIZE` initialization parameters are set to zero, then Oracle Streams transfers an amount equal to 10% of the shared pool from the buffer cache to the Oracle Streams pool.
`STREAMS_POOL_SIZE`	Mandatory	Default: `0` Range: `0` to operating system-dependent limit Modifiable? Yes	Specifies (in bytes) the size of the Oracle Streams pool. The Oracle Streams pool contains buffered queue messages. In addition, the Oracle Streams pool is used for internal communications during parallel capture and apply. If the `MEMORY_TARGET` or `MEMORY_MAX_TARGET` initialization parameter is set to a nonzero value, then the Oracle Streams pool size is set by Automatic Memory Management, and `STREAMS_POOL_SIZE` specifies the minimum size. If the `SGA_TARGET` initialization parameter is set to a nonzero value, then the Oracle 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 Oracle Streams processes and jobs might not run. Ensure that there is enough memory to accommodate the Oracle Streams components. The following are the minimum requirements: 15 MB for each capture process parallelism 10 MB or more for each buffered queue. The buffered queue is where the buffered messages are stored. 1 MB for each apply process parallelism 1 MB for each XStream outbound server 1 MB for each XStream inbound server parallelism For example, if parallelism is set to 3 for a capture process, then at least 45 MB is required for the capture process. If a database has two buffered queues, then at least 20 MB is required for the buffered queues. If parallelism is set to 4 for an apply process, then at least 4 MB is required for the apply process. You can use the `V$STREAMS_POOL_ADVICE` dynamic performance view to determine an appropriate setting for this parameter.
`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 statistics related to time are collected. To collect elapsed time statistics in the dynamic performance views related to Oracle 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`	Recommended	Default: `900` Range: `0` to 2³² - 1 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 run 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.

12.4 Oracle Database 11g Release 1 (11.1) Audit Source Parameter Recommendations

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.

Table 12-2 describes the hidden parameter that you must configure for each source database that will use the REDO log collector.

Table 12-2 Hidden Initialization Parameters to Be Configured for the 11.1 Source Database

Parameter Name and Recommendation	Mandatory or Recommended Parameter	Default Value	Description
`_job_queue_interval=1`	Recommended	5	Scan rate interval (seconds) of job queue

Table 12-4 lists the initialization parameters that you must configure for each source database that will use the REDO log collector. 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 12-3 Initialization Parameters to Be Configured for the 11.1 Source Database

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.
J`OB_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 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`	Recommended	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

12.5 Oracle Database 10g Release 2 (10.2) Audit Source Parameter Recommendations

Table 12-4 describes the hidden parameter that you must configure for each source database that will use the REDO log collector.

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

Parameter Name and Recommendation	Mandatory or Recommended Parameter	Default Value	Description
`_job_queue_interval=1`	Recommended	5	Scan rate interval (seconds) of job queue

Table 12-5 lists the initialization parameters that you must configure for each source database. 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 12-5 Initialization Parameters to Be Configured for the 10.2 Source Database

Parameter Name and Recommendation Mandatory or Recommended Parameter Default Value Description

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 database must maintain compatibility. Oracle databases with different compatibility levels can interoperate. To use the new Streams features introduced in Oracle Database 10g release 1, set this parameter to `10.1.0` or higher. To use downstream capture, set this parameter `10.1.0` or higher for both the source database and the downstream database. To use the new Streams features introduced in Oracle Database 10g release 2, set this parameter 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` for each database that participates in your Streams environment.
`JOB_QUEUE_PROCESSES=4`	Mandatory	Default: 0 Range: 0 to 1000 Modifiable? Yes	Specifies the number of job queue processes for each instance (J000 ... J999). Job queue processes handle requests created by the `DBMS_JOB` PL/SQL package. Set this parameter to at least `2` for each database that propagates events in your Streams environment, and then set it to the same value as the maximum number of jobs that can run simultaneously, plus `2`.
`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, external procedures, and cartridges, each of which uses a separate process. In a Streams environment, set this parameter 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 Database increases 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 an Oracle database. 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 you set this parameter 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 you set the `SGA_TARGET` and `STREAMS_POOL_SIZE` initialization parameters to zero, then Streams transfers an amount equal to 10 percent 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, Oracle Database uses the Streams pool for internal communications during parallel capture and apply. If you set the `SGA_TARGET` initialization parameter to a nonzero value, then the Streams pool size is set by Automatic Shared memory management, and `STREAMS_POOL_SIZE` specifies the minimum size. You should set the `STREAMS_POOL_SIZE` initialization parameter 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 does not require you to increase the `SGA_TARGET` initialization parameter setting. 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 you should increase the `SGA_TARGET` initialization parameter value similarly. This parameter is modifiable. If you reduce this parameter setting to zero when an instance is running, then Streams processes and jobs cannot 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 you set parallelism to 3 for a capture process, then increase the Streams pool by 30 MB. If you set parallelism 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 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 following views include elapsed time statistics: `V$STREAMS_CAPTURE` `V$STREAMS_APPLY_COORDINATOR` `V$STREAMS_APPLY_READER` `V$STREAMS_APPLY_SERVER`
`UNDO_RETENTION=3600`	Recommended	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, set this parameter 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

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 database must maintain compatibility. Oracle databases with different compatibility levels can interoperate.

To use the new Streams features introduced in Oracle Database 10g release 1, set this parameter to 10.1.0 or higher. To use downstream capture, set this parameter 10.1.0 or higher for both the source database and the downstream database.

To use the new Streams features introduced in Oracle Database 10g release 2, set this parameter 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 for each database that participates in your Streams environment.

JOB_QUEUE_PROCESSES=4

Mandatory

Default: 0

Range: 0 to 1000

Modifiable? Yes

Specifies the number of job queue processes for each instance (J000 ... J999). Job queue processes handle requests created by the DBMS_JOB PL/SQL package.

Set this parameter to at least 2 for each database that propagates events in your Streams environment, and then set it to the same value as the maximum number of jobs that can run simultaneously, plus 2.

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.

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, external procedures, and cartridges, each of which uses a separate process.

In a Streams environment, set this parameter 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 Database increases 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 an Oracle database.

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 you set this parameter 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 you set the SGA_TARGET and STREAMS_POOL_SIZE initialization parameters to zero, then Streams transfers an amount equal to 10 percent 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, Oracle Database uses the Streams pool for internal communications during parallel capture and apply.

If you set the SGA_TARGET initialization parameter to a nonzero value, then the Streams pool size is set by Automatic Shared memory management, and STREAMS_POOL_SIZE specifies the minimum size.

You should set the STREAMS_POOL_SIZE initialization parameter 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 does not require you to increase the SGA_TARGET initialization parameter setting. 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 you should increase the SGA_TARGET initialization parameter value similarly.

This parameter is modifiable. If you reduce this parameter setting to zero when an instance is running, then Streams processes and jobs cannot 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 you set parallelism to 3 for a capture process, then increase the Streams pool by 30 MB. If you set parallelism 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 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 following views include elapsed time statistics:

V$STREAMS_CAPTURE

V$STREAMS_APPLY_COORDINATOR

V$STREAMS_APPLY_READER

V$STREAMS_APPLY_SERVER

UNDO_RETENTION=3600

Recommended

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, set this parameter 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

12.6 Oracle Database 10g Release 1 (10.1) Audit Source Parameter Recommendations

Table 12-6 describes the hidden parameter that you must configure for each source database that will use the REDO log collector.

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

Parameter Name and Recommendation	Mandatory or Recommended Parameter	Default Value	Description
`_job_queue_interval=1`	Recommended	5	Scan rate interval (seconds) of job queue

Table 12-7 lists the initialization parameters that you must configure for each source database that will use the REDO log collector.

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

Parameter Name and Recommendation Mandatory or Recommended Parameter Default Value Description

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 database must maintain compatibility. Oracle databases with different compatibility levels can interoperate. To use the new Streams features introduced in Oracle Database 10g, set this parameter to `10.1.0` or higher. To use downstream capture, set the parameter to `10.1.0` or higher for 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` for each database in your Streams environment.
`JOB_QUEUE_PROCESSES=4`	Mandatory	Default: 0 Range: 0 to 1000 Modifiable? Yes	Specifies the number of job queue processes for each instance (J000 ... J999). Job queue processes handle requests created by the `DBMS_JOB` PL/SQL package. Set this parameter to at least 2 at each database that propagates events in your Streams environment, and then set it to the same value as the maximum number of jobs that can run simultaneously, plus `2`.
`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, have at least one log archive destination on the site that runs 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, external procedures, and cartridges, each of which uses a separate process. In a Streams environment, set this parameter 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 Database increases 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 Database. 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 you set the `STREAMS_POOL_SIZE` initialization parameter to zero, then Streams can use up to 10 percent 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, Oracle Database uses the Streams pool for internal communications during parallel capture and apply. If the size of the Streams pool is greater than zero, then Oracle Database allocates any SGA memory used by Streams from the Streams pool. If you set the Streams pool size to zero, then Oracle Database allocates SGA memory used by Streams from the shared pool and can use up to 10 percent of the shared pool. You can modify this parameter. However, if you set this parameter to zero when a database 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 you set this parameter to a value greater than zero when an instance starts and is then reduce it 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, suppose you set parallelism to `3` for a capture process, and then increase the Streams pool by 30 MB. If you set parallelism to 5 for an apply process, then you must 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 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 following views include elapsed time statistics: `V$STREAMS_CAPTURE` `V$STREAMS_APPLY_COORDINATOR` `V$STREAMS_APPLY_READER` `V$STREAMS_APPLY_SERVER`
`UNDO_RETENTION=3600`	Recommended	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, set this parameter 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

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 database must maintain compatibility. Oracle databases with different compatibility levels can interoperate.

To use the new Streams features introduced in Oracle Database 10g, set this parameter to 10.1.0 or higher. To use downstream capture, set the parameter to 10.1.0 or higher for 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 for each database in your Streams environment.

JOB_QUEUE_PROCESSES=4

Mandatory

Default: 0

Range: 0 to 1000

Modifiable? Yes

Specifies the number of job queue processes for each instance (J000 ... J999). Job queue processes handle requests created by the DBMS_JOB PL/SQL package.

Set this parameter to at least 2 at each database that propagates events in your Streams environment, and then set it to the same value as the maximum number of jobs that can run simultaneously, plus 2.

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, have at least one log archive destination on the site that runs the downstream capture process.

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.

OPEN_LINKS

Recommended

Default: 4

Range: 0 to 255

Modifiable? No

In a Streams environment, set this parameter 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

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 Database.

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 you set the STREAMS_POOL_SIZE initialization parameter to zero, then Streams can use up to 10 percent 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

If the size of the Streams pool is greater than zero, then Oracle Database allocates any SGA memory used by Streams from the Streams pool. If you set the Streams pool size to zero, then Oracle Database allocates SGA memory used by Streams from the shared pool and can use up to 10 percent of the shared pool.

You can modify this parameter. However, if you set this parameter to zero when a database 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 you set this parameter to a value greater than zero when an instance starts and is then reduce it 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, suppose you set parallelism to 3 for a capture process, and then increase the Streams pool by 30 MB. If you set parallelism to 5 for an apply process, then you must 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 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 following views include elapsed time statistics:

V$STREAMS_CAPTURE

V$STREAMS_APPLY_COORDINATOR

V$STREAMS_APPLY_READER

V$STREAMS_APPLY_SERVER

UNDO_RETENTION=3600

Recommended

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, set this parameter to specify an adequate undo retention period.

See Also: Oracle Database Administrator's Guide for more information about the retention period and the undo tablespace

12.7 Oracle9i Database Release 2 (9.2) Audit Source Parameter Recommendations

Table 12-8 describes the hidden parameter that you must configure for each source database that will use the REDO log collector.

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

Parameter Name and Recommendation Mandatory or Recommended Parameter Default Value Description

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

_first_spare_parameter=200M/(current_shared_pool_size+200M)

Mandatory

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

Scan rate interval (seconds) of job queue

Table 12-9 lists the initialization parameters that you must configure for each source database that will use the REDO log collector. The SHARED_POOL_SIZE parameter is of particular importance for REDO collectors.

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

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 higher starts the specified number of queue monitor processes. These queue monitor processes manage time-based operations of messages such as delay and expiration, clean up retained messages after the specified retention time, and clean 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 database must maintain compatibility. Oracle databases with different compatibility levels can interoperate. To use Streams, then set this parameter 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` for each database that in your Streams environment.
`JOB_QUEUE_PROCESSES=4`	Mandatory	Default: 0 Range: 0 to 1000	Specifies the number of 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` SQL statement. Set this parameter to at least 2 for each database that propagates events in your Streams environment, and set it to the same value as the maximum number of jobs that can run simultaneously, plus 2.
`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 set this parameter to 1. Setting this parameter to 1 does not affect the parallelism of capture. You can set parallelism for a capture process running 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, external procedures, and cartridges, each of which uses a separate process. In a Streams environment, set this parameter 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 Database increases 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 Database. 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 to store logical change records (LCRs) in the buffer queue. Size this parameter so that LCRs remain in memory as long as possible. Use the following formula to calculate the point at which LCRs will spill to disk. `SHARED_POOL_SIZE * _first_spare_parameter / 100`
`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 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 following views include elapsed time statistics: `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 set to `true`, Oracle Database 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 Database 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 can 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. Set `TRANSACTION_AUDITING` to `TRUE` for databases that have a Streams capture process configured

Table 12-10 describes the initialization parameter that you must configure for an Oracle Real Application Clusters (Oracle RAC) environment, in addition to the parameters described in Table 12-8 and Table 12-9. Configure this parameter on each Oracle RAC instance.

Table 12-10 ARCHIVE_LAG_TARGET Recommended Setting

Parameter Name and Recommendation Mandatory or Recommended Parameter Default Value Description

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 an Oracle 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)

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 an Oracle 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)