Re: Golden Gate - Find a transaction or is it orphaned? Newbie desires to know.

From: Sourav Biswas <biswas.sourav_at_hotmail.com>
Date: Fri, 26 Jan 2018 07:29:39 +0000
Message-ID: <PN1PR0101MB2063AE7B7DDFF77657DB337CF0E00_at_PN1PR0101MB2063.INDPRD01.PROD.OUTLOOK.COM>

Hello Chris,

I usually start with below syntax on GGSCI to figure out the concerned transaction:

GGSCI> send extract showtrans tabular

Then I check for the transaction from gv$transaction:

set lines 200 pages 1000
col xid for a16
col username for a18
col schemaname for a18
col osuser for a12
select s.inst_id, t.start_time, t.xidusn||'.'||t.xidslot||'.'||t.xidsqn xid, s.status, s.sid,s.serial#,s.username,s.status,s.schemaname, decode(s.sql_id,null,s.prev_sql_id) sqlid, decode(s.sql_child_number,null,s.prev_child_number) child from gv$transaction t, gv$session s
where s.saddr = t.ses_addr
order by t.start_time;

In case I don't find the required details, I execute below set of queries on SQL*Plus using logminer to fetch it from the archive logs. The sequence# and thread# information is derived from "showtrans" command mentioned earlier.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

SQL> set numformat 9999999999999999999999

SQL> exec dbms_logmnr.add_logfile('+RECO1/costd_co/archivelog/2017_10_02/thread_4_seq_145341.644.956341945'); SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

SQL> select xidusn, xidslt, xidsqn, operation,seg_type_name, seg_owner, seg_name,table_name from v$logmnr_contents where XIDUSN ='70'and XIDSLT ='27' and XIDSQN='25036770';

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;   HTH. Best Regards,
Sourav Biswas
+91-9650017306

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Chris Taylor <christopherdtaylor1994_at_gmail.com> Sent: Wednesday, January 24, 2018 12:44 AM To: ORACLE-L
Subject: Golden Gate - Find a transaction or is it orphaned? Newbie desires to know.

OGG 12.1.0.2.xx

I have this transaction in my extract:

---

XID:                  159.12.29901130
Items:                1
Extract:              EXT_ALL
Redo Thread:          3
Start Time:           2018-01-22:19:12:44
SCN:                  212.838551413 (911371618165)
Redo Seq:             189389
Redo RBA:             12380879828
Status:               Running

How can I find the transaction in the DB for that? I'm using this query:

I tried:

SELECT t.start_time

, t.xidusn

                 || '.'
                 || t.xidslot
                 || '.'
                 || t.xidsqn
                     xid


                ,s.status

                ,s.sid

                ,s.serial#

                ,s.username

                ,s.status

                ,s.schemaname


                ,DECODE ( s.sql_id, NULL, s.prev_sql_id )


                     sqlid


                ,DECODE ( s.sql_child_number, NULL, s.prev_child_number )


                     child
            FROM v$transaction t, v$session s
           WHERE s.saddr = t.ses_addr
          and t.xidusn = 159

But that returns 0 rows - is that an orphan transaction?

Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 26 2018 - 08:29:39 CET