Database 12c: What’s New with Data Pump? Lots.

Aug 20, 2013 / By Michael Abbey

Tags: , , , ,

This article will discuss some of the new stuff on board with Oracle Database 12c and one of our favorite tools: data pump. When Oracle Data Pump hit the streets, there was a veritable gold mine of opportunities to play with the new toy. Seasoned presenters such as yours truly embraced the new product. It was a nice marriage for attendees at tech presentations on this topic. They were hungry for new stuff, and these sessions provided fast-tracked learning. Sounds to me like a dream come true for all.

We will look at the following new parameters:

  • LOGGING
  • DISABLE_ARCHIVE_LOGGING (part of the TRANSFORM parameter)
  • ENCRYPTION_PWD_PROMPT
  • COMPRESSION_ALGORITHM

Let’s get started…

LOGGING

DBAs and other technical personnel thirst for answers to nagging questions:

  • How long is this going to take?
  • Does the time-to-market to complete a job grow at the same rate as the data volume?
  • Can we predict how long work will take based on past experiences?

With Oracle Database 12c, some of these questions can be addressed by a new parameter introduced in Oracle Data Pump – LOGGING. This command-line parameter can have four values:

  1. NONE: No timestamp information is displayed. (This is the default.)
  2. STATUS: Timestamp messages on status are displayed.
  3. LOGFILE: Same as STATUS, but only displayed for logfile messages.
  4. ALL: A combination of STATUS and LOGFILE.

Recently, we needed to copy a schema from development to production for a client, and one of the approaches we considered was data pump exp/imp. While the jobs were running, we leveraged one of the DBA’s best friends, V$SESSION_LONGOPS. Coupled with the information displayed based on the setting for the LOGGING command-line  parameter, we have more information at our fingertips. Is this a big deal on its own? Some may say it is, and to those who don’t: Remember that every enhancement rolled together with others becomes a big deal. Next is a quick look at a data pump export job with LOGGING set to ALL:

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/home/oracle> expdp full=y dumpfile=pythian_logging logtime=all

