Complete Oracle stream replication Configuration

 
Basic Environment:

Sqlplus '/As sysdba'

Alter system set aq_tm_processes = 2 Scope = both; // do not set it to 0 or 10. This may disable queue monitoring and affect the memory usage of the stream pool.

Alter system set global_names = true scope = both;

Alter system set job_queue_processes = 10 Scope = both; // The recommended minimum value is 4, which defines the number of propagation processes.

Alter system set parallel_max_servers = 20 scope = both; // maximum number of processes available for Parallel Execution

Alter system set undo_retention = 3600 scope = both; // the minimum number of seconds after data is submitted.
Alter system set nls_date_format = 'yyyy-MM-DD hh24: MI: ss' scope = spfile; // time format
Alter system set streams_pool_size = 256 m scope = spfile; // No need to configure the capture process
Alter system set utl_file_dir = '*' scope = spfile; // * indicates that any directory can be operated.

Alter system set open_links = 4 Scope = spfile; // number of database connections. db_link

Restart Database

Shutdown;

Startup;

* Set the database to archive:

Sqlplus '/As sysdba'

Alter system set log_archive_dest_1 = 'location = E: \ oracle \ product \ 10.2.0 \ archive \ arch 'scope = spfile; // archive Log Path

Alter system set log_archive_start = true scope = spfile;

Alter system set log_archive_format = 'arch % T _ % S _ % R. arc' scope = spfile; // you can specify the archive mode parameter.
Shutdown immediate;

Startup Mount;

Alter database archivelog;

Alter database open;

 

// Alter system set log_archive_dest_state_2 = 'enable' scope = both;

SQL> archive log list

 

Create a master-slave stream Management User:

Create a dedicated stream tablespace for the primary environment

Connect/As sysdba

Create tablespace tbs_stream datafile 'e: \ oracle \ product \ 10.2.0 \ oradata \ Michael \ tbs_stream01.dbf' size 100 m autoextend on maxsize unlimited segment space management auto; // The tablespace size depends on the situation

Execute dbms_logmnr_d.set_tablespace ('tbs _ stream ');

 

Create Stream Management User and authorize

Create user strmadmin identified by strmadmin default tablespace tbs_stream temporary tablespace temp;

Grant connect, resource, DBA, aq_administrator_role to strmadmin;

Begin

Dbms_streams_auth.grant_admin_privilege (

Grantee => 'strmadmin ',

Grant_privileges => true );

End;

Create a primary database chain:

Connect strmadmin/strmadmin

Create public database link Peter. landun connect to strmadmin identified by strmadmin using 'Peter ';

Create a slave database chain
Connect strmadmin/strmadmin

Create public database link Michael connect to strmadmin identified by strmadmin using 'Michael. landun '; // create a public db_link named "Michael. landun ".

 

 

Downstream_capture configuration 2008-11-15: 18 initialisation parameters -- Minimum
Alter system set aq_tm_processes = 3 Scope = both;
Alter system set global_names = true scope = both;
Alter system set job_queue_processes = 10 Scope = both;
Alter system set undo_retention = 3600 scope = both;
Alter system set streams_pool_size = 180 m scope = both;

Shutdown immediate;
Startup Mount;
Alter database archivelog;
Alter database open;

At source database
Alter system set log_archive_dest_2 = 'service = desttry lgwr async optional
Noregister valid_for = (online_logfile, primary_role )';
Alter system set log_archive_dest_state_2 = 'enable ';
Alter system set log_archive_config = 'send' scope = both;

At the downstream database:

Alter system set log_archive_dest_2 = 'location = E: \ oracle \ product \ 10.2.0 \ db_1 \ admin \ sample \ arch \ archive2 mandatory valid_for = (standby_logfile, primary_role )'
Alter system set log_archive_dest_state_2 = 'enable ';
Alter system set log_archive_config = 'receive 'scope = both;

Alter database add standby logfile Group 4 ('e: \ oracle \ product \ 10.2.0 \ redodate \ redo04a. log') Size 10 m;
Alter database add standby logfile group 5 ('e: \ oracle \ product \ 10.2.0 \ redodate \ redo05a. log') Size 10 m;
Alter database add standby logfile group 6 ('e: \ oracle \ product \ 10.2.0 \ redodate \ redo06a. log') Size 10 m;
Alter database add standby logfile group 7 ('e: \ oracle \ product \ 10.2.0 \ redodate \ redo07a. log') Size 10 m;

Set verify off
Set echo off
Spool streams_setup.log

Conn sys/sys @ desttry as sysdba
Create tablespace downtargetstream6 datafile 'd: \ downtargetstream6.dbf 'size 25 m reuse autoextend on maxsize unlimited;
Create user streamadmin identified by streamadmin default tablespace downtargetstream6 quota unlimited on downtargetstream6;
Grant DBA to streamadmin;
Grant execute on dbms_lock to streamadmin;
Grant execute on dbms_pipe to streamadmin;
Create user streamuser identified by streamuser;
Grant connect, resource, DBA to streamuser identified by streamuser;
Exec dbms_streams_auth.grant_remote_admin_access ('streamadmin ');