- July 1, 2016 at 12:03 am #8839Frank KnotParticipant
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?July 1, 2016 at 1:04 am #8841Frank KnotParticipant
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.
- You must be logged in to reply to this topic.