Long Live Data Pump!

Oracle DBAs would love to reminisce about export and import utilities. The primary usage of export was to perform logical backup of the database, and then import the dump file to recreate the database on the same OS platform or different OS platform. Quite useful! In addition, Oracle DBAs routinely extracted DDLs and user creation scripts off of export dump file. Few third-party tools and utilities were specifically designed to extract DDLs off of the dump file. In fact, export backup did save my day when I had to extract hashed passwords for few users whose passwords were accidently changed.

As of Oracle11g, Export/Import has been desupported and given way to much more versatile Data Pump utility. Oracle describes Data Pump as very high-speed data movement utility. Indeed, Data Pump is quite powerful and DBAs should add data pump backups to Database Disaster/Recovery Plan.

The purpose of this blog is to examplify few of its indirect usages that will come quite handy. For example, you may ask, “Can I extract hashed passwords with data pump?” The answer is, “Of course!” Let’s discuss.

Extract Hashed Passwords

To extract hashed passwords, all you need to do is run data pump import with SQLFILE and INCLUDE parameters, as illustrated below:

C:\Oracle\oraback\jdev11g>impdp system/****** directory=dp_backup dumpfile=jdev11g.dmp logfile=jdev11gimp.log include=USER sqlfile=jdev11g.sql

Import: Release 11.2.0.1.0 - Production on Mon Sep 27 21:58:04 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** dumpfile=jdev11g_1.dmp directory=dp_backup include=USER logfile=jdev11gimp.log sqlfile=jdev 11g.sql content=metadata_only Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type

DATABASE_EXPORT/SCHEMA/USER Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:10

--- Contents from SQLFILE ---

CREATE USER "JMEHTA" IDENTIFIED

BY VALUES 'hash string'     

DEFAULT TABLESPACE "USERS"  

TEMPORARY TABLESPACE "TEMP";

SQLFILE parameter directs data pump import (impdp) to write DDLs to the file specified with this parameter. Writing DDLs to the SQLFILE is “instead of behavior.” Impdp doesn’t execute the DDLs in the database. Impdp needs a database connection to execute DBMS_METADATA and DBMS_DATAPUMP PL/SQL packages to perform its work.

Also note that with SQLFILE parameter, you cannot specify CONTENT=ALL or DATA_ONLY. In other words, impdp doesn’t generate SQL statements that could be used later to populate the database tables. SQLFILE includes DDLs for tablespaces, users, role grants, packages, procedures, functions, tables, indexes, primary and foreign keys, etc.

INCLUDE parameter allows to target the DDLs you are interested in. Specifying INCLUDE=USER will give you CREATE USER statements. To see a list of valid paths for use with the INCLUDE parameter, you can query the following views: DATABASE_EXPORT_OBJECTS for Full mode, SCHEMA_EXPORT_OBJECTS for schema mode, and TABLE_EXPORT_OBJECTS for table and tablespace mode.