Export: Release 12.1.0.1.0 - Production on Fri Aug 16 18:11:24 2013

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Database Directory Object has defaulted to: "DPDUMP".
16-AUG-13 18:11:35.115: Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA full=y dumpfile=pythian_logging logtime=all 
16-AUG-13 18:11:36.703: Estimate in progress using BLOCKS method...
16-AUG-13 18:11:40.411: Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
16-AUG-13 18:11:41.966: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
16-AUG-13 18:11:43.494: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
16-AUG-13 18:11:48.396: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
16-AUG-13 18:11:48.594: Total estimation using BLOCKS method: 2.890 MB
16-AUG-13 18:11:49.255: Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
16-AUG-13 18:11:49.269: Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
16-AUG-13 18:11:49.489: Processing object type DATABASE_EXPORT/TABLESPACE
16-AUG-13 18:11:49.812: Processing object type DATABASE_EXPORT/PROFILE
16-AUG-13 18:11:49.855: Processing object type DATABASE_EXPORT/SYS_USER/USER
16-AUG-13 18:11:49.888: Processing object type DATABASE_EXPORT/SCHEMA/USER
16-AUG-13 18:11:49.957: Processing object type DATABASE_EXPORT/ROLE
16-AUG-13 18:11:49.991: Processing object type DATABASE_EXPORT/RADM_FPTM
16-AUG-13 18:11:50.471: Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
16-AUG-13 18:11:50.775: Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
16-AUG-13 18:11:50.786: Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
16-AUG-13 18:11:50.790: Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
16-AUG-13 18:11:50.796: Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
16-AUG-13 18:11:50.858: Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
16-AUG-13 18:11:50.887: Processing object type DATABASE_EXPORT/RESOURCE_COST
16-AUG-13 18:11:50.962: Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
16-AUG-13 18:11:51.030: Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
16-AUG-13 18:12:11.437: Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
16-AUG-13 18:12:20.503: Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
16-AUG-13 18:12:21.512: Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
16-AUG-13 18:12:23.077: Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
16-AUG-13 18:12:43.930: Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
16-AUG-13 18:12:48.049: Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
16-AUG-13 18:12:53.759: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
16-AUG-13 18:13:26.664: Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
16-AUG-13 18:13:50.085: Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
16-AUG-13 18:13:55.129: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
16-AUG-13 18:14:08.783: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
16-AUG-13 18:14:12.618: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
16-AUG-13 18:14:12.682: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
16-AUG-13 18:14:13.987: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
16-AUG-13 18:14:17.118: Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
16-AUG-13 18:14:17.141: Processing object type DATABASE_EXPORT/STATISTICS/MARKER
16-AUG-13 18:14:32.021: Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
16-AUG-13 18:14:33.141: Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
16-AUG-13 18:14:38.490: Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
16-AUG-13 18:14:38.911: Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
16-AUG-13 18:14:38.977: Processing object type DATABASE_EXPORT/AUDIT
16-AUG-13 18:14:39.201: Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
16-AUG-13 18:14:41.683: . . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.054 KB      36 rows
16-AUG-13 18:14:42.952: . . exported "ORDDATA"."ORDDCM_DOCS"                     252.9 KB       9 rows
16-AUG-13 18:14:43.235: . . exported "LBACSYS"."OLS$AUDIT_ACTIONS"               5.734 KB       8 rows
16-AUG-13 18:14:43.382: . . exported "LBACSYS"."OLS$DIP_EVENTS"                  5.515 KB       2 rows
16-AUG-13 18:14:43.400: . . exported "LBACSYS"."OLS$INSTALLATIONS"               6.937 KB       2 rows
16-AUG-13 18:14:43.444: . . exported "LBACSYS"."OLS$PROPS"                       6.210 KB       5 rows
16-AUG-13 18:14:43.487: . . exported "SYS"."DAM_CONFIG_PARAM$"                   6.507 KB      14 rows
16-AUG-13 18:14:43.529: . . exported "SYS"."TSDP_PARAMETER$"                     5.929 KB       1 rows
16-AUG-13 18:14:43.570: . . exported "SYS"."TSDP_POLICY$"                        5.898 KB       1 rows
16-AUG-13 18:14:43.618: . . exported "SYS"."TSDP_SUBPOL$"                        6.304 KB       1 rows
16-AUG-13 18:14:43.692: . . exported "SYSTEM"."REDO_DB"                          23.42 KB       1 rows
16-AUG-13 18:14:43.962: . . exported "WMSYS"."WM$ENV_VARS$"                      6.054 KB       5 rows
16-AUG-13 18:14:44.042: . . exported "WMSYS"."WM$EVENTS_INFO$"                   5.789 KB      12 rows
16-AUG-13 18:14:44.077: . . exported "WMSYS"."WM$HINT_TABLE$"                    9.429 KB      75 rows
16-AUG-13 18:14:44.124: . . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.351 KB       1 rows
16-AUG-13 18:14:44.170: . . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.960 KB       1 rows
16-AUG-13 18:14:44.225: . . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.08 KB       1 rows
16-AUG-13 18:14:44.274: . . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          6.539 KB       8 rows
16-AUG-13 18:14:44.281: . . exported "LBACSYS"."OLS$AUDIT"                           0 KB       0 rows
16-AUG-13 18:14:44.290: . . exported "LBACSYS"."OLS$COMPARTMENTS"                    0 KB       0 rows
16-AUG-13 18:14:44.296: . . exported "LBACSYS"."OLS$DIP_DEBUG"                       0 KB       0 rows
16-AUG-13 18:14:44.302: . . exported "LBACSYS"."OLS$GROUPS"                          0 KB       0 rows
16-AUG-13 18:14:44.308: . . exported "LBACSYS"."OLS$LAB"                             0 KB       0 rows
16-AUG-13 18:14:44.341: . . exported "LBACSYS"."OLS$LEVELS"                          0 KB       0 rows
16-AUG-13 18:14:44.351: . . exported "LBACSYS"."OLS$POL"                             0 KB       0 rows
16-AUG-13 18:14:44.359: . . exported "LBACSYS"."OLS$POLICY_ADMIN"                    0 KB       0 rows
16-AUG-13 18:14:44.366: . . exported "LBACSYS"."OLS$POLS"                            0 KB       0 rows
16-AUG-13 18:14:44.370: . . exported "LBACSYS"."OLS$POLT"                            0 KB       0 rows
16-AUG-13 18:14:44.410: . . exported "LBACSYS"."OLS$PROFILE"                         0 KB       0 rows
16-AUG-13 18:14:44.417: . . exported "LBACSYS"."OLS$PROFILES"                        0 KB       0 rows
16-AUG-13 18:14:44.424: . . exported "LBACSYS"."OLS$PROG"                            0 KB       0 rows
16-AUG-13 18:14:44.431: . . exported "LBACSYS"."OLS$SESSINFO"                        0 KB       0 rows
16-AUG-13 18:14:44.438: . . exported "LBACSYS"."OLS$USER"                            0 KB       0 rows
16-AUG-13 18:14:44.445: . . exported "LBACSYS"."OLS$USER_COMPARTMENTS"               0 KB       0 rows
16-AUG-13 18:14:44.452: . . exported "LBACSYS"."OLS$USER_GROUPS"                     0 KB       0 rows
16-AUG-13 18:14:44.458: . . exported "LBACSYS"."OLS$USER_LEVELS"                     0 KB       0 rows
16-AUG-13 18:14:44.466: . . exported "SYS"."AUD$"                                    0 KB       0 rows
16-AUG-13 18:14:44.472: . . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
16-AUG-13 18:14:44.480: . . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
16-AUG-13 18:14:44.486: . . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
16-AUG-13 18:14:44.494: . . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
16-AUG-13 18:14:44.500: . . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
16-AUG-13 18:14:44.507: . . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
16-AUG-13 18:14:44.513: . . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
16-AUG-13 18:14:44.518: . . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
16-AUG-13 18:14:44.522: . . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
16-AUG-13 18:14:44.529: . . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
16-AUG-13 18:14:44.534: . . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows
16-AUG-13 18:14:44.540: . . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows
16-AUG-13 18:14:44.544: . . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows
16-AUG-13 18:14:44.549: . . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows
16-AUG-13 18:14:44.554: . . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows
16-AUG-13 18:14:44.559: . . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows
16-AUG-13 18:14:44.566: . . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows
16-AUG-13 18:14:44.571: . . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows
16-AUG-13 18:14:44.579: . . exported "WMSYS"."WM$REMOVED_WORKSPACES_TABLE$"          0 KB       0 rows
16-AUG-13 18:14:44.590: . . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows
16-AUG-13 18:14:44.597: . . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows
16-AUG-13 18:14:44.604: . . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows
16-AUG-13 18:14:44.611: . . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows
16-AUG-13 18:14:44.618: . . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows
16-AUG-13 18:14:44.624: . . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows
16-AUG-13 18:14:44.631: . . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows
16-AUG-13 18:14:44.639: . . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows
16-AUG-13 18:14:44.645: . . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows
16-AUG-13 18:14:47.053: . . exported "SYSTEM"."SCHEDULER_JOB_ARGS"               8.640 KB       4 rows
16-AUG-13 18:14:48.309: . . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           10.18 KB      22 rows
16-AUG-13 18:14:49.731: . . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.929 KB       2 rows
16-AUG-13 18:14:51.556: . . exported "SYS"."NACL$_ACE_EXP"                       9.906 KB       1 rows
16-AUG-13 18:14:52.456: . . exported "SYS"."NACL$_HOST_EXP"                      6.890 KB       1 rows
16-AUG-13 18:14:53.929: . . exported "WMSYS"."WM$EXP_MAP"                        7.695 KB       3 rows
16-AUG-13 18:14:54.029: . . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
16-AUG-13 18:14:54.036: . . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
16-AUG-13 18:14:54.042: . . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
16-AUG-13 18:14:54.048: . . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
16-AUG-13 18:14:54.177: . . exported "SCOTT"."DEPT"                                  6 KB       4 rows
16-AUG-13 18:14:54.225: . . exported "SCOTT"."EMP"                                8.75 KB      14 rows
16-AUG-13 18:14:54.267: . . exported "SCOTT"."SALGRADE"                          5.929 KB       5 rows
16-AUG-13 18:14:54.274: . . exported "SCOTT"."BONUS"                                 0 KB       0 rows
16-AUG-13 18:14:56.693: Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
16-AUG-13 18:14:56.709: ******************************************************************************
16-AUG-13 18:14:56.710: Dump file set for SYS.SYS_EXPORT_FULL_01 is:
16-AUG-13 18:14:56.715:   /u01/app/oracle/dpdump/pythian/pythian_logging.dmp
16-AUG-13 18:14:56.749: Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Fri Aug 16 18:14:56 2015 elapsed 0 00:03:25

