Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for June, 2011

ORA-39142: incompatible version number 3.1 in dump file “/u01/oracle/dpdump/expdat.dmp”

Posted by Hendry chinnapparaj on June 29, 2011

Problem

——————-

I was exporting data from a 11.2.0.2 database and trying to import the dump into a 10.2.0.3 database

While importing the dump into a lower version database , the foll errors were thrown :

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file “/u01/oracle/dpdump/expdat.dmp”
Solution

—————-

To avoid this error, while exporting from the 11g database , add the foll parameter –  version=10.2

exdp xxx/xxx parfile=abc.par version=10.2

Then import this dump into the 10.2.0.3 database and it goes through without any errors.

 

Advertisements

Posted in Oracle Utilities | Leave a Comment »

11g Datapump – how fast is it?

Posted by Hendry chinnapparaj on June 20, 2011

How long to export 1 billion (yes, not a million) rows (8 columns) from a single table in the DSE?

Pick a time and scroll down for the answer….

 

 

 

9 mins & 9 seconds…         –             1,047,869,238 rows

 

 

That’s about 1.9 million rows per second.

Exports out to 48Gb of dump files on disk.

This is with 12 concurrent processes, uncompressed. (3 core LPAR)

Interestingly, doing the same, exporting out to compressed dump files finishes in 10 mins 59secs (17% slower) but the compressed dumps total 5.39Gb (89% improvement)  Not surprisingly, we’ll be using the compressed option on the 11g upgraded MSE!

Export: Release 11.2.0.2.0 – Production on Mon Jun 20 12:06:25 2011

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

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning option

