V$Propagation_sender Wrongly Populated After Propagation Recreation

(Doc ID 1367343.1)

Last updated on AUGUST 27, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

After recreation of propagation process (e.g. by automatic split and merge procedures or by manual drop and create) v$propagation_sender is frozen - shows same values(usually zeros).

The following steps can be used to reproduce the problem for testing purposes:

-Configure stream between two or more database
-Drop one propagation process and recreate it with the same name (alternatively perform split and merge operation)
-No updates for new propagation process in v$propagation_sender


This problem has been observed and reproduced on 11.2.0.1 databases, the following configurations has been used to reproduce :

source database: ST11G21 (single test node RAC 11.2.0.1 installation)
destination1: D2T11G21 (single test node RAC 11.2.0.1 installation)
destination2: D4T11G21 (single test node RAC 11.2.0.1 installation)

All the databases are on linux RHEL5.

The test case:

Schema level streams DDL/DML has been created on scott schema using the same steps exactly in the following article : <Note:878638.1>

See the following reproducible testcase using drop and recreate the propagation

On source :
-------------
SQL> insert into scott.emp(empno,ename,sal) values (999,'Ahmed',999);

On target :
--------------

SQL> select * from scott.emp where empno=999;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
999 Ahmed 999



On source :
--------------------

SQL> select PROPAGATION_NAME,TOTAL_MSGS, ELAPSED_PROPAGATION_TIME from v$propagation_sender;

PROPAGATION_NAME TOTAL_MSGS ELAPSED_PROPAGATION_TIME
------------------------------ ---------- ------------------------
PROPAGATION$_19 50 .0003


SQL> create table scott.emp2 as select * from scott.emp;

Table created.

SQL> commit;

Commit complete.

SQL> select PROPAGATION_NAME,TOTAL_MSGS, ELAPSED_PROPAGATION_TIME from v$propagation_sender;

PROPAGATION_NAME TOTAL_MSGS ELAPSED_PROPAGATION_TIME
------------------------------ ---------- ------------------------
PROPAGATION$_19 80 .0004


SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,QUEUE_TO_QUEUE from dba_PROPAGATION;

PROPAGATION_NAME SOURCE_QUEUE_NAME
------------------------------ ------------------------------
DESTINATION_QUEUE_NAME
------------------------------
DESTINATION_DBLINK
--------------------------------------------------------------------------------
QUEUE
-----
PROPAGATION$_19 STRM112P$CAPQ
STRM112P$APPQ
STRM112Q.US.ORACLE.COM
TRUE

SQL> exec dbms_propagation_adm.drop_propagation('PROPAGATION$_19');

PL/SQL procedure successfully completed.

SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,QUEUE_TO_QUEUE from dba_PROPAGATION;

no rows selected

SQL> BEGIN
DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
propagation_name => 2 3 'PROPAGATION$_19',
4 source_queue => 'STRMADMIN.STRM112P$CAPQ',
5 destination_queue => 'STRMADMIN.STRM112P$APPQ',
6 destination_dblink => 'STRM112Q.US.ORACLE.COM',
7 queue_to_queue => true );
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,QUEUE_TO_QUEUE from dba_PROPAGATION;

PROPAGATION_NAME SOURCE_QUEUE_NAME
------------------------------ ------------------------------
DESTINATION_QUEUE_NAME
------------------------------
DESTINATION_DBLINK
--------------------------------------------------------------------------------
QUEUE
-----
PROPAGATION$_19 STRM112P$CAPQ
STRM112P$APPQ
STRM112Q.US.ORACLE.COM
TRUE


SQL> select PROPAGATION_NAME,TOTAL_MSGS, ELAPSED_PROPAGATION_TIME from v$propagation_sender;

PROPAGATION_NAME TOTAL_MSGS ELAPSED_PROPAGATION_TIME
------------------------------ ---------- ------------------------
0 0

SQL> create table scott.dept2 as select * from scott.dept;

Table created.

SQL> select PROPAGATION_NAME,TOTAL_MSGS, ELAPSED_PROPAGATION_TIME from v$propagation_sender;

PROPAGATION_NAME TOTAL_MSGS ELAPSED_PROPAGATION_TIME
------------------------------ ---------- ------------------------
0 0
SQL> select count(*) from dba_PROPAGATION;

COUNT(*)
----------
1

SQL> select PROPAGATION_NAME, STATUS, ERROR_MESSAGE from dba_PROPAGATION;

PROPAGATION_NAME STATUS
------------------------------ --------
ERROR_MESSAGE
--------------------------------------------------------------------------------
PROPAGATION$_19 ENABLED



SQL> select PROPAGATION_NAME,TOTAL_MSGS, ELAPSED_PROPAGATION_TIME from v$propagation_sender;

PROPAGATION_NAME TOTAL_MSGS ELAPSED_PROPAGATION_TIME
------------------------------ ---------- ------------------------
0 0


On Target :
----------------

SQL> select table_name from dba_tables where owner='SCOTT' order by table_name;

TABLE_NAME
------------------------------
BONUS
DEPT
DEPT2
EMP
EMP2
EMPMOD
SALGRADE

7 rows selected.

SQL> desc scott.dept2;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL>


That mean the changes has been propagated successfully ..

SQL> select TOTAL_MSGS, STATE, PROPAGATION_NAME from V$propagation_receiver
2 ;

TOTAL_MSGS STATE
---------- -------------------------------------------
PROPAGATION_NAME
------------------------------
36 Waiting for message from propagation sender PROPAGATION$_19

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms