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.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

slightwv (䄆 Netminder)Connect With a Mentor Commented:
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.
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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.
sam15Author Commented:
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.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

slightwv (䄆 Netminder) Commented:
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.
sam15Author Commented:
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.
slightwv (䄆 Netminder) Commented:
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.
sam15Author Commented:
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.
sam15Author Commented:
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.
All Courses

From novice to tech pro — start learning today.