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

Thread: merging 2 oracle databases

Hybrid View

  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
    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.

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

  9. #9
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by reddybc
    ixion...thanks for your suggestions....will think over them and get back to you....
    PM Sent.

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

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