?
Solved

Oracle Migration

Posted on 2011-10-28
18
Medium Priority
?
317 Views
Last Modified: 2012-05-12
Hello guys,

I need to migrate my Oracle database.  I have a server running Oracle 8i and Windows 2000 Server.  I'm install Windows 2008 Server and Oracle 11g.  I have dump fles.  I wanna now if I could import database from 8i to 11g using imp command.

Thanks.
0
Comment
Question by:anovaes
  • 7
  • 7
  • 4
18 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044215
I'm not sure a gap that big can be done but what is the question here?

Try the 11g import command and see what happens.

If you get an error post it here along with the exp and imp commands used.
0
 

Author Comment

by:anovaes
ID: 37044258
I got this error
ORA-12899: value too large for column "FPW"."CFGFUNCAO"."CUVALOR" (actual: 320, maximum: 250)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044267
Are you trying a FULL import or just the schemas you need?

I'm not familiar with the FPW schema so I'm thinking it is an app specific one.

Did you pre-create the tables in the new database?

Are the database charactersets the same?  The old database might be multi-byte.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 7

Expert Comment

by:Jacobfw
ID: 37044268
Here is the compatibility list for export/import to 11g

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/expimp.htm#i262247
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 37044273
Value too large for column is related to Character sets.
What are the character set of the two databases?
0
 

Author Comment

by:anovaes
ID: 37044329
Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044346
What is the main characterset of the export db?
0
 

Author Comment

by:anovaes
ID: 37044362
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)
 
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044373
>>NCHAR character set

What is the primary one not the NCHAR one?
0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 800 total points
ID: 37044387
We just had a similiar problem with exp/imp with dissimiliar character sets
Had to do with the original column set as varchar2 but multi-byte

We had to pre-create the table and change the column as follows:
ALTER TABLE table_name MODIFY (column_name varchar2(1024 CHAR));

In our case we determined to skip the missing columns that did not import (they were not important), but then our application did not work either because it was attempting to place a larger amount of data then the column was defined.

Hope this helps
0
 

Author Comment

by:anovaes
ID: 37044504
For example
ORA-12899: value too large for column "FPW"."CFGFUNCAO"."CUVALOR" (actual: 320, maximum: 250)
I just increased that specific table column from 320 characters to 500 characters but I don't know if I can do that.  I didn't get errors anymore.  Can I do that?
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 37044530
Can you set it back to 250 now using this command?
ALTER TABLE table_name MODIFY (column_name varchar2(250 CHAR));

It would totally depend on the application if the larger column size will be ok and not cause other problems.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044549
>>Can I do that?

I wouldn't.  If you are changing character sets between the databases, the data might not have been converted properly.  If not, you have corrupted/lost data.

0
 

Author Comment

by:anovaes
ID: 37045619
I can't set it back to 250 now using this command.
ALTER TABLE table_name MODIFY (column_name varchar2(250 CHAR));
0
 

Author Comment

by:anovaes
ID: 37045648
I got error when I try to set it back to 250 using:
ALTER TABLE table_name MODIFY (column_name varchar2(250 CHAR));

01441 - cannot decrease column length because some value is too big.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37045699
If you have a multi-byte character set in the old database it is likely for a reason.
You should have a multi-byte database for your new one.

Check out the docs:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/ch11charsetmig.htm#i1006004

Replacement Characters that Result from Using the Export and Import Utilities
 
The Export and Import utilities can convert character sets from the original database character set to the new database character set. However, character set conversions can sometimes cause data loss or data corruption. For example, if you are migrating from character set A to character set B, then the destination character set B should be a superset of character set A. The destination character, B, is a superset if it contains all the characters defined in character set A. Characters that are not available in character set B are converted to replacement characters, which are often specified as ? or ¿ or as a character that is related to the unavailable character. For example, ä (a with an umlaut) can be replaced by a. Replacement characters are defined by the target character set.
0
 

Author Comment

by:anovaes
ID: 37056338
What should I do?
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 total points
ID: 37056896
Recreate the 'new' database to use the same character set as the original.
or
Determine what data will be lost and decide if that is acceptable.

You can 'scan' the database several different ways:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/ch12scanner.htm

check out:
The Database Character Set Scanner
 
The Database Character Set Scanner assesses the feasibility of migrating an Oracle database to a new database character set. The Database Character Set Scanner checks all character data in the database and tests for the effects and problems of changing the character set encoding. A summary report is generated at the end of the scan that shows the scope of work required to convert the database to a new character set.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Viewers will learn the basics of making and using Impulse Kits in Ableton Live. Load new Impulse into an empty MIDI track: Fill the 8 empty sample slots with drum samples: Adjust parameters to tailor each sound as desired: Proceed to create be…
Viewers will learn key ranges in Sampler to make their sampled instruments sound more realistic Gather samples of various notes and drag them to Key Range panel: Set proper root key for each sample: Select all the samples with Command-A (or Ctrl…
Suggested Courses

862 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