Hi all. We're under a migration, and thus we need to replicate the data of some of our table to a new database. The old and the new system are on different machines/databases. Old system uses 9iR2 while new system uses 10gR2 RAC. This is a 24/7 system (ATM) and thus I need to provide the less downtime possible. The tables structure on the new system have some litle estructural change, but no big deal.. however, they have no primary key defined.
Here's what I have tested so far:
1) Create mv log on old system with rowid and create mv on the new system schema with the ON PREBUILT TABLE option, unfortunatelly, this is not possible since the materialized view log is not created with primary key.
2) Create mv log on old system with rowid and create mv on the new system schema with the same name as the old tables, so they reflect the table itself. Create indexes and such and use drop materialized view mv PRESERVE TABLE, so the mv stays as a table once is dropped. Unfortunatelly, this requires oracle to scan the whole table again once the command is executed, this is not feasible as this will last some time since some of those tables has more than 1m rows.
These are the only two online option I have thought about, if anyone out there have any suggestion, please let me know. My goal is, as I have said, to have the less downtime possible.