Re: Oracle8.1.7 snapshot refresh failed without any error message

From: Frank van Bortel <>
Date: Tue, 04 Dec 2007 20:11:41 +0100
Message-ID: <fj48pc$kos$> wrote:
> hi,
> we use snapshot in oralce8.1.7 (HP-UX 11i). There are three
> snapshots which map main DB data to three satalite servers. On each
> satalite server there are three jobs configured to refresh snapshot
> tables every 5 min. One table( named service_t) which is included in
> snapshot contains 5.5million records.
> There had been refresh failures before. At those failure time, I
> cound determine refresh failure by " select job, failures, last_sec,
> next_sec from user_jobs".

Hmmm - does not sound like a stable system. Ever found out what causes these errors?

> But, today application running on snapshot failed because data
> in snapshots does not sync. with main DB. there is NO failure record
> in user_jobs table, there is no error record in alert.log either. we
> tried with manual refresh , the operation finish successfully within
> one minute. But, new record in service_t did not synced to snapshot
> at all.
> As last resort, we delete and recreate MLOG table on service_t.

Well - you indicated the snapshot was out of sync (by the way, we all *love* the actual error codes), so there's no job to run (it ran, but with a user error - not job related)

Suggest you check your time settings on the servers (best to let then synchronize among each others, and have one synchronize, using the nntp (time) protocol.
Also, different speed, or loads may cause this - it's simply put, the situation where the log thinks it's ahead of time with the MV (that is what the error said, too). In many cases, dropping and recreating the MV Log tables is the fastest solution; take care with your next refresh - it will be a full (make sure you use FORCE, not FAST)

Frank van Bortel

Top-posting is one way to shut me up...
Received on Tue Dec 04 2007 - 13:11:41 CST

Original text of this message