Table of contents
Preamble
In a previous article we have seen the basic Streams configuration. In this one I wanted to go a bit further and show the additional steps you would execute in a living production environment. Means adding and removing table from Streams replication.
Manual add of a table
The following procedure can be used:
On source database:
Add supplemental log on the table (all, primary key, unique, foreign key):
undef table_name ALTER TABLE &table_name. ADD supplemental LOG data (PRIMARY KEY) columns; |
Add object to capture process:
undef table_name EXEC DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name => '&table_name.', streams_type => 'capture', streams_name => 'capture_stream', queue_name => 'strmadmin.streams_queue', include_dml => TRUE, include_ddl => TRUE, inclusion_rule => TRUE); |
Add it to propagation process:
undef table_name EXEC DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(table_name => '&table_name.', streams_name => 'DB1_to_DB2', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@db2', include_dml => TRUE, include_ddl => TRUE, source_database => 'DB1', inclusion_rule => TRUE); |
Instantiate it:
undef table_name DECLARE source_scn NUMBER; BEGIN source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); dbms_output.put_line('Source SCN:' || source_scn); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@db2(source_object_name => '&table_name.', source_database_name => 'DB1', instantiation_scn => source_scn); END; / |
On target database:
Add it to apply process:
undef table_name EXEC DBMS_STREAMS_ADM.ADD_TABLE_RULES(table_name => '&table_name.', streams_type => 'apply', streams_name => 'apply_stream', queue_name => 'strmadmin.streams_queue', include_dml => TRUE, include_ddl => TRUE, source_database => 'DB1', inclusion_rule => TRUE); |
With the supplied Streams procedure
DECLARE tables DBMS_UTILITY.UNCL_ARRAY; BEGIN tables(1) := 'strmadmin.test1'; DBMS_STREAMS_ADM.MAINTAIN_TABLES(table_names => tables, source_directory_object => NULL, destination_directory_object => NULL, source_database => 'db1', destination_database => 'db2', perform_actions => FALSE, script_name => 'test1.sql', script_directory_object => 'TMP', capture_name => 'capture_stream', capture_queue_table => 'strmadmin.streams_queue_table', capture_queue_name => 'strmadmin.streams_queue', capture_queue_user => 'strmadmin', propagation_name => 'db1_to_db2', apply_name => 'apply_stream', apply_queue_table => 'strmadmin.streams_queue_table', apply_queue_name => 'strmadmin.streams_queue', apply_queue_user => 'strmadmin', include_ddl => TRUE, instantiation => DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK); END; / |
Remark:
Note that the parameter table_names can also be a single table name (procedure overloaded).
When a configuration procedure is run, information about its progress is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then see Oracle Streams Replication Administrator’s Guide for instructions about using the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM
If PL/SQL is failing for something like:
ERROR AT line 1: ORA-23622: Operation SYS.DBMS_STREAMS_ADM.MAINTAIN_TABLES IS IN progress. ORA-06512: AT "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: AT "SYS.DBMS_STREAMS_MT", line 2334 ORA-06512: AT "SYS.DBMS_STREAMS_MT", line 7451 ORA-06512: AT "SYS.DBMS_STREAMS_ADM", line 2476 ORA-06512: AT line 5 |
then issue:
SQL> SELECT * FROM DBA_RECOVERABLE_SCRIPT; SCRIPT_ID CREATION_ INVOKING_PACKAGE_OWNER INVOKING_PACKAGE -------------------------------- --------- ------------------------------ ------------------------------ INVOKING_PROCEDURE INVOKING_USER STATUS TOTAL_BLOCKS DONE_BLOCK_NUM ------------------------------ ------------------------------ ------------ ------------ -------------- SCRIPT_COMMENT ---------------------------------------------------------------------------------------------------------------------------------- 84A604DEF2002396E044AA748217E981 20-APR-10 SYS DBMS_STREAMS_ADM MAINTAIN_TABLES STRMADMIN ERROR 12 1 |
You could purge the whole maintenance activity with here below, but it is clearly not the objective:
SQL> EXEC DBMS_STREAMS_ADM.RECOVER_OPERATION(script_id => '84A604DEF2002396E044AA748217E981', operation_mode => 'PURGE'); PL/SQL PROCEDURE successfully completed. |
Then re-execute the procedure, it may fail again for:
ERROR AT line 1: ORA-26727: Cannot ALTER queue_to_queue property OF existing propagation. ORA-06512: AT "SYS.DBMS_STREAMS_ADM", line 836 ORA-06512: AT "SYS.DBMS_STREAMS_ADM", line 765 ORA-06512: AT line 48 |
Check the progress, and where it fails, with:
SQL> SELECT * FROM DBA_RECOVERABLE_SCRIPT; SCRIPT_ID CREATION_ INVOKING_PACKAGE_OWNER INVOKING_PACKAGE -------------------------------- --------- ------------------------------ ------------------------------ INVOKING_PROCEDURE INVOKING_USER STATUS TOTAL_BLOCKS DONE_BLOCK_NUM ------------------------------ ------------------------------ ------------ ------------ -------------- SCRIPT_COMMENT ---------------------------------------------------------------------------------------------------------------------------------- 84A604DEF2072396E044AA748217E981 23-APR-10 SYS DBMS_STREAMS_ADM MAINTAIN_TABLES STRMADMIN EXECUTING 12 2 |
SELECT block_num,forward_block,status FROM DBA_RECOVERABLE_SCRIPT_BLOCKS WHERE script_id='852574FF11803A83E044AA748217E981' ORDER BY BLOCK_NUM; BLOCK_NUM FORWARD_BLOCK STATUS ---------- -------------------------------------------------------------------------------- ------------ 1 -- EXECUTED -- Add supplemental log group for table "STRMADMIN"."TEST2" -- BEGIN EXE 2 -- EXECUTED -- Set up queue "STRMADMIN"."STREAMS_QUEUE" -- BEGIN dbms_streams_adm.se 3 -- ERROR -- PROPAGATE changes for table "STRMADMIN"."TEST2" -- DECLARE version_nu 4 -- NOT EXECUTED -- Disable propagation. Enable after destination has been setup -- DECLARE 5 -- NOT EXECUTED -- CAPTURE changes for table "STRMADMIN"."TEST2" -- DECLARE compat 6 -- NOT EXECUTED -- Start capture process CAPTURE_STREAM -- BEGIN dbms_capture_adm.start_ 7 -- NOT EXECUTED -- Datapump TABLE MODE IMPORT (NETWORK) -- DECLARE h1 NUMB 8 -- NOT EXECUTED -- Set up queue "STRMADMIN"."STREAMS_QUEUE" -- BEGIN dbms_streams_adm.se 9 -- NOT EXECUTED -- APPLY changes for table "STRMADMIN"."TEST2" -- DECLARE compat 10 -- NOT EXECUTED -- Get tag value to be used for Apply -- DECLARE found BINARY_INTEGER 11 -- NOT EXECUTED -- Start apply process APPLY_STREAM -- BEGIN dbms_apply_adm.start_apply( 12 -- NOT EXECUTED -- Enable propagation schedule for "STRMADMIN"."STREAMS_QUEUE" -- to DB2 12 ROWS selected. |
Go more in details with:
SQL> SELECT * FROM DBA_RECOVERABLE_SCRIPT_ERRORS; SCRIPT_ID BLOCK_NUM ERROR_NUMBER -------------------------------- ---------- ------------ ERROR_MESSAGE -------------------------------------------------------------------------------- ERROR_CRE --------- 852574FF11803A83E044AA748217E981 3 -26727 ORA-26727: Cannot ALTER queue_to_queue property OF existing propagation. 26-APR-10 |
To display the property of the propagation process:
SQL> SELECT propagation_name, status, queue_to_queue FROM dba_propagation; PROPAGATION_NAME STATUS QUEUE ------------------------------ -------- ----- DB1_TO_DB2 ENABLED FALSE |
This is explained in Streams recommended configuration and performance. Starting with 10gR2 you should create the propagation and if upgrading from 9iR2 or 10gR1 drop and recreate the propagation process. Oracle says “A queue-to-queue propagation always has its own exclusive propagation job to propagate messages from the source queue to the destination queue. Because each propagation job has its own propagation schedule, the propagation schedule of each queue-to-queue propagation can be managed separately”.
So let’s drop and recreate the propagation process with the queue to queue property set to true. This, maybe, also clarify how to explicitly create the propagation process without adding rules to a table:
SQL> EXEC dbms_capture_adm.stop_capture('CAPTURE_STREAM'); PL/SQL PROCEDURE successfully completed. SQL> SELECT capture_name, status FROM dba_capture; CAPTURE_NAME STATUS ------------------------------ -------- CAPTURE_STREAM DISABLED SQL> SELECT propagation_name, status FROM dba_propagation; PROPAGATION_NAME STATUS ------------------------------ -------- DB1_TO_DB2 ENABLED SQL> EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION('DB1_TO_DB2'); PL/SQL PROCEDURE successfully completed. SQL> EXEC DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(propagation_name => 'DB1_TO_DB2', source_queue => 'strmadmin.streams_queue', destination_queue => 'strmadmin.streams_queue', destination_dblink => 'db2', queue_to_queue => TRUE); PL/SQL PROCEDURE successfully completed. SQL> SELECT propagation_name, status, queue_to_queue FROM dba_propagation; PROPAGATION_NAME STATUS QUEUE ------------------------------ -------- ----- DB1_TO_DB2 ENABLED TRUE SQL> SELECT capture_name, status FROM dba_capture; CAPTURE_NAME STATUS ------------------------------ -------- CAPTURE_STREAM DISABLED SQL> EXEC dbms_capture_adm.start_capture('CAPTURE_STREAM'); PL/SQL PROCEDURE successfully completed. |
When you think you have done it is even worst due to another bug (!!):
- Changing Propagation/ queue_to_queue : false -> true does does not work; no LCRs propagated [ID 556309.1]
- Bug 6640411 – AQ propogation fails after changing queue_to_queue=>true [ID 6640411.8]
- Queue_to_Queue Propagation does not Propagate Messages but no Errors are reported [ID 402809.1]
If you want to try to drop and recreate the capture process it is even worst because dropping the full Streams configuration with the supplied package is failing for another bug:
Bug 4627457 – ORA-24042 from REMOVE_STREAMS_CONFIGURATION if queue_to_queue is true [ID 4627457.8]
Then at the end either you are able to remove manually the configuration and/or you drop the strmadmin account cascade…
Then recreate everything, on source:
SQL> EXEC DBMS_CAPTURE_ADM.CREATE_CAPTURE(queue_name=>'strmadmin.streams_queue',capture_name=>'CAPTURE_STREAM'); PL/SQL PROCEDURE successfully completed. SQL> EXEC DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(propagation_name => 'DB1_TO_DB2', source_queue => 'strmadmin.streams_queue', destination_queue => 'strmadmin.streams_queue', destination_dblink => 'db2', queue_to_queue => TRUE); PL/SQL PROCEDURE successfully completed. |
On target:
db2> EXEC DBMS_APPLY_ADM.CREATE_APPLY(queue_name=>'strmadmin.streams_queue',apply_name=>'APPLY_STREAM'); PL/SQL PROCEDURE successfully completed. |
Then re-execute the procedure DBMS_STREAMS_ADM.MAINTAIN_TABLES… Which is now failing for “ORA-22921: length of input buffer is smaller than amount requested”…
It is Bug 4644059: DBMS_STREAMS_ADM.MAINTAIN_TABLES FAILS WITH ORA-22921 happening when source and target databases do not have the same character set… Up to 29th of April 2010 there is no patch and the only workarounds are having databases with same character set or do not use DBMS_STREAMS_ADM.MAINTAIN_TABLES procedure…
How to remove one table from Streams
Apparently no automatic procedure to do it !!!
Start to remove supplemental login on this table:
SELECT * FROM dba_log_groups ORDER BY owner, table_name, log_group_type; |
ALTER TABLE &table_name. DROP supplemental LOG data (ALL, PRIMARY KEY, UNIQUE, foreign KEY) columns; |
Then remove all the rules on this table for source (capture and propagation) and target site(s) (apply):
SELECT streams_type, streams_name, streams_rule_type, object_name FROM dba_streams_rules ORDER BY streams_type, streams_name, streams_rule_type, object_name; |
SELECT rule_owner, rule_name FROM dba_rules ORDER BY rule_owner, rule_name; |
SELECT streams_name, streams_type, table_owner, table_name, rule_name FROM dba_streams_table_rules ORDER BY streams_name, streams_type, table_owner, table_name, rule_name; |
EXEC DBMS_STREAMS_ADM.REMOVE_RULE(rule_name => '&rule_name.', streams_type => '&streams_type.', streams_name => '&streams_name.'); |
Example:
BEGIN FOR rules IN (SELECT streams_name,streams_type,table_owner,table_name,rule_name FROM dba_streams_table_rules WHERE table_name LIKE 'MD_CUBE%' ORDER BY streams_name,streams_type,table_owner,table_name,rule_name) LOOP DBMS_STREAMS_ADM.REMOVE_RULE(rule_name => rules.rule_name, streams_type => rules.streams_type, streams_name => rules.streams_name); END LOOP; END; / |
If still rule remain use the following:
EXEC DBMS_RULE_ADM.DROP_RULE(rule_name=>'&rule_name.'); |
BEGIN FOR rules IN (SELECT rule_name FROM dba_rules WHERE rule_name LIKE 'MD_CUBE%') LOOP DBMS_RULE_ADM.DROP_RULE(rule_name=> rules.rule_name); END LOOP; END; / |
If you get:
ORA-24148: cannot drop rule STRMADMIN.MD_CUBE_9040 with dependents ORA-06512: at "SYS.DBMS_RULE_ADM", line 230 ORA-06512: at line 1 |
Either try to force the removal of the rule:
SQL> EXEC DBMS_RULE_ADM.DROP_RULE(rule_name=>'MD_CUBE_9040', force=>TRUE); PL/SQL PROCEDURE successfully completed. |
Or try to remove the context first:
db1> SELECT evaluation_context_owner,evaluation_context_name FROM DBA_EVALUATION_CONTEXTS; EVALUATION_CONTEXT_OWNER EVALUATION_CONTEXT_NAME ------------------------------ ------------------------------ SYS AQ$_AQ$_PROP_TABLE_1_V SYS AQ$_SYS$SERVICE_METRICS_TAB_V SYS AQ$_ALERT_QT_V SYS AQ$_AQ$_MEM_MC_V SYS EVAL_CTX$_9 SYS EVAL_CTX$_5 SYS EVAL_CTX$_1 SYS STREAMS$_EVALUATION_CONTEXT SYS AQ$_KUPC$DATAPUMP_QUETAB_V SYS AQ$_SCHEDULER$_EVENT_QTAB_V SYS AQ$_SCHEDULER$_JOBQTAB_V WMSYS AQ$_WM$EVENT_QUEUE_TABLE_V STRMADMIN AQ$_STREAMS_QUEUE_TABLE_V SYS STREAMS$_EVALUATION_CONTEXT |
SQL> EXEC DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT(evaluation_context_name=>'sys.STREAMS$_EVALUATION_CONTEXT'); BEGIN DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT(evaluation_context_name=>'sys.STREAMS$_EVALUATION_CONTEXT'); END; * ERROR AT line 1: ORA-24152: cannot DROP evaluation CONTEXT SYS.STREAMS$_EVALUATION_CONTEXT WITH dependents ORA-06512: AT "SYS.DBMS_RULE_ADM", line 119 ORA-06512: AT line 1 SQL> EXEC DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT(evaluation_context_name=>'sys.STREAMS$_EVALUATION_CONTEXT',force=>TRUE); PL/SQL PROCEDURE successfully completed. |
Then control if table is out of the prepared table (source and target site(s)):
SELECT * FROM dba_capture_prepared_tables ORDER BY table_owner, table_name; |
If still wrong entries in this table:
EXEC DBMS_CAPTURE_ADM.ABORT_TABLE_INSTANTIATION(table_name => '&table_name.'); |
SELECT * FROM dba_apply_instantiated_objects ORDER BY source_database, source_object_owner, source_object_name; |
If still wrong entries in this table:
EXEC DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name=> '&table_name.', source_database_name=>'DB1.WORLD', instantiation_SCN=>NULL); |
Remark:
dba_rule_sets needed ?
References
- Streams Performance Recommendations [ID 335516.1]
- 10gR2 Streams Recommended Configuration [ID 418755.1]
- How to alter propagation from queue_to_queue to queue_to_dblink [ID 827473.1]
