This forum is currently locked. You can't register or post questions at this time. (read more)

data pump export is very slow. For 50GB export has taken mor

All posts relating to Oracle database administration.

Moderator: Tim...

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

data pump export is very slow. For 50GB export has taken mor

Postby royalyogi » Wed May 15, 2013 4:13 pm

data pump export is very slow. For 50GB export has taken more than 24Hrs with one below error:

Database Version:11.2.0.2.0
OS: Windows server 2008 r2
Increased more 10GB RAM and CPU 6 to 8
Error:
ORA-31693: Table data object "BNCSDB"."MS_DATA_PTORE" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_4037596720$" too small

snipped of Export log:


Export: Release 11.2.0.2.0 - Production on Tue May 14 20:03:25 2013

Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl dumpfile=BCSDB04_19.dmp logfile=BCSDB04_19.log
directory=BACKUP schemas=('WRKFLW','BTCH_USR','IIMS')
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 51.60 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/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/DB_LINK
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/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/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/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/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
Processing object type SCHEMA_EXPORT/JAVA_RESOURCE/JAVA_RESOURCE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/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_TABLE_ACTION
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
ORA-31693: Table data object "BANCSDB"."MS_DATA_PTORE" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_4037596720$" too small
. . exported "BNCSDB"."MS_BUFFER_REGISTRY" 158.1 MB 647096 rows
. . exported "IIMS_PRD"."T_RULE_LOG_DETAIL" 2.015 MB 8975 rows
. . exported "BNCSDB"."MS_BUFFER_TRACK" 202.5 MB 1775579 rows
. . exported "BNCSDB"."EXCHANGE_RATE" 137.7 MB 641125 rows
. . exported "BNCSDB"."MS_ERROR_LOG" 136.7 MB 693902 rows
................................................................................
...
................................................................................
...
................................................................................
...
................................................................................

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: data pump export is very slow. For 50GB export has taken

Postby Tim... » Wed May 15, 2013 5:08 pm

Hi.

Wow. That is seriously slow for 50G. :)

Ok, there are a couple of ways at looking at this:

1) The snapshot too old message means you don't have enough undo to keep the view of the table consistent, but...

2) It's hardly surprising you don't have enough undo, as the export took 24 hours to get this far. :)

I think the first thing you should do is target the performance of the export. Once you start the export, you can identify the session running the export and check what it is taking all its time on. A really useful tool for that is Snapper.

http://tech.e2sn.com/oracle-scripts-and ... on-snapper

Of course, if you have diagnostics and tuning pack, you can use the Top Activity screen in Cloud Control to monitor the session.

When you see what the performance bottlenecks are, you can make some decisions about how you can approach tuning the export.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: data pump export is very slow. For 50GB export has taken

Postby royalyogi » Fri May 17, 2013 11:54 am

Database is NoArchive log mode:

SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 28397
Current log sequence 28399
SQL>

Snipped of alert log:
Wed May 15 17:27:39 2013
WARNING: Heavy swapping observed on system in last 5 mins.
pct of memory swapped in [5863741.05%] pct of memory swapped out [6099530.61%].
Please make sure there is no memory pressure and the SGA and PGA
are configured correctly. Look at DBRM trace file for more details.
Wed May 15 17:35:56 2013
Thread 1 cannot allocate new log, sequence 28276
Private strand flush not complete
Current log# 3 seq# 28275 mem# 0: E:\APP\ORACLE\ORADATA\ORCL\REDO03.LOG
Thread 1 advanced to log sequence 28276 (LGWR switch)
Current log# 1 seq# 28276 mem# 0: E:\APP\ORACLE\ORADATA\ORCL\REDO01.LOG
Wed May 15 17:36:24 2013
Thread 1 cannot allocate new log, sequence 28277
Checkpoint not complete
Current log# 1 seq# 28276 mem# 0: E:\APP\ORACLE\ORADATA\ORCL\REDO01.LOG
Thread 1 advanced to log sequence 28277 (LGWR switch)
Current log# 2 seq# 28277 mem# 0: E:\APP\ORACLE\ORADATA\ORCL\REDO02.LOG
Wed May 15 17:36:54 2013
Thread 1 cannot allocate new log, sequence 28278
Checkpoint not complete
Current log# 2 seq# 28277 mem# 0: E:\APP\ORACLE\ORADATA\ORCL\REDO02.LOG
Thread 1 advanced to log sequence 28278 (LGWR switch)
Current log# 3 seq# 28278 mem# 0: E:\APP\ORACLE\ORADATA\ORCL\REDO03.LOG

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: data pump export is very slow. For 50GB export has taken

