Re: problem with ORA-02019

Difficult. I don't know DB2. Is the post of Vimal Rai of any help? Are you testing the link connected as the same user as the user that initiated the transaction?

Something went wrong on 19-aug. Since fail_time is a date the time to the second is known (alter session set nls_date format='dd-mon-yy hh24:mi:ss'; or so)
What happened at that time? Check alert.log and trace files of your database at that timestamp.
Check what happended with the DB2 database and check the "alert.log" of that database too. Is there also a pending transaction in the DB2 database? Was a password changed in DB2? Or in the Oracle db? RECO will keep trying to recover this transaction with increasing intervals, again today (retry_time, also a date so you can see it to the second again) Be carefull by forcing this side of the transaction. You don't know what the DB2 dba will do with that side of the transaction. Potentially you could create an inconsistency when one site is committed and the other site is rolledback.
In the Distribution Database Systems manual I found a remark that rows involved in the transaction are kept locked for both read and write as long as the in doubt transaction is there. Maybe searching for these rows in V$LOCK gives a hint?

Ed Stevens <spamdump_at_nospam.noway.nohow> schreef in berichtnieuws 3d79022a.81732925_at_ausnews.austin.ibm.com...
| OK, this seems to be on the right track, but check this out -- after the
| queries, I tried to do a COMMIT FORCE, and you can see the results.
|
| SQL> select
| <snip formatting of all cols>
| 16 from dba_2pc_pending
| 17 /
|
| LOCAL_TRAN_ID: 5.4.51
| GLOBAL_TRAN_ID: XCTTDB.WORLD.e1ffeb38.5.4.51
| STATE: collecting
| MIXED: no
| ADVICE:
| TRAN_COMMENT:
| FAIL_TIME: 19-AUG-02
| FORCE_TIME:
| RETRY_TIME: 06-SEP-02
| OS_USER: PUR003
| OS_TERMINAL: 23MVT12
| HOST: NMMC\23MVT12
| DB_USER: ORCLNMM
| COMMIT#: 3618681
|
|
|
| SQL> select
| <snip formatting of all cols>
| 10 from dba_2pc_neighbors
| 11 Input truncated to 1 characters
| /
|
| LOCAL_TRAN_ID: 5.4.51
| IN_OUT: in
|
| DATABASE:
|
| DBUSER_OWNER: ORCLNMM
| INTERFACE: N
| DBID:
| SESS#: 1
|
| BRANCH: 0000
|
|
|
| LOCAL_TRAN_ID: 5.4.51
| IN_OUT: out
|
| DATABASE: LN_DB2T.WORLD
|
| DBUSER_OWNER: ORCLNMM
| INTERFACE: N
| DBID: DB2817
| SESS#: 1
|
| BRANCH: 4
|
|
|
| LOCAL_TRAN_ID: 5.4.51
| IN_OUT: out
|
| DATABASE: LN_ORCL.WORLD
|
| DBUSER_OWNER: ORCLNMM
| INTERFACE: N
| DBID: d8a8f4b3
| SESS#: 1
|
| BRANCH: 3
|
|
|
|
| SQL> commit force '5.4.51';
| commit force '5.4.51'
| *
| ERROR at line 1:
| ORA-02058: no prepared transaction found with ID 5.4.51
|
|
| SQL> spool off
|
| I also logged on as SYS AS SYSDBA and tried this:
|
| SVRMGR> commit force 'xcttdb.world.e1ffeb38.5.4.51', 3618681;
| commit force 'xcttdb.world.e1ffeb38.5.4.51', 3618681
| *
| ORA-00922: missing or invalid option
| SVRMGR>
|
| Looks like we're close, but how do I kill this guy?
|
| On Thu, 5 Sep 2002 21:34:04 +0200, "Anton Buijs" <aammbuijs_at_xs4all.nl>
 wrote:
|
| >Looks like there is an unfinished distributed transaction between your
| >database and the DB2 database accessed using the database link. The
 database
| >background process RECO tries to solve it.
| >The 2nd error line suggests the password used in the database link is not
| >the proper password. Or better: the DB2 database doesn't allow a connect
| >based on the credentials passed.
| >
| >Query views DBA_2PC_PENDING and DBA_2PC_NEIGHBORS to get more details
 about
| >the in-doubt transaction.
| >Read "Oracle8i Distributed Transaction Systems" for more info.
| >
| >Ed Stevens <spamdump_at_nospam.noway.nohow> schreef in berichtnieuws
| >3d77a09a.77577089_at_ausnews.austin.ibm.com...
| >| Platform: Oracle SE 8.1.7 on NT4
| >|
| >| Any ideas of what I'm seeing here?
| >|
| >| I'm not sure when this started, but I'm finding in one of my
 test/development
| >| databases (alert log) the following series of messages:
| >|
| >| Errors in file e:\oradmin\xctt\bdump\xcttRECO.TRC:
| >| ORA-02019: connection description for remote database not found
| >| ORA-01017: invalid username/password; logon denied
| >| ORA-02063: preceding line from LN_DB2T
| >|
| >| These have occurred at ever increasing intervals, beginning less than a
 minute
| >| after completion of database startup:
| >|
| >| Thu Sep 05 09:51:00 2002
| >| Thu Sep 05 09:51:36 2002
| >| Thu Sep 05 09:52:29 2002
| >| Thu Sep 05 09:53:50 2002
| >| Thu Sep 05 09:55:50 2002
| >| Thu Sep 05 09:58:53 2002
| >| Thu Sep 05 10:03:25 2002
| >| Thu Sep 05 10:10:22 2002
| >| Thu Sep 05 10:20:38 2002
| >| Thu Sep 05 10:36:24 2002
| >| Thu Sep 05 10:59:43 2002
| >| Thu Sep 05 11:34:47 2002
| >| Thu Sep 05 12:27:47 2002
| >|
| >| If I bounce the database, the pattern starts over again. Once I
 started
 chasing
| >| this down I made sure that the users (developers) were off the
 database,
 so that
| >| the errors are coming from some internal db activity, rather than
 connected user
| >| activity.
| >|
| >| The object LN_DB2T referenced in the ORA-02063 is a database link to an
 Oracle
| >| Transparent Gateway associated with a DB2 region on our mainframe.
 When I
 test
| >| that link myself, it seems to be functioning, as shown in this spool
 from
| >| SQL*Plus:
| >|
| >| SQL*Plus: Release 8.1.7.0.0 - Production on Thu Sep 5 12:53:15 2002
| >|
| >| (c) Copyright 2000 Oracle Corporation. All rights reserved.
| >|
| >| SQL> connect system_at_xcttdb
| >| Enter password: ******
| >| Connected.
| >| SQL> select count(*) from nmm.online_user_at_LN_DB2T;
| >|
| >| COUNT(*)
| >| ----------
| >| 3993
| >|
| >| SQL>
| >|
| >| This tells me (??) that the link in the database and the TNSNAMES entry
| >| referencing the DB2 region are both defined correctly.
| >|
| >| The listener log doesn't show any irregularities, and none of the
 connections
| >| listed there coincide with the errors reported in the alert log.
| >|
| >| --
| >| Ed Stevens
| >| (Opinions expressed do not necessarily represent those of my employer.)
| >
|
| --
| Ed Stevens
| (Opinions expressed do not necessarily represent those of my employer.)
  Received on Fri Sep 06 2002 - 16:37:10 CDT