12c New Feature: Datapump enhancements


transport

Also Datapump has some new interesting New Feature which are usefull for tranporting your data.

Creating SecureFile LOBs During Import

You can now specify the LOB Storage during import

Command line example:

impdp scott/tiger DIRECTORY=dpump1 DUMPFILE=export.dmp LOB_STORAGE:SECUREFILE

Compressing Tables During Import

You can now specify a Compression method during import

Command line example:

impdp scott/tiger DIRECTORY=dpump1 DUMPFILE=export.dmp TRANSFORM=TABLE_COMPRESSION_CLAUSE:"COMPRESS FOR OLTP"

Specifying the Encryption Password

You can now specify silently a password during runtime. When you import a encrypted Dumpfile you can pass the Password for that file from the STDIN. The password will not visible by commands like ps or will not be stored in scripts.

Command line example:

expdp scott/tiger DIRECTORY=dpump1 DUMPFILE=export.dmp ENCRYPTION_PWD_PROMPT=Y

Exporting Views as Tables

You can now export a View as a table.

Command line example:

expdp scott/tiger DIRECTORY=dpump1 DUMPFILE=scott1.dmp VIEWS_AS_TABLES=empview tables=emp

Export: Release 12.1.0.1.0 - Production on Sat Jul 13 13:00:55 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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** DIRECTORY=dpump1 DUMPFILE=scott1.dmp VIEWS_AS_TABLES=empview tables=emp 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 80 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SCOTT"."EMP"                               8.757 KB      14 rows
. . exported "SCOTT"."EMPVIEW"                           8.765 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /tmp/scott1.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jul 13 13:01:55 2013 elapsed 0 00:00:5

Disabling Logging for Oracle Data Pump Import

You can now use the DISABLE_ARCHIVE_LOGGING Paramaeter to disable logging for table, index or both during import. Logging is not completely disable but only a small  amount is generated. Also don`t forget that there is a database parameter FORCE LOGGING which overwrites this feature.

Command Line example:

impdp scott/tiger DIRECTORY=dpump1 DUMPFILE=scott1.dmp  schemas=scott TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
impdp scott/tiger DIRECTORY=dpump1 DUMPFILE=scott1.dmp  schemas=scott TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX
impdp scott/tiger DIRECTORY=dpump1 DUMPFILE=scott1.dmp  schemas=scott TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y TRANSFORM=DISABLE_ARCHIVE_LOGGING:N:TABLE

Full Transportable Export/Import

This feature is usefull for example:

  • Upgrading to a new release of Oracle Database (Transport a 11.2.0.3 into a non CDB or a PDB)
  • Transporting a database to a new computer system (Tranport a PDB into a other PDB)
  • Transporting a non-CDB into a non-CDB or CDB

Command line example:

expdp scott/tiger FULL=y DUMPFILE=fullexp.dmp DIRECTORY=dpump1 TRANSPORTABLE=always VERSION=12.0 LOGFILE=export.log
impdp scott/tiger FULL=y DUMPFILE=fullexp.dmp DIRECTORY=dpump1 TRANSPORT_DATAFILES='/u01/app/oracle/oradata/db1211/users01.dbf' LOGFILE=import.log

The necessary steps:

  1. Set your Tablespaces read only
  2. Export the database with the transportable parameter
  3. Endian conversion if needed
  4. Transfer your datafiles and the dumpfile
  5. Import the database with the TRANSPORT_DATAFILES Parameter

Note: You can also use the network_link Parameter to import directly in target database.

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s