Starting “SYSTEM”.”AD_IVR_CALL_EVENTS”:  system/******** parfile=/u01/local/dbhk/scripts/par/AD_IVR_CALL_EVENTS.par

Estimate in progress using STATISTICS method…

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200808″  709.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200904″  637.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200701″  586.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201007″  578.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200907″  569.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200812″  569.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200412″  562.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200910″  557.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200807″  557.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200810″  555.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200912″  553.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201003″  550.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201012″  550.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201008″  546.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200607″  543.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200606″  542.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200406″  542.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200707″  540.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200903″  539.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200911″  538.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201103″  537.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200905″  536.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200603″  536.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200710″  536.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200801″  535.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200705″  530.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200503″  530.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200608″  529.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201006″  529.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201004″  528.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200703″  528.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200805″  528.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200706″  527.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200403″  525.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200605″  524.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200708″  524.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201005″  521.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200908″  521.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200512″  521.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200612″  520.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200508″  519.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200803″  519.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200809″  519.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200611″  518.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200906″  518.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200909″  517.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201011″  517.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201105″  517.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200610″  515.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200407″  514.7 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200804″  513.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201101″  509.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200404″  508.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201001″  508.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200511″  507.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201009″  506.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200601″  505.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201104″  503.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201010″  502.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200806″  500.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200501″  500.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200802″  499.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200704″  498.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200711″  496.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200409″  495.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200510″  494.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200604″  493.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200509″  491.7 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200811″  489.7 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200901″  488.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200405″  485.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200609″  483.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200504″  481.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201002″  480.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201102″  479.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200702″  477.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200709″  477.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200411″  477.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200712″  477.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200408″  477.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200602″  471.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200402″  469.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200902″  462.7 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200502″  451.7 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200507″  441.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200410″  439.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200505″  115.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201106″  26.43 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200506″      0 KB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_MAXVAL”      0 KB

Total estimation using STATISTICS method: 43.72 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/AUDIT_OBJ

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200810″  604.1 MB 12939912 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201007″  632.0 MB 13474288 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200912″  606.3 MB 12892550 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200412″  616.2 MB 13085351 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200910″  606.3 MB 12986081 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200807″  616.8 MB 13260488 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200907″  631.7 MB 13574572 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200701″  641.0 MB 13655199 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200904″  694.5 MB 14862177 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200812″  634.7 MB 13592978 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201012″  605.0 MB 12850284 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201008″  599.1 MB 12726329 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200808″  771.8 MB 16531124 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201103″  590.2 MB 12535919 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201003″  603.8 MB 12834227 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200607″  591.3 MB 12653571 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200911″  586.8 MB 12556209 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200606″  591.7 MB 12651270 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200707″  589.2 MB 12603246 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200903″  600.9 MB 12868423 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200710″  595.2 MB 12773875 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200406″  592.2 MB 12637972 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200905″  597.8 MB 12800999 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200603″  586.0 MB 12493271 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200801″  594.8 MB 12746291 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200705″  579.4 MB 12348292 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200503″  582.5 MB 12377092 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201004″  579.7 MB 12321619 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201006″  578.0 MB 12330863 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200703″  576.7 MB 12292556 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200403″  575.4 MB 12247907 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200805″  574.6 MB 12309609 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200608″  578.8 MB 12340844 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200706″  575.1 MB 12293222 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200708″  573.1 MB 12214623 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200605″  573.2 MB 12220353 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200512″  569.2 MB 12137220 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201005″  571.1 MB 12138518 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200908″  580.8 MB 12437572 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201011″  567.9 MB 12065936 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201105″  566.1 MB 12021580 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200508″  566.9 MB 12093661 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201101″  559.8 MB 11891896 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200612″  569.4 MB 12133487 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200803″  575.9 MB 12336807 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200809″  564.6 MB 12093460 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200611″  566.9 MB 12083574 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200909″  575.3 MB 12320296 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200906″  574.5 MB 12347610 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200407″  560.6 MB 11976380 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201009″  555.4 MB 11801545 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201104″  554.2 MB 11769178 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200610″  562.4 MB 11988388 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201010″  550.3 MB 11696143 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200804″  571.6 MB 12245285 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200404″  556.5 MB 11840166 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201001″  556.8 MB 11841551 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200511″  554.1 MB 11818410 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200601″  552.7 MB 11788350 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200806″  554.6 MB 11926007 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200501″  548.2 MB 11651476 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200802″  555.8 MB 11909144 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200704″  546.2 MB 11640865 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200711″  551.5 MB 11823282 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200604″  539.8 MB 11507238 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200510″  538.6 MB 11502404 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201102″  526.4 MB 11181328 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200509″  535.7 MB 11438151 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200409″  543.6 MB 11565692 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200901″  543.1 MB 11636825 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200811″  532.6 MB 11412449 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200405″  530.1 MB 11287595 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201002″  526.7 MB 11198516 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200609″  528.3 MB 11264507 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200702″  520.8 MB 11101477 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200504″  528.6 MB 11240207 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201106″  28.90 MB  615580 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200411″  522.1 MB 11119657 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200712″  531.0 MB 11379792 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200505″  127.2 MB 2705187 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200602″  514.8 MB 10981016 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200408″  523.2 MB 11144453 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200709″  521.1 MB 11109906 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200402″  514.0 MB 10943865 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200902″  503.3 MB 10782745 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200502″  495.0 MB 10525001 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200410″  480.5 MB 10239449 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200506″      0 KB       0 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_MAXVAL”      0 KB       0 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200507″  480.7 MB 10284825 rows

Master table “SYSTEM”.”AD_IVR_CALL_EVENTS” successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.AD_IVR_CALL_EVENTS is:

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_01.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_02.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_03.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_04.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_05.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_06.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_07.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_08.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_09.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_10.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_11.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_12.dmp

Job “SYSTEM”.”AD_IVR_CALL_EVENTS” successfully completed at 12:15:34

Posted in Oracle Utilities | Leave a Comment »

DATAPUMP – KILL JOBS ONLY USING THE DATAPUMP TOOL

Posted by Hendry chinnapparaj on June 20, 2011

Killing an expdp or impdp process from the oslevel will not stop the job – so expdp or impdp jobs must be killed from the database level.

Example ?

Start an export job…..

Check the dba_datapump_jobs view…

SQL> select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                               OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_10             OPS$ORACLE                     EXECUTING
SYS_EXPORT_FULL_01             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_05             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_03             OPS$ORACLE                     NOT RUNNING

SQL> exit

Kill the job from the oslevel …

cmi1d:/u03/oradata/export/cmi1d> ps -ef |grep expdp

oracle 26214652 18350150   0 13:27:38  pts/2  0:00 expdp / DUMPFILE=expdp_full_cmi1d_170611_132738_%U.dmp LOGFILE=expdp_full_cmi1d_170611_132738.log PARFILE=/u01/local/dbhk/scripts/par/expdp_full.par
cmi1d:/u03/oradata/export/cmi1d> kill -9 26214652
cmi1d:/u03/oradata/export/cmi1d>

Now check database again – a few minutes after the oslevel kill.

SQL> select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                                      OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_10             OPS$ORACLE                     EXECUTING

View still shows the job is executing.

If you look at the export dump area – it is clear the job is still executing in the background.

-rw-r—–    1 oracle   oinstall  877879296 Jun 17  13:43 expdp_full_cmi1d_170611_132738_02.dmp
-rw-r—–    1 oracle   oinstall 1382027264 Jun 17 13:43 expdp_full_cmi1d_170611_132738_01.dmp

Therefore it is not a good idea to kill a datapump job only from the oslevel – the job will not stop.

This is the way to do it.

First attach to the job via expdp….

The job name is SYS_EXPORT_FULL_10  started by OPS$ORACLE – in the view above.

To attach to a job, you must connect as the owner of the job….therefore I am using the ” / ” for connection as ops$oracle

expdp / attach=SYS_EXPORT_FULL_10

Export: Release 11.2.0.2.0 – Production on Fri Jun 17 13:45:10 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_10
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: TRUE
GUID: A5E2A4EB79A500E4E0430A35C8EC00E4
Start Time: Friday, 17 June, 2011 13:27:42
Mode: FULL
Instance: cmi1d
Max Parallelism: 2
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        /******** DUMPFILE=expdp_full_cmi1d_170611_132738_%U.dmp LOGFILE=expdp_full_cmi1d_170611_132738.log PARFILE=/u01/local/dbhk/scripts/par/expdp_full.par
COMPRESSION           ALL
INCLUDE_METADATA      1
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 2
Job Error Count: 0
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_%u.dmp
size: 8,589,934,592
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_01.dmp
size: 8,589,934,592
bytes written: 4,096
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_02.dmp
size: 8,589,934,592
bytes written: 15,687,680

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: EBS_GL_ARCHIVE
Object Name: GL_ACCT_BALANCES
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 894
Completed Rows: 81,746,354
Worker Parallelism: 1

Worker 2 Status:
Process Name: DW01
State: EXECUTING
Object Schema: EBS_GL_ARCHIVE
Object Name: GL_JOURNAL_LINES
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 894
Completed Rows: 91,359,564
Worker Parallelism: 1

Export>

Enter KILL_JOB to  kill job..

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes
.
Check again –

select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                       OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_01             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_05             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_03             OPS$ORACLE                     NOT RUNNING

Posted in Oracle Utilities | Leave a Comment »

streams_pool_size defaulting to 179516211 in oracle alert log

Posted by Hendry chinnapparaj on June 16, 2011

streams_pool_size defaulting to 179516211 in oracle alert log

 

Problem

———-

The streams_pool_size dynamically changed from 0 to 179516211

and the following were recorded in the oracle alert log indicating issues related to data pump.

 

streams_pool_size defaulting to 179516211

kupprdp: master process DM00 started with pid=61, OS id=39190678

to execute – SYS.KUPM$MCP.MAIN(‘SYS_EXPORT_FULL_01’, ‘OPS$ORACLE’, ‘KUPC$C_1_20110516040004’, ‘KUPC$S_1_20110516040004’, 0);

kupprdp: worker process DW01 started with worker id=1, pid=115, OS id=14024736

to execute – SYS.KUPW$WORKER.MAIN(‘SYS_EXPORT_FULL_01’, ‘OPS$ORACLE’);

Mon May 16 04:29:12 2011

streams_pool_size defaulting to 179516211

streams_pool_size        = 184549376

 

alert_zoeprd.log.110514:  streams_pool_size        = 0

alert_zoeprd.log.110521:streams_pool_size defaulting to 179516211

alert_zoeprd.log.110521:  streams_pool_size        = 184549376

 

alert_zoeprd.log.110514:  db_cache_size            = 1291845632

alert_zoeprd.log.110521:  db_cache_size            = 1107296256

 

 

Cause

———

Oracle11g  doc has the following explanation

Setting the Size Of the Buffer Cache In a Streams Environment

Oracle Data Pump uses Streams functionality to communicate between processes. If the SGA_TARGET initialization parameter is set, then the STREAMS_POOL_SIZE initialization parameter is automatically set to a reasonable value.

If the SGA_TARGET initialization parameter is not set and the STREAMS_POOL_SIZE initialization parameter is not defined, then the size of the streams pool automatically defaults to 10% of the size of the shared pool.

When the streams pool is created, the required SGA memory is taken from memory allocated to the buffer cache, reducing the size of the cache to less than what was specified by the DB_CACHE_SIZE initialization parameter. This means that if the buffer cache was configured with only the minimal required SGA, then Data Pump operations may not work properly. A minimum size of 10M is recommended for STREAMS_POOL_SIZE in order to ensure successful Data Pump operations.

Oracle10g has the details

Oracle’s Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value. If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value, and the SGA_TARGET parameter is set to 0 (zero), then the Streams pool size is the value specified by the STREAMS_POOL_SIZE parameter, in bytes.

If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool.

 

Setting the Size Of the Buffer Cache In a Streams Environment

As of Oracle Database 10g release 10.2, if Streams functionality is used, but the STREAMS_POOL_SIZE initialization parameter is not defined, then the size of the streams pool automatically defaults to 10% of size of the shared pool.

When the streams pool is created, the required SGA memory is taken from memory allocated to the buffer cache, reducing the size of the cache to less than what was specified by the DB_CACHE_SIZE initialization parameter. This means that if the buffer cache was configured with only the minimal required SGA, then Data Pump operations may not work properly.

Here it doesn’t mention the minimum requirement of 10m.

Fix

—-

If you are using data pump, then increase the size of streams_pool_size to atleast 10m as recommended by oracle and increase the buffer_cache_size to it’s original size

Alter system set streams_pool_size=10m scope=spfile sid=’*’;

Alter system set db_cache_size=1291845632 scope=spfile sid=’*’;

And bounce the database

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »