Why does it take a long time before capture starts moving after a restart?

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #8839
    Frank Knot
    Participant

    Capture processes are sometimes shut down for planned maintenance e.g. to adapt a channel. Upon restarting the capture it may sometimes take a long time before the capture log shows any activity. Why is this the case?

    #8841
    Frank Knot
    Participant

    Long-running transactions that were active when the capture was shut down cause this behavior. HVR will go back in the transaction logs to find the oldest open transaction that it was tracking when it was shut down. This may be many archived transaction logs back. HVR will then read through every single transaction log file to find out what ever happened to this transaction. Only once the capture reaches the point that transactions commit that were not previously captured will the log report activity on the capture process. This can take minutes and in extreme cases very many minutes. Note that even if the transaction did not yet touch any tables in the channel then still HVR will go all the way back into the transaction logs to process its history.

    In an Oracle Database a DBA can find out at the current point in time what the open transactions are. Below is a query that provides such information. The longest running transaction will be first in the list. The script with formatting etc. runs in sqlplus; to run it in another tool simply take out the formatting. Consider running this query before shutting down capture on an Oracle Database to see if some meaningless transactions in this list have a very long duration. Traditionally the database management tool Toad used to be a common violator of meaningful transactions; upon connecting it would start a transaction and only end it upon disconnecting.

    set linesize 200
    set pagesize 5000
    col transaction_duration format a45
    
    with transaction_details as
    ( select inst_id
      , ses_addr
      , sysdate - start_date as diff
      from gv$transaction
    )
    select s.username
    , to_char(trunc(t.diff))
                 || ' days, '
                 || to_char(trunc(mod(t.diff * 24,24)))
                 || ' hours, '
                 || to_char(trunc(mod(t.diff * 24 * 60,24)))
                 || ' minutes, '
                 || to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))
                 || ' seconds' as transaction_duration
    , s.program
    , s.terminal
    , s.status
    , s.sid
    , s.serial#
    from gv$session s
    , transaction_details t
    where s.inst_id = t.inst_id
    and s.saddr = t.ses_addr
    order by t.diff desc
    /

    This query has also been included in the Oracle Log-based Capture check script that is on the wiki in an expert note.

Viewing 2 posts - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.

© 2020 HVR

Test drive Contact us