Home » RDBMS Server » Server Utilities » issue wit impdp (Oracle Server, 12.1.0.2.0 , Linux x86 64)
issue wit impdp [message #659781] Mon, 30 January 2017 09:47 Go to next message
samiraly
Messages: 57
Registered: June 2009
Member

i have a expdp export where schema ISBR_ADMIN exported , below is the logfile from the exprt process
;;; 
Export: Release 12.1.0.2.0 - Production on Wed Jan 25 12:10:23 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=ISBR_ADMIN.DMP LOGFILE=isbr_admin-expdp.log schemas=ISBR_ADMIN 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 151.3 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "ISBR_ADMIN"."ISBR_POWERCARD"               119.6 MB     723 rows
. . exported "ISBR_ADMIN"."ISBR_MFT"                     2.113 MB      54 rows
. . exported "ISBR_ADMIN"."ISBR_SERVICE_MANAGEMENT"      1.161 MB      17 rows
. . exported "ISBR_ADMIN"."ISBR_DPS"                     256.2 KB     199 rows
. . exported "ISBR_ADMIN"."ISBR_FILE_MANAGER"            226.9 KB     222 rows
. . exported "ISBR_ADMIN"."ISBR_KPI"                     144.2 KB       9 rows
. . exported "ISBR_ADMIN"."FILE_STATUS"                  638.2 KB   18942 rows
. . exported "ISBR_ADMIN"."DATAFILES"                    262.6 KB    3700 rows
. . exported "ISBR_ADMIN"."ISBR_HYPERCARE"               10.47 KB       2 rows
. . exported "ISBR_ADMIN"."ISBR_SERVICE_MGMT_TYPE"       5.804 KB       7 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /data/oracle/app/oracle/product/12.1.0/dbhome_1/rdbms/log/ISBR_ADMIN.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 25 12:12:10 2017 elapsed 0 00:01:46

- as you can see every thing was fine and normal
- when i tried to import schema isbr_admin again in another 12c DB no objects imported
impdp  \"/ as sysdba\"  directory =DATA_PUMP_DIR schemas=ISBR_ADMIN dumpfile=ISBR_ADMIN.DMP logfile=isbr-impdp.log

below is the listing from import dump file
;;;
Import: Release 12.1.0.2.0 - Production on Mon Jan 30 16:34:29 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR schemas=ISBR_ADMIN dumpfile=ISBR_ADMIN.DMP logfile=isbr-impdp.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ISBR_ADMIN" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Mon Jan 30 16:34:43 2017 elapsed 0 00:00:08

after that no objects imported schema isbr_admin still empty

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "ISBR_ADMIN"
SQL> select count(*) from user_objects;

  COUNT(*)
----------
	 0

SQL> 


please help , thanks in advance .
Re: issue wit impdp [message #659782 is a reply to message #659781] Mon, 30 January 2017 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ORA-31684: Object type USER:"ISBR_ADMIN" already exists

ISBR_ADMIN user should NOT exist prior to invoking impdp
Re: issue wit impdp [message #659783 is a reply to message #659782] Mon, 30 January 2017 09:54 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
You're also not meant to use sys for that.
Re: issue wit impdp [message #659785 is a reply to message #659782] Mon, 30 January 2017 10:18 Go to previous messageGo to next message
samiraly
Messages: 57
Registered: June 2009
Member

BlackSwan wrote on Mon, 30 January 2017 09:50
>ORA-31684: Object type USER:"ISBR_ADMIN" already exists

ISBR_ADMIN user should NOT exist prior to invoking impdp
yes , i dropped the schema and did the import again , and i have the same result , no objects imported.


Re: issue wit impdp [message #659786 is a reply to message #659785] Mon, 30 January 2017 10:24 Go to previous messageGo to next message
samiraly
Messages: 57
Registered: June 2009
Member

please check logfile after dropping schema and using system schema to import

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracle@oraupg:~/admin/oraupg/dpdump> impdp  system/manager  directory =DATA_PUMP_DIR schemas=ISBR_ADMIN dumpfile=ISBR_ADMIN.DMP logfile=isbr-impdp.log

Import: Release 12.1.0.2.0 - Production on Mon Jan 30 17:20:18 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=DATA_PUMP_DIR schemas=ISBR_ADMIN dumpfile=ISBR_ADMIN.DMP logfile=isbr-impdp.log 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Jan 30 17:20:23 2017 elapsed 0 00:00:04

oracle@oraupg:~/admin/oraupg/dpdump> 

select object_name from dba_objects where owner ='ISBR_ADMIN';
no rows selected

[Updated on: Mon, 30 January 2017 10:26]

Report message to a moderator

Re: issue wit impdp [message #659787 is a reply to message #659786] Mon, 30 January 2017 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You didn't import the file you exported.

Re: issue wit impdp [message #659788 is a reply to message #659782] Mon, 30 January 2017 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan wrote on Mon, 30 January 2017 16:50
>ORA-31684: Object type USER:"ISBR_ADMIN" already exists

ISBR_ADMIN user should NOT exist prior to invoking impdp
It is a not fatal error maybe expected.

Re: issue wit impdp [message #659790 is a reply to message #659788] Mon, 30 January 2017 15:39 Go to previous messageGo to next message
samiraly
Messages: 57
Registered: June 2009
Member

Michel Cadot wrote on Mon, 30 January 2017 11:34
BlackSwan wrote on Mon, 30 January 2017 16:50
>ORA-31684: Object type USER:"ISBR_ADMIN" already exists

ISBR_ADMIN user should NOT exist prior to invoking impdp
It is a not fatal error maybe expected.

i tried again without the schema exist and i have the same result , no objects imported

[Updated on: Mon, 30 January 2017 15:40]

Report message to a moderator

Re: issue wit impdp [message #659791 is a reply to message #659790] Mon, 30 January 2017 15:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> impdp system/manager directory=DATA_PUMP_DIR schemas=ISBR_ADMIN dumpfile=ISBR_ADMIN.DMP logfile=isbr-impdp.log

what is content of isbr-impdp.log file?
cat isbr-impdp.log

what is content of capture.lis after issuing commands below

impdp system/manager directory=DATA_PUMP_DIR schemas=ISBR_ADMIN dumpfile=ISBR_ADMIN.DMP sqlfile=capture.lis
cat capture.lis
Re: issue wit impdp [message #659794 is a reply to message #659790] Tue, 31 January 2017 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 30 January 2017 18:32

You didn't import the file you exported.
Re: issue wit impdp [message #659820 is a reply to message #659794] Tue, 31 January 2017 03:30 Go to previous messageGo to next message
samiraly
Messages: 57
Registered: June 2009
Member


Hello

i will check and will keep you informed with results

BR
Samir Aly
Re: issue wit impdp [message #659840 is a reply to message #659820] Wed, 01 February 2017 00:49 Go to previous messageGo to next message
samiraly
Messages: 57
Registered: June 2009
Member

Hello Thank you for your support i managed to import the dump successfully , something went wrong during transferring the dump and unziping it , dump file was not a good one
every thing is ok now , Thanks alot for your support.
Re: issue wit impdp [message #659865 is a reply to message #659840] Wed, 01 February 2017 08:08 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
You should have mentioned that you were "transferring" the file and that it was zipped, as the first thing people would have suggested was that it was not ftp'd in binary format or the zip file was no good.

However, I fail to see how the file could have been no good if you got a message that said import successfully completed.

[Updated on: Wed, 01 February 2017 08:11]

Report message to a moderator

Previous Topic: SQL Loader double quotes in clob data (3 threads from 2 sub-forums merged by bb)
Next Topic: datapump export import
Goto Forum:
  


Current Time: Thu Mar 28 06:08:35 CDT 2024