Oracle Golden gate - Long running transactions while stopping an extract ?
Below is how you can find it out and kill if needed.
GGSCI (ggnode1) 7>send extract EXRAJ, showtrans duration 20 MIN
Sending showtrans request to EXTRACT EXRAJ ...
Oldest redo log files necessary to restart Extract are:
Redo Thread 1, Redo Log Sequence Number 74058, SCN 1586.3900894393 (6815719025849), RBA 104066064
Redo Thread 2, Redo Log Sequence Number 79546, SCN 1586.3894494112 (6815712625568), RBA 1458358288
------------------------------------------------------------
XID: 1630.17.201166
Items: 1
Extract: EXRAJ
Redo Thread: 1
Start Time: 2013-05-10:21:43:50
SCN: 1586.3900894393 (6815719025849)
Redo Seq: 74058
Redo RBA: 104066064
Status: Running
------------------------------------------------------------
XID: 9068.29.296116
Items: 1
Extract: EXRAJ
Redo Thread: 2
Start Time: 2013-05-10:21:13:50
SCN: 1586.3894494112 (6815712625568)
Redo Seq: 79546
Redo RBA: 1458358288
Status: Running
------------------------------------------------------------
XID: 8805.6.296139
Items: 1
Extract: EXRAJ
Redo Thread: 2
Start Time: 2013-05-10:21:24:33
SCN: 1586.3896755063 (6815714886519)
Redo Seq: 79547
Redo RBA: 791874576
Status: Running
22:09:08 SQL> select * from gv$transaction where xidusn=8805;
INST_ID ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW START_UEXT START_UBAFIL
---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------
START_UBABLK START_UBASQN START_UBAREC SES_ADDR FLAG SPA REC NOU PTX NAME PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN
------------ ------------ ------------ ---------------- ---------- --- --- --- --- ---------------------------------------------------------------------- ---------- ---------- ---------- ----------
PTX_XIDSLT PTX_XIDSQN DSCN-B DSCN-W USED_UBLK USED_UREC LOG_IO PHY_IO CR_GET CR_CHANGE START_DATE DSCN_BASE DSCN_WRAP START_SCN DEPENDENT_SCN XID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ---------- ---------- ---------- ------------- ----------------
PRV_XID PTX_XID
---------------- ----------------
2 0000001BFF3DEBD8 8805 6 296139 856 125452 27952 5 ACTIVE 05/10/13 21:24:33 3896755063 1586 4 856
125452 27952 5 0000001C84587F40 7683 NO NO NO NO 0 0 0 0
0 0 0 0 1 1 5 0 0 0 10-MAY-13 0 0 6.8157E+12 0 22650006000484CB
0000000000000000 0000000000000000
22:09:38 SQL> select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='0000001BFF3DEBD8';
SID SERIAL# EVENT MACHINE SQL_ID SECONDS_IN_WAIT PREV_SQL_ID MODULE PROGRAM
----- ---------- ------------------------------ --------------- ------------- --------------- ------------- -------------- ------------------------------------------------
ACTION
----------------------------------------------------------------
9871 167 SQL*Net message from client client07 2710 7zwu0n8myp5vn occ-raj-jar occworker@client07 (TNS V1-V3)
20f669b057722
22:09:43 SQL> select
hash_value, address,
executions,buffer_gets, disk_reads,
round(buffer_gets/decode(executions, 0, 1, executions), 1) avg_gets,
round(disk_reads/decode(executions, 0, 1, executions), 1) avg_disk,
last_load_time,
module,
sql_fulltext
from v$sqlarea
where sql_id='&sql_id';
Enter value for sql_id: 7zwu0n8myp5vn
HASH_VALUE ADDRESS EXECUTIONS BUFFER_GETS DISK_READS AVG_GETS AVG_DISK LAST_LOAD_TIME MODULE
---------- ---------------- ---------- ----------- ---------- ---------- ---------- ------------------- -------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
669685620 0000001A4E4E8840 16774570 43243144 5728 2.6 0 10-MAY-13 occ-raj-jarers
INSERT INTO user_info (uid, part_key, t_id, uname) VALUES (:uid, :part_key, :t_id) /*Truncated the sql*/
you will see the following error message in ggserr.log file
2013-05-10 22:15:15 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): send EXRAJ showtrans duration 45m.
2013-05-10 22:15:16 INFO OGG-01021 Oracle GoldenGate Capture for Oracle, EXRAJ.prm: Command received from GGSCI: showtrans duration 45m.
Long Running Transaction: XID 8805.6.296139, Items 1, Extract EXRAJ, Redo Thread 2, SCN 1586.3896755063 (6815714886519), Redo Seq #79547, Redo RBA 791874576.
2013-05-10 22:15:20 WARNING OGG-01027 Oracle GoldenGate Capture for Oracle, EXRAJ.prm: Long Running Transaction: XID 8805.6.296139, Items 1, Extract EXRAJ, Redo Thread 2, SCN 1586.3896755063 (6815714886519), Redo Seq #79547, Redo RBA 791874576.
2013-05-10 22:15:26 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): send EXRAJ showtrans duration 90m.
2013-05-10 22:15:28 INFO OGG-01021 Oracle GoldenGate Capture for Oracle, EXRAJ.prm: Command received from GGSCI: showtrans duration 90m.
select logon_time,status,LAST_CALL_ET from gv$session where sid=9871 and inst_id=2;
LOGON_TIME STATUS LAST_CALL_ET
------------------- -------- ------------
05/10/2013 19:26:36 INACTIVE 3226
or you can use:
set echo on
set timing on
col sid format 999999
col serial# format 999999
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';
select t.start_time,t.status TSTATUS, s.status SSTATUS,s.sid, s.serial# ,s.machine , s.sql_id,s.prev_sql_id,s.process,t.XIDUSN||'.'||t.XIDSLOT||'.'||t.XIDSQN XID from gv$transaction t, gv$session s where t.addr=s.taddr and t.inst_id=s.inst_id and t.start_date < (sysdate-1/142) order by t.start_time;
-- if needed, you can go ahead and kill the sql (if it is not of a major impact)
alter system kill session '9871,167';
Below is how you can find it out and kill if needed.
GGSCI (ggnode1) 7>send extract EXRAJ, showtrans duration 20 MIN
Sending showtrans request to EXTRACT EXRAJ ...
Oldest redo log files necessary to restart Extract are:
Redo Thread 1, Redo Log Sequence Number 74058, SCN 1586.3900894393 (6815719025849), RBA 104066064
Redo Thread 2, Redo Log Sequence Number 79546, SCN 1586.3894494112 (6815712625568), RBA 1458358288
------------------------------------------------------------
XID: 1630.17.201166
Items: 1
Extract: EXRAJ
Redo Thread: 1
Start Time: 2013-05-10:21:43:50
SCN: 1586.3900894393 (6815719025849)
Redo Seq: 74058
Redo RBA: 104066064
Status: Running
------------------------------------------------------------
XID: 9068.29.296116
Items: 1
Extract: EXRAJ
Redo Thread: 2
Start Time: 2013-05-10:21:13:50
SCN: 1586.3894494112 (6815712625568)
Redo Seq: 79546
Redo RBA: 1458358288
Status: Running
------------------------------------------------------------
XID: 8805.6.296139
Items: 1
Extract: EXRAJ
Redo Thread: 2
Start Time: 2013-05-10:21:24:33
SCN: 1586.3896755063 (6815714886519)
Redo Seq: 79547
Redo RBA: 791874576
Status: Running
22:09:08 SQL> select * from gv$transaction where xidusn=8805;
INST_ID ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW START_UEXT START_UBAFIL
---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------
START_UBABLK START_UBASQN START_UBAREC SES_ADDR FLAG SPA REC NOU PTX NAME PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN
------------ ------------ ------------ ---------------- ---------- --- --- --- --- ---------------------------------------------------------------------- ---------- ---------- ---------- ----------
PTX_XIDSLT PTX_XIDSQN DSCN-B DSCN-W USED_UBLK USED_UREC LOG_IO PHY_IO CR_GET CR_CHANGE START_DATE DSCN_BASE DSCN_WRAP START_SCN DEPENDENT_SCN XID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ---------- ---------- ---------- ------------- ----------------
PRV_XID PTX_XID
---------------- ----------------
2 0000001BFF3DEBD8 8805 6 296139 856 125452 27952 5 ACTIVE 05/10/13 21:24:33 3896755063 1586 4 856
125452 27952 5 0000001C84587F40 7683 NO NO NO NO 0 0 0 0
0 0 0 0 1 1 5 0 0 0 10-MAY-13 0 0 6.8157E+12 0 22650006000484CB
0000000000000000 0000000000000000
22:09:38 SQL> select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='0000001BFF3DEBD8';
SID SERIAL# EVENT MACHINE SQL_ID SECONDS_IN_WAIT PREV_SQL_ID MODULE PROGRAM
----- ---------- ------------------------------ --------------- ------------- --------------- ------------- -------------- ------------------------------------------------
ACTION
----------------------------------------------------------------
9871 167 SQL*Net message from client client07 2710 7zwu0n8myp5vn occ-raj-jar occworker@client07 (TNS V1-V3)
20f669b057722
22:09:43 SQL> select
hash_value, address,
executions,buffer_gets, disk_reads,
round(buffer_gets/decode(executions, 0, 1, executions), 1) avg_gets,
round(disk_reads/decode(executions, 0, 1, executions), 1) avg_disk,
last_load_time,
module,
sql_fulltext
from v$sqlarea
where sql_id='&sql_id';
Enter value for sql_id: 7zwu0n8myp5vn
HASH_VALUE ADDRESS EXECUTIONS BUFFER_GETS DISK_READS AVG_GETS AVG_DISK LAST_LOAD_TIME MODULE
---------- ---------------- ---------- ----------- ---------- ---------- ---------- ------------------- -------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
669685620 0000001A4E4E8840 16774570 43243144 5728 2.6 0 10-MAY-13 occ-raj-jarers
INSERT INTO user_info (uid, part_key, t_id, uname) VALUES (:uid, :part_key, :t_id) /*Truncated the sql*/
you will see the following error message in ggserr.log file
2013-05-10 22:15:15 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): send EXRAJ showtrans duration 45m.
2013-05-10 22:15:16 INFO OGG-01021 Oracle GoldenGate Capture for Oracle, EXRAJ.prm: Command received from GGSCI: showtrans duration 45m.
Long Running Transaction: XID 8805.6.296139, Items 1, Extract EXRAJ, Redo Thread 2, SCN 1586.3896755063 (6815714886519), Redo Seq #79547, Redo RBA 791874576.
2013-05-10 22:15:20 WARNING OGG-01027 Oracle GoldenGate Capture for Oracle, EXRAJ.prm: Long Running Transaction: XID 8805.6.296139, Items 1, Extract EXRAJ, Redo Thread 2, SCN 1586.3896755063 (6815714886519), Redo Seq #79547, Redo RBA 791874576.
2013-05-10 22:15:26 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): send EXRAJ showtrans duration 90m.
2013-05-10 22:15:28 INFO OGG-01021 Oracle GoldenGate Capture for Oracle, EXRAJ.prm: Command received from GGSCI: showtrans duration 90m.
select logon_time,status,LAST_CALL_ET from gv$session where sid=9871 and inst_id=2;
LOGON_TIME STATUS LAST_CALL_ET
------------------- -------- ------------
05/10/2013 19:26:36 INACTIVE 3226
or you can use:
set echo on
set timing on
col sid format 999999
col serial# format 999999
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';
select t.start_time,t.status TSTATUS, s.status SSTATUS,s.sid, s.serial# ,s.machine , s.sql_id,s.prev_sql_id,s.process,t.XIDUSN||'.'||t.XIDSLOT||'.'||t.XIDSQN XID from gv$transaction t, gv$session s where t.addr=s.taddr and t.inst_id=s.inst_id and t.start_date < (sysdate-1/142) order by t.start_time;
-- if needed, you can go ahead and kill the sql (if it is not of a major impact)
alter system kill session '9871,167';
15 comments:
good material :)
Awesome Work , we reply on this Blog :)
Awesome Work , we reply on this Blog :)
Sure, if you guys need anything else, please let me know and i will try to provide info on that and Thank you for visiting my page :)
Hi Thnaks for ur info. if its a dml operation and i cannot kill that and i dont have the archive of that session. then whats next. is to tell the application team to commit that transaction so that it will update in new archives
Hi,
1. you can identify that long transaction using the steps i have provided and kill it.
2. If you don't want to kill the transaction, you can always skip that transaction telling Goldengate to skip it so that golden gate won't have to go back and scan from that point.
[oracle@rajhost:RAJDB]$ ggsci
GGSCI (rajhost) 1> send extraj, showtrans tabular
Sending showtrans request to EXTRACT extraj ...
Oldest redo log file necessary to restart Extract is:
Redo Thread 1, Redo Log Sequence Number 218164, SCN 1705.4049486930 (7326968726610), RBA 1175823888
XID Items Extract Redo Thread Start Time SCN Redo Seq Redo RBA Status
-----------------------------------------------------------------------------------------------------------------------------------------------
67.11.5144940 extraj 1 2014-05-16:11:00:03 1705.4049486930 (7326968726610) 218164 1175823888 Running
44.29.13307600 extraj 1 2014-05-16:12:32:14 1705.4055463640 (7326974703320) 218180 1040708624 Running
43.29.12978550 extraj 1 2014-05-16:12:44:43 1705.4056044963 (7326975284643) 218182 1977586192 Running
46.8.14357240 extraj 1 2014-05-16:13:43:11 1705.4062267706 (7326981507386) 218191 461408272 Running
60.39.8864090 extraj 1 2014-05-16:13:54:43 1705.4062947854 (7326982187534) 218192 810472464 Running
31.45.16493810 extraj 1 2014-05-16:13:55:20 1705.4062959956 (7326982199636) 218192 946033168 Running
29.47.16388810 extraj 1 2014-05-16:14:05:59 1705.4063587311 (7326982826991) 218193 1713235984 Running
45.42.13572120 extraj 1 2014-05-16:14:10:13 1705.4063746802 (7326982986482) 218194 307289616 Running
41.37.12289620 extraj 1 2014-05-16:14:10:50 1705.4063767132 (7326983006812) 218194 359904784 Running
66.46.3485370 extraj 1 2014-05-16:14:13:39 1705.4063883794 (7326983123474) 218194 541217296 Running
40.10.13026290 extraj 1 2014-05-16:14:13:40 1705.4063884607 (7326983124287) 218194 543233040 Running
65.31.4377780 extraj 1 2014-05-16:14:13:41 1705.4063885085 (7326983124765) 218194 544701968 Running
23.40.18259770 extraj 1 2014-05-16:14:13:41 1705.4063885266 (7326983124946) 218194 545349136 Running
64.46.4678110 extraj 1 2014-05-16:14:13:49 1705.4063891462 (7326983131142) 218194 562826768 Running
38.36.14360220 extraj 1 2014-05-16:14:13:50 1705.4063891641 (7326983131321) 218194 563274256 Running
At this point you will have a list of open transactions that the extract is monitoring. Note, goldengate has to monitor all open transactions because it obviously doesn't know if they will affect tables specified to extract before the transaction is complete. Now match current running transactions in the database to look for ones that should be excluded:
sys@rajhost:RAJDB> select a.sid || ',' || a.serial# who,
substr(a.username,1,12) username,
substr(a.program,1,12) program,
substr(a.machine,1,18) machine,
b.start_time,
decode(a.command, 0,'No Command', 1,'Create Table', 2,'Insert', 3,'Select', 6,'Update', 7,'Delete', 9,'Create Index', 15,'Alter Table', 21,'Create View', 23,'Validate Index', 35,'Alter Database', 39,'Create Tablespace', 41,'Drop Tablespace', 40,'Alter Tablespace', 53,'Drop User', 62,'Analyze Table', 63,'Analyze Index', a.command||': Other') command
from gv$session a,
gv$transaction b
where a.taddr = b.addr
order by b.start_time;
the above query will give you the details about the start time of the transactions, and you can decided if the one you are going to skip is good to go.
GGSCI (rajhost) 9> send extraj skiptrans 67.11.514494
Sending skiptrans request to EXTRACT extraj ...
Are you sure you sure you want to skip transaction [XID 67.11.514494, Redo Thread 1, Start Time 2014-05-16:11:00:03, SCN 1705.4049486930 (7326968726610)]? (y/n)y
Sending skiptrans request to EXTRACT extraj ...
Transaction [XID 67.11.514494, Redo Thread 1, Start Time 2014-05-16:11:00:03, SCN 1705.4049486930 (7326968726610)] skipped.
--NOTE, if this command returns an error that the transaction was not found, remove the trailing 0 (if there is one) at the end of the XID from ggsci. This is a bug where ggsci shows 67.11.514494 as 67.11.5144940 when using the SHOWTRANS TABULAR keyword. You can verify the correct XID by skipping TABULAR
Do this for each transaction that can safely be excluded from the extract.
but what if i cannot kill that session and i am not sure abt skipping it. then what to do next. do we have any other option to replicate that transcation.
1)do i want to tell the owner of session to commit it ?
2) or else to kill it and tell the owner to run that again and commit once done ?
And one more doubt .. y goldengate is hold session that is doing select?
not sure what you meant by "y goldengate is hold session that is doing select?"
golden gate doesn't hold anything of that sort.
options are :
1. you can certainly ask the owner to commit the transaction or do their transactions in a controlled manner.
2. its always good to check with the owner before killing the transaction.
3. other option is to increase your archive storage and put in few more days worth of archive logs and let the transaction take its own course of time and let golden gate catch up whenever the transaction commits and go back to those old archive logs.
Thanks for your reply.
y goldengate is hold session that is doing select?..
sorry i dint mention the above question properly.
what i mean was.
in showtrans command i can see one long running session after digging down and getting the sid . i found that sid is doing select from one table(just select). so y it checking the select statement as it not making any change in table.
In case of long running transactions, you have below two options:
- Skip the long running transaction
- FORCETRANS option to force a transaction in its current state to the trail as a committed transaction.
In case of Goldengate upgradation , you have to consider the 2nd option.
Thanks,
If you want people to completely understand what you're doing, why are you
1. Not including explanations in-between your various steps? You simply jump from one step to the next and you expect everybody to really, truly understand what they're looking at with complete comprehension? I don't think so.
2. You're selecting * from these views (gv$transaction, gv$session, etc). Do people REALLY need all the columns available in these views? Which ones are TRULY required in order to obtain required information? Certainly not ALL of them. That's why we filter queries with ONLY required columns. Not every column available in a table or view is required in order to ascertain relevant information.
If you truly intend your blogs to be informative with a desired objective of assisting other DBAs, you really need to adhere to the suggestions I'm bringing to your attention here. This is annoying and it doesn't have to be that way.
Well, this worked for me perfectly. The long running transactions were just gathering stats, so I used Raj's skiptrans (I couldn't kill the session) and everything is fine now. Thanks Raj.
I'm glad it helped @john Taylor
Post a Comment