Home » RDBMS Server » Server Utilities » Error during import using Impdp (Solaris 5.10, Oracle 10.2.0.2)
Error during import using Impdp [message #297304] Thu, 31 January 2008 01:26 Go to next message
kamragulshan
Messages: 66
Registered: May 2005
Location: Delhi
Member
Hi Experts,

I am trying to import the schema using impdp but it throws the below error:


ORA-31694: master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-39774: parse of metadata stream failed with the following error:
 LPX-00217: invalid character 0 (U+0000
ORA-39014: One or more workers have prematurely exited.


After troubleshooting, i find that the cause of the above error is because of difference in NLS_NCHAR_CHARACTERSET.

The values of NLS_NCHAR_CHARACTERSET of both databases:

Export database:  UTF8
Import database:  AL16UTF16


Please confirm that is this the reason for the import failure.

Now, Is there any possibility to change the existing characterset from AL16UTF16 to UTF8?

I got the below solution from internet:

Execute the following commands in sqlplus connected as "/ AS SYSDBA":

       SHUTDOWN IMMEDIATE;
       STARTUP MOUNT;  
       ALTER SYSTEM ENABLE RESTRICTED SESSION; 
       ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
       ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
       ALTER DATABASE OPEN; 
       ALTER DATABASE NATIONAL CHARACTER SET UTF8;
       SHUTDOWN IMMEDIATE;


Is this the correct way to change the charactset? What would be the impact of changing the characterset and is there any data loss? Or Do we need to recreate the database again, as it would be quite difficult as other schemas are exist and users are accessing the same.

Please confirm my analysis and suggest the best way to solve the problem.

Thanks in Advance
Gulshan
Re: Error during import using Impdp [message #297306 is a reply to message #297304] Thu, 31 January 2008 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First did you transfer the file in binary mode?

Regards
Michel
Re: Error during import using Impdp [message #297339 is a reply to message #297306] Thu, 31 January 2008 03:16 Go to previous messageGo to next message
kamragulshan
Messages: 66
Registered: May 2005
Location: Delhi
Member
yes, i transferred the file using ftp in binary mode.
Re: Error during import using Impdp [message #297350 is a reply to message #297339] Thu, 31 January 2008 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Noew you can't change database character set this way.
You have to recreate the database.

Regards
Michel
Re: Error during import using Impdp [message #297517 is a reply to message #297304] Fri, 01 February 2008 03:58 Go to previous messageGo to next message
kamragulshan
Messages: 66
Registered: May 2005
Location: Delhi
Member
Hi Michel,

I have created another database with the required characterset but getting the same error.

Please suggest something to solve the error.

Thanks
Gulshan
Re: Error during import using Impdp [message #297525 is a reply to message #297517] Fri, 01 February 2008 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The error does not come from the character set.

Regards
Michel
Re: Error during import using Impdp [message #297542 is a reply to message #297304] Fri, 01 February 2008 05:11 Go to previous messageGo to next message
kamragulshan
Messages: 66
Registered: May 2005
Location: Delhi
Member
Yes Michel, the problem was not because of different characterset.

I have tried a lot on internet but did not get any clue.
Is there any other cause of this error.

How can i come out of this error.

Please suggest.

Thanks
Gulshan
Re: Error during import using Impdp [message #297554 is a reply to message #297542] Fri, 01 February 2008 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think it comes from a corrupted dump file.

Regards
Michel
Re: Error during import using Impdp [message #298465 is a reply to message #297542] Wed, 06 February 2008 02:59 Go to previous messageGo to next message
fouzia_2008
Messages: 6
Registered: February 2008
Location: Pakistan
Junior Member

Well
check this ,may be you get your solution.
http://ORA-31694.ora-code.com
just change the error number and check for your all errors.
Hope your problem solve soon.
Regards
Fouzia
Re: Error during import using Impdp [message #298635 is a reply to message #297304] Wed, 06 February 2008 22:29 Go to previous message
kamragulshan
Messages: 66
Registered: May 2005
Location: Delhi
Member
Hi Fouzia,

I have checked all the error but did not find any clue and the way to resolve the error.

Could you please provide you valuable input on same.

Thanks
Gulshan
Previous Topic: How to increase perfromance while loading data into IOT's?
Next Topic: to_date
Goto Forum:
  


Current Time: Sat Jun 15 22:17:06 CDT 2024