DataPump Exports – semi-intelligently

I’m working on a project with a series of Oracle databases holding SAP data. To make it fun, the data sources are in Canada and my local copy is a DataGuard physical standby. Simple enough – cancel managed recovery, convert to a snapshot standby and start my extracts to populate new databases with.

Wouldn’t it be nice if I could push a button and extract my data? With that in mind, I created some scripts to make this process simpler.

The script will:
– identify the non-default schemas (i.e. SAP schemas)
– identify tables with LOBs greater than 5GB in size
– generate DataPump expdp scripts

Here’s the dialogue once I’ve logged in and run the extract script.


SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 1 21:15:51 2017
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Schema Objects
OWNER                            COUNT(*)
------------------------------ ----------
CLM                                  2698
SAPSR3DB                             2765

So far so good. Two schemas that we’re interested in. Now, let’s see what LOBs over our 5GB limit.

BLOBS over 5GB to be split 10 ways
NAME      OWNER      TABLE_NAME                     SEGMENT_NAME                             GB
--------- ---------- ------------------------------ ------------------------------ ------------
PCL       CLM        FCI_ATTACHMENT_BLOB            SYS_LOB0000017250C00009$$               122

Finally it will spit out the export commands we’ll be using. Specifically:
– full database metadata export
– full database data export
– schema metadata exports (one per schema)
– schema data exports (one per schema)
– import to generate SQL scripts (one for every metadata export)