The next snippet from a PYTHIAN user export offers an idea of where the power of this parameter may lie – discovering exactly where time is being spent for large and small schema objects:

16-AUG-13 18:28:12.983: Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
16-AUG-13 18:30:15.652: . . exported "PYTHIAN"."PMAST"                           2.384 GB 23252224 rows
16-AUG-13 18:30:16.388: . . exported "PYTHIAN"."LOC"                             21.01 MB  199999 rows
16-AUG-13 18:30:18.194: Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

DISABLE_ARCHIVE_LOGGING

This is one of the many options available with the TRANSFORM parameter to data pump import. This parameter may indeed be a dream come true for those very large datasets. Sometimes, the archived redo generated by import detracts from its speed and leaves many wondering why archived redo needs to be generated. Some of the more familiar TRANSFORM options that have been around since the dawn of the product are:

  • OID is used to force the assignment of new IDs for objects in the export file and not to attempt to reuse IDs during the import phase. The OID in the export file may clash with an ID in an existing object in the target schema, causing the object to be skipped.
  • SEGMENT_ATTRIBUTES is used to permit the placement of objects in a different tablespace from where they were exported. The physical, storage, and logging attributes of objects are ignored, and they inherit the characteristics as set for the target schema(s).
  • PCTSPACE is specified as a multiplier to be used for object extent requests and datafile sizes.

