Practice ORACLE STREAMS (3)

Jan 17 posted in Oracle (RSS), 112 views.

The fifth step, to enable an additional log:
Can be based on Database level or Table level, to enable additional logging (Supplemental Log). According to Schema particle size in the establishment of Oracle Stream replication environment, if confirmed, all Table have reasonable Schema primary key (Primary Key), you will no longer need to enable additional logging.

# Enable additional logging Database
alter database add supplemental log data;

# Enable additional logging Table
alter table add supplement log group log_group_name (table_column_name) always;

Step six: Create DBlink:
According to Oracle 10gR2 Stream official documents, the main library for the establishment of the name of the database chain must be the same as from the database global_name. If you need to modify the global_name, implementation of the "alter database rename global_name to xxx".

Creating the main library database links
# To strmadmin status, log on the master database.
connect strmadmin / strmadmin

# Create the database links
create database link h10g connect to strmadmin identified by strmadmin using 'h10g';

Preparation of libraries to create a database Chain
# To strmadmin status, log on from the database.
connect strmadmin / strmadmin

# Create the database links
create database link prod connect to strmadmin identified by strmadmin using 'prod';

Seventh step, to create a stream queue:
Create Master Flow Queuing
# To strmadmin status, log on the main library.
connect strmadmin / strmadmin

begin
dbms_streams_adm.set_up_queue (
queue_table => 'prod_queue_table',
queue_name => 'prod_queue');
end;
/

Create Backup Stream Queue
# To strmadmin status, log on prepared base.
connect strmadmin / strmadmin

begin
dbms_streams_adm.set_up_queue (
queue_table => 'h10g_queue_table',
queue_name => 'h10g_queue');
end;
/

Eighth step, create a capture process:
# To strmadmin status, log on the main library. A reminder of this document to scott user to do an example.
connect strmadmin / strmadmin

begin
dbms_streams_adm.add_schema_rules (
schema_name => 'scott',
streams_type => 'capture',
streams_name => 'capture_prod',
queue_name => 'strmadmin.prod_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/

Ninth step, an instance of copying the database:
In the main database environment, the implementation of the following Shell statement. From the library scott user does not exist, create a blank user scott.
Note that if the new user, must remember to give permission to connect, etc. Otherwise, the process may be Baocuo imp.
exp userid = scott / scott @ prod file = '/ arch / scott.dmp' object_consistent = y rows = y

imp userid = system / manager @ h10g file = '/ arch / scott.dmp' ignore = y commit = y log = '/ arch / scott.log' streams_instantiation = y fromuser = scott touser = scott

10th step, create a communication process:
# To strmadmin status, log on the master database.
connect strmadmin / strmadmin

begin
dbms_streams_adm.add_schema_propagation_rules (
schema_name => 'scott',
streams_name => 'prod_to_h10g',
source_queue_name => 'strmadmin.prod_queue',
destination_queue_name => 'strmadmin.h10g_queue @ h10g',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'prod',
inclusion_rule => true);
end;
/

# Modify the propagation sleep time 0, indicating real-time dissemination of LCR.
begin
dbms_aqadm.alter_propagation_schedule (
queue_name => 'prod_queue',
destination => 'h10g',
latency => 0);
end;
/

11th step, create an application process:
# To strmadmin status, log on prepared base.
connect strmadmin / strmadmin

begin
dbms_streams_adm.add_schema_rules (
schema_name => 'scott',
streams_type => 'apply',
streams_name => 'apply_h10g',
queue_name => 'strmadmin.h10g_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'prod',
inclusion_rule => true);
end;
/

12th step, start STREAM
# To strmadmin status, log on prepared base.
connect strmadmin / strmadmin

# Start the Apply Process
begin
dbms_apply_adm.start_apply (
apply_name => 'apply_h10g');
end;
/

# To strmadmin status, log on the main library.
connect strmadmin / strmadmin

# Start the Capture Process
begin
dbms_capture_adm.start_capture (
capture_name => 'capture_prod');
end;
/

NOTE: Stop stream please refer to the following command:
# To strmadmin status, log on the main library.
connect strmadmin / strmadmin

# Stop Capture Process
begin
dbms_capture_adm.stop_capture (
capture_name => 'capture_prod');
end;
/

# To strmadmin status, log on prepared base.
connect strmadmin / strmadmin

# Apply the process to stop
begin
dbms_apply_adm.stop_apply (
apply_name => 'apply_h10g');
end;
/

Remove all configuration information to make it clear Stream configuration information, you must first stop Stream process.

# To strmadmin status, log on the main library.
connect strmadmin / strmadmin
exec DBMS_STREAMS_ADM.remove_streams_configuration ();

# To strmadmin status, log on prepared base.
connect strmadmin / strmadmin
exec DBMS_STREAMS_ADM.remove_streams_configuration ();

The above steps done, stream configuration to this end, the following test stream began to achieve success.