data pump tips, techniques and tricks - · pdf filedata pump tips, techniques and tricks biju...

Data Pump Tips, Techniques and Tricks

Biju ThomasOneNeck IT Solutions

April 2016

@biju_thomas

©2014 TDS Hosted & Managed Services, LLC. All rights reserved. All other trademarks are the property of their respective owners.

• Oracle ACE Director

• Principal Solutions Architect with OneNeck® IT Solutions

• More than 20 years of Oracle database administration experience and over 7 years of E-Business Suite experience

• Author of Oracle12c OCA, Oracle11g OCA, co-author of Oracle10g, 9i, 8i certification books published by Sybex/Wiley.

• Presentations, Articles, Blog, Tweet

• www.oneneck.comwww.bijoos.com

Me!

http://www.oneneck.com/

http://www.bijoos.com/

©2014 OneNeck IT Solutions LLC. All rights reserved. All other trademarks are the property of their respective owners.3

• Legacy tool – exp / imp• Available since Oracle 5 days

• 12c supports exp / imp

• Less flexibility, newer object types not supported

• New features in Oracle Database 10g and later releases are not supported

• Client side tool – dump file created on the client machine

• Data Pump tool – expdp / impdp• Introduced in 10g

• Each major version of database comes with multiple enhancements

• Granular selection of objects or data to export / import

• Jobs can be monitored, paused, stopped, restarted

• Server side tool – dump file created on the DB server

• PL/SQL API – DBMS_DATAPUMP, DBMS_METADATA

Export Import Tools - Introduction


• Am I writing the export dump file to a directory? If yes, do I have enough space to save the dump file ?

• Is there a directory object exist pointing to that directory? Or is my default DATA_PUMP_DIR directory location good enough to save the export dump file?

• Can I skip writing the dump file and copy the objects directly using the network link feature of Data Pump, utilizing a database link?

• What options or parameters should I use to do the export and import?

• Is the characterset same or compatible between the databases where export/import is performed?

• How do I estimate the size needed for the dump file?

Copying Objects using Export…


• DIRECTORY (default DATA_PUMP_DIR)

• DUMPFILE (default expdat.dmp)

• LOGFILE (default export.log)

• FULL

• SCHEMAS (default login schema)

• TABLES

• EXCLUDE

• INCLUDE

• CONTENT =[ALL | DATA_ONLY | METADATA_ONLY]

Common Parameters

Mutually Exclusive

Mutually Exclusive


• ESTIMATE_ONLY=Y

• Gives the expected export size of the tables in the dump file.

Estimating Space Requirement


• Copy a table from one database to another. • TABLES=

• Copy a schema from one database to another, or to duplicate schema objects under another user in the same database. • SCHEMAS=

• Copy the entire database, usually performed for platform migration or to backup. • FULL=Y

• Tablespace mode: you can specify the tablespace names and only objects belonging to the tablespace are exported. The dependent objects, even if they belong to another tablespace are exported. • TABLESPACES=

• Transport tablespace mode: only the metadata for the tables and their dependent objects within a specified set of tablespaces is exported. This mode is used to copy a tablespace physically (by copying the data files belonging to the tablespace) from one tablespace to another. • TRANSPORT_TABLESPACES=

Export / Import Modes


• Problem: cannot qualify the database link with a schema name to create the database link.

• Use INCLUDE parameter to export only database links

• The database links for the whole database can be saved to a dump file using

• $ expdp dumpfile=uatdblinks.dmp directory=clone_save

full=y include=db_link userid =\"/ as sysdba\"

• If you are interested only in the private database links belonging few schema names

• $ expdp dumpfile=uatdblinks.dmp directory=clone_save

schemas=APPS,XX,XXL include=db_link userid =\"/ as

sysdba\"

Exporting Database Links


• Full database export/import

• DATABASE_EXPORT_OBJECTS.

• Schema level export/import

• SCHEMA_EXPORT_OBJECTS

• Table level or tablespace level export/import

• TABLE_EXPORT_OBJECTS

Valid INCLUDE & EXCLUDE Values

Looking for valid options to exclude constraints.SQL> SELECT object_path, comments

FROM database_export_objectsWHERE object_path like '%CONSTRAINT%'AND object_path not like '%/%';

OBJECT_PATHCOMMENTS---------------------------------------------------CONSTRAINTConstraints (including referential constraints)

REF_CONSTRAINTReferential constraints


