About the Recommended Settings for Collection from REDO Logs
Oracle Database 11g Release 2 (11.2) and 12c Secured Target Audit Parameter Recommendations
Oracle Database 11g Release 1 (11.1) Secured Target Audit Parameter Recommendations
Oracle Database 10g Release 2 (10.2) Secured Target Audit Parameter Recommendations
This chapter describes recommendations for setting initialization parameters if you plan to use the TRANSACTION LOG audit trail type to collect audit data from the REDO logs of an Oracle Database secured target. After you change the initialization parameters described in these sections, you must restart the secured target database before configuring the TRANSACTION LOG audit trail to collect audit data.
See Also:
"Oracle Database Setup Scripts" for instructions on setting up privileges in the Oracle Database for collecting audit data from the REDO logs.
Oracle Audit Vault and Database Firewall Auditor's Guide for instructions on creating a capture rule for redo log files
For best results in a REDO collection environment, set the following initialization parameters at each participating database: COMPATIBLE
, GLOBAL_NAMES
, _job_queue_interval
, SGA_TARGET
, STREAMS_POOL_SIZE
.
Note:
Oracle AVDF does not support Oracle 12c pluggable databases (PDBs) or multitenant container databases (CDBs).Table C-1 lists the initialization parameters that you must configure for each secured target database that will use the TRANSACTION LOG audit trail.
Table C-1 Initialization Parameters for an Oracle 11.2 or 12c Secured Target Database
Parameter | Mandatory or Recommended Parameter | Default Value | Description |
---|---|---|---|
Mandatory |
Default: Range: 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 |
|
Recommended |
Default: Range: Modifiable? Yes |
Specifies whether a database link is required to have the same name as the database to which it connects. Recommended value is Ensure that the global name for the secured target database is a fully qualified name (for example,
To use Oracle Streams to share information between databases, set this parameter to |
|
Recommended |
Default: Range: Values:
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 ( To use downstream capture and copy the redo data to the downstream database using redo transport services, specify the |
|
Recommended |
Default: None Range: None Modifiable? Yes |
Defines up to 31 log archive destinations, where 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. |
|
Recommended |
Default: Range: One of the following:
Modifiable? Yes |
Specifies the availability state of the corresponding destination. The parameter suffix ( 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 |
|
Recommended |
Default: 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. |
|
Recommended |
Default: Range: Modifiable? No |
Specifies the maximum systemwide usable memory for an Oracle database. If the |
|
Recommended |
Default: Range: 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 |
|
Recommended |
Default: Range: 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 |
|
Recommended |
Default: Range: 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. |
|
Recommended |
Default: Derived from:
Range: 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. |
|
Mandatory |
Default: Initial size of SGA at startup Range: Modifiable? No |
Specifies the maximum size of System Global Area (SGA) for the lifetime of a database instance. If the |
|
Mandatory |
Default: Range: Modifiable? Yes |
Specifies the total size of all System Global Area (SGA) components. If If this parameter is set to a nonzero value, then the size of the Oracle Streams pool is managed by Automatic Shared Memory Management. |
|
Recommended |
Default: When When 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 |
|
Mandatory |
Default: Range: 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 If the 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:
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 |
|
Recommended |
Default: If If The default for Range: 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 |
|
Recommended |
Default: Range: 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 |
For best results in a REDO collection environment, set the following initialization parameters at each participating database: compatible
, GLOBAL_NAMES
, _job_queue_interval
, SGA_TARGET
, STREAMS_POOL_SIZE
.
Table C-2 describes the hidden parameter that you must configure for each secured target database that will use the TRANSACTION LOG audit trail.
Table C-2 Hidden Initialization Parameters for aA Release 11.1 Secured Target Database
Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description |
---|---|---|---|
|
Recommended |
5 |
Scan rate interval (seconds) of job queue |
Table C-3 lists the initialization parameters that you must configure for each secured target database that will use the TRANSACTION LOG audit trail. 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 C-3 Initialization Parameters for a Release 11.1 Secured Target Database
Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description |
---|---|---|---|
|
Mandatory |
Default: Range: 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 To use the new Streams features introduced in Oracle Database 10g release 2, this parameter must be set to To use the new Streams features introduced in Oracle Database 11g release 1, this parameter must be set to |
|
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 |
J |
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 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. |
|
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. |
|
Recommended |
Default: enable Range: One of the following:
Modifiable? Yes |
Specifies the availability state of the corresponding destination. The parameter suffix (1 through 10) specifies one of the ten corresponding 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 |
|
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. |
|
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. |
|
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. |
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 |
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 |
|
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 The |
|
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 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 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. |
|
Recommended |
Default: If If The default for Range: 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:
|
|
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 See Also: Oracle Database Administrator's Guide for more information about the |
For best results in a REDO collection environment, set the following initialization parameters at each participating database: COMPATIBLE
, GLOBAL_NAMES
, _job_queue_interval
, SGA_TARGET
, STREAMS_POOL_SIZE
.
Table C-4 describes the hidden parameter that you must configure for each secured target database that will use the TRANSACTION LOG audit trail.
Table C-4 Hidden Initialization Parameters for a Release 10.2 Secured Target Database
Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description |
---|---|---|---|
|
Recommended |
5 |
Scan rate interval (seconds) of job queue |
Table C-5 lists the initialization parameters that you must configure for each secured target 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 C-5 Initialization Parameters for a Release 10.2 Secured Target Database
Parameter Name and Recommendation | Mandatory or Recommended Parameter | Default Value | Description |
---|---|---|---|
|
Mandatory |
Default: Range: 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 To use the new Streams features introduced in Oracle Database 10g release 2, set this parameter to |
|
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 |
|
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 Set this parameter to at least |
|
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. |
|
Recommended |
Default: enable Range: One of the following:
Modifiable? Yes |
Specifies the availability state of the corresponding destination. The parameter suffix (1 through 10) specifies one of the ten corresponding 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 |
|
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 |
Set this parameter to at least 20. |
Mandatory |
Default: Derived from the values of the following parameters:
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. |
|
Recommended |
Default: Derived from 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. |
|
Recommended |
Default: Derived from: (1.1 * 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. |
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 |
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 |
|
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 |
|
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 You should set the 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:
You can use the 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. |
|
Recommended |
Default: If If The default for Range: 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:
|
|
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 See Also: Oracle Database Administrator's Guide for more information about the |