Wierd snapshot jobs behavior

Hey guys,

I guess you have to know when to holler for "what in the heck is going on" here....

In short, a lot of maintenance and security implementations happened last weekend, but the key ones were security. We implemented secure dblinks between all of our production oltp databases and any dss reporting database using those links for replication (simple materialized views). Databases are 8.1.7.x, on Solaris 2.6.

Here's the scenario:

1. drop the database links on the target database
2. create new secure database links on the target database which point to new secure user accounts on the source database (created first)
3. refresh snapshots (3 schema owners for the snapshots and 3 new secure links matching the schema owners with privs)
4. 15 of 69 snapshots fail to refresh on 942's (no one schema or owner failed... mixed across 2 of the three)
5. Granted select on the mlog$'s for the failed tables to the new link owner via the schema owner
6. Every time I reset the time in sys.job$ to something less than the next time the job was supposed to run, the job immediately went to pending
   (understand the bug there, I think but can't explain the next one)
   
7. Tonight I reset the time from 5/28/03 12:** to 5/28/03 00:05:00 (which was less then the next start time) and the jobs did NOT start until the earlier time.
8. I've done this 5 times in the last couple of days and every time if the new "next time" was less then the old "next time" the job immediately went pending and started
9. The solution so far has been to wait for the job to fail and grant select on the mlog$ for that snapshot.

Question,
1. When the job fires on the target side, is its query directed at the mlog$ first, or is there an underlying flag which say's this table has changed so it then hits the mlog$
2. If there were 0 rows in the table before the last refresh, and 0 rows at the next refresh, and it completed successfully the last refresh and failed on the next refresh, and 0 rows after the last refresh, and 0 rows in the source table through all refreshes?????

This one is simply not making any sense. There are no snapshot groups, everything is a simple read only snapshot with sysdate+1 next. Perhaps the mlog$s were not created under a consistent owner? One time all snapshots would refresh, the next time one or two would fail. As of this very late writing, all jobs competed, but I still can't explain the failure.

Has anyone seen this behavior before. It seems to be isolated to the mlogs and select privs on them and in very limited for some reason.

Thanks,

Bill Burke
"The Kinder and Gentler DBA"
International Oracle Users Group www.ioug.org Executive Vice President
Director of Marketing
Board of Directors 2003-2005, 2000-2002
Oracle Development Tools User Group www.odtug.com Treasurer, Membership Chair
Board of Directors 1996-2000

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Burke, William F (Bill)
  INET: wbburke_at_avaya.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).