• The syntax of EXCLUDE and INCLUDE parameter allows a name clause along with the object type. The name clause evaluates the expression.• INCLUDE=TABLE:EMPLOYEES

• INCLUDE=PROCEDURE

• Export only the foreign key constraints belonging to HR schema.• schemas=HR

• include='REF_CONSTRAINT'

• Export full database, except HR and SH schemas. Notice the use of IN expression. • full=y

• exclude=schema:"in ('HR','SH')"

• Export all tables in the database that end with TEMP and do not belong to SYSTEM and APPS schemas. Remember, we cannot use both INCLUDE and EXCLUDE at the same time.• full=y

• include=table:"IN (select table_name from dba_tables

where table_name like '%TEMP' and owner not in

('SYSTEM', 'APPS'))"

More on INCLUDE / EXCLUDE


• The EXCLUDE and INCLUDE parameters are very flexible and powerful, and they accept a subquery instead of hard coded values.

• Export only the public database links.

• full=y

• include=db_link:"IN (SELECT db_link from

dba_db_links Where Owner = 'PUBLIC')"

• Export public synonyms defined on the schema HR

• full=y

• include=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT

synonym_name from dba_synonyms Where Owner =

'PUBLIC' and table_owner = 'HR')"

Exporting PUBLIC Objects


• Use the impdp import with SQLFILE parameter, instead of doing the actual import, the metadata from the dump file is written to the file name specified in SQLFILE parameter.

• $ impdp dumpfile=pubdblinks.dmp directory=clone_save

full=y sqlfile=testfile.txt

• If you are not interested in the rows, make sure you specify CONTENT=METADATA_ONLY

• Validate the full export did exclude HR and SH, use the impdp with SQLFILE. We just need to validate if there is a CREATE USER statement for HR and SH users. INCLUDE=USER only does the user creation, no schema objects are imported.

• sqlfile=validate.txt

• include=USER

Verifying Content of Dump File


• Public Database Link Export Dump File Content

$ cat testfile.txt

-- CONNECT SYS

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';





ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK

CREATE PUBLIC DATABASE LINK "CZPRD.BT.NET"

USING 'czprd';

CREATE PUBLIC DATABASE LINK "ERP_ARCHIVE_ONLY_LINK.BT.NET"

CONNECT TO "AM_STR_HISTORY_READ" IDENTIFIED BY VALUES '05EDC7F2F211FF3A79CD5A526EF812A0FCC4527A185146A206C88098BB1FF8F0B1'

USING 'ILMPRD1';

Example: SQLFILE Text


• QUERY parameter

• Specify a WHERE clause

• SAMPLE parameter

• Specify percent of rows

• VIEWS_AS_TABLES parameter

• Create a view to extract required data

• Export view as table

Exporting subset of Data


• QUERY parameter

• The WHERE clause you specify in QUERY is applied to all tables included in the export

• Multiple QUERY parameters are permitted, restrict QUERY to a single table allowed.

• schemas=GL

• query=GL_TRANSACTIONS:"where account_date >

add_months(sysdate, -6)"

• query=GL.GL_LINES:" where transaction_date >

add_months(sysdate, -6)"

Using QUERY Parameter


• We want to apply the query condition to all tables that end with TRANSACTION in GL schema. Perform two exports.

• First:

• schemas=GL

• include=table:"like '%TRANSACTION'"

• query="where account_date > add_months(sysdate, -6)"

• Second:

• schemas=GL

• exclude=table:"like '%TRANSACTION'"

QUERY Example


• Specify a percentage of rows to export.

• Similar to QUERY, you can limit the sample condition to just one table or to the entire export.

• Example: All the tables in GL schema will be exported, for GL_TRANSACTIONS only 20 percent of rows exported and for GL_LINES only 30 percent of rows exported.

• schemas=GL

• sample=GL_TRANSACTIONS:20

• sample=GL.GL_LINES:30

Using SAMPLE Parameter


• Good way to export subset of data, if the data set is based on more than one table or complex joins.

• views_as_tables=scott.emp_dept

Using VIEWS_AS_TABLES parameter

• Import to another schema in same database remap_schema=scott:mike


• Data Pump import is very flexible to change the properties of objects during import.

• Partitioned table SALES_DATA owned by SLS schema exported using Data Pump exp. During import, need to create the table as non-partitioned under SLS_HIST schema.• REMAP_SCHEMA=SLS:SLS_HIST

