Re: Snapshots: Problematic Large Table

Ian S. Fogarty (fogartis_at_capital.net) wrote:
> I posted the following on the MetaLink Oracle Server 7 forum a week or
> so ago. I guess it's a stumper. Any ideas?
 

> There is one production database, and one snapshoted replica. The
> replica is kept up to date nightly through the use of fast refresh.
> A few weeks ago the replica database encountered space problems, and
> unfortunately the snapshot was dropped. To avoid ever growing snapshot
> log tables, the snapshot logs were dropped a day later.
 

> Lately, administrators of the reporting/replica database have been
> importing logical exports of the large table, to keep the database
> somewhat consistent.
 

> The table has over 10 million rows and seems to take an eternity to
> replicate within the batch window, and runs into rollback segment
> consistency problems, even though on-line usage is low. WAN?
 

> Sooo... I was hoping that someone might have already encountered this
> situation and figured out some way to: 1) dump the table to flat file
> with the rowid included, 2) recreate the snapshot log, 3) create the
> snapshot with a subquery that will replicate 1 row, 4) delete the row
> within the snap$_<table> on the replica database, 5) sqlload the data
> into the snap$_<table>, 6) then trick the snapshot into thinking it's
> up to date, 7) change the subquery to replicate everything, 8) fast
> refresh forever after.
 

> We have performed some preliminary tests that do not look promising.
> Namely, rows manually placed within the snap$_<table> seem to be
> removed.

You are pretty close...the steps I used are:

1. dump the table to a flatfile with rowid included
2. re-create the snapshot log
3. create the snapshot with a subquery the following where clause: "where 0=1" (ie, it replicate no rows, just the structure)
4. run a complete and a fast refresh to make sure they both work fine you can verify they are both working by checking SNAPTIME in the slave's SNAP$ table and the master's SLOG$ table to see if they are identical after each type of refresh
5. export the index definition I_SNAP$_<table> and drop the index
6. sqlload the data into SNAP$_<table>
7. recreate the index I_SNAP$_<table>
8. recreate the view MVIEW$_<table> on the slave to get rid of the "where 0=1" clause using "CREATE OR REPLACE VIEW..."
9. fast refresh forever after...

You have to make sure that there are no DML operations on the master table between steps (1) and (4) inclusively; otherwise, you'll get an inconsistent final snapshot.

good luck

jc

--

Johnny Chan
j4ychan_at_pacbell.com
Independent Oracle Specialist