Oracle single table stream replication stream

The Oracle single-Table stream replication solution fails because of the many procedures of oraclestream and many problems encountered previously.

The Oracle single-Table stream replication solution fails because of the many procedures of oracle stream and many problems encountered previously.

Oracle single table stream replication is completed. Due to the many steps of oracle stream operations, many problems have been encountered before,

Failed because I did not clarify my thinking or did not understand this. For important tables, stream replication is similar to the logging uard Application Log, master server

Capture, then pass, and accept from the server. Briefly record the main steps.

1 main Server OS: windows sid: rman database version 10.2.0.1

Slave Server OS: windows sid format database version 10.2.0.1

2. Run the following statements in the master database and slave database respectively:
Sqlplus '/as sysdba'
Alter system set aq_tm_processes = 2 scope = both;
Alter system set global_names = true scope = both;
Alter system set job_queue_processes = 10 scope = both;
Alter system set parallel_max_servers = 20 scope = both;
Alter system set undo_retention = 3600 scope = both;
Alter system set nls_date_format = 'yyyy-MM-DD HH24: MI: ss' scope = spfile;
Alter system set streams_pool_size = 25 M scope = spfile; -- I didn't execute this because oracle automatically assigned
Alter system set utl_file_dir = '*' scope = spfile;
Alter system set open_links = 4 scope = spfile;

3. set alter database archivelog on the master/slave machine to enable alter system set log_archive_start = true in the archived mout state;

Check whether the archive is successful select recid, name, first_time from v $ archived_log;

4 Master/Slave table space and user

Create tablespace stream_tbs datafile 'G: \ oracle \ oradata \ rman \ stream01.dbf' size 200 m

Autoextend on extent management local uniform size 1 m segment space management auto;

The tablespace has been created.

-- Create a streams management user and grant the dba permission

JSSWEB> create user test identified by test default tablespace stream_tbs;

The user has been created.

-- Transfers the data dictionary of logminer from the system tablespace to the new tablespace to prevent the system tablespace from being fully occupied.
Execute dbms_logmnr_d.set_tablespace ('tbs _ stream ');

-- Because the streams user operation requires more permissions, it is only used for demonstration here. dba permissions are directly granted during the simple process.

Authorize test to Manage Users

JSSWEB> grant dba to test;

Begin
Dbms_streams_auth.grant_admin_privilege (
Grantee => 'test ',
Grant_privileges => true );
End;

5 Master/Slave table space and user

Create tablespace stream_tbs datafile 'd: \ oracle \ oradata \ rman \ stream01.dbf' size 200 m

Autoextend on extent management local uniform size 1 m segment space management auto;

The tablespace has been created.

-- Create a streams management user and grant the dba permission

JSSWEB> create user strmadmin identified by strmadmin default tablespace stream_tbs;

The user has been created.

-- Transfers the data dictionary of logminer from the system tablespace to the new tablespace to prevent the system tablespace from being fully occupied.
Execute dbms_logmnr_d.set_tablespace ('tbs _ stream ');

-- Because the streams user operation requires more permissions, it is only used for demonstration here. dba permissions are directly granted during the simple process.

Authorize test to Manage Users

JSSWEB> grant dba to strmadmin;

Begin
Dbms_streams_auth.grant_admin_privilege (
Grantee => 'strmadmin ',
Grant_privileges => true );
End;

6. Configure the network connection

Add the slave database configuration in the master database (tnsnames. ora.
RMAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.103) (PORT = 1521 ))
)
(CONNECT_DATA =
(SID = rman)
(SERVER = DEDICATED)
)
)
Configure the slave environment tnsnames. ora
Add the configuration of the primary database from the database (tnsnames. ora.
FORMAT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.104) (PORT = 1521 ))
)
(CONNECT_DATA =
(SID = format)
(SERVER = DEDICATED)
)
)

7. Create a database link

MASTER: create database link format connect to strmadmin identified by strmadmin using 'format ';

From: create database link rman connect to strmadmin identified by strmadmin using 'rman ';

Then tnsping or select sysdate from dual @ format/rman;

8 main database exec dbms_streams_adm.set_up_queue ();

Slave database exec dbms_streams_adm.set_up_queue ();

9 create a capture rule

Begin

Dbms_streams_adm.add_table_rules (

Table_name => 'Scott. emp ',

Streams_type => 'capture ',

Streams_name => 'capture _ Stream ',

Queue_name => 'test. streams_queue ',

Include_dml => true,

Include_ddl => true,

Inclusion_rule => true );

End;
-- Create a propagation rule

Begin

Dbms_streams_adm.add_table_propagation_rules (

Table_name => 'Scott. emp ',

Streams_name => 'sour _ to_targ ',

Source_queue_name => 'test. streams_queue ',

Destination_queue_name => 'strmadmin. streams_queue @ format ',

Include_dml => true,

Include_ddl => true,

Source_database => 'rman ',

Required sion_rule => true,

Queue_to_queue => true );

End;

Select capture_name, status from dba_capture;

CAPTURE_NAME STATUS

--------------------------------------

CAPTURE_STREAM DISABLED

,