Postby Tim... » Fri May 17, 2013 1:42 pm

Hi.

You are getting a log switch every few seconds. That's not going to work well. I think you need to increase the size of the redo logs. Normally I would expect a log switch every 15-20 minutes. Now an import is not normal running, but doing a switch every few seconds is not great. Definitely increase them in size, even if you have to revert them later, when the import is finished.

Of course, I am making an assumption here. If snapper isn't saying log switches is the problem, then this advice is bad. You really need to monitor the process to know what the bottleneck is, so you can make the appropriate choice when trying to fix it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: data pump export is very slow. For 50GB export has taken

Postby royalyogi » Sat May 18, 2013 7:53 am

As per above database is in noarchive mode. So why need to increase the redo log file and why above redo error is coming on alert?

SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 28397
Current log sequence 28399

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: data pump export is very slow. For 50GB export has taken

Postby Tim... » Sat May 18, 2013 9:56 am

Hi.

Whether you are using archivelog or noarchivelog, redo logs are still important. Every time you switch logfiles you you cause cause a checkpoint. What does a checkpoint do? One of the things is does is make sure all modified blocks in memory are forced to disk. Disk is a lot slower than memory, so anything that artificially forces blocks to disks can adversely affect performance. Reducing checkpoints usually improves performance, especially for applications that are not performing direct path writes.

Sizing your redo logs appropriately is important, no matter what mode your database is running in. As I mentioned previously, under "normal" running I would suggest a log switch rate of 15-20 minutes is a good starting point. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: data pump export is very slow. For 50GB export has taken

Postby royalyogi » Sun May 19, 2013 1:35 pm

Thanks a lot Tim. I am very much Impressed from you and your posted.
I will increased the redo log now and we will check it.

Found that the table "BANCSDB"."MS_DATA_PTORE" contain BLOB data in this export. If we exclude this Table MS_DATA_PTORE then export is very fast.but when included this LOB table export is very slow.

ORA-31693: Table data object "BANCSDB"."MS_DATA_PTORE" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_4037596720$" too small.

So If increase the redo log file size then will it resolve the issue ? Please give your suggestion to resolve this issue

Thanks a lot in advance.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: data pump export is very slow. For 50GB export has taken

Postby Tim... » Sun May 19, 2013 5:49 pm

Hi.

Once you increase the redo log sizes, try again. If the performance improves, you may no longer have the snapshot too old message. If you still do, then you will need to increase the size of the undo tablespace, to make sure there is enough undo to keep a consisten view of the data during the table export.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: data pump export is very slow. For 50GB export has taken

Postby royalyogi » Mon May 20, 2013 12:18 pm

I thought I will increase the speed of expdp by using the PARALLEL=4 and COMPRESSION=ALL But getting below error like feature not enabled or not supported by this version. I am surprising why it is not supported on version 11.2.0.2. Please through some light on this.

and also please help me to know that shall we use PARALLEL=4 , COMPRESSION=all and Direct = y at same time on expdp ?

expdp system@ORCL directory=DIR1 dumpfile=ORCL18012012.dmp logfile=ORCL18012012.log PARALLEL=4 schemas=('WRKFLW','BATCH')
Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-39094: Parallel execution not supported in this database edition.



expdp system@ORCL directory=DIR1 dumpfile=ORCL18012012.dmp logfile=ORCL18012012.log COMPRESSION=all schemas=('WRKFLW','BATCH')
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-00439: feature not enabled: Dump File Data Compression

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: data pump export is very slow. For 50GB export has taken

