Re: Need information on Oracle Snapshots

Michael Kelly (Michael_Kelly_at_dge.ceo.dg.com) wrote:
: Can anybody out there point me in the direction of any documentation (on-line
: or paper based) that describes Oracle's Snapshot facilities in greater detail
: than the Oracle manuals?
: The information I'm after can be summarised as follows:
: 1. What system tables are used to keep track of the snapshots?
: (Through experimentation I've sussed out SNAP$, SLOG$ & MLOG$
: are there any others?)

Those are the base data dictionary tables, the info in them are presented in a slightly more user-readable format using the data dictionary view dba_snapshots and dba_snapshot_logs. But that is as far as I know all that's used to administrate snapshots.

: 2. How does Oracle keep track of multiple 'snapshots' of the same table(s)?

There are two types of snapshots, complete and fast refresh.

The first case is trivial...basically, every time the slave needs to get a sync itself with the master, it copies the whole table, along with an extra column to hold the rowid of the rows in the master, to the slave node. The model can be extended to N number of snapshot slaves without any difficulty.

The second case is more interesting (and I pressume is the case you are interested in). It's somewhat difficult to explain without some pictures, but here goes: When you create a snapshot log for a master, an entry is made into the mlog$ table with the timestamps of youngest and oldest being the time of the log creation. On the slave node, when you create your snapshot, the slave will see that a snapshot log exists and therefore an entry is made into slog$, with snaptime and snapshot equal to the date the snapshot was created.

Let's suppose we have a master and two slave nodes (slave-A and slave-B). You want to snapshot a table called EMP and allow for fast refreshes. A snapshot log would be created first, followed by the snapshot creations (with slave-A created first and slave-B created an hour later). What you should have now are two slog$ entries for master='EMP': One would have snaptime for slave-A and the other the snaptime for slave-B (one hour after slave-A). You would also have one mlog$ entry, the youngest would be the snaptime for slave-B and the oldest would be the snaptime for slave-A.

When you request a slave to do a fast refresh, it first validates whether that is possible by checking (a) there is a snapshot log and (b) whether it was created sometime before a snapshot was created. In our example, if slave-A were asked to do a fast refresh, it would check for an entry in mlog$ with master='EMP' and check if it's slog$ snapshot is before oldest in mlog$. If so, it'll do the fast refresh; otherwise, it'll give an error saying that is not possible (which i think answers your last question below).

slave-A locks the rows in the mlog$_EMP table and applies the DML action specified. After the sync is complete (ie, all rows in snapshot log are applied), it sees that snaptime$$ was previously null and that there are other snapshots on this master ( >1 row in slog$), hence it will not delete the rows in the mlog$_EMP but instead mark them by stamping SNAPTIME$$ with the time the snapshot refresh was initiated. This is the same time that's stamped in it's slog$ entry snaptime value, and the mlog$'s youngest is stamped as such as well.

now, lets say four hours later slave-B wants to do a fast refresh. In the space of those previous four hours DML operations have occurred on EMP so that there are now two distinct types of entries in the mlog$_EMP table. One type has a snaptime$$ of slave-A's snaptime, which marks the rows as being DML operations that had been performed before slave-A's snaptime. The other type has a null snaptime$$, which marks the rows as being DML operations which has transpired in the four hours after slave-A's snaptime. slave-B knows it needs to pull all the mlog$_EMP rows over because its slog$ snaptime is before the snaptime$$ values in the mlog$_EMP table. So it does so (after doing the same check slave-A did to verify that a fast refresh is possible) and does some bookkeeping in mlog$. Specifically it will see that its snaptime is younger than youngest and that no slog$ snaptime is older than oldest. Hence, it will now set oldest = youngest (which is slave-A snaptime) and set youngest to it's own (slave-B) snaptime. Now, it needs decide if and which rows to purge from mlog$_EMP. It will get rid of all rows whose snaptime$$ is older that or equal to oldest from mlog$ and set all snaptime$$ which are null to slave-B's snaptime. So now, immediately after slave-B's fast refresh is completed, mlog$_EMP will have only one type of rows: those with snaptime$$ equal to slave-B's snaptime. The cycle repeats itself when slave-A gets refreshed some time later.

The model extends itself similarly with N-number of slaves, though it does get slightly more complicated to follow.

: 3. Given a simple snapshot and a snapshot log at the source site, what
: conditions will cause Oracle to return a 'FAST REFRESH cannot be used'
: error when trying to do a fast refresh?
: (We've had lots of problems with this one and I'm particularly interested
: in understanding the mechanisms behind fast refreshes.)

answered above i believe. The key to avoiding this problem is to create the snapshot log *before* creating any snapshots. also make sure you drop snapshot logs and snapshots using standard syntax rather than dropping the base snap$_<master> tables or the mlog$_<master> tables. doing so will royally screw up the snapshot bookkeeping.

: My database is in the UK with 44 simple snapshots of tables in a US based
: database (time difference = 5 hours).

one last item. the timestamps used are always the sysdate from the master node, so time zone differences and even system clocks being un-synchronized does not present problems for snapshots (though other areas of the database may be gravely effected by those conditions).

Johnny Chan
Independent Oracle Specialist Received on Tue Aug 08 1995 - 00:00:00 CEST