The DISABLE_ARCHIVE_LOGGING can be set globally or for indexes and/or tables tables. If set to Y, the logging attributes of the specified target are altered before it is imported. Then, they are reset to their original characteristics when the work completes. The parameter passed on the command-line can have three values:

  1. transform=disable_archive_logging:Y
  2. transform=disable_archive_logging:Y:table
  3. transform=disable_archive_logging:Y:index

All import activities are logged unless one of the three options listed above is coded in the call to data pump import. The following listing illustrates the usage and output to completely disable a generation of archived redo.

 
oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/u01/app/oracle/dpdump/pythian> impdp dumpfile=pythian.dmp table_exists_action=append schemas=pythian transform=disable_archive_logging:Y

Import: Release 12.1.0.1.0 - Production on Sun Aug 18 05:47:14 2013

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Database Directory Object has defaulted to: "DPDUMP".
Master table "SYS"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_03":  /******** AS SYSDBA dumpfile=pythian.dmp table_exists_action=append schemas=pythian transform=disable_archive_logging:Y 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"PYTHIAN" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "PYTHIAN"."LOC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "PYTHIAN"."PMAST" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

ENCRYPTION_PWD_PROMPT

As we have heard so many times but for some reason seem to experience difficulty putting into practice: “Thou shalt not enter an encryption password on the command-line.” This new-fangled parameter can force entry of this parameter manually when prompted. A data pump export of PYTHIAN.LOC with this parameter set to YES would proceed as follows:

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/u01/app/oracle/dpdump/pythian> expdp dumpfile=pythian_loc.dmp tables=pythian.loc encryption_pwd_prompt=yes

Export: Release 12.1.0.1.0 - Production on Sun Aug 18 05:55:04 2013

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Encryption Password:
Database Directory Object has defaulted to: "DPDUMP".
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=pythian_loc.dmp tables=pythian.loc encryption_pwd_prompt=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 50 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "PYTHIAN"."LOC"                             42.00 MB  399998 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/dpdump/pythian/pythian_loc.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sun Aug 18 05:55:51 2013 elapsed 0 00:00:41

The ensuing import of that very same export would resemble the following (mistyped password entry deliberate):

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/u01/app/oracle/dpdump/pythian> impdp dumpfile=pythian_loc.dmp table_exists_action=replace tables=pythian.loc encryption_pwd_prompt=yes

Import: Release 12.1.0.1.0 - Production on Sun Aug 18 05:59:33 2013

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Encryption Password: 
Database Directory Object has defaulted to: "DPDUMP".
ORA-39002: invalid operation
ORA-39176: Encryption password is incorrect.

It would have been nice if it had asked for encryption password twice on export. Then it may not have been so “easy” for me to forget. Let’s try that again…

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/u01/app/oracle/dpdump/pythian> impdp dumpfile=pythian_loc.dmp table_exists_action=replace tables=pythian.loc encryption_pwd_prompt=yes