Export Commands
expdp enkitec/******* directory=enkitec_dir  \
      dumpfile=PCL_full_meta.dmp  \
      logfile=PCL_full_meta.log  \
      full=y content=metadata_only

expdp enkitec/******* directory=enkitec_dir \
      dumpfile=PCL_full_%U.dmp  \
      logfile=PCL_full.log  \
      full=y compression=all filesize=5G parallel=8

expdp enkitec/******* directory=enkitec_dir  \
      dumpfile=PCL.CLM_schema_%U.dmp  \
      logfile=PCL.CLM_schema.log  \
      schemas=CLM compression=all filesize=5G parallel=8

expdp enkitec/******* directory=enkitec_dir  \
      dumpfile=PCL.CLM_schema_meta.dmp  \
      logfile=PCL.CLM_schema_meta.log  \
      schemas=CLM content=metadata_only

expdp enkitec/******* directory=enkitec_dir  \
      dumpfile=PCL.SAPSR3DB_schema_%U.dmp  \
      logfile=PCL.SAPSR3DB_schema.log  \
      schemas=SAPSR3DB compression=all filesize=5G parallel=8

expdp enkitec/******* directory=enkitec_dir  \
      dumpfile=PCL.SAPSR3DB_schema_meta.dmp  \
      logfile=PCL.SAPSR3DB_schema_meta.log  \
      schemas=SAPSR3DB content=metadata_only

expdp enkitec/******* directory=enkitec_dir \
      dumpfile=PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_0_%U.dmp \
      logfile= PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_0.log \
      tables=  CLM.FCI_ATTACHMENT_BLOB \
      query=   CLM.FCI_ATTACHMENT_BLOB:'"where mod(dbms_rowid.rowid_block_number(rowid),10)=0"' \
      compression=all filesize=5G

expdp enkitec/******* directory=enkitec_dir \
      dumpfile=PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_1_%U.dmp \
      logfile= PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_1.log \
      tables=  CLM.FCI_ATTACHMENT_BLOB \
      query=   CLM.FCI_ATTACHMENT_BLOB:'"where mod(dbms_rowid.rowid_block_number(rowid),10)=1"' \
      compression=all filesize=5G

expdp enkitec/******* directory=enkitec_dir \
      dumpfile=PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_2_%U.dmp \
      logfile= PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_2.log \
      tables=  CLM.FCI_ATTACHMENT_BLOB \
      query=   CLM.FCI_ATTACHMENT_BLOB:'"where mod(dbms_rowid.rowid_block_number(rowid),10)=2"' \
      compression=all filesize=5G

expdp enkitec/******* directory=enkitec_dir \
      dumpfile=PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_3_%U.dmp \
      logfile= PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_3.log \
      tables=  CLM.FCI_ATTACHMENT_BLOB \
      query=   CLM.FCI_ATTACHMENT_BLOB:'"where mod(dbms_rowid.rowid_block_number(rowid),10)=3"' \
      compression=all filesize=5G

expdp enkitec/******* directory=enkitec_dir \
      dumpfile=PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_4_%U.dmp \
      logfile= PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_4.log \
      tables=  CLM.FCI_ATTACHMENT_BLOB \
      query=   CLM.FCI_ATTACHMENT_BLOB:'"where mod(dbms_rowid.rowid_block_number(rowid),10)=4"' \
      compression=all filesize=5G

expdp enkitec/******* directory=enkitec_dir \
      dumpfile=PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_5_%U.dmp \
      logfile= PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_5.log \
      tables=  CLM.FCI_ATTACHMENT_BLOB \
      query=   CLM.FCI_ATTACHMENT_BLOB:'"where mod(dbms_rowid.rowid_block_number(rowid),10)=5"' \
      compression=all filesize=5G

expdp enkitec/******* directory=enkitec_dir \
      dumpfile=PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_6_%U.dmp \
      logfile= PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_6.log \
      tables=  CLM.FCI_ATTACHMENT_BLOB \
      query=   CLM.FCI_ATTACHMENT_BLOB:'"where mod(dbms_rowid.rowid_block_number(rowid),10)=6"' \
      compression=all filesize=5G

expdp enkitec/******* directory=enkitec_dir \
     dumpfile=PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_7_%U.dmp \
      logfile= PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_7.log \
      tables=  CLM.FCI_ATTACHMENT_BLOB \
      query=   CLM.FCI_ATTACHMENT_BLOB:'"where mod(dbms_rowid.rowid_block_number(rowid),10)=7"' \
      compression=all filesize=5G

expdp enkitec/******* directory=enkitec_dir \
      dumpfile=PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_8_%U.dmp \
      logfile= PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_8.log \
      tables=  CLM.FCI_ATTACHMENT_BLOB \
      query=   CLM.FCI_ATTACHMENT_BLOB:'"where mod(dbms_rowid.rowid_block_number(rowid),10)=8"' \
      compression=all filesize=5G

expdp enkitec/******* directory=enkitec_dir \
      dumpfile=PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_9_%U.dmp \
      logfile= PCL.CLM.FCI_ATTACHMENT_BLOB_LOB_9.log \
      tables=  CLM.FCI_ATTACHMENT_BLOB \
      query=   CLM.FCI_ATTACHMENT_BLOB:'"where mod(dbms_rowid.rowid_block_number(rowid),10)=9"' \
      compression=all filesize=5G

impdp enkitec/******* directory=enkitec_dir  \
      dumpfile=PCL_full_meta.dmp  \
      sqlfile=PCL_full_meta

impdp enkitec/******* directory=enkitec_dir  \
      dumpfile=PCL.CLM_schema_meta.dmp  \
      sqlfile=PCL.CLM_schema_meta

impdp enkitec/******* directory=enkitec_dir  \
      dumpfile=PCL.SAPSR3DB_schema_meta.dmp  \
      sqlfile=PCL.SAPSR3DB_schema_meta

So, the result of running the script is output that you can run to generate the exports you need. Here’s some thoughts though.

  • whilst the script will isolate and export large LOBs, it does not exclude these from the schema/full data exports
  • you probably would want to increase the minimum threshold for LOBs from 5GB
  • you may consider increasing the filesize parameter.  Since there’s going to be a lot of scp going on, I wanted to make sure we were dealing with smaller chunks

LOB exports and Compression

You may notice the use of the compression=all switch on these exports.  Great for data but not so much for LOBs which themselves seem pretty compressed.  So I decided to run a small test.

lob-timing

For the 15% or so of space saving, it takes 4 times longer.  No thanks.  Sure, your enterprise class server may be able to offer up some better compression times (this was on an Oracle T5-5 SPARC server) but I don’t think you’re going to recoup that time in either file transfers or import times.

The SQL Script

Yours to enjoy.

prompt
prompt
prompt Tablespaces
set pagesize 60 linesize 255
select tablespace_name, contents from dba_tablespaces;
prompt
prompt
prompt Schema Objects
select owner, count(*) from dba_objects where owner not in ('APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS') group by owner order by owner;
prompt
prompt
prompt BLOBS over 5GB to be split 10 ways
col owner format a10
col table_name format a30
col segment_name format a30
col GB format 999,999,999
select d.name, l.owner, l.table_name, l.segment_name, round(sum(e.bytes)/1024/1024/1024) as GB
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name;

prompt Export Commands
set echo off feedback off trimspool on timing off pagesize 0

-- Export for the full database - data and metadata
select 'expdp enkitec/blueTech2016! directory=enkitec_dir dumpfile='||name||'_full_%U.dmp logfile='||
name||'_full.log full=y compression=all filesize=5G parallel=8'
from v$database
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir dumpfile='||name||'_full_meta.dmp logfile='||
name||'_full_meta.log full=y content=metadata_only' from v$database;

-- Export for each schema - data and metadata
select 'expdp enkitec/blueTech2016! directory=enkitec_dir dumpfile='||d.name||'_'||
o.owner||'_schema_%U.dmp logfile='||d.name||'_'||o.owner||'_schema.log full=n schemas='||o.owner||' compression=all filesize=5G parallel=8'
from v$database d, dba_objects o
where o.owner not in ('APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir dumpfile='||d.name||'_'||
o.owner||'_schema_meta.dmp logfile='||d.name||'_'||o.owner||'_schema_meta.log full=n schemas='||o.owner
||' content=metadata_only' from v$database d, dba_objects o
where o.owner not in ('APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
group by d.name, o.owner;

-- Exports for LOBS - data only
select 'expdp enkitec/blueTech2016! directory=enkitec_dir \'||chr(10)||
' dumpfile='||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_1_%U.dmp \'||chr(10)||
' logfile= '||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_1.log \'||chr(10)||
' tables= '||l.owner||'.'||l.table_name||' \'||chr(10)||
' query= '||l.owner||'.'||l.table_name||':'||''''||'"where mod(dbms_rowid.rowid_block_number(rowid),10)=1"'||''''||' \'||chr(10)||
' compression=all filesize=5G'||chr(10)
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir \'||chr(10)||
' dumpfile='||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_2_%U.dmp \'||chr(10)||
' logfile= '||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_2.log \'||chr(10)||
' tables= '||l.owner||'.'||l.table_name||' \'||chr(10)||
' query= '||l.owner||'.'||l.table_name||':'||''''||'"where mod(dbms_rowid.rowid_block_number(rowid),10)=2"'||''''||' \'||chr(10)||
' compression=all filesize=5G'||chr(10)
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir \'||chr(10)||
' dumpfile='||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_3_%U.dmp \'||chr(10)||
' logfile= '||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_3.log \'||chr(10)||
' tables= '||l.owner||'.'||l.table_name||' \'||chr(10)||
' query= '||l.owner||'.'||l.table_name||':'||''''||'"where mod(dbms_rowid.rowid_block_number(rowid),10)=3"'||''''||' \'||chr(10)||
' compression=all filesize=5G'||chr(10)
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir \'||chr(10)||
' dumpfile='||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_4_%U.dmp \'||chr(10)||
' logfile= '||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_4.log \'||chr(10)||
' tables= '||l.owner||'.'||l.table_name||' \'||chr(10)||
' query= '||l.owner||'.'||l.table_name||':'||''''||'"where mod(dbms_rowid.rowid_block_number(rowid),10)=4"'||''''||' \'||chr(10)||
' compression=all filesize=5G'||chr(10)
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir \'||chr(10)||
' dumpfile='||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_5_%U.dmp \'||chr(10)||
' logfile= '||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_5.log \'||chr(10)||
' tables= '||l.owner||'.'||l.table_name||' \'||chr(10)||
' query= '||l.owner||'.'||l.table_name||':'||''''||'"where mod(dbms_rowid.rowid_block_number(rowid),10)=5"'||''''||' \'||chr(10)||
' compression=all filesize=5G'||chr(10)
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir \'||chr(10)||
' dumpfile='||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_6_%U.dmp \'||chr(10)||
' logfile= '||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_6.log \'||chr(10)||
' tables= '||l.owner||'.'||l.table_name||' \'||chr(10)||
' query= '||l.owner||'.'||l.table_name||':'||''''||'"where mod(dbms_rowid.rowid_block_number(rowid),10)=6"'||''''||' \'||chr(10)||
' compression=all filesize=5G'||chr(10)
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir \'||chr(10)||
' dumpfile='||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_7_%U.dmp \'||chr(10)||
' logfile= '||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_7.log \'||chr(10)||
' tables= '||l.owner||'.'||l.table_name||' \'||chr(10)||
' query= '||l.owner||'.'||l.table_name||':'||''''||'"where mod(dbms_rowid.rowid_block_number(rowid),10)=7"'||''''||' \'||chr(10)||
' compression=all filesize=5G'||chr(10)
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir \'||chr(10)||
' dumpfile='||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_8_%U.dmp \'||chr(10)||
' logfile= '||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_8.log \'||chr(10)||
' tables= '||l.owner||'.'||l.table_name||' \'||chr(10)||
' query= '||l.owner||'.'||l.table_name||':'||''''||'"where mod(dbms_rowid.rowid_block_number(rowid),10)=8"'||''''||' \'||chr(10)||
' compression=all filesize=5G'||chr(10)
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir \'||chr(10)||
' dumpfile='||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_9_%U.dmp \'||chr(10)||
' logfile= '||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_9.log \'||chr(10)||
' tables= '||l.owner||'.'||l.table_name||' \'||chr(10)||
' query= '||l.owner||'.'||l.table_name||':'||''''||'"where mod(dbms_rowid.rowid_block_number(rowid),10)=9"'||''''||' \'||chr(10)||
' compression=all filesize=5G'||chr(10)
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name
union
select 'expdp enkitec/blueTech2016! directory=enkitec_dir \'||chr(10)||
' dumpfile='||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_0_%U.dmp \'||chr(10)||
' logfile= '||d.name||'.'||l.owner||'.'||l.table_name||'_LOB_0.log \'||chr(10)||
' tables= '||l.owner||'.'||l.table_name||' \'||chr(10)||
' query= '||l.owner||'.'||l.table_name||':'||''''||'"where mod(dbms_rowid.rowid_block_number(rowid),10)=0"'||''''||' \'||chr(10)||
' compression=all filesize=5G'||chr(10)
from dba_lobs l, dba_extents e, v$database d
where l.owner not in ('CTXSYS','APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
and l.segment_name=e.segment_name
having round(sum(e.bytes)/1024/1024/1024)>5
group by d.name, l.owner, l.table_name, l.segment_name;

-- Import statements to generate the SQL files from the metadata output
select 'impdp enkitec/blueTech2016! directory=enkitec_dir dumpfile='||name||'_full_meta.dmp sqlfile='||
name||'_full_meta' from v$database;
select 'impdp enkitec/blueTech2016! directory=enkitec_dir dumpfile='||d.name||'_'|| o.owner||'_schema_meta.dmp sqlfile='||d.name||'_'||o.owner||'_schema_meta'
from v$database d, dba_objects o
where o.owner not in ('APPQOSSYS','PUBLIC','OUTLN','SYSTEM','ORACLE_OCM','DBSNMP','SYS')
group by d.name, o.owner;

set echo on feedback on timing on

One thought on “DataPump Exports – semi-intelligently”

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s