Oracle Streams advanced configuration

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]
This entry was posted in Oracle and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>