Re: struggeling with replication

EdStevens schreef:

> Frank,
>
> Thanks again for bearing with me.
>
> Through continued experimentation, I've ascertained that the CREATE
> MATERIAL VIEW statement seem to require sysdba authority, and tries to
> create a table ... thus the failure on my manual CREATE statement. I'd
> presume that if the db-link had been in place at the time of the
> import, the CREATE mv statements would have failed like my manual ones
> did, seeing as the import had already created the underlying table.

I'd take a good look at that - you may need some rights to create/maintain materilized views (and SYS may be the one to grant these), but I would be extremely careful when creating materialized views as SYS (presumably on SYS tables, or tables from other owners). It surely does not resemble the 'least privelege' concept!
>
> At this point I think I'm in pretty good shape. I've used OEM to
> extract the CREATE mv statements, and will pre-pend to each a
> corresponding DROP TABLE and DROP MATERERIALIZED VIEW statement. Those
> can then be run immediately after each import cycle. I've done this
> manually to prove it out, and everything seems to be ok with it. The
> connectivity seems to be ok as well. FWIW, the import is also
> bringing in a lot of tables that are NOT associated with an MV, so we
> are still reliant on the import to do the basic production cutover
> load.

So, go for both - use the import for the bulk, and just redo the MV's. Any scenario is OK, as long as you understand the consequences.

>
> I think the only open question I have at this point (maybe there are
> questions I *should* ask and don't know to!) are with the REFRESH ...
> START WITH clause. It looks as if the date value in my extracted DDL
> is as of the time of the ddl extract. Should this be manually adusted?
> Come time for the production cutover, the master site is going to be
> exported and moved about 48 hours before the MV site.

Yup - start with is not an option often used - I never did. But it should be there for completeness - the table imported already contains all data from before that point (and has this "knowlegde"), so no use in trying to get that data again.
If you drop and create MV's from scratch, don't bother - the start with will be the moment you execute the statement(s) - the MV table will hold all data, preceding that moment (as it will extract the data from the base tables).
So just ignore the start with, and rebuild the MVs. Received on Wed Apr 26 2006 - 04:43:40 CDT