Hey all!
So, these days I was about to stop the GGate extract to add a new table to replication and saw this:
GGSCI (greporasrvr) 2> stop extract GREPORA_EXT Sending STOP request to EXTRACT GREPORA_EXT ... STOP request pending. There are open, long-running transactions. Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts. To force Extract to stop, use the SEND EXTRACT GREPORA_EXT, FORCESTOP command. Oldest redo log file necessary to restart Extract is: Redo Log Sequence Number 616, RBA 135673360. 2018-04-12 15:55:41 WARNING OGG-01742 Command sent to EXTRACT GREPORA_EXT returned with an invalid response.
Interesting, right?
Let’s discover what is causing the situation:
GGSCI (greporasrvr) 2> send extract GREPORA_EXT, showtrans duration 20 MIN Sending showtrans request to EXTRACT GREPORA_EXT ... Oldest redo log file necessary to restart Extract is: Redo Log Sequence Number 616, RBA 135673360 ------------------------------------------------------------ XID: 92.1.1460254 Items: 1 Extract: GREPORA_EXT Redo Thread: 1 Start Time: 2018-04-12:08:27:03 SCN: 22.120909138 (94610189650) Redo Seq: 616 Redo RBA: 135673360 Status: Running ------------------------------------------------------------ XID: 69.7.1771671 Items: 1 Extract: GREPORA_EXT Redo Thread: 1 Start Time: 2018-04-12:13:27:29 SCN: 22.121387178 (94610667690) Redo Seq: 629 Redo RBA: 1457168 Status: Running 15:59:12 SYS@GREPDB>select * from gv$transaction where xidusn=92; 1 000000018FEA2170 92 1 1460254 121 548870 24255 48 ACTIVE 04/12/18 08:27:02 120909138 22 168 2 131578 24097 43 0000000198A49D68 7683 NO NO NO NO 0 0 0 0 0 0 0 0 81875 4581610 23108750 1415217 -1.675E+09 197 12-APR-2018 08:27:02 0 0 9.4610E+10 0 5C0001001E481600 0000000000000000 0000000000000000 1 row selected. 16:00:08 SYS@GREPDB>select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='000000018FEA2170'; 885 55577 db file sequential read feedprocs12 39za7ttrfjbr1 55 6x9pg77q982g3 JDBC Thin Client JDBC Thin Client 1 row selected. 16:00:37 SYS@GREPDB>set verify off set pagesize 999 col username format a13 col prog format a22 col sql_text format a140 col sid format 999 col child_number format 99999 heading CHILD col ocategory format a10 col avg_etime format 9,999,999.99 col etime format 9,999,999.99 col execs for 99999,999 select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username, sql_text from v$sql s, dba_users u where sql_id like nvl('&sql_id',sql_id) and u.user_id = s.parsing_user_id / Enter value for sql_id: 39za7ttrfjbr1 SQL_ID CHILD PLAN_HASH EXECS ETIME AVG_ETIME USERNAME ------------- ------ ---------- ---------- ------------- ------------- ------------- SQL_TEXT -------------------------------------------------------------------------------------------------------------------------------------------- 39za7ttrfjbr1 0 72089250 0 25,434.97 25,434.97 GREPORA --SQL Name:summary/envision/insertMyTable INSERT INTO mytable ( .......) 1 row selected.
Nice!
Now we know it’s being caused by this long insert in mytable, but how can I proceed?
There are basically two options:
1. Kill the session on DB: Long rollback predicted, be aware of the impact and rollback activity, plus application effects if any.
2. Force stop of Extract with SEND EXTRACT GREPORA_EXT, FORCESTOP: In this case there is no effect for the database and no rollback, HOWEVER, when extract start again it will need to start from this cancelled sequence. First question, do I have this sequence still available? As you could see before, sequence 616 on would be needed, lets check on our availability:
16:01:41 SYS@GREPDB>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +FRA_GREPDB Oldest online log sequence 619 Next log sequence to archive 634 Current log sequence 634
Ouch!
So, in case it be needed, be aware you’ll need your archivelogs and, if already removed, restore may be needed.
There is also third option: 3. Wait until transaction be completed. 🙂
If 1 or 2 need to be chosen, choose wisely. 🙂
Hope this helps.
Cheers!
Thank you man you saved our life