Home » RDBMS Server » Server Utilities » Expdp taking more time to dump the file (Oracle 11.2.0.4.0 , linux 2.6)
Expdp taking more time to dump the file [message #620928] Thu, 07 August 2014 06:05 Go to next message
ora_2014
Messages: 41
Registered: July 2014
Location: india
Member
Hi,

1.When I copy /paste export command in OS I could not see the entire command i copied in command line as below

usircp:F0001:/opt/app/oracle/admin/F0001/dpdump>ICS_IM logfile=exp_DEMO.log 
Export: Release 11.2.0.4.0 - Production on Thu Aug 7 12:40:41 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Here is the expdp command.

expdp dumpfile=exp_DEMO.dmp directory=DATA_PUMP_DIR schemas=NOKIA_KICS_IM logfile=exp_demo.log

Can you tell me what went wrong here ?

2. The size of the schema export that i am running is 25GB .It has running for along time say 1 hour and so far it has exported only one table

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA dumpfile=exp_demo.dmp directory=DATA_PUMP_DIR schemas=DEMO logfile=exp_demo.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18.30 GB
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/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
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/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "DEMO"."CONTENT"               4.774 GB  532949 rows


Can you tell me what could be the reason and why it is taking such long time to export ?

Regards,

[Updated on: Thu, 07 August 2014 06:11]

Report message to a moderator

Re: Expdp taking more time to dump the file [message #620941 is a reply to message #620928] Thu, 07 August 2014 07:13 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
You should not perform an export as SYS. See Metalink note 277237.1

HTH
-g
Re: Expdp taking more time to dump the file [message #620943 is a reply to message #620941] Thu, 07 August 2014 07:18 Go to previous messageGo to next message
ora_2014
Messages: 41
Registered: July 2014
Location: india
Member
Hi Gazzag,

So you mean to say export is tasking long time to run is because of using sys user ?

Regards
Re: Expdp taking more time to dump the file [message #620950 is a reply to message #620943] Thu, 07 August 2014 07:29 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
I'm saying that we don't know. Connect to the job and issue a "status" command. In your case:

$ impdp 'sys/<password> as sysdba' attach=SYS_EXPORT_SCHEMA_01

IMPORT> STATUS


Copy and paste the whole session here please.

HTH
-g
Re: Expdp taking more time to dump the file [message #620953 is a reply to message #620950] Thu, 07 August 2014 07:51 Go to previous messageGo to next message
ora_2014
Messages: 41
Registered: July 2014
Location: india
Member
Gazzag,

I killed that export session and reran it .It got completed in 5 mins. Thanks for your guidance.

Regards,

[Updated on: Thu, 07 August 2014 11:01]

Report message to a moderator

Re: Expdp taking more time to dump the file [message #621072 is a reply to message #620953] Fri, 08 August 2014 06:10 Go to previous messageGo to next message
ora_2014
Messages: 41
Registered: July 2014
Location: india
Member
When i run same schema export second time , it got ended as below.
. . exported "DEMO"."YYDC0B11560DED82D50C"          0 KB       0 rows
. . exported "DEMO"."YYF02911560DEDD856AC"          0 KB       0 rows
. . exported "DEMO"."YYF045D1560DED82D50C"          0 KB       0 rows
ORA-31694: master table "SYS"."SYS_EXPORT_SCHEMA_03" failed to load/unload
ORA-19502: write error on file "/opt/app/oracle/admin/VAKICSP1/dpdump/exp_demo.dmp", block number 429576 (block size=4096)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 429576
Additional information: -1

Job "SYS"."SYS_EXPORT_SCHEMA_03" stopped due to fatal error at Fri Aug 8 14:19:20 2014 elapsed 0 00:04:45

When i compared the old log file and new log file i found all objects were exported.
Would like to know why there was a error at completion of export ?

Here is the mount point details where export is run.
usircp:F0001:/opt/app/oracle/admin/F0001/dpdump>df -h /opt/app/oracle/admin/F0001/dpdump
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg02-opt_app.lv
                       99G   86G  7.6G  92% /opt/app

/opt/app/oracle/admin/F0001/dpdump -- This is the path where i run export.

Regards,

[Updated on: Fri, 08 August 2014 06:37]

Report message to a moderator

Re: Expdp taking more time to dump the file [message #621073 is a reply to message #621072] Fri, 08 August 2014 06:38 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Oracle still hadn't finished writing to the dumpfile because you ran out of disk space.
Re: Expdp taking more time to dump the file [message #621074 is a reply to message #621073] Fri, 08 August 2014 06:43 Go to previous messageGo to next message
ora_2014
Messages: 41
Registered: July 2014
Location: india
Member
Hi Gazzag,
Quote:
Oracle still hadn't finished writing to the dumpfile because you ran out of disk space.

Is that because of space issue export stopped ?

Regards,
Re: Expdp taking more time to dump the file [message #621075 is a reply to message #621074] Fri, 08 August 2014 06:48 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:

ORA-19502: write error on file "/opt/app/oracle/admin/VAKICSP1/dpdump/exp_demo.dmp", block number 429576 (block size=4096)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device

Re: Expdp taking more time to dump the file [message #621076 is a reply to message #621075] Fri, 08 August 2014 06:49 Go to previous messageGo to next message
ora_2014
Messages: 41
Registered: July 2014
Location: india
Member
Yes i got it.But would like to know why it has taken more time to export schema when run for first time .
Could you clarify it.

Regards,
Re: Expdp taking more time to dump the file [message #621078 is a reply to message #621076] Fri, 08 August 2014 07:23 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
As I said, we don't know. You didn't give us the chance to investigate. You killed the job and started a new one.

HTH
-g
Re: Expdp taking more time to dump the file [message #621080 is a reply to message #621078] Fri, 08 August 2014 07:25 Go to previous message
ora_2014
Messages: 41
Registered: July 2014
Location: india
Member
Thanks for your guidance though.
Previous Topic: TAB DELIMETER IS CONSIDERING TWO TABS AS ONE TAB
Next Topic: data pump error
Goto Forum:
  


Current Time: Sun Oct 25 06:18:21 CDT 2020