• TABLES=SLS.SALES_DATA

• PARTITION_OPTIONS=MERGE

• Partitioned table SALES_DATA owned by SLS schema exported using Data Pump. During import, need to create separate non-partitioned table for each partition of SALES_DATA. • PARTITION_OPTIONS=DEPARTITION

• TABLES=SLS.SALES_DATA

• Since the partition names in SALES_DATA are SDQ1, SDQ2, SDQ3, SDQ4, the new table names created would be SALES_DATA_SDQ1, SALES_DATA_SDQ2, SALES_DATA_SDQ3, SALES_DATA_SDQ4.

Changing Table Partition Properties


• Employee table (HR.ALL_EMPLOYEES) contain the social security number of employee along with other information. When copying this table from production to development database, you want to scramble the social security number, or mask it or whatever a function return value can be.

• First create a package and a function that returns the scrambled SSN when the production SSN is passed in as a parameter. Let’s call this procedure HR_UTILS.SSN_SCRAMBLE.

• During import use the REMAP_DATA parameter change the data value.

TABLES=HR.ALL_EMPLOYEES

REMAP_DATA=HR.ALL_EMPLOYEES.SSN:HR.HR_UTILS.SSN_SCRAMBLE

• If you want to secure the dump file, it may be appropriate to use this function and parameter in the export itself, thus the dump file will not have SSN, instead the scrambled numbers only.

Data Masking


• Sometimes you have to bring back a table from export backup to the same database, but with a different name. Here table DAILY_SALES is imported to the database as DAILY_SALES_031515. The export dump is daily SALES schema export. The original table is on tablespaceSALES_DATA, the restored table need to go on tablespace SALES_BKUP.

TABLES=SALES.DAILY_SALES

REMAP_TABLE=SALES.DAILY_SALES:DAILY_SALES_031515

REMAP_TABLESPACE:SALES_DATA:SALES_BKUP

Different Table Name, Tablespace


• If you do not wish import to bring any of the attributes associated with a table or index segment, and have the object take the defaults for the user and the destination database/tablespace, you could do so easily with a transform option. • TRANSFORM=SEGMENT_ATTRIBUTES TABLESPACE, STORAGE

• TRANSFORM=STORAGE STORAGE

• In the example, SALES schema objects are imported, but the tablespace, storage attributes and logging clause all will default to what is defined for the user MIKE and his default tablespace. • SCHEMAS=SALES

• REMAP_SCHEMA=SALES:MIKE

• TRANSFORM=SEGMENT_ATTRIBUTES:N

• In the above example, if you would like to keep the original tablespace but only want to remove the storage attributes for object type index, you could specify the optional object along with the TRANSFORM parameter. • SCHEMAS=SALES

• REMAP_SCHEMA=SALES:MIKE

• TRANSFORM=SEGMENT_ATTRIBUTES:N:TABLE

• TRNASFORM=STORAGE:Y:INDEX

Ignore Storage Attributes


• If you are familiar with exp/imp, the same parameters may be used with expdp/impdp

Data Pump Legacy Mode

CONSISTENT FLASHBACK_TIME

FEEDBACK STATUS=30

GRANTS=N EXCLUDE=GRANT

INDEXES=N EXCLUDE=INDEX

OWNER SCHEMAS

ROWS=N CONTENT=METADATA_ONLY

TRIGGERS=N EXCLUDE=TRIGGER

IGNORE=Y TABLE_EXISTS_ACTION=APPEND

INDEXFILE SQLFILE

TOUSER REMAP_SCHEMA


• METRICS=YES

• The number of objects and the elapsed time are recorded in the Data Pump log file.

• LOGTIME=[NONE | STATUS | LOGFILE | ALL]

• Specifies that messages displayed during export/import operations be time stamped.

• Use the timestamps to figure out the elapsed time between different phases of a Data Pump operation.

Tracking Time


• VERSION=[COMPATIBLE | LATEST | version_string]

• The VERSION parameter allows to identify the version of objects.

Import to Lower Version


• Clean out orphaned data pump jobs

• Check DBA_DATAPUMP_JOBS, STATE would be NOT RUNNING

• Master table name is JOB_NAME owned by the user initiating the data pump export or import

• Just drop the master table

• If jobs are still left in DBA_DATAPUMP_JOBS

• DBMS_DATAPUMP.ATTACH

• DBMS_DATAPUMP.STOP_JOB

Clean Data Pump Jobs


