Oracle 10g Stream table-level replication Configuration

1. experiment environment Configuration
This experiment is carried out in the vmware9.0 environment, the installation of the operating system CentOS-6.4-x86_64, database version is Oracle10g10.2.0.1, the source database and the target database are installed with the database, the source database ORACLE_SID = myorcl, the target database ORACLE_SID = orcl

Introduction and Setup of Oracle Streams

Oracle Stream construction from a single instance to a single instance

Oracle 10gR2 Streams Delete All configurations

Oracle single table Stream replication Stream

Oracle simple Stream one-way replication configuration for users

2. Set initialization parameters for the source and target Databases
The operations in the source database are as follows:
 
SQL> select instance_name, status from v $ instance;

INSTANCE_NAME STATUS
----------------------------
Myorcl OPEN

SQL> alter system set aq_tm_processes = 1 scope = spfile; -- queue wait time

SQL> alter system set job_queue_processes = 2 scope = spfile; -- at least 2

SQL> alter system set global_names = true scope = spfile; -- used to control database link with the same name

SQL> alter database rename global_name to myorcl.net;

SQL> alter system set streams_pool_size = 50 m scope = spfile;
Restart the database and view the configured global_name as follows:

SQL> shutdown immediate
SQL> startup
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
MYORCL. NET
Perform the following operations on the target database:
 
SQL> select instance_name, status from v $ instance;

INSTANCE_NAME STATUS
----------------------------
Orcl OPEN

SQL> alter system set aq_tm_processes = 1 scope = spfile;

SQL> alter system set job_queue_processes = 2 scope = spfile;

SQL> alter system set global_names = true scope = spfile;

SQL> alter database rename global_name to orcl.net;

SQL> alter system set streams_pool_size = 50 m scope = spfile;
SQL> shutdown immediate
Restart the database and view the configured global_name as follows:

SQL> shutdown immediate
SQL> startup

 

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL. NET

3. Configure tnsnames. ora in the source and target databases as follows:

Primary =
 
(DESCRIPTION =
 
(ADDRESS_LIST =
 
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.41.6.118) (PORT = 1521 ))
 
)
 
(CONNECT_DATA =
 
(SERVICE_NAME = myorcl)
 
)
 
)
 
Standby =
 
(DESCRIPTION =
 
(ADDRESS_LIST =
 
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.41.6.119) (PORT = 1521 ))
 
)
 
(CONNECT_DATA =
 
(SERVICE_NAME = orcl)
 
)
 
)
 
The connectivity test is as follows:
First, disable the firewall and stop the service iptables.
Test in the source database as follows:

[Oracle @ dayong ~] $ Tnsping standby

TNS Ping Utility for Linux: Version 10.2.0.1.0-Production on 03-APR-2014 11:47:41
 
Copyright (c) 1997,200 5, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.41.6.119) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = orcl )))
OK (10 msec)

Test in the target database as follows:
[Oracle @ xiaoru ~] $ Tnsping primary

TNS Ping Utility for Linux: Version 10.2.0.1.0-Production on 03-APR-2014 11:53:24
 
Copyright (c) 1997,200 5, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.41.6.118) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = myorcl )))
OK (0 msec)

4. Create a copy administrator for the source and target Databases
You cannot use sys and system as the flow administrator, and the flow administrator cannot use system tablespace as the default tablespace;
The source database operations are as follows:
SQL> create tablespace streamtbs datafile '/u01/app/oradata/myorcl/streamtbs. dbf' size 100 m;
 
SQL> create user streamadmin identified by oracle default tablespace streamtbs quota unlimited on streamtbs;
 
SQL> grant connect, resource, dba to streamadmin;

The operations on the target database are as follows:
SQL> create tablespace streamtbs datafile '/u01/app/oradata/orcl/streamtbs. dbf' size 100 m;
 
SQL> create user streamadmin identified by oracle default tablespace streamtbs quota unlimited on streamtbs;
 
SQL> grant connect, resource, dba to streamadmin;

 

For more details, please continue to read the highlights on the next page:

1 2 Next Page