If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

merging 2 oracle databases

Hi All,

We have 2 production databases (on a single Sun box), one is 8.1.7.4 and the other is 9.2.0.5. We want to merge these databases so that we have only 1 database. We also plan to upgrade to the latest release 10gR2. When we do the database upgrade,
we will be migrating from solaris OS to SLES 9 on HP hardware.

The 8.1.7.4 database connects to another 8.1.7.4 (this is not our database and will not be upgraded anytime soon) database through a dblink for loading data every night.

Kindly let me know how I can merge these databases. And if I should do it before upgrade to 10gR2 or after the upgrade. Also, I would like to know if dblinks work between 10gR2 db
and 8.1.7.4 db. This is essential for our nightly load processes.

We have 2 production databases (on a single Sun box), one is 8.1.7.4 and the other is 9.2.0.5. We want to merge these databases so that we have only 1 database. We also plan to upgrade to the latest release 10gR2. When we do the database upgrade,
we will be migrating from solaris OS to SLES 9 on HP hardware.

The 8.1.7.4 database connects to another 8.1.7.4 (this is not our database and will not be upgraded anytime soon) database through a dblink for loading data every night.

Kindly let me know how I can merge these databases. And if I should do it before upgrade to 10gR2 or after the upgrade. Also, I would like to know if dblinks work between 10gR2 db
and 8.1.7.4 db. This is essential for our nightly load processes.

Any help is greatly appreciated and thanks in advance.

Regards,
Reddy.

The the two dbs are really big (100G or more), I'd upgrade the 8.1.7.4 db to 9iR2 and use transportable tablespaces to "plug" them into the 9.2 db.

If the 8.1.7.4 is small or you have storage issues you want to correct, I'd exp from 8.1.7.4 and imp to 9iR2.

Then I'd upgrade to 10gR2 on Solaris and use the cross platform capabilites of 10gR2 to migrate from Solaris to Linux.

Can't help you if 10gR2 links talk to 8.1.7.4 dbs, but somewhere in the back of my head says it won't work because of the client version.

Thanks for the reply! Some more information about the databases we have. The 8.1.7.4 db is of almost 30-32GB in size, and the 9.2.0.5 db is 50GB. The 2 databases are completely different, I mean they have different schemas, tables, data, etc.

The main concern is that these dbs should be always running as they have nightly load processes, and our Sun hardware is pretty old and not reliable. We had problems with the hardware while applying OS patches recently, and we did not complete that task. The servers are slow, and we dont have much storage space also.

So according to you suggestion, I could upgrade my 8174 db to 9205 and then upgrade the new database to 10gR2. Again I need to confirm that dblink work between 8.1.7.4 database and 9.2.0.5.

I need to mention one more thing. These databases are mainly used for Discoverer reporting. So we have an application server (9.0.4) and Discoverer EULs for these 2 databases. These also need to be upgraded to 10g in the near future. What will happen to the Discoverer End User Layer (Business Areas, Workbooks) of the 8.1.7.4 database when I merge it with the 9.2.0.5 database. Any pointers?

Thanks for the reply! Some more information about the databases we have. The 8.1.7.4 db is of almost 30-32GB in size, and the 9.2.0.5 db is 50GB. The 2 databases are completely different, I mean they have different schemas, tables, data, etc.

Only 32G, I'd just exp/imp and be done with it.

The main concern is that these dbs should be always running as they have nightly load processes, and our Sun hardware is pretty old and not reliable. We had problems with the hardware while applying OS patches recently, and we did not complete that task. The servers are slow, and we dont have much storage space also.

Always, or you can afford some downtime. If they always have to be available, then you won't even be able to upgrade to 9.2, so it is pointless.

So according to you suggestion, I could upgrade my 8174 db to 9205 and then upgrade the new database to 10gR2.

upgrade your 8.1.7.4 to 9.2.0.5, merge, THEN upgrade.

Again I need to confirm that dblink work between 8.1.7.4 database and 9.2.0.5.

It's worked for me for years.

I need to mention one more thing. These databases are mainly used for Discoverer reporting. So we have an application server (9.0.4) and Discoverer EULs for these 2 databases. These also need to be upgraded to 10g in the near future. What will happen to the Discoverer End User Layer (Business Areas, Workbooks) of the 8.1.7.4 database when I merge it with the 9.2.0.5 database. Any pointers?

Not really sure, but data is data. Point your app server to the correct database and it should work.

I thought about your suggestion of merging 8.1.7.4 db (using import/export) with 9.2.0.5, then upgrade to 10gR2 on Solaris itself, and then finally migrate to SLES. However, I am looking at the following problems:

1. Out Sun server is almost 4-5 years old, it does not have much space. But I can try to create some space if I choose to take this path. Then what I can/should do is merge the 8174 db with 9205 db, but would want keep the 8174 db running in case something goes wrong. Please correct me if I am wrong.

2. I would like to test this process, but dont have development instances. Since both the databases involved are production, I want to be very careful before proceeding and be very confident that this works.

3. Other major problem is server resources. I doubt if out db server can handle the new load. Even now I get aiowait timed out error on one of the databases once in a while during have processing.

Be careful with export/import. I recall a bug in one of the 8 versions where not all data/structures were fully exported. I'm sorry I don't remember the details of the bug.

Here's a different view point.

1. Create your linux server, installing 10gR2.
2. Create only a single database.
3. Create schemas to hold data from the two seperate databases.
4. Export structures only from production (no data).
5. Import structures into your Linux 10g database. (for each schema).
6. Then script the data load from your linux system into your 10gR2 instance. Using Direct path insert. This will be a lot quicker than a full 32gig + 50g data import.
7. Perform a backup.

With this you could pick a point in time to copy rows to the new db, backfill using the timestamps, then switch your client/app to the new db with very little downtime. But you've got GOBS of time. Lucky...

The nice thing about your scenario is that you can do a test migration on your new hardware.

I'd consider pre-building your tables before you import. It's a good time to look at compression (requires a rebuild or a direct path load to actually compress .. I believe), Index Orgainzed Tables, uniform extents, etc.