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.

We are greatful, Mr. Export/Import!

We had a production issue yesterday that caused an outage. Export backups came to the rescue and saved the day!

Per organizational security policy, Oracle database passwords of all database accounts, including those used in database links must be changed at regular interval. Database links have been established over secure network between our database and our partner’s database. DB Links are in place for over 10 years, and serving the business purpose of one-directional data/information dissemination. We have DB accounts in our database that our partners use in database links to access our database.

Per organizational security policy, yesterday was the password-change day. DBAs changed the passwords of DB link accounts. When DBAs tried to contact our partner so that they could re-create the database links with the new passwords, there was no one on the other side to re-create the DB link. DBAs tried home and cell, but no avail. A five-minute of planned maintenance window turned into a thirty-minute of production outage, and still no response from the other side. That’s when a decision was made to revert the passwords back to original passwords. But DBAs realized that they didn’t have, and they don’t maintain DB Link accounts passwords. Production outage had reached a 45-minute mark. DBAs tried jogging their memory, referred to old notes, etc., but couldn’t trace the original passwords.

This is when an idea came up! Export backup! This was an Oracle10g database. Nightly export backups were performed. Export dump file stores “CREATE USER username IDENTIFIED BY VALUES ’hash string'” statements. Hash strings represent hashed Oracle passwords. DBAs quickly located last night’s export dump file, grabbed the applicable CREATE USER statements, and changed passwords to their original values with “ALTER USER username IDENTIFIED BY VALUES 'hash string'”. DBAs had to work around password reuse settings in database profiles to restore the passwords, but that wasn’t a problem. DB Links were back in operation! Export saved the day! We are greatful, Mr. Export/Import!

P.S. As of Oracle 11g R2, Export/Import has been desupported in favor of more versatile Data Pump. Export and Import executables are provided with Oracle11g so data from older versions of Oracle can be imported or exported to. Oracle Data Pump import do provide option to retrieve hashed passwords.