Posted on 2012-09-20
Last Modified: 2012-11-11
Do you have any rough ideas on tasks and time required to move a 9i database from one machine to a new machine that will have 11gR2 server.

There new 11g server will run on a RHEL virtual machine.

Also, the source database is currently in western european character set and the 11g target database will be in Unicode or UTF8 format.

The database has a couple of pl/sql applications so these would need to be tested too..

There is no RAC involed. There are a couple of database with a couple of instances.
Question by:sam15
    LVL 34

    Assisted Solution

    I would say it should take you about a day to get the 11g server configured and up and running.

    To move the data, I assume you would use exp/imp, and the amount of time that takes is going to be based on the amount of data as well as many other factors.  The best way to get an approximate timing is to do a test run.

    Author Comment

    The exp takes about 30 minuts for 10 GB database. It is not a big issue.

    I think most time here is testing the applications and running and reviewing the CSSCAN reports for character set converion since i am going to unicode.

    There is also some setup for oracle HTTP server. This is installed with database.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Imports can take significantly longer since it has to build indexes and handle all the constraints.

    Depending on how you prep the new database there is also the truncate/delete/create/??? that you need to perform.

    There is also the time to transfer the file from point-A to point-B.

    As far as testing:  I agree this will likely take the most time especially when changing character sets since you have to make sure you don't drop data during the conversion.

    I agree with the first post about test runs.  Take a few smaller tables that you think might be issues and test with those.  Once you are confident the data conversions work properly then the biggest effort is testing the applications.

    Author Comment

    I think the CSSCAN report should be the best indicator if there will be any data truncation or not.

    How else can you test every single table and field data to check it manually? You have to compare new data in 11g to your old data in 9i to confirm if they match or not. I doubt anyone does that.

    It seems this whole effor can be done in 2-3 weeks.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    The level of checking is directly related to and policies in place, compliance standards, etc...

    For example:  Are there things like federal regulations that you must comply with?

    If three are things like this in place I'm not sure I would look at one row and if it is OK assume the millions of others are fine.

    Author Comment

    no, there is not any regulations.

    But if the CSSCAN reports were OK do you still run tests to validate data. Can you explain what kind of tests you would run to make sure data is correct in the new target DB.

    The two databases (source and target) are in different cities and cannot talk to each other.
    LVL 76

    Accepted Solution

    I've never run CSSCAN.

    When I have done data migrations in the past I typically write code to check things for me.  The MINUS operator and linked databases work very nicely.

    Since your databases cannot talk you can always spool out data and copy them to a common location and do a simple diff on the files.

    If there is a TON of data involved, you can look at selecting the PK fields and generating a checksum on the rest of the columns.  Spool that out then diff the files.

    If possible:  I check ALL rows.

    You should go as far as you feel confident that everything is OK.

    Author Comment

    may i ask why you dont use CSSCAN. That is oracle standard check tool when you migrate from character set to another.

    I like your idea about doing a checksum on data or tables. Is there a link or article that shows code on how you implement that using oracle functions.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    One of the new features of a version 7.0 or later virtual machine, supported in VMware vSphere 4.1, 5.0 or the VMware vSphere Hypervisor ESXi 4.1, ESXi 5.0 often overlooked by VMware Administrators is the ability to add and connect USB devices conne…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now