Re: snapshots won't refresh - any suggestions?

A couple of suggestions:

1. Get a support contract. It's really something you need when things go wrong.
2. I would use absolute time rather than relative time to ensure the refreshes take place at the same time each day. Otherwise the refresh time may move.
3. Neither one or two have any bearing on your question of course. If there are no errors in the alert log, have you checked the trace files for the snapshot processes (they'll be called SNP_*.trc or something like that, and be in the background_dump_dest location).
4. Check the status of the refresh in the DBA_JOBS view. Is it broken (BROKEN=Y)?

rcordingley_at_my-deja.com wrote:

> I'm running oracle 8.0.5 on Solaris 2.6 (Ultra 2). I have two separate
> machines, running identical software. I set up a reporting database
> using read only snapshots of needed tables from the production
> database. I put snapshot logs on the prod database tables and then
> created the snapshots in the reporting database. After creating the
> snapshots, I added them to a refresh group and set it up update twice a
> day ('sysdate + .5'). This schemed worked like clockwork for a while.
> The tables got refreshed twice a day and all was well. Then, one awful
> day, somebody started a nasty program on the db server that at CPU
> cycles like mad and connected to the database over and over again. The
> snapshot refresh tried to do its thing while the server was bogged and
> it failed. Unbeknownst to me, even after we killed the offending
> program and yelled at the programmer until he cried, the snapshot
> refresh never again worked properly. Two and a half days later, I
> noticed that the snapshot log tables had grown to epic proportions. I
> tried to force a snapshot refresh, and it just sat there all day, doing
> nothing. Running snoop on the ethernet interface showed no sqlnet
> packets flowing at all. I bounced the reporting database down then up,
> and tried again...still no joy. I bounced the production database,
> resulting in much wailing and gnashing of teeth. Ahh...that did the
> trick! I manually ran the refresh and it worked just fine. I came in
> the next day and discovered that the automatic refresh had failed. I
> tried to run a manual refresh and it failed again. I strongly suspect
> that I could bounce the prod database again and probably get one more
> refresh to go. However, if I try to bounce the prod database every day,
> I'll soon find myself up against a wall confronting an ugly crowd
> consisting of everybody else in my company.
>
> I've only been at this company for three months (I'm a contractor,
> thank God) and today I discovered that we have no support contract with
> Oracle. In light of that, can anybody here give me any constructive
> suggestions?
>
> I've looked in the alert log on both systems, but found no errors.
> I've tested the database link from report to prod and it works just
> fine.
> I've looked in the job queue and the job is there and not broken.
> I have snapshot (queue) processes on both servers.
> I have the snapshot processing interval set to 6 minutes.
>
> Help me comp.databases.oracle, you're my only hope.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

--
Regards

Pete

Received on Wed Jul 28 1999 - 17:09:14 CDT