Expdp Impdp Tutorial Part -2

10. ENCRYPTION in export Import

ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.

ENCRYPTION_PWD_PROMPT ——– >>>>>>> INTRODUCED IN ORACLE 12C
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.

ENCRYPTION_PASSWORD:

To encrypt any dumpfile, provide password to ENCRYPTION_PASSWORD in parfile.

1 2 3 4 5 6 7 8 9

dumpfile=emp_enc1.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PASSWORD=oracle ENCRYPTION_ALGORITHM=AES256

1 2 3 4 5 6 7 8 9 10 11

Export: Release 12.1.0.2.0 - Production on Tue Jan 24 12:25:15 2017 Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=emp_enc1.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PASSWORD=******** ENCRYPTION_ALGORITHM=AES256 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER . . exported "DBATEST"."EMP_TAB"                         19.16 MB  175340 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ******************************************************************************

ENCRYPTION_PWD_PROMPT

With this parameter, No need to write the password in parfile or expdp command , we can pass the password, when it asks for input.

1 2 3 4 5 6 7

expdp dumpfile=emp_enc.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PWD_PROMPT=YES

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Encryption Password: ------- > Here provide the password FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=emp_enc.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PWD_PROMPT=YES Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 23 MB . . exported "DBATEST"."EMP_TAB"                         19.16 MB  175340 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ******************************************************************************

While importing this dump, we have to pass the same password.

1 2 3 4 5 6 7

dumpfile=emp_enc.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PWD_PROMPT=YES

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Encryption Password: ----- >>> PASS THE PASSWORD HERE --- > Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA dumpfile=emp_enc.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PWD_PROMPT=YES Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "DBATEST"."EMP_TAB"                         19.16 MB  175340 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 Tue Jan 24 12:23:38 2017 elapsed 0 00:01:52

If you try to import without encryption password, it will throw error as : ORA-39174: Encryption password must be supplied.

1 2 3 4 5 6 7

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options ORA-39002: invalid operation ORA-39174: Encryption password must be supplied.

11. CONTENT parameter in EXPDP/IMPDP:

CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

CONTENT=ALL is the default value

CONTENT=METADATA_ONLY:

It will take export only the skeleton/DDL on the object/schema. It wont export any of the data/rows.

1 2 3 4 5 6 7 8

dumpfile=emp_enc1.dmp logfile=emp_enc.log directory=VEN tables=DBATEST.EMP_TAB CONTENT=METADATA_ONLY

1 2 3 4 5 6 7 8 9 10 11

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=emp_enc1.dmp logfile=emp_enc.log directory=VEN tables=DBATEST.EMP_TAB CONTENT=METADATA_ONLY Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

CONTENT=DATA_ONLY:

It will take export only the ROWS/DATA of the tables , by excluding the DDL.

1 2 3 4 5 6 7 8

dumpfile=emp_enc2.dmp logfile=emp_enc.log   directory=VEN tables="DBATEST"."EMP_TAB" CONTENT=DATA_ONLY

1 2 3 4 5 6 7 8 9 10 11 12

FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=emp_enc2.dmp logfile=emp_enc.log directory=VEN tables=DBATEST.EMP_TAB CONTENT=DATA_ONLY Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 23 MB . . exported "DBATEST"."EMP_TAB"                         19.16 MB  175340 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

Before importing the dump into another database, make sure that the table exists in that table, as we have taken export using DATA_ONLY,
which don’t contain the DDL. Else import will fail with error as below.

Here we tried to load this dump into a database, where table doesn’t exists.

1 2 3 4 5 6

select table_name from dba_tables where table_name='EMP_TAB'; no rows selected

1 2 3 4 5 6 7 8

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=emp_enc2.dmp logfile=emp_enc.log directory=VEN Processing object type TABLE_EXPORT/TABLE/TABLE_DATA ORA-39034: Table TABLE_DATA:"DBATEST"."EMP_TAB" does not exist. ---- >>>>>>>>  ERROR Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Jan 24 14:45:01 2017 elapsed 0 00:00:54

So it failed.

NOTE – NEVER USE TABLE_EXISTS_ACTION=REPLACE 

12. CLUSTER PARAMETER IN RAC:

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].

In a RAC database, if you are taking export with parallel option and the datapump directory is not shared between the nodes, then export might fail with below error.
Because, the remote node is unable to write the dumps to that directory.

ORA-31617: unable to open dump file “/dumparea/TEST/asset_75.dmp” for write
ORA-19505: failed to identify file “/dumparea/TEST/asset_75.dmp”
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory

So to avoid this, Use CLUSTER=N parameter, with which , the export will run worker processes only on the local node.

parfile:

1 2 3 4 5 6 7 8

dumpfile=asset_%U.dmp logfile=asset.log directory=VEN parallel=32 cluster=N

13.EXPDP to an ASM DISKGROUP:

We can use asm diskgroups also to store the expdp dumpfile.

Create a directory pointing to asm diskgroup( for dumpfiles)

1 2 3 4 5 6 7 8 9 10

SQL> create directory SOURCE_DUMP as '+NEWTST/TESTDB2/TEMPFILE'; Directory created. SQL> grant read,write on directory SOURCE_DUMP to public; Grant succeeded.

Create a directory pointing to normal filesystem ( for logfiles)

1 2 3 4 5 6

SQL> create directory EXPLOG as '/export/home/oracle'; Directory created.

export syntax:

expdp dumpfile=dumpfile.dmp logfile= LOGFILE_DIR:logfile.log directory=DUMP_DIR  tables=….

expdp dumpfile=test.dmp logfile=EXPLOG:test.log directory=SOURCE_DUMP tables=dbatest.EMPTAB exclude=statistics

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

expdp dumpfile=test.dmp logfile=EXPLOG:test.log directory=SOURCE_DUMP tables=dbatest.EMPTAB exclude=statistics Export: Release 12.1.0.2.0 - Production on Wed Feb 8 10:07:54 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=test.dmp logfile=EXPLOG:test.log directory=SOURCE_DUMP tables=dbatest.EMPTAB exclude=statistics Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 11 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "DBATEST"."EMPTAB"                          8.691 MB   81753 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is:   +NEWTST/TESTDB2/tempfile/test.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 8 10:08:13 2017 elapsed 0 00:00:15

Note: If you dont mention the logfile directory, then it will fail with below error.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

expdp dumpfile=test.dmp logfile=test.log directory=SOURCE_DUMP tables=dbatest.EMPTAB exclude=statistics Export: Release 12.1.0.2.0 - Production on Wed Feb 8 10:06:12 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation

14.EXPDP TO MULTIPLE DIRECTORIES:

If you wish to take a expdp backup of a big table/schema, but you don’t sufficient space in a single mount point to keep the dump. In thiscase we take expdp dump to multiple directory.

Refer link –  EXPDP TO MULTIPLE DIRECTORIES: