TIP 88#: Progress of DataPump import with LOB data

If you have a big LOB to be imported through DataPump, you may realize that datapump session stays for a long time and you wonder if it is working and the client also may ask you how much more time is needed to be completed.
The sad news is that v$session_longops and query like the following can not help you that much.Also you can get too much info on how much longer impdp run from datapump views in database or datapump commands (like status).


select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete"
from
v$session_longops
where
opname like '%%' and opname not like '%aggregate%' and totalwork != 0 and sofar <> totalwork;


The best way is to check the size of LOB segment and see if it grows. Also you could compare the LOB segment size in a import DB with a source DB to get a better estimate of how much more work DataPump has to do :


select sum(bytes)/1024/1024,sysdate from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='&table_name');

TIP # 68 : Export hangs, Shutdown immediate hangs !!!

One of my client is running Oracle 9i (9.2.0.7) and Oracle 10g (10.2.0.3) On Solaris 64 bit in the same box.Oracle 10g is mainly used for recovery catalog and Grid repository.
All my efforts to take export of recovery catalog schema faild.With tradition export tool (exp), export hangs in exporting "Cluster Definition". Data pump export/import (expdp and impdp) runs forever.
Data pump sessions were waiting on "wait for unread message on broadcast channel" which is idle wait.Export sessions were waiting on cursor: pin S wait on X.In order to fix the issue, I just turned off mutex by seting hidden parameter. (I will explain in next post about mutex in 10g database).
To make this change effective, database should be bounced.
Surprisingly, shutdown immediate also hung, alert log reported Oracle was waiting for DBWx (Database Writer) to be shut down !!!!.
I ended up to shutdown abort database and startup it again though bouncing database sis not fix the situation.
The first export after bouncing database failed with ORA-0600 in alert log and with the following error lines in exporting cluster definitions.

EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00000: Export terminated unsuccessfully

All of the next runs of exp and expdp hung same as before bounce.
Eventually, I found that problem was because of improper setting of LD_LIBRARY_PATH and LIBPATH.This parameter needs to be set to point to $ORACLE_HOME/lib in 10g as first reference. Setting those parameter properly fixed the situation including shutdown problem and exp/expdp problem. (For more info, please refer to metalink note : 351650.1).

TIP 53# : Exclude option in data pump on Window.

As all know, filtering option in datapump (10g version of traditional export/import) is very powerful.
I just wanted to use this option and exclude a table from whole schema export per client `s request.A client is running 10g database on Windows.
Regarding to Oracle documentation and expdp help=y, it is supposed to be as simple as adding exclude=table:table_name.

However, I got the following errors when I ran it.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE_NAME_EXPR filter is badly formed.
ORA-00920: invalid relational operator



I tried option with the following syntax :

exclude=TABLE:"= 'TABLE_NAME'"
exclude=TABLE:TABLE_NAME
exclude=TABLE:" IN ('TABLE_NAME') "

No luck. Got the same error.
Finally, I figured out that window can not parse " and need to add escape seuqence.
So exclude option on windows should have syntax like this :


exclude=TABLE:\"='TABLE_NAME'\"