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.

Any help is greatly appreciated and thanks in advance.

Regards,
Reddy.

Quote:

---

Originally Posted by reddybc

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.

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.

Hi Jeff,

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 in advance,
Reddy.

Quote:

---

Originally Posted by reddybc

Hi Jeff,

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

That was quick! Thanks again for the advise.....will do some more research, and post again.......

Hello Jeff,

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.

Please let me know if you have any suggestions.

Thanks a lot.....Reddy.

Well, that's simple then. Export your 8.1.7.4 db and import on your Linux box. It's only 30G, so it shouldn't be too bad. Then once you get that running the way you want you can do the same with your 9.2 db. This way you have an instant rollback plan, just bring the old db up.

Not sure about the 10g->8.1.7.4 db link, though. You'd have to setup the 10g db and create the link and test to see if it works. If it doesn't, you just install 9.2.0.7 on Linux and you know it will work...

Ya...even I am thinking along those lines.....just to check that I understood you right:

1. first upgrade both 8174 and 9205 databases to 10gR2 on SLES using imp/exp

2. all the while keep running the old databases in case there is any problem with the dblink (10gR2 --> 8.1.7.4)

3. test the new databases

4. then merge both the databases which would then be on the same version 10gR2.

I hope the above steps are correct....please correct if something is wrong.....thanks a lot for your suggestions and time.......Reddy.

How much downtime can you actually tolerate?

Do you have any long, long/raw, clob or blob columns in your data?

Is your data timestamped?

Ken

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.

Ken,

>How much downtime can you actually tolerate?
We can actually bring the databases down for say 30-35 hours on the weekend if required.

Do you have any long, long/raw, clob or blob columns in your data?
>NO...its just plain data.....but have huge tables (4-5 tables) containing almost 10 million records each....

Is your data timestamped?
>What do you mean by timestamped?

ixion...thanks for your suggestions....will think over them and get back to you....

Thanks,
Reddy.

>timestamped...
Does your data have date/time columns like created_on and/or last_mod_on?

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.

So many things to consider. 30 hours ...

Ken
Bow to your Sensei...

Quote:

---

Originally Posted by reddybc

ixion...thanks for your suggestions....will think over them and get back to you....

---

PM Sent.