|Streams checkpoint prediction [message #638618]
||Tue, 16 June 2015 02:59
Registered: May 2010
We have a problem with a bug (5703311) in streams, the capture is hanging whilst it advances the first scn (currently a weekly basis).
I'm unable, however, to find where the checkpoint is stored to calculate when this is going to hit us again.
Is it possible to see this checkpoint scn/time somewhere?
The method in the documentation doesn't make logical sense.
The checkpoint retention time is the amount of time, in number of days, that a capture process retains checkpoints before purging them automatically. A capture process periodically computes the age of a checkpoint by subtracting the NEXT_TIME of the archived redo log file that corresponds to the checkpoint from FIRST_TIME of the archived redo log file containing the required checkpoint SCN for the capture process. If the resulting value is greater than the checkpoint retention time, then the capture process automatically purges the checkpoint by advancing its first SCN value. Otherwise, the checkpoint is retained. The DBA_REGISTERED_ARCHIVED_LOG view displays the FIRST_TIME and NEXT_TIME for archived redo log files, and the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE view displays the required checkpoint SCN for a capture process.
Here is the detail from the capture
08:46:49 SQL> @print_tbl
Enter value for sql_stmt: select * from dba_capture
old 2: p_query varchar2(4000) := q'@&sql_stmt@';
new 2: p_query varchar2(4000) := q'@select * from dba_capture@';
CAPTURE_NAME : OGG$CAP_XGIMETLA
QUEUE_NAME : OGG$Q_XGIMETLA
QUEUE_OWNER : GGATE
RULE_SET_NAME : OGG$XGIMETLA_CAPTURE_I
RULE_SET_OWNER : GGATE
CAPTURE_USER : GGATE
START_SCN : 28454725720
STATUS : ENABLED
CAPTURED_SCN : 31867681787
APPLIED_SCN : 31867681714
USE_DATABASE_LINK : NO
FIRST_SCN : 28454725720
SOURCE_DATABASE : GINFO1P
SOURCE_DBID : 1251307385
SOURCE_RESETLOGS_SCN : 17881276282
SOURCE_RESETLOGS_TIME : 837671590
LOGMINER_ID : 3
NEGATIVE_RULE_SET_NAME : OGG$XGIMETLA_CAPTURE_E
NEGATIVE_RULE_SET_OWNER : GGATE
MAX_CHECKPOINT_SCN : 31867576427
REQUIRED_CHECKPOINT_SCN : 31867681714
LOGFILE_ASSIGNMENT : IMPLICIT
STATUS_CHANGE_TIME : 12-jun-2015 18:35:39
VERSION : 126.96.36.199.0
CAPTURE_TYPE : LOCAL
LAST_ENQUEUED_SCN : 31867681827
CHECKPOINT_RETENTION_TIME : 7
PURPOSE : GoldenGate Capture
CLIENT_NAME : XGIMETLA
CLIENT_STATUS : ATTACHED
OLDEST_SCN : 31867681714
FILTERED_SCN : 0
PL/SQL procedure successfully completed.
So based on the docs I think I want to be looking for an entry in dba_registered_archived_logs where REQUIRED_CHECKPOINT_SCN is between the first scn and next scn. Except that does't work, that always gives me a log a few minutes old (this switches a lot). In fact any SCNs up in the capture results are not as expected, I'm expecting to hit a checkpoint date of approx last friday at 1800 local.
We didnt configure streams, OGG did this as part of integrated capture so I'm deep in unknown territory here so forgive me if useful data is missing or I sound like I'm being stupid here.
Basically I'm trying to predict the next time the problem is going to occur - obviously production will be next friday but I need to do the same thing in test to see if any fixes work to stop the hangs - but the trouble is without knowing when the thing is meant to checkpoint I can't really tell if it's fixed, or just not happened yet.