Add additional dump files. ADD_FILE

Exit interactive mode and enter logging mode. CONTINUE_CLIENT

Stop the export client session, but leave the job running. EXIT_CLIENT

Redefine the default size to be used for any subsequent dump

files.

FILESIZE

Display a summary of available commands. HELP

Detach all currently attached client sessions and terminate the

current job.

KILL_JOB

Increase or decrease the number of active worker processes for

the current job. This command is valid only in the Enterprise

Edition of Oracle Database 11g or later.

PARALLEL

Restart a stopped job to which you are attached. START_JOB

Display detailed status for the current job and/or set status interval. STATUS

Stop the current job for later restart. STOP_JOB

Interactive Mode Commands

http://docs.oracle.com/database/121/SUTIL/GUID-1A2BF459-680D-4D2B-B35F-9A3F4EE72530.htm

http://docs.oracle.com/database/121/SUTIL/GUID-64B5616E-7A7A-4012-AA9C-CA89A12846D5.htm

http://docs.oracle.com/database/121/SUTIL/GUID-A6C2B758-2832-461F-8B3A-600D1CBB465A.htm

http://docs.oracle.com/database/121/SUTIL/GUID-3A52BB37-BFA8-43E8-897B-515EA991B337.htm

http://docs.oracle.com/database/121/SUTIL/GUID-15BE7A31-7944-4DD7-BA21-7EAA5A682ED1.htm

http://docs.oracle.com/database/121/SUTIL/GUID-9DA12603-B6FA-4631-8DFA-B75466CAF178.htm

http://docs.oracle.com/database/121/SUTIL/GUID-6097E6AE-CC65-4012-8A06-F00585357000.htm

http://docs.oracle.com/database/121/SUTIL/GUID-2565CDB9-27D4-435A-86A1-9D8835BA5826.htm

http://docs.oracle.com/database/121/SUTIL/GUID-9582CC68-6ACC-49C0-9511-010643161725.htm

http://docs.oracle.com/database/121/SUTIL/GUID-51E3C354-0ACE-448F-933E-5EBD9D0B151C.htm


• OPEN: To define an export or import job

• ADD_FILE: To define the dump file name and log file name

• METADATA_FILTER: provide the exclude or include clauses here

• START_JOB: kick off the export or import job

• WAIT_FOR_JOB: wait until the job completes

• DETACH: close the job.

DBMS_DATAPUMP


Example – Using PL/SQL API

CREATE OR REPLACE PROCEDURE XX_BACKUP_TABLES (

schema_name VARCHAR2 DEFAULT USER)

IS

/***************************************************

NAME: XX_BACKUP_TABLES

PURPOSE: Backup tables under schema

****************************************************/

BEGIN

-- Export all the tables belonging to schema to file system

DECLARE

dpumpfile NUMBER;

dpumpstatus VARCHAR2 (200);

tempvar VARCHAR2 (200);

BEGIN

EXECUTE IMMEDIATE

'CREATE OR REPLACE DIRECTORY BACKUP_DIR AS