Import: Release 12.1.0.1.0 - Production on Sun Aug 18 06:06:19 2013

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Encryption Password: 
Database Directory Object has defaulted to: "DPDUMP".
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA dumpfile=pythian_loc.dmp table_exists_action=replace tables=pythian.loc encryption_pwd_prompt=yes 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PYTHIAN"."LOC"                             42.00 MB  399998 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sun Aug 18 06:06:55 2013 elapsed 0 00:00:30

One of the first questions I asked myself is how does/could this possibly work for unattended jobs where the parameter value is passed as YES:

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/u01/app/oracle/dpdump/pythian> nohup impdp parfile=locimp.parfile &
[1] 24311

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/u01/app/oracle/dpdump/pythian> nohup: ignoring input and appending output to `nohup.out'

[1]+  Exit 1                  nohup impdp parfile=locimp.parfile

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/u01/app/oracle/dpdump/pythian> cat n*out

Import: Release 12.1.0.1.0 - Production on Sun Aug 18 06:09:33 2013

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Encryption Password: 
ORA-39001: invalid argument value
ORA-39207: Value NULL is invalid for parameter ENCRYPTION_PASSWORD.

Maybe I’ll give Chuck a call and ask him to submit an enhancement request.

COMPRESSION_ALGORITHM

This enhancement is all about trade-offs, measuring resource consumption against compression ratio. Like many life experiences, you cannot have the two together. In other words, the higher the compression ratio, the more CPUs are required to pull it off. The values for this parameter are as follows:

  • BASIC offers the most efficient usage of CPU and effective compression ratio; it is deemed to be applicable to most sessions.
  • LOW favors size in comparison to CPU and yields a larger file size with a lower compression ratio.
  • MEDIUM is similar to BASIC. It uses a different algorithm as it performs the work at hand.
  • HIGH is a good choice when the size of the export file is the determining factor; on the source site, it yields the smallest file, but it could be the most CPU-intensive.

The Advanced Compression option must be installed to use this data pump parameter. The familiar dialogue with data pump export goes as follows:

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/home/oracle> expdp dumpfile=pythian_pmast.dmp tables=pythian.pmast compression_algorithm=high

Export: Release 12.1.0.1.0 - Production on Mon Aug 19 13:58:43 2013

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=pythian_pmast.dmp tables=pythian.pmast compression_algorithm=high 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11.03 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "PYTHIAN"."PMAST"                           9.536 GB 93008896 rows
Master table "SYS"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_04 is:
  /u01/app/oracle/dpdump/pythian/pythian_pmastlc.dmp
Job "SYS"."SYS_EXPORT_TABLE_04" successfully completed at Mon Aug 19 14:25:45 2013 elapsed 0 00:08:20

real    8m31.600s
user    0m0.017s
sys     0m0.026s

Export: Release 12.1.0.1.0 - Production on Mon Aug 19 14:25:49 2013

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Database Directory Object has defaulted to: "DPDUMP".
Starting "SYS"."SYS_EXPORT_TABLE_04":  /******** AS SYSDBA dumpfile=pythian_pmasthc.dmp tables=pythian.pmast compression_algorithm=high 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11.03 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "PYTHIAN"."PMAST"                           9.536 GB 93008896 rows
Master table "SYS"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_04 is:
  /u01/app/oracle/dpdump/pythian/pythian_pmasthc.dmp
Job "SYS"."SYS_EXPORT_TABLE_04" successfully completed at Mon Aug 19 14:36:31 2013 elapsed 0 00:07:52

real    10m45.764s
user    0m0.018s
sys     0m0.028s

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/home/oracle> ll /u01/app/oracle/dpdump/pythian
total 20019600
-rw-r-----. 1 oracle oinstall 10240008192 Aug 19 14:36 pythian_pmasthc.dmp
-rw-r-----. 1 oracle oinstall 10240008192 Aug 19 14:25 pythian_pmastlc.dmp

Not surprisingly, since a relatively small amount of data as exported (a mere 11.03Gb of data), the difference in the export file sizes is not dramatic. The fact that the export with high compression took close to 25% longer and consumed close to 8% more “sys” time is not dramatic, but it offers a flavor of what this parameter can do for you.

Wrap-up

Many fondly remember the arrival of data pump with release 10gR1. We tingled with this new-fangled tool to allow us to perform the ever-popular logical backup of Oracle database. We discovered the foundation of one of the underlying PL/SQL objects called DBMS_DATAPUMP and had an absolute field day as we rappelled into the depths of the product. Bear in mind: The trip is not over.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>