Re: That flakey little animal called SNAPSHOT -- pl. comment

From: Chuck Hamilton <chuckh_at_dvol.com>
Date: 1996/08/02
Message-ID: <3201faa9.1500880_at_n5.gbso.net>#1/1


Rajiv Tandon <rajiv.tandon_at_bridge.bellsouth.com> wrote:

>Problems
>--------
>Ever since the snapshots were turned on, I haven't slept much.
>The one problem we could get a fix for was and ORA-600: [17271] Instantiation space
>leak, for which Oracle has a patch (namely, #275335 generic, #286670 for HP-UX)
>
>When the 3rd remote snapshot was added, an entry was made in DBA_SNAPSHOT_LOGS.
>But all the entries look darned identical for a given master table, except for the
>field CURRENT_SNAPSHOTS which is a timestamp of when the remote site was last updated.
>
>(1) i.e. If you have more than one snapshot of the same master, what distinguishes
> the records in DBA_SNAPSHOT_LOGS from each other, associating them with the remote
> database ? Did we botch our data dictionary ? Or is it that there are some fields
> in SYS.MLOG$ and SYS.SLOG$ (tables the DBA_SNAPSHOT_LOGS view is based on) which
> are taking care of it ?
>
>(2) Along the same line, since I trashed my 3rd site, how can I remove the vagrant
> entries from DBA_SNAPSHOT_LOGS ? I know which ones they are because the
> CURRENT_SNAPSHOTS field has the oldest date, but obviously I cannot remove
> the records from a data dict. table manually. Would dropping and re-creating
> the logs (and subsequently doing complete refreshes on dependent 2 sites) be the
> only way or is there a command or package I am unaware of ?

We have a similar setup - one master database being snapshotted to two snapshot databases. We also have had similar problems. I for one do not believe snapshots are very stable at all. Out solution so far is that when things get out of sync: drop/recreate the logs, drop/recreate the snapshots. Doing a complete refresh has been out of the question due to the RBS requirements of such a transaction. Recreating is much faster and requires less RBS.

One thing that's making it simpler for us is the fact that we are only doing manual refreshes. If the master database is down, we simply won't run the procedure that refreshes the snapshots.

We still haven't figured out why things are getting out of sync though. Everything was created the way Oracle says to do it, and in the order they recommend - logs first, then snapshots. But every month when we try to do a manual refresh, at least one snapshot on one of the databases (usually more) says "fast refresh cannot be used" and we have to drop and recreate that one again.

Where is dbms_snapshot.purge documented. I've never heard of it?

As for other problems - 7.1.3 is a known buggy release. You should probably upgrade asap. We have 7.1.3 and 7.1.4 as our snapshot databases, and 7.2.2 as our master. The 7.1.3 instance has given us far more problems than any other - space leaks, snapshots out of sync for no apparent reason, temporary segments that hang around forever, etc.

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!
Received on Fri Aug 02 1996 - 00:00:00 CEST

Original text of this message