Postby Tim... » Mon May 20, 2013 5:17 pm

Hi.

I'm guessing you are using either Oracle XE, Oracle SE or Oracle SE One. Typically, parallel functionality is limited to Enterprise Edition, so you can't use it, as the message says.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: data pump export is very slow. For 50GB export has taken

Postby royalyogi » Fri Aug 02, 2013 1:43 pm

while taking export backup of the schema BCSDB it is taking more time. But while expdp with exclude table = MS_DATA_STORE is taking very
less time and size is also very less.Could please let me know how this table is releated to SYS_LOB* table why SYS_LOB* table size is increasing
day by day.

OWNER SEGMENT_NAME GBytes
------------------------------ --------------------------------------------------------------------------------- ----------
BCSDB IDX_MBR_STATUS_DASH .1640625
BCSDB MS_ERROR_LOG .1640625
BCSDB EXCHANGE_RATE .1875
BCSDB IDX_MBR_CMNCOUNT_L .21875
BCSDB MS_DATA_STORE .2265625
BCSDB MS_BUFFER_TRACK .241210938
BCSDB MS_BUFFER_REGISTRY .34375
BCSDB SYS_LOB0000223615C00004$$ 20.3222656
BCSDB SYS_LOB0000223615C00003$$ 37.453125

5674 rows selected.

SQL> select OWNER,sum(bytes)/1024/1024 "SIZE_IN_MB" from dba_segments where owner ='BCSDB' group by owner order by owner;

OWNER SIZE_IN_MB
------------------------------ ----------
BCSDB 64612.3125

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: data pump export is very slow. For 50GB export has taken

Postby Tim... » Fri Aug 02, 2013 2:02 pm

Hi.

It looks like this table has got columns defined as large objects, BLOBs or CLOBs. As a result, when the table gets an insert, the LOB data is stored in an offline LOB segment. If you look at the types of your objects, you will have (amongst other things) tables, LOB segments and LOB indexes. This is how the large objects are stored.

Why are they getting bigger? Because you are inserting more data into the table.

Why does the export take a long time when this table is included? Because it is a very big table, or at least, the combination of the table and the LOB segments is very large.

You need to read up on LOBs in the Oracle documentation.

http://docs.oracle.com/cd/E16655_01/ser ... #CNCPT1536

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: data pump export is very slow. For 50GB export has taken

Postby royalyogi » Sat Aug 03, 2013 1:41 pm

Yes, Tim I fully agreed with you, SYS_LOB* is increasing due to insert data and application team are not using any external table to store data. I didn’t find solution on given below link. Please help me to identified the data and how can we move data from sys_lob* to a non LOB table(MS_DATA_STORE).

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: data pump export is very slow. For 50GB export has taken

Postby Tim... » Sat Aug 03, 2013 2:58 pm

Hi.

I think you misunderstood my point. This is how LOBs work. LOBs should not be stored in the main body of the table. They should be stored in overflow segments. That is a good thing, not something you should aim to reverse.

Read about LOBs, then you will understand what you are seeing, and understand that it is a good thing.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: data pump export is very slow. For 50GB export has taken

Postby royalyogi » Mon Aug 12, 2013 6:23 pm

First my special thanks to you for always helping to me.

I have compared prod expdp and nonprod expdp but prod expdp is very fast compare to nonprod expdp

Prod expdp:

1) Tables/schema’s structure is same on prod & nonprod.
2) Oracle database is enterprise edition.
3) OS is using local drive.
4) Expdp will complete within 3-4Hrs for database size 150GB(expdp is very fast than nonprod expdp).
5) All the parameters are same in prod and nonprod database.

NonProd expdp:

1) Tables/schema’s structure is same on prod & nonprod.
2) Oracle database is standard edition 1.0
3) OS is using Virtual drive (VMWare).
4) Expdp will take more 20Hrs for database size 50GB.


So kindly let me know what should be reason to slowness of expdp on nonprod database? or let me know if need to check anything more ?


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests

cron