snapshots won't refresh - any suggestions?

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. Received on Wed Jul 28 1999 - 15:55:26 CDT