The main concepts for streams configuration:1) Create queues and db links:
1) Create queue at origin
2) Create db link from origin to target
3) Create queue at target
2) Configure Capture, Propagation and Apply:
1) Configure propagation at origin
2) Configure capture at origin
3) Get SCN from origin and set it on target
4) Configure apply at target
5) Start apply at target
6) Start capture at origin
Details of the streams configuration:First of all you have to set the parameter global_names to true at each database that is participating in your Streams environment.
ALTER system SET global_names=TRUE scope=BOTH;
I divided the setup in 2 scripts. str1 is the origin database and str2 the target. The table to be replicated is hr.jobs .
To just add a table to the replication click here.
Here are the steps to follow in more detail.
/************************* BEGINNING OF FIRST SCRIPT ******************************
Step 1 Show Output and Spool Results
Run SET ECHO ON and specify the spool file for the script.
Check the spool file for errors after you run this script.
*/
SET ECHO ON
SPOOL streams_setup_simple.OUT
/*
Step 2 Set Up Users at str1.net
Connect to str1.net as SYSTEM user.
*/
CONNECT system/manager@str1.net
/*
Create the Streams administrator named strmadmin and grant this user the necessary privileges.
These privileges enable the user to manage queues, execute subprograms in packages related to Streams,
create rule sets, create rules, and monitor the Streams environment by querying data dictionary views
and queue tables. You can choose a different name for this user.
Note:
* The ACCEPT command must appear on a single line in the script.
*/
GRANT DBA TO strmadmin IDENTIFIED BY strmadminpw;
ACCEPT streams_tbs PROMPT 'Enter Streams administrator tablespace on str1.net: '
ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs
QUOTA UNLIMITED ON &streams_tbs;
/*
Step 3 Create the ANYDATA Queue at str1.net
Connect as the Streams administrator at the database where you want to capture changes.
In this example, that database is str1.net.
*/
CONNECT strmadmin/strmadminpw@str1.net
/*
Run the SET_UP_QUEUE procedure to create a queue named streams_queue at str1.net.
This queue will function as the ANYDATA queue by holding the captured changes that
will be propagated to other databases.
Running the SET_UP_QUEUE procedure performs the following actions:
* Creates a queue table named streams_queue_table. This queue table is owned by
the Streams administrator (strmadmin) and uses the default storage of this user.
* Creates a queue named streams_queue owned by the Streams administrator (strmadmin).
* Starts the queue.
*/
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(
/*
Step 4 Create the Database Link at str1.net
Create the database link from the database where changes
are captured to the database where changes are propagated.
In this example, the database where changes are captured is str1.net,
and these changes are propagated to str2.net.
*/
CREATE DATABASE LINK str2.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw
USING 'str2.net';
/*
Step 5 Set Up Users at str2.net
Connect to str2.net as SYSTEM user.
*/
CONNECT SYSTEM/MANAGER@str2.net
/*
Create the Streams administrator named strmadmin and grant this user
the necessary privileges. These privileges enable the user to manage queues,
execute subprograms in packages related to Streams, create rule sets,
create rules, and monitor the Streams environment by querying data dictionary
views and queue tables. In this example, the Streams administrator will be
the apply user for the apply process and must be able to apply changes
to the hr.jobs table at str2.net. Therefore, the Streams administrator
is granted ALL privileges on this table. You can choose a different name for the
Streams administrator.
Note:
* The ACCEPT command must appear on a single line in the script.
*/
GRANT DBA TO strmadmin IDENTIFIED BY strmadminpw;
ACCEPT streams_tbs PROMPT 'Enter Streams administrator tablespace on str2.net: '
ALTER USER strmadmin DEFAULT TABLESPACE &streams_tbs
QUOTA UNLIMITED ON &streams_tbs;
GRANT ALL ON hr.jobs TO strmadmin;
/*
Step 6 Set Up the ANYDATA Queue at str2.net
Connect as the Streams administrator at str2.net.
*/
CONNECT strmadmin/strmadminpw@str2.net
/*
Run the SET_UP_QUEUE procedure to create a queue named streams_queue at str2.net.
This queue will function as the ANYDATA queue by holding the changes that will be applied
at this database.
Running the SET_UP_QUEUE procedure performs the following actions:
* Creates a queue table named streams_queue_table. This queue table is owned by the
Streams administrator (strmadmin) and uses the default storage of this user.
* Creates a queue named streams_queue owned by the Streams administrator (strmadmin).
* Starts the queue.
*/
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE(
/*
Step 7 Check the Spool Results
Check the streams_setup_simple.out spool file to ensure that all actions finished successfully
after this script is completed.
*/
SET ECHO OFF
SPOOL OFF
/*************************** END OF FIRST SCRIPT ****************************/
/************************* BEGINNING OF SECOND SCRIPT ******************************
Step 1 Show Output and Spool Results
Run SET ECHO ON and specify the spool file for the script.
Check the spool file for errors after you run this script.
*/
SET ECHO ON
SPOOL streams_share_jobs.OUT
/*
Step 2 Configure Propagation at str1.net
Connect to str1.net as the strmadmin user.
*/
CONNECT strmadmin/strmadminpw@str1.net
/*
Configure and schedule propagation of DML and DDL changes to the hr.jobs table from
the queue at str1.net to the queue at str2.net.
*/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_
table_name => 'hr.jobs',
streams_name => 'str1_to_str2',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@str2'
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'str1',
inclusion_rule => TRUE,
queue_to_queue => TRUE);
END;
/
/*
Step 3 Configure the Capture Process at str1.net
Configure the capture process to capture changes to the hr.jobs table at str1.net.
This step specifies that changes to this table are captured by the capture process
and enqueued into the specified queue.
This step also prepares the hr.jobs table for instantiation and enables supplemental
logging for any primary key, unique key, bitmap index, and foreign key columns in
this table. Supplemental logging places additional information in the redo log for changes
made to tables. The apply process needs this extra information to perform certain
operations, such as unique row identification and conflict resolution. Because str1.net is
the only database where changes are captured in this environment, it is the only
database where supplemental logging must be enabled for the hr.jobs table.
*/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_
table_name => 'hr.jobs',
streams_type => 'capture',
streams_name => 'capture_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => TRUE,
include_ddl => TRUE,
inclusion_rule => TRUE);
END;
/
/*
Step 4 Set the Instantiation SCN for the hr.jobs Table at str2.net
This example assumes that the hr.jobs table exists at both the str1.net
database and the str2.net database, and that this table is synchronized at
these databases. Because the hr.jobs table already exists at str2.net,
this example uses the GET_SYSTEM_CHANGE_NUMBER function in the
DBMS_FLASHBACK package at str1.net to obtain the current SCN for the source database.
This SCN is used at str2.net to run the SET_TABLE_INSTANTIATION_SCN procedure
in the DBMS_APPLY_ADM package. Running this procedure sets the instantiation SCN for
the hr.jobs table at str2.net.
The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table
are ignored by an apply process and which LCRs for a table are applied by an apply process.
If the commit SCN of an LCR for a table from a source database is less than or equal to
the instantiation SCN for that table at a destination database, then the apply process at
the destination database discards the LCR. Otherwise, the apply process applies the LCR.
In this example, both of the apply process at str2.net will apply transactions to the hr.jobs
table with SCNs that were committed after SCN obtained in this step.
Note:
This example assumes that the contents of the hr.jobs table at str1.net and str2.net
are consistent when you complete this step. Make sure there is no activity on this table
while the instantiation SCN is being set. You might want to lock the table at each database
while you complete this step to ensure consistency. If the table does not exist at the
destination database, then you can use export/import for instantiation.
*/
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_
DBMS_APPLY_ADM.SET_TABLE_
source_object_name => 'hr.jobs',
source_database_name => 'str1',
instantiation_scn => iscn);
END;
/
/*
Step 5 Configure the Apply Process at str2.net
Connect to str2.net as the strmadmin user.
*/
CONNECT strmadmin/strmadminpw@str2.net
/*
Configure str2.net to apply changes to the hr.jobs table.
*/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_
table_name => 'hr.jobs',
streams_type => 'apply',
streams_name => 'apply_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'str1',
inclusion_rule => TRUE);
END;
/
/*
Step 6 Start the Apply Process at str2.net
Set the disable_on_error parameter to n so that the apply process will not be
disabled if it encounters an error, and start the apply process at str2.net.
*/
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_simp',
parameter => 'disable_on_error',
VALUE => 'n');
END;
/
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_simp');
END;
/
/*
Step 7 Start the Capture Process at str1.net
Connect to str1.net as the strmadmin user.
*/
CONNECT strmadmin/strmadminpw@str1.net
/*
Start the capture process at str1.net.
*/
BEGIN
DBMS_CAPTURE_ADM.START_
capture_name => 'capture_simp');
END;
/
/*
Step 8 Check the Spool Results
Check the streams_share_jobs.out spool file to ensure that all actions
finished successfully after this script is completed.
*/
SET ECHO OFF
SPOOL OFF
/*************************** END OF SECOND SCRIPT ******************************
-- at str1 (origin)
CONNECT strmadmin/strmadminpw@str1
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_
table_name => 'hr.regions',
streams_name => 'str1_to_str2',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@str2'
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'str1',
inclusion_rule => TRUE,
queue_to_queue => TRUE);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_
table_name => 'hr.regions',
streams_type => 'capture',
streams_name => 'capture_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => TRUE,
include_ddl => TRUE,
inclusion_rule => TRUE);
END;
/
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_
DBMS_APPLY_ADM.SET_TABLE_
source_object_name => 'hr.regions',
source_database_name => 'str1',
instantiation_scn => iscn);
END;
/
-- at str2 (target)
CONNECT SYSTEM/MANAGER@str2
GRANT ALL ON hr.regions TO strmadmin;
CONNECT strmadmin/strmadminpw@str2
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_
table_name => 'hr.regions',
streams_type => 'apply',
streams_name => 'apply_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'str1',
inclusion_rule => TRUE);
END;
Propagation
select * from dba_propagation where propagation_name =
'PROPAGATION_TEST'
Propagation rules
select DBA_RULES.* from dba_propagation, DBA_RULE_SET_RULES,
DBA_RULES
where DBA_RULE_SET_RULES.rule_set_
dba_propagation.rule_set_name
and DBA_RULES.rule_name (+)= DBA_RULE_SET_RULES.rule_name
Capture troubleshooting
--View capture status
SELECT c.capture_name, SUBSTR (s.program, INSTR (s.program, '(') +
1, 4) process_name, c.SID,
c.serial#, c.state, c.total_messages_captured,
c.total_messages_enqueued, c.enqueue_time last_enqueue,
sysdate
FROM v$streams_capture c, v$session s
WHERE c.SID = s.SID AND c.serial# = s.serial#;
Queue troubleshooting
SELECT * FROM DBA_QUEUES where owner = 'STRMADMIN'
Capture troubleshooting
--View capture status
SELECT c.capture_name, SUBSTR (s.program, INSTR (s.program, '(') +
1, 4) process_name, c.SID,
c.serial#, c.state, c.total_messages_captured,
c.total_messages_enqueued, c.enqueue_time last_enqueue,
sysdate
FROM v$streams_capture c, v$session s
WHERE c.SID = s.SID AND c.serial# = s.serial#;
Apply troubleshooting
Apply rules
select DBA_RULES.* from dba_apply, DBA_RULE_SET_RULES, DBA_RULES
where DBA_RULE_SET_RULES.rule_set_
dba_apply.rule_set_name
and DBA_RULES.rule_name (+)= DBA_RULE_SET_RULES.rule_name
SELECT dba_apply.*, sysdate FROM DBA_apply where apply_name =
'APPLY_TEST'
select * from dba_rules where upper(rule_condition) like
upper('%TESTONA%')
#The apply process stopped for a full tbs, now the tbs is ok how
many messages are now to be dequeued?
SELECT s.SUBSCRIBER_NAME, q.QUEUE_SCHEMA, q.QUEUE_NAME,
s.LAST_DEQUEUED_SEQ,
s.NUM_MSGS, s.TOTAL_SPILLED_MSG
FROM V$BUFFERED_QUEUES q, V$BUFFERED_SUBSCRIBERS s, DBA_APPLY a
WHERE q.QUEUE_ID = s.QUEUE_ID AND s.SUBSCRIBER_ADDRESS IS NULL AND
s.SUBSCRIBER_NAME = a.APPLY_NAME;
#Is there an apply history(10g)?
select * from DBA_HIST_STREAMS_APPLY_SUM
#What does this do?
SELECT * FROM DBA_APPLY_DML_HANDLERS
Views, monitoring and troubleshooting
select capture_name, queue_name, ERROR_NUMBER, ERROR_MESSAGE
from dba_capture where status != 'ENABLED'
select * FROM DBA_CAPTURE;
select * from dba_propagation;
SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME,
r.DICTIONARY_BEGIN,
r.DICTIONARY_END
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
No comments:
Post a Comment