Re: Synchronization Wizard weirdness

[Quoted] [Only top-posting platform and version info! More comments embedded below.]

Whoops, should have known better about explaining platform and versions!

Running Oracle 10g R2 on a Linux server (don't have the hardware specifics available).
Using the Oracle Enterprise Manager and Oracle Change Manager packages, which includes the Oracle DB Synchronizer. This is the piece which is causing me trouble.

In <dt28in$rus$1_at_news4.zwoll1.ov.home.nl> article, Frank van Bortel said:
: A Nice Guy wrote:
: > Hi All,
: > I'm an old-time Informix DBA who has now switched over to Oracle. I've
: > been learning the Oracle packages as I go.
: >
: > I have a Test Schema in the same database as the Main schema and need to
: > synchronize Test with Main (all table & data mods).
: >
: And how did you implement this? Sorry to bother you, but the
: crystal ball is gift-wrapped.

How implemented? First I created a blank schema, then I ran the Comparison Wizard between the Main schema and Test schema -- obviously returned the full set of objects since Test was empty. Then I ran the DB Synch program which copied the objects and the data from Main to Test. That's how the Test schema was originally built.

We've made several changes to the Main schema (which is mislabeled and should be Dev :-) ), including renaming tables, columns, and indexes. These modifications need to be incorporated into the Test schema. So, I ran the Comparison Wizard again between the 2 schemas and the called the DB Synch Wizard from the Comparison Viewer screen (it's the icon on the left, just above the Object Viewer icon).

: > All seems to go well, until the script actually runs. The Impact Report
: > only has warnings, but the Script fails.
: What script? What error(s)?

Well, have you run the Synch Wizard? It basically generates an "Impact Report" and "Script" for the modifications you want done --- namely synching the databases or schemas. The Impact Report gives warnings and errors that the Synch Wizard feels you should know about. The Script will never run if the Impact Report flags "Error"s of any sort, but usually will run fine if only Warnings are found.

When the Script fails in this case, no errors are returned! The message is "Script Execution Failed". period. It took a bit of looking through the results in the schema to determine what was really happening, which I describe next:

: > The problem: When the Sych script modifies a table, it renames the original
: > table. *** But it doesn't rename or drop the indexes on the table *** so
: > when it tries to recreate the index, the script fails because "an index
: > with that name already exists in the database".
:
: Change script? Should it fail on that?

Well, that was my question. What should I change in the script? The only thing I can think of is removing all of the Recovery table renames, which leaves me with a script naked of rollback possibilities. Yuck! Also, it seems as though the Synch Wizard will let you Edit the script, but then runs the original script anyway. So, that's not really an option unless someone can tell me how to get it to Execute the editted script.

As to posting the script, you really don't want that! It's about 75 pages long, printed. :-) But if you're familiar with the Synch, it's a bunch of XML calls, building SQL DDL statements.

: > Any idea what to do about this??? I'll take any suggestion and test it.
: Post the script.
: Learn about materialized views, refresh on commit
: or refresh on demand.

[Quoted] Not sure exactly what materialized views and the different forms of refreshing those views has anything to do with my issue. I'm dealing with DDL, not the underlying data (although I also move some data between the schemas, this is not the root of my problem).

: --
: Regards,
: Frank van Bortel

Thanks again for any assistance!

Mike Hoffman Received on Thu Feb 16 2006 - 20:31:50 CET