Configure Oracle10g bidirectional stream Replication

Building an Oracle two-way stream replication is a very complicated process. The purpose of writing this document is to list the tasks that need to be done in an organized manner as much as possible to help DBAs build a stream replication environment more effectively.

Building an Oracle two-way stream replication is a very complicated process. The purpose of writing this document is to list the tasks that need to be done in an organized manner as much as possible to help DBAs build a stream replication environment more effectively.

Building an Oracle two-way stream replication is a very complicated process. The purpose of writing this document is to list what needs to be done in an organized manner as much as possible to help DBAs build a stream replication environment more effectively.

1. the scott mode is used as a copy example. Generally, this scott mode exists only when the sample schema is installed during database creation. at least ensure that the schema exists in the source database, so that it can be initialized to the target database.

2. Create a strmadmin stream management user in the source and target databases. You can also choose another name. At the same time, the tablespace used by streams should be created in both databases so that logmnr can use it:

Create tablespace streams_tbs DATAFILE 'xxxx' SIZE 100 m reuse autoextend on maxsize unlimited;

/* Logmnr in 10g r2 uses the SYSAUX tablespace by default */

Exec DBMS_LOGMNR_D.SET_TABLESPACE ('streams _ tbs ');

/* After creating the tablespace, create the strmadmin user */

Create user strmadmin identified by strmadmin
Default tablespace streams_tbs
Quota unlimited on streams_tbs;

Grant dba to strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (
Grantee => 'strmadmin ',
Grant_privileges => true );
END;
/

/* Query the dba_streams_administrator view to check whether the user has correctly granted the streaming management permission */

SQL> SELECT * FROM dba_streams_administrator;

USERNAME LOC ACC
------------------------------------
MACLEAN YES
STRMADMIN YES

3. You need to set reasonable instance initialization parameters in the 2-side database. Let's take 10g release2 as an example:

Parameter Name and recommended value: _ job_queue_interval = 1
Description: scan parameters of the job Queue. The default value is 5, that is, one scan within 5 seconds.
For what reason: setting a smaller _ job_queue_interval is conducive to the propagation job.
How to set: alter system set "_ job_queue_interval" = 1 scope = spfile;

/* Note that the parameters of scope = spfile must be restarted to take effect */

Parameter Name and recommended value: COMPATIBLE> = 10.2.0.0
Description: database version compatibility parameter. It was previously introduced and will not be expanded.
For what reason: some new Streams features of 10g release2 require that this parameter be at least 10.2.0.0 or higher.
How to Set: this parameter must be set only for databases that have been upgraded from earlier versions to 10g r2,
Alter system set compatible = "10.2.0.0" scope = spfile;

Parameter Name and recommended value: GLOBAL_NAMES = true
Description: determines whether the database link name must be consistent with the global name of the database. The default value is FALSE, that is, the database link name does not need to be consistent.
Considerations: helps us accurately identify the relationship between database link and database to avoid misoperation
How to set: alter system set global_names = true scope = spfile;

Parameter Name and recommended value: job_queue_processes> 4
Description: Number of job Queue processes in the instance (for example, J000... J999 ).
For what reason: this parameter controls the maximum number of jobs that can be run in parallel in the instance, and should be set to a value greater than the configured propagations
Number of database jobs.
How to set: alter system set job_queue_processes = 15;

Parameter Name and recommended value: PARALLEL_MAX_SERVERS
Description: Maximum number of parallel processes in the instance.
Considerations:
In the Streams environment, both the capture process and the apply process use multiple parallel processes.
Set this initialization parameter to an appropriate value (10 * CPU #) to ensure that there are always sufficient concurrent processes available;
For every one more capture or apply process, it is necessary to add the join capture or apply process parallelism parameter for this parameter + 2.
How to Set:
Alter system set PARALLEL_MAX_SERVERS = 40;

Parameter Name and recommended value: REMOTE_ARCHIVE_ENABLE
Description: whether to send archived logs to a remote destination.
For what reason: Only downstream capture is used and not expanded.

Parameter Name and recommended value: SGA_MAX_SIZE
Description: set a reasonable maximum SGA memory.
For what reason: common parameters are not expanded

Parameter Name and recommended value: SGA_TARGET = 0
Description: disable Automatic Shared Memory Management in 10 Gb.
For specific considerations: We recommend that you manually specify the size of streams_pool and shared_pool in the stream environment without using the memory automatic management feature of 10 Gb.
How to Set:
Alter system set sga_target = 0;

Parameter Name and recommended value: optimize STREAMS_POOL_SIZE
Description:
Specify the stream pool size. The stream pool includes cached queue messages. In addition, the stream pool is also used for internal communication between parallel capture and apply.
We recommend that you determine the optimal size based on the V $ STREAMS_POOL_ADVICE view to avoid spill overflow.
Considerations:
This parameter can be dynamically modified. If this parameter is set to zero, streams-related processes and jobs in the instance cannot run. The size of the stream pool is affected by the following factors:
1. The concurrency of the capture process. It is necessary for each added capture process to increase the size of the stream pool by 10 MB;
In addition, when the capture parameter PARALLELISM is greater than 1, it is necessary to increase the size of the stream pool by 10 Mb * parallelism;
For example, if parallelism of a capture process is set to 3, the Streams pool needs to be increased by 30 Mb.
2. The degree of parallelism of the apply process. It is necessary to add 1 MB for each added apply process to the streams pool;
In addition, when the degree of parallelism of the apply process is greater than 1, the size of parallelism is increased by 1 Mb to the streams pool;
For example, if parallelism of an apply process is set to 5, you need to increase the streams pool by 5 Mb.
3. Logical Change Records (LCRS) is stored in the buffered queues cache queue;
Increase the size of the Streams pool to adapt to the data volume replicated in the source and target databases;
We recommend that you set the minimum Streams pool to 256 Mb for low-load databases and 500 Mb for active OLTP environments;
We recommend that you further adjust the size of the Streams Pool through the recommendations provided in the V $ STREAMS_POOL_ADVISE view.
To a reasonable value to avoid excessive cache queue overflow to the disk.