''/u01/app/oracle/expdp''';

BEGIN

-- verify if the Data Pump job table exist.

-- drop if exist.

SELECT table_name

INTO tempvar

FROM user_tables

WHERE table_name = 'BACKUP_TABLE_EXP';

EXECUTE IMMEDIATE 'DROP TABLE BACKUP_TABLE_EXP';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

NULL;

END;

-- define job

dpumpfile :=

DBMS_DATAPUMP.open (OPERATION => 'EXPORT',

JOB_MODE => 'SCHEMA',

JOB_NAME => 'BACKUP_TABLE_EXP');

-- add dump file name

DBMS_DATAPUMP.add_file (

HANDLE => dpumpfile,

FILENAME => 'BACKUP_tabs.dmp',

DIRECTORY => 'BACKUP_DIR',

FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,

REUSEFILE => 1);

-- add log file name

DBMS_DATAPUMP.add_file (

HANDLE => dpumpfile,

FILENAME => 'BACKUP_tabs.log',

DIRECTORY => 'BACKUP_DIR',

FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

-- add filters to export only one schema

DBMS_DATAPUMP.metadata_filter (HANDLE => dpumpfile,

NAME => 'SCHEMA_LIST',

VALUE => schema_name);

-- start the export job

DBMS_DATAPUMP.start_job (HANDLE => dpumpfile);

-- wait until the job completes

DBMS_DATAPUMP.wait_for_job (HANDLE => dpumpfile, JOB_STATE =>

dpumpstatus);

-- close the job

DBMS_DATAPUMP.detach (HANDLE => dpumpfile);

EXCEPTION

WHEN OTHERS

THEN

DBMS_DATAPUMP.detach (HANDLE => dpumpfile);

RAISE;

END;

END XX_BACKUP_TABLES;

/


• Generate DDL scripts using DBMS_METADATA

• Example:

• https://oracle-base.com/dba/script_creation/user_ddl.sql

DBMS_METADATA

select dbms_metadata.get_ddl('USER',

u.username) AS ddlfrom dba_users u

where u.username = :v_username

union all

select

dbms_metadata.get_granted_ddl('TABL

ESPACE_QUOTA', tq.username) AS ddl

from dba_ts_quotas tq

where tq.username = :v_username

and rownum = 1

union all

select

dbms_metadata.get_granted_ddl('ROLE

_GRANT', rp.grantee) AS ddlfrom dba_role_privs rp

where rp.grantee = :v_username

and rownum = 1

union all

select

dbms_metadata.get_granted_ddl('SYST

EM_GRANT', sp.grantee) AS ddlfrom dba_sys_privs sp

where sp.grantee = :v_username

and rownum = 1

union all

select

dbms_metadata.get_granted_ddl('OBJECT_G

RANT', tp.grantee) AS ddlfrom dba_tab_privs tp

where tp.grantee = :v_username

and rownum = 1

union all

select

dbms_metadata.get_granted_ddl('DEFAULT_

ROLE', rp.grantee) AS ddlfrom dba_role_privs rp

where rp.grantee = :v_username

and rp.default_role = 'YES'

and rownum = 1

union all

select dbms_metadata.get_ddl('PROFILE',

u.profile) AS ddlfrom dba_users u

where u.username = :v_username

and u.profile <> 'DEFAULT'

https://oracle-base.com/dba/script_creation/user_ddl.sql


• DBA_DATAPUMP_JOBS

• Shows a summary of all active Data Pump jobs on the system.

• DBA_DATAPUMP_SESSIONS

• Shows all sessions currently attached to Data Pump jobs.

• V$SESSION_LONGOPS

• Shows progress on each active Data Pump job.

Dictionary Views


• Collect DBMS_STATS.GATHER_DICTIONARY_STATS before a Data Pump job for better performance, if dictionary stats is not collected recently.

• No Logging Operation during import

• TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

• The objects are created by the Data Pump in NOLOGGING mode, and are switched to its appropriate LOGGING or NOLOGGING once import completes. If your database is running in FORCE LOGGING mode, this Data Pump no logging will have no impact.

• Estimate using STATISTICS method (BLOCKS is more accurate)

• Exclude statistics and Collect statistics after import

• EXCLUDE=STATISTICS

• On RAC database use CLUSTER=N

• Also, set PARALLEL_FORCE_LOCAL=TRUE

Performance Improvement Considerations


• Avoid using REMAP_*

• Bigger STREAMS_POOL_SIZE

• Parallelizing Data Pump jobs using PARALLEL parameter

• Impact of PARALLEL in impdp

• First worker – metadata tablespaces, schema & tables

• Parallel workers load table data

• One worker again loads metadata, till package spec

• Parallel workers load package bodies

• One worker loads remaining metadata

• For bigger jobs, try to exclude INDEX in full export/import, and create indexes immediately after the table import completes using script, may be multiple sessions.

Performance Improvement Considerations


• Want to change database characterset during upgrade.

• Considering platform migration.

• You archived or purged large amounts of data. Utilize the upgrade/migration opportunity reduce the size of the database.

• In Oracle database 12c you want to use multitenant architecture by combining multiple 10g and 11g databases.

• If the database you are upgrading from is older than 10g, then there is no Data Pump available. You will have to use the legacy exp/imp tool. The good news is that the exp/imp tools are still supported in 12c database.

Upgrading Database – Why Data Pump


Thank you!

Daily #oratidbit on Facebook and Twitter. Follow me!

Tweets: @biju_thomas

Facebook:

facebook.com/oraclenotes

Blog: bijoos.com/oraclenotes

https://www.facebook.com/hashtag/oratidbit

https://twitter.com/hashtag/oratidbit

data pump tips, techniques and tricks - · pdf filedata pump tips, techniques and tricks biju...

Documents