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.
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
Nicely done, Stephen