12c, backup/recovery, data pump

Oracle 12c – Import data pump

Leave a comment

IMPDP utility is used to read in the contents of an EXPDP dump file.   It can be run in three modes:

  • Command-line – Enables you to specify most of the export parameters
  • Parameter File – Allows you to specify command line parameters in a PARFILE
  • Interactive-Command – Displays an import prompt and you enter various commands.

IMPDP utility can accept the following parameters during execution:

  • ABORT_STEP – Stop the job after it is initialized or at the indicated object.Valid values are -1 or N where N is zero or greater.N corresponds to the object’s process order number in the master table.
  • ACCESS_METHOD – Instructs Import to use a particular method to load data.Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATHand EXTERNAL_TABLE.
  • ATTACH – Attach to an existing job. For example, ATTACH=job_name.
  • CLUSTER – Utilize cluster resources and distribute workers across the Oracle RAC [YES].
  • CONTENT – Specifies data to load. Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.
  • DATA_OPTIONS – Data layer option flags. Valid keywords are: DISABLE_APPEND_HINT and SKIP_CONSTRAINT_ERRORS.
  • DIRECTORY – Directory object to be used for dump, log and SQL files.
  • DUMPFILE – List of dump files to import from [expdat.dmp]. For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
  • ENCRYPTION_PASSWORD – Password key for accessing encrypted data within a dump file. Not valid for network import jobs.
  • ENCRYPTION_PWD_PROMPT – Specifies whether to prompt for the encryption password [NO]. Terminal echo will be suppressed while standard input is read.
  • ESTIMATE – Calculate network job estimates. Valid keywords are: [BLOCKS] and STATISTICS.
  • EXCLUDE – Exclude specific object types. For example, EXCLUDE=SCHEMA:”=’HR'”.
  • FLASHBACK_SCN – SCN used to reset session snapshot.
  • FLASHBACK_TIME – Time used to find the closest corresponding SCN value.
  • FULL – Import everything from source [YES].
  • HELP – Display help messages [NO].
  • INCLUDE – Include specific object types. For example, INCLUDE=TABLE_DATA.
  • JOB_NAME – Name of import job to create.
  • KEEP_MASTER – Retain the master table after an import job that completes successfully [NO].
  • LOGFILE – Log file name [import.log].
  • LOGTIME – Specifies that messages displayed during import operations be timestamped. Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
  • MASTER_ONLY – Import just the master table and then stop the job [NO].
  • METRICS – Report additional job information to the import log file [NO].
  • NETWORK_LINK – Name of remote database link to the source system.
  • NOLOGFILE – Do not write log file [NO].
  • PARALLEL – Change the number of active workers for current job.
  • PARFILE – Specify parameter file.
  • PARTITION_OPTIONS – Specify how partitions should be transformed. Valid keywords are: DEPARTITION, MERGE and [NONE].
  • QUERY – Predicate clause used to import a subset of a table. For example, QUERY=employees:”WHERE department_id > 10″.
  • REMAP_DATA – Specify a data conversion function. For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
  • REMAP_DATAFILE – Redefine data file references in all DDL statements.
  • REMAP_SCHEMA – Objects from one schema are loaded into another schema.
  • REMAP_TABLE – Table names are remapped to another table. For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.
  • REMAP_TABLESPACE – Tablespace objects are remapped to another tablespace.
  • REUSE_DATAFILES – Tablespace will be initialized if it already exists [NO].
  • SCHEMAS – List of schemas to import.
  • SERVICE_NAME – Name of an active Service and associated resource group to constrain Oracle RAC resources.
  • SKIP_UNUSABLE_INDEXES – Skip indexes that were set to the Index Unusable state.
  • SOURCE_EDITION – Edition to be used for extracting metadata.
  • SQLFILE – Write all the SQL DDL to a specified file.
  • STATUS – Frequency (secs) job status is to be monitored where the default [0] will show new status when available.
  • STREAMS_CONFIGURATION – Enable the loading of Streams metadata [YES].
  • TABLE_EXISTS_ACTION – Action to take if imported object already exists. Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
  • TABLES – Identifies a list of tables to import. For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
  • TABLESPACES – Identifies a list of tablespaces to import.
  • TARGET_EDITION – Edition to be used for loading metadata.
  • TRANSFORM – Metadata transform to apply to applicable objects. Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE,  LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, STORAGE, and TABLE_COMPRESSION_CLAUSE.
  • TRANSPORTABLE – Options for choosing transportable data movement. Valid keywords are: ALWAYS and [NEVER].  Only valid in NETWORK_LINK mode import operations.
  • TRANSPORT_DATAFILES – List of data files to be imported by transportable mode.
  • TRANSPORT_FULL_CHECK – Verify storage segments of all tables [NO]. Only valid in NETWORK_LINK mode import operations.
  • TRANSPORT_TABLESPACES – List of tablespaces from which metadata will be loaded. Only valid in NETWORK_LINK mode import operations.
  • VERSION – Version of objects to import. Valid keywords are: [COMPATIBLE], LATEST, or any valid database version.  Only valid for NETWORK_LINK and SQLFILE.
  • VIEWS_AS_TABLES – Identifies one or more views to be imported as tables. For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.  Note that in network import mode, a table name may be appended  to the view name.

 

