Amalgamation of Oracle 8.0.6 databases

[davidstraker]

Hi

We have a situation where we have to amalgamate two databases, which I hope someone can help with.

Due to user requirements, the amalgamation has to be accomplished in the course of one night.

Can anyone point us to the quickest, safest way of doing this? We've tried EXP/IMP in tests, but the habit that IMP has of slowing down is raising serious questions about how much we dare rely on it given the imposed time limitation.

I'd be grateful for any pointers anyone could give on this, please.

Thanx
David F. Straker


Background information
======================
Each of the two databases is on its own server, and the two servers are in geographically separate locations (both in the UK).

The two databases are joined via database links: one server is designated as the "master" and the other as the "slave", and a system of data copying is in force between them, using a mix of triggers and procedure-based overnight batch jobs to keep reference data and archive data in sync. on both sites.

We have to take both these databases and amalgamate them to form a single database on a third server, which will then replace the two separate servers.

All 3 databases in question are running in Oracle 8.0.6.

There are two schemas, of the same names on each database, which have to be amalgamated:

PRODUCT_USER: data currently being processed
MIS_USER: archive of all data processed through the PRODUCT_USER schema

The amalgamation must take the PRODUCT_USER schema on the master database, the PRODUCT_USER schema on the slave database, and amalgamate them to form one PRODUCT_USER schema on the new single database.

The data in the PRODUCT_USER schema on both servers is not identical, but as both use widely differing ranges of primary key values there is no risk of primary keys being violated by the amalgamation of data.

Via the system of data copying, the data in the MIS_USER schema IS identical on both servers - so we only need to copy the master database's MIS_USER schema to the new single database.

The MIS_USER schema is about 20Gb in size; the two PRODUCT_USER schemas that have to be amalgamated are each about 1/6th the size of the MIS_USER schema.

The PRODUCT_USER schema also contains the majority of the packages, procedures, functions & triggers in the application.

 

[SantaMufasa]

David, Here is an idea that will take you FAR less time for migration that strictly EXP/IMP. But, it hinges on the notion that the new, third, consolidated database has no other business functionality than what you described. Specifically, for this idea to work, the new, third database INITIALLY should have no other purpose than to house the consolidation of the old master and old slave.
Here, then are the functional steps:

1) Shutdown both the master and slave databases.
2) Copy all old-master database files (as though it was a full, cold backup) to the new, third database. This includes all database datafiles, on-line redo log files, and control files. Ensure that you have on the new-db machine, updated &quot;oratab&quot;, listener, and init<SID>.ora (parameter) files to support the &quot;new&quot; third database. Then, startup the &quot;new&quot; database from the old-master database files.
3) Startup the old-slave database (in RESTRICTED mode so no user logons spoil your integrity) and export the slave PRODUCT_USER schema.
4) Copy the old-slave export dump files to the machine with the new, third database.
5) &quot;IMP&quot; the PRODUCT_USER schema on the new, third database. Since there should be no PK conflicts, your import should be fine and it will consume only the time it takes to import the 3.3GB slave PRODUCT_USER schema (approximately 9.1 hrs. @6MB per min. import rate.)

Note: steps 3&4 can run concurrently with step 2, above.

Let me know if this sounds more feasible for you or if you have additional questions.

Dave Hunt (Oracle DBA instructor for about 63 dog years.)
801-733-5333

 

[davidstraker]

Dave

That sounds great - many thanx.

The amalgamation project deadline is several months away, so I'll get back in touch as when & if I need further assistance.

Dave.