Home » RDBMS Server » Server Utilities » Import from 11g to 10g results in strange errors (Linux, Oracle 11g to 10g)
Import from 11g to 10g results in strange errors [message #586980] Wed, 12 June 2013 03:59 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
I wanted to migrate two schemas from:

Source Database machine OS: OEL 5.9 , 64-bit
Source Database version/addition: 11.2.0.1.0 SE

to:

Target Database machine OS: Red Hat Enterprise Linux Server release 5.4 , 64-bit
Target Database version/addition: 10.2.0.4.0 EE

Therefore, I did an export using Oracle Data Pump:

;;; 
Export: Release 11.2.0.1.0 - Production on Tue Jun 11 13:13:28 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_04":  system/******** directory=DATA_PUMP_DIR dumpfile=exp_PRODSCHEMAS_20130611.dmp schemas=PRD_100,SHR_100 logfile=exp_PRODSCHEMAS_20130611.log VERSION=10.2.0.4.0 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.026 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
****
****
. . exported "PRD_100"."X1_APP_OB"                      0 KB       0 rows
. . exported "PRD_100"."X2_APP_OB"                   0 KB       0 rows
. . exported "PRD_100"."X3_APP_OB"      0 KB       0 rows
. . exported "PRD_100"."X4_APP_OB"           0 KB       0 rows
. . exported "PRD_100"."X5_APP_OB"      0 KB       0 rows
. . exported "PRD_100"."X6_APP_OB"      0 KB       0 rows
. . exported "PRD_100"."X7_APP_OB"                             0 KB       0 rows
. . exported "PRD_100"."X8_APP_OB"            0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_04 is:
  /u01/app/oracle/admin/*****/dpdump/exp_PRODSCHEMAS_20130611.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully completed at 13:15:18


and then the import, with user SYSTEM:

;;; 
Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 11 June, 2013 16:14:04

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=EXP_DIR dumpfile=exp_PRODSCHEMAS_20130611.dmp remap_schema=PRD_100:PRD_LIVE,SHR_100:SHR_LIVE transform=segment_attributes:n,oid:n logfile=imp_PRODSCHEMAS_20130611 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SHR_LIVE" already exists
ORA-31684: Object type USER:"PRD_LIVE" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
****
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
****
. . imported "SHR_LIVE"."TAB1"    3.773 MB   65278 rows
. . imported "SHR_LIVE"."TAB2"    3.772 MB   65257 rows
. . imported "SHR_LIVE"."TAB3"    3.776 MB   65376 rows
ORA-31693: Table data object "PRD_LIVE"."A_CHECK_QTABLE" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
ORA-39779: type "PRD_LIVE"."A_CHECK_QTYPE" not found or conversion to latest version is not possible
***
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/VIEW/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
ORA-39083: Object type PROCACT_INSTANCE failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
BEGIN 
DECLARE   dbvers varchar2(20);   compat varchar2(20); BEGIN   execute immediate 'begin sys.DBMS_UTILITY.DB_VERSION(:1, :2); end;' using out dbvers, out compat;  IF (sys.dbms_logrep_exp.version_var2_2_number(dbvers) >=       sys.dbms_logrep_exp.version_var2_2_number('11.2')) THEN    execute immediate 'begin SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_DEQUEUELOG_TABLE(''AQ$_SM_CHECK_QTABLE
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 33 error(s) at 16:19:55



I am most worried about this error:

ORA-39083: Object type PROCACT_INSTANCE failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
BEGIN
DECLARE dbvers varchar2(20); compat varchar2(20); BEGIN execute immediate 'begin sys.DBMS_UTILITY.DB_VERSION(:1, :2); end;' using out dbvers, out compat; IF (sys.dbms_logrep_exp.version_var2_2_number(dbvers) >= sys.dbms_logrep_exp.version_var2_2_number('11.2')) THEN execute immediate 'begin SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_DEQUEUELOG_TABLE(''AQ$_SM_CHECK_QTABLE


questions:
What does it mean? does it have to do with the VERSION parameter value?
What can I do to solve it? what can I check to investigate?

Thanks in advance for your advice,
Best Regards,
Andrey

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #586987 is a reply to message #586980] Wed, 12 June 2013 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First, you have to export with the LOWEST version and import with the TARGET version.

Regards
Michel

[Updated on: Wed, 12 June 2013 04:41]

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #586988 is a reply to message #586987] Wed, 12 June 2013 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
you have to export with the LOWEST version


You can use the VERSION parameter of expdp.

Regards
Michel

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #586990 is a reply to message #586988] Wed, 12 June 2013 05:06 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Wed, 12 June 2013 12:43
Quote:
you have to export with the LOWEST version


You can use the VERSION parameter of expdp.

Regards
Michel


I did, look at my export output.

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #586992 is a reply to message #586990] Wed, 12 June 2013 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So it is not sufficient, use my first option.

Regards
Michel

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #586994 is a reply to message #586992] Wed, 12 June 2013 06:16 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Michel Cadot wrote on Wed, 12 June 2013 14:08
So it is not sufficient, use my first option.

Regards
Michel


Michel,

I don't understand, can you please elaborate?

My source is 11g,
My target is 10g,

When I exported I used the oldest version of the two as the value of VERSION parameter,
as: 
VERSION=10.2.0.4.0


So , where did I get it wrong?

Thanks in advance,
Andrey

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #586995 is a reply to message #586994] Wed, 12 June 2013 06:29 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
Do the export with the 10.2.0.4 version of expdp.

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #586998 is a reply to message #586995] Wed, 12 June 2013 06:37 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
gazzag wrote on Wed, 12 June 2013 14:29
Do the export with the 10.2.0.4 version of expdp.


Thanks gazzag for the reply.

How do I do that? Do I need to install a 10.2.0.4.0 full database client on my source Linux machine hosting a 11.2.0 database???

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #587009 is a reply to message #586998] Wed, 12 June 2013 07:12 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
Use the NETWORK_LINK parameter and export from the 10.2.0.4 database:

http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm#sthref142

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #587155 is a reply to message #587009] Wed, 12 June 2013 14:28 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Ok, Here's what I tried:


1. Created a DB LINK from the 10.2.0.4.0 target database to the 11g source database database:

C:\Documents and Settings\oracle>sqlplus sys@10g_connstring as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 13 09:14:04 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production

SQL> define _editor = notepad
SQL>
SQL>
SQL> CREATE DATABASE LINK 11g_src_link CONNECT TO system IDENTIFIED BY ***** USING 'new_beta_db';

Database link created.

SQL>
SQL>
SQL> quit
Disconnected from Oracle Database 10g Release 10.2.0.4.0 - Production


2. Used this DB LINK to do the import of data residing on 11g database with the 10g database's Data Pump utility:

C:\Documents and Settings\oracle>expdp 'system@10g_connstring' directory=DATA_PUMP_DIR dumpfile=exp_PRODSCHEMAS_20130613.dmp NETWORK_L
INK=11g_src_link schemas=PRD_100,SHR_100 logfile=exp_PRODSCHEMAS_20130613.log VERSION='10.2.0.4.0'

Export: Release 10.2.0.4.0 - Production on Thursday, 13 June, 2013 9:22:30

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  'system/********@10g_connstring' directory=DATA_PUMP_DIR dumpfile=exp_PRODSCHEMAS_20130613.
dmp NETWORK_LINK=11g_src_lnk schemas=PRD_100,SHR_100 logfile=exp_PRODSCHEMAS_20130613.log VERSION='10.2.0.4.0'
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.MAIN [INSERT INTO sys.ku$_list_filter_temp@11g_src_link  SELECT process_order, dup
licate, object_name, base_process_order FROM  "SYSTEM"."SYS_EXPORT_SCHEMA_02"  WHERE process_order = :1]
ORA-00947: not enough values

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6345

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
B88568AC     15032  package body SYS.KUPW$WORKER
B88568AC      6372  package body SYS.KUPW$WORKER
B88568AC      1357  package body SYS.KUPW$WORKER
B8854694         2  anonymous block

Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at 09:22:59


C:\Documents and Settings\oracle>


So, I figured I may be doing something wrong with explicitly specifying the 10.2.0.4.0 version,
When I am already using a database and Data Pump utility of that very version to export,
So I tried it without the VERSION parameter using DB LINK and apart from tables with LONG datatype(I don't want to concentrate on those at this point) columns - export worked:


C:\Documents and Settings\oracle>expdp 'system@10g_connstring' directory=DATA_PUMP_DIR dumpfile=exp_PRODSCHEMAS_20130613.dmp NETWORK_L
INK=11g_src_lnk schemas=PRD_100,SHR_100 logfile=exp_PRODSCHEMAS_20130613.log

Export: Release 10.2.0.4.0 - Production on Thursday, 13 June, 2013 9:45:15

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_03":  'system/********@10g_connstring' directory=DATA_PUMP_DIR dumpfile=exp_PRODSCHEMAS_20130613.
dmp NETWORK_LINK=11g_src_lnk schemas=PRD_100,SHR_100 logfile=exp_PRODSCHEMAS_20130613.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.032 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
******
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
******
ORA-31679: Table data object "PRD_100"."SOB1" has long columns, and longs can not be loaded/unloaded using a
 network link
******
 . . exported "PRD_100"."XOB1"                             0 KB       0 rows
. . exported "PRD_100"."XOB2"            0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:
  C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\LOG\EXP_PRODSCHEMAS_20130613.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_03" completed with 5 error(s) at 09:51:24


C:\Documents and Settings\oracle>



So then I tried to impdp the dump file to the 10g database, however I got the same error:


C:\Documents and Settings\oracle>impdp 'system/******' directory=DATA_PUMP_DIR dumpfile=EXP_PRODSCHEMAS_20130613.DMP remap_schema=PRD_100:PRD_LIVE,SHR_100:SHR_LIVE transform=segment_attributes:n,oid:n logfile=IMP_PRODSCHEMAS_20130613.log

Import: Release 10.2.0.4.0 - Production on Thursday, 13 June, 2013 10:15:11

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  'system/********' directory=DATA_PUMP_DIR dumpfile=EXP_PRODSCHEMAS_20130613.DMP remap_schem
a=PRD_100:PRD_LIVE,SHR_100:SHR_LIVE transform=segment_attributes:n,oid:n logfile=IMP_PRODSCHEMAS_20130613.log
Processing object type SCHEMA_EXPORT/USER
*****
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
ORA-39083: Object type PROCACT_INSTANCE failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
BEGIN
DECLARE   dbvers varchar2(20);   compat varchar2(20); BEGIN   execute immediate 'begin sys.DBMS_UTILITY.DB_VERSION(:1, :2); end;' us
ing out dbvers, out compat;  IF (sys.dbms_logrep_exp.version_var2_2_number(dbvers) >=       sys.dbms_logrep_exp.version_var2_2_numbe
r('11.2')) THEN    execute immediate 'begin SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_DEQUEUELOG_TABLE(''AQ$_SM_CHECK_QTABLE
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 29 error(s) at 10:37:52


C:\Documents and Settings\oracle>



So I'm still stuck... I tried same thing with just using the Data Pump of 10g to export directly from the 11g database and in the import got the same error.

I'm starting to think it is not the export at all, but the import that is causing the problem.
Then thing that catches my attention now is the 
execute immediate 'begin SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_DEQUEUELOG_TABLE(''

Part that makes me suspicious that there is some kind of problem importing queue objects or a special handling for those...

I am not sure though what should I do or check, I'm a little lost here...Does anybody have an idea?
(And a great thank you for the help and efforts so far)

Regards,
Andrey

[Updated on: Thu, 13 June 2013 04:46]

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #587210 is a reply to message #587155] Thu, 13 June 2013 04:08 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
Can you patch your 11.2.0.1 database to 11.2.0.3?

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #587222 is a reply to message #587210] Thu, 13 June 2013 04:53 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
I will but it will take a couple of weeks because it is a production database, and other organizational limitations.

Thank you all for the help so far, please post if you have other ideas I can try in the meantime.

Regards,
Andrey

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #587224 is a reply to message #587222] Thu, 13 June 2013 05:00 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
Sorry, I was getting confused...

Metalink article 1334938.1 recommends patching the 10.2.0.4 instance to 10.2.0.5. Will that be possible?

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #587233 is a reply to message #587224] Thu, 13 June 2013 06:06 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
gazzag , The Metalink is talking about the error I received using the VERSION parameter when exporting from a 10g Data Pump client using a 11g DB LINK.
When I didn't specify the VERSION parameter - export was successful. So I tend to believe the export is not my problem but the import itself.

Report message to a moderator

Re: Import from 11g to 10g results in strange errors [message #587345 is a reply to message #587233] Fri, 14 June 2013 03:48 Go to previous message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
Andrey_R wrote on Thu, 13 June 2013 11:06
So I tend to believe the export is not my problem but the import itself.


I'd log a SR, if I were you.

Report message to a moderator

Previous Topic: SQLLDR Squence Number
Next Topic: want to perform impdp command for specific function
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Feb 07 01:00:17 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.