merging 2 oracle databases
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: merging 2 oracle databases

  1. #1
    Join Date
    Jul 2006
    Posts
    6

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2006
    Posts
    6
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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.

    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jul 2006
    Posts
    6
    That was quick! Thanks again for the advise.....will do some more research, and post again.......

  6. #6
    Join Date
    Jul 2006
    Posts
    6
    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.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Jul 2006
    Posts
    6
    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.

  9. #9
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  10. #10
    Join Date
    Jun 2006
    Posts
    259
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width