ORA-604, ORA-6550, PLS-201 When Pushing the Deferred Transactions in Multimaster Environment (Doc ID 468120.1)

Last updated on JUNE 21, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

After successful upgrade of one multimaster replication site from 8i or 9i to 10g, the next errors may be occur when trying to push the deferred transactions:

Errors :
------
The next example demonstrates Multimaster replication environment includes 2 sites: DB1 and DB2, we have upgraded DB2 from 8i to 10g, while DB1 is still not upgraded:

SQL>declare rc binary_integer; begin rc := sys.dbms_defer_sys.push(destination=>'DB2.COM', delay_seconds=>0, parallelism=>1); end;
2 /
declare rc binary_integer; begin rc := sys.dbms_defer_sys.push(destination=>'DB2.COM', delay_seconds=>0, parallelism=>1); end;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_DEFER@DB2.COM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_DEFER_SYS", line 1520
ORA-06512: at "SYS.DBMS_DEFER_SYS", line 1583
ORA-06512: at line 1

When tracing the problem, find the next in the trace file :

SQL>alter session set max_dump_file_size = unlimited ;

Session altered.

SQL>alter session set timed_statistics = true;

Session altered.


SQL>alter session set events '10046 trace name context forever, level 12' ;

Session altered.

SQL>declare rc binary_integer; begin rc := sys.dbms_defer_sys.push(destination=>'DB2.COM', delay_seconds=>0, parallelism=>1); end;
2 /
declare rc binary_integer; begin rc := sys.dbms_defer_sys.push(destination=>'DB2.COM', delay_seconds=>0, parallelism=>1); end;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_DEFER@DB2.COM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_DEFER_SYS", line 1520
ORA-06512: at "SYS.DBMS_DEFER_SYS", line 1583
ORA-06512: at line 1


SQL>alter session set events '10046 trace name context off';

Session altered.

SQL>exit


The trace file :
============
PARSING IN CURSOR #18 len=191 dep=1 uid=0 oct=6 lid=0 tim=3608232468 hv=1792955087 ad='b376301c'
update system.def$_destination set last_delivered = :1, last_enq_tid = :2, last_seq = :3, last_txn_count = :4, last_error_number = :5, last_error_message = :6 where rowid = :7
END OF STMT
PARSE #18:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=3608232468
BINDS #18:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=12 oacfl2=1 size=24 offset=0
bfp=2017dae4 bln=22 avl=08 flg=09
value=8155439141995
bind 1: dty=1 mxl=32(22) mal=00 scl=00 pre=00 oacflg=12 oacfl2=1 size=32 offset=0
bfp=2017dafa bln=32 avl=11 flg=09
value="1.185.65841"
bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=00 oacfl2=1 size=72 offset=0
bfp=20141360 bln=22 avl=02 flg=05
value=3
bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=00 oacfl2=1 size=0 offset=24
bfp=20141378 bln=22 avl=01 flg=01
value=0
bind 4: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=00 oacfl2=1 size=0 offset=48
bfp=20141390 bln=22 avl=03 flg=01
value=604
bind 5: dty=1 mxl=2000(216) mal=00 scl=00 pre=00 oacflg=10 oacfl2=1 size=2000 offset=0
bfp=20177c38 bln=2000 avl=216 flg=09
value="ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_DEFER@DB2.COM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
bind 6: dty=11 mxl=16(16) mal=00 scl=00 pre=00 oacflg=10 oacfl2=1 size=16 offset=0
bfp=2017738c bln=16 avl=16 flg=09
value=00000969.0002.0001
EXEC #18:c=0,e=0,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=4,tim=3608232468
XCTEND rlbk=0, rd_only=0
WAIT #7: nam='log file sync' ela= 2 p1=1161 p2=0 p3=0
WAIT #7: nam='SQL*Net message to dblink' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #7: nam='SQL*Net message from dblink' ela= 0 p1=1413697536 p2=1 p3=0
STAT #16 id=1 cnt=1 pid=0 pos=0 obj=2585 op='TABLE ACCESS BY INDEX ROWID DEF$_DESTINATION '
STAT #16 id=2 cnt=2 pid=1 pos=1 obj=2586 op='INDEX UNIQUE SCAN '

Changes

The database was upgraded from 8i or 9i to 10g .

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