Oracle Data Pump:
Oracle Data Pump is a newer, faster and more flexible
alternative to the "exp" and "imp" utilities used in
previous Oracle versions.
Roles:
Many Data Pump Export and Import operations require the user
to have the DATAPUMP_EXP_FULL_DATABASE role and/or
the DATAPUMP_IMP_FULL_DATABASE role.The DATAPUMP_EXP_FULL_DATABASE
role
affects only export operations. The DATAPUMP_IMP_FULL_DATABASE role affects
import operations and operations that use the Import SQLFILE parameter.
Key Features:
Fast Performance:
Operations performed with the new Data Pump Export and
Import utilities are typically much faster than operations performed with the
original Export and Import utilities. With Data Pump Export, when the direct
path method of unloading is used, a single stream of data unload is about two
times faster than original Export. This is because the direct path API has been
modified to be even more efficient than before. Depending on the level of
parallelism, the performance improvement can be even greater.
With Data Pump Import, a single stream of data load is about
15-45 times faster than original Import. This is because original Import uses
only conventional mode inserts, whereas Data Pump Import uses the direct path
method of loading. As with Export, the performance improvement can be even
greater depending on the level of parallelism.
Improved Management
Restart:
Every Data Pump operation has a master table that is created
in the schema of the user running a Data Pump job. The master table maintains
information about all aspects of the job, such as the current state of every
object exported or imported and its location in the dump file set. In the event
of a planned or unplanned job stoppage, Data Pump knows which objects were
currently being worked on and whether or not they completed successfully.
Therefore, all stopped Data Pump jobs can be restarted without loss of data as
long as the master table and dump file set remain undisturbed while the job is
stopped.
Object Selection:
A Data Pump job can exclude or include virtually any type of
object and any subset of objects within a type. The following client parameters
are used:
The EXCLUDE parameter
filters the metadata that is exported and imported by specifying objects and
object types to be excluded from the current operation. An optional name
qualifier can be used for finer selectivity within each object type specified.
The INCLUDE parameter
filters the metadata that is exported and imported by specifying objects and
object types to be included for the current operation. An optional name
qualifier can be used for finer selectivity within each object type specified.
The CONTENT parameter
specifies what is exported or imported: metadata only, data only, or both.
The QUERY parameter
filters data by specifying a clause for a SQL SELECT statement, which is
applied to all tables in the export job or to a specific table.
Monitoring and Estimating
Capability:
In addition to the standard progress and error messages
printed by the client and to the log file, the new interactive STATUS command
displays cumulative status of the job, along with a description of the current
operation. An estimated completion percentage for the job is also returned. The
user can also specify a time period, in seconds, for an automatic status update
at specific intervals.
Because more than one client can be attached to a running
job, the user can start a job at work, detach from it, go home, reattach to the
job at home, and monitor it throughout the evening.
The start of every export job now includes an estimate phase
in which the approximate amount of all data to be unloaded is determined. This
allows the user to allocate a sufficient amount of disk space for the dump file
set.
Network Mode:
Data Pump Export and Import both support a network mode in
which the job's source is a remote Oracle instance. When you perform an import
over the network, there are no dump files involved because the source is
another database, not a dump file set.
When you perform an export over the network, the source can
be a read-only database on another system. Dump files are written out on the
local system just as they are with a local (non-networked) export.
Default Locations for
Dump, Log, and SQL Files
Because Data Pump is server-based, rather than client-based,
dump files, log files, and SQL files are accessed relative to server-based
directory paths. Data Pump requires you to specify directory paths as directory
objects. A directory object maps a name to a directory path on the file system.
For example, the following SQL statement creates a directory
object named dpump_dir1 that is mapped to a directory located at /u01/app/dumpfiles.
SQL> CREATE DIRECTORY dpump_dir1 AS '/u01/app/dumpfiles';
The reason that a directory object is required is to ensure
data security and integrity. For example:
If you were allowed to specify a directory path location for
an input file, you might be able to read data that the server has access to,
but to which you should not.
If you were allowed to specify a directory path location for
an output file, the server might overwrite a file that you might not normally have
privileges to delete.
On Unix and Windows NT systems, a default directory object, DATA_PUMP_DIR, is created
at database creation or whenever the database dictionary is upgraded. By
default, it is available only to privileged users.
If you are not a privileged user, before you can run Data
Pump Export or Data Pump Import, a directory object must be created by a
database administrator (DBA) or by any user with the CREATE ANY DIRECTORY privilege.
After a directory is created, the user creating the
directory object needs to grant READ or WRITE permission on the directory to
other users. For example, to allow the Oracle database to read and write files
on behalf of user hr in the directory named by dpump_dir1, the DBA must execute
the following command:
SQL> GRANT
READ, WRITE ON DIRECTORY dpump_dir TO ramesh;
Note that READ or WRITE permission to a directory object
only means that the Oracle database will read or write that file on your
behalf. You are not given direct access to those files outside of the Oracle
database unless you have the appropriate operating system privileges.
Similarly, the Oracle database requires permission from the operating system to
read and write files in the directories.
Original Export and
Import versus Data Pump Export and Import
If you are familiar with the original Export (exp) and Import
(imp) utilities,
it is important to understand that many of the concepts behind them do not
apply to Data Pump Export (expdp) and Data Pump Import (impdp). In
particular:
Data Pump Export and Import operate on a group of files
called a dump file set rather than on a single sequential dump file.
Data Pump Export and Import access files on the server
rather than on the client. This results in improved performance. It also means
that directory objects are required when you specify file locations.
Data Pump Export and Import use parallel execution rather
than a single stream of execution, for improved performance. This means that
the order of data within dump file sets and the information in the log files is
more variable.
Data Pump Export and Import represent metadata in the dump
file set as XML documents rather than as DDL commands. This provides improved
flexibility for transforming the metadata at import time.
Data Pump Export and Import are self-tuning utilities.
Tuning parameters that were used in original Export and Import, such as BUFFER and RECORDLENGTH, are neither
required nor supported by Data Pump Export and Import.
At import time there is no option to perform interim commits
during the restoration of a partition. This was provided by the COMMIT parameter in
original Import.
There is no option to merge extents when you re-create
tables. In original Import, this was provided by the COMPRESS parameter.
Instead, extents are reallocated according to storage parameters for the target
table.
Sequential media, such as tapes and pipes, are not supported.
The Data Pump method for moving data between different
database versions is different than the method used by original Export/Import.
With original Export, you had to run an older version of Export (exp) to
produce a dump file that was compatible with an older database version. With
Data Pump, you can use the current Export (expdp) version and
simply use the VERSION parameter to specify the target database
version.
When you are importing data into an existing table using
either APPEND or TRUNCATE, if any row
violates an active constraint, the load is discontinued and no data is loaded.
This is different from original Import, which logs any rows that are in
violation and continues with the load.
Data Pump Export and Import consume more undo tablespace than
original Export and Import. This is due to additional metadata queries during
export and some relatively long-running master table queries during import. As
a result, for databases with large amounts of metadata, you may receive an
ORA-01555: snapshot too old error. To avoid this, consider adding additional
undo tablespace or increasing the value of the UNDO_RETENTION
initialization parameter for the database.
If a table has compression enabled, Data Pump Import
attempts to compress the data being loaded. Whereas, the original Import
utility loaded data in such a way that if a even table had compression enabled,
the data was not compressed upon import.
Examples:
--------------
Export Full Database Mode
Only users with the DBA role or
the DATAPUMP_EXP_FULL_DATABASE role
can export in full database mode. In this example, an entire database is
exported to the filedba.dmp.
Scenario:
Connect to database and create user.
Sql>create user ramesh identified by
ramesh;
User created
SQL> grant connect,resource to
ramesh;
Grant succeeded.
SQL>create directory dump as
‘/u01/app/backup’;
“NOTE:
Check the directory exists or not physically in OS level. In not there need to
create.
$mkdir /u01/app/backup”
SQL>grant read,write on directory
dump to system;
SQL> conn ramesh
Enter password:
Connected.
And create some tables in the user, I created two tables as
bellow
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -------
----------
TEST TABLE
TEST_TBL TABLE
Sql>conn sys as sysdba
Enter password:
connected
Sql>create user siva identified by
siva;
User created
SQL> grant connect,resource to siva;
Grant succeeded.
SQL> conn siva
Enter password:
Connected.
Create table in this user also.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -------
----------
EMPLOYEE TABLE
Now export full database by using full=y parameter.
$expdp system/xxxxx directory=dump dumpfile=full_db.dmp
logfile=full_db.log full=y
After completing the export, drop the user.
SQL> drop user ramesh cascade;
User dropped.
SQL> drop user siva cascade;
User dropped.
Check the user existence in database;
SQL> select username from dba_users
where username in ('RAMESH',’SIVA’);
no rows selected
Now import the exported dumpfile to restore the dropped user
using ignore=y.
$impdp system/xxxxxx directory=dump
dumpfile=full_database.dmp logfile=full_db.log table_exists_action=append
full=y
Now export data imported, now check the user in the
database.
SQL> select username from dba_users
where username IN ('RAMESH',’SIVA’);
USERNAME
------------------------------
RAMESH
SIVA
SQL> conn ramesh
Enter password:
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -------
----------
TEST TABLE
TEST_TBL TABLE
User Mode
For exporting user owner parameter will be used.
Scenario:
Export the user using owner parameter
$expdp system/xxxxx directory=dump dumpfile=ramesh_data.dmp
logfile=ramesh_data.log schemas=ramesh
Now drop the user in database
SQL>drop user ramesh cascade;
User dropped.
Now check the user existence.
SQL>select username
from dba_users where username=’RAMESH’;
no rows selected
Now try to import the dumpfile into database, so that
dropped user will come back.
[oracle@localhost ~]$ impdp system/xxxxx
directory=dump dumpfile=ramesh_data.dmp logfile=ramesh.log;
Import is done
SQL> select username from dba_users
where username='RAMESH';
USERNAME
------------------------------
RAMESH
SQL> select
object_name,object_type from dba_objects where owner='RAMESH';
OBJECT_NAME OBJECT_TYPE
--------------------
--------------------
TEST_TBL TABLE
TEST TABLE
Exporting table:
To export a table we use tables parameter in expdp utility.
In this example we are taking backup of the table test_tbl belongs to ramesh schema to the dump file tbl_exp.dmp.
[oracle@localhost ~]$ expdp
system/xxxxx directory=dump dumpfile=tbl_exp.dmp logfile=tbl_exp.log
tables=ramesh.test_tbl;
Export: Release 11.2.0.1.0
- Production on Thu Jan 8 07:44:48 2015
Copyright (c) 1982, 2009, Oracle and/or
its affiliates. All rights reserved.
EXP-00056: ORACLE error 28002
encountered
ORA-28002: the password
will expire within 7 days
Connected to: Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII
character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252
character set (possible charset conversion)
About to export specified
tables via Conventional Path ...
Current user changed to
RAMESH
. . exporting table TEST_TBL 5 rows exported
Export terminated
successfully without warnings.
Now import the exported table into
another user siva. To do this, we need to user remapschema parameters as bellow.
[oracle@localhost ~]$ impdp
system/xxxxx directory=dump dumpfile=tbl_exp.dmp logfile=tbl_imp.log remap_schema=ramesh:siva
tables=ramesh.test_tbl;
Parameter File Method
$expdp SYSTEM/password
PARFILE=params.par
The params.dat file contains the following
information:
Userid=’system/xxxxx’
dumpfile=/u01/app/full_db.dmp
logfile=/u01/app/full_db.log
FULL=y
=========