——————————————————————————

 

The following commands are valid while in interactive mode.

Note: abbreviations are allowed.

 

  • CONTINUE_CLIENT – Return to logging mode. Job will be restarted if idle.
  • EXIT_CLIENT – Quit client session and leave job running.
  • HELP – Summarize interactive commands.
  • KILL_JOB – Detach and delete job.
  • PARALLEL – Change the number of active workers for current job.
  • START_JOB – Start or resume current job. Valid keywords are: SKIP_CURRENT.
  • STATUS – Frequency (secs) job status is to be monitored where the default [0] will show new status when available.
  • STOP_JOB – Orderly shutdown of job execution and exits the client. Valid keywords are: IMMEDIATE.

 

 

This article will give a brief example of IMPDP utility by importing an export of table scott.emp into a new table scott.emp2.

 

 

 

  1. Logon to your server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Tue Jan 10 09:26:15 EST 2016 on pts/2

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Logon to your database as the user scott default password tiger.

 

[oracle@linux2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 08:56:36 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Fri Jan 06 2016 11:01:31 -05:00

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL> create directory dmpdir as ‘/home/oracle’;

Directory created.

SQL>

 

 

  1. Log out of SQLPLUS and change directory to oracle software owners home directory.

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$ cd

[oracle@linux2 ~]$

 

  1. Execute the command below to export the table scott.emp to file scott_emp.dmp. NOTE: you may have some errors with name of constraints from original table, but the data will be imported.

 

impdp scott/tiger DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir REMAP_TABLE=scott.emp:emp2

 

[oracle@linux2 ~]$ impdp scott/tiger DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir REMAP_TABLE=scott.emp:emp2

Import: Release 12.1.0.2.0 – Production on Wed Jan 11 10:03:13 2016

 

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

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

Master table “SCOTT”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded

Starting “SCOTT”.”SYS_IMPORT_FULL_01″:  scott/******** DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir REMAP_TABLE=scott.emp:emp2

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “SCOTT”.”EMP2″                              8.820 KB      15 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

ORA-31684: Object type INDEX:”SCOTT”.”BT_SCOTT_EMP_JOB” already exists

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

ORA-31684: Object type CONSTRAINT:”SCOTT”.”PK_EMP” already exists

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ORA-31684: Object type REF_CONSTRAINT:”SCOTT”.”FK_DEPTNO” already exists

Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

ORA-31684: Object type INDEX:”SCOTT”.”BM_SCOTT_EMP_JOB” already exists

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job “SCOTT”.”SYS_IMPORT_FULL_01″ completed with 4 error(s) at Wed Jan 11 10:04:08 2016 elapsed 0 00:00:53

 

[oracle@linux2 ~]$

 

  1. Now logon to SQLPLUS as the user scott and describe your new table emp2.

 

[oracle@linux2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 10:06:05 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Jan 11 2016 10:03:13 -05:00

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL> desc emp2

 Name                                      Null?    Type

 —————————————– ——– —————————-

 EMPNO                                              NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 DEPTNO                                             NUMBER(2)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 

SQL> select count(*) from emp2;

 

  COUNT(*)

———-

        15

 

SQL>

 

  1. This completes a simple example of IMPDP utility in Oracle 12c.

 

Larry Catt

OCP

Leave a Reply