DataPump

Creating a physical directory for storing dump files (generated by expdp)

cd /u01

$mkdir dump

Create logical directory and map the physical directory with it

sql> create directory dump as '/u01/dump';

Levels of logical backups (exports)

1. Table level: Exporting a table

$expdp dumpfile=emp.dmp directory=dump tables=scott.emp logfile=emp.log

Username: / as sysdba

(If user is sys otherwise specify db username and password)
(if we use any other user other than "/ as sysdba", then remember that user must have the "DATAPUMP_EXP_FULL_DATABASE" and "DATAPUMP_EXP_FULL_DATABASE" privileges or dba privilege)

(if we have more than one table enter comma seperated like)
scott.emp, scott.dept

Importing table in a particular schema.

$impdp dumpfile=emp.dmp directory=dump remap_schema=scott:u1

(here scott is the old user and u1 is the new user where we are importing)

2. Schema Level (all scotts schema export for ex)

$expdp dumpfile=scott.dmp directory=dump schemas=scott

importing the scott schema into u1 schema

$impdp dumpfile=scott.dmp directory=dump remap_schema=scott:u1

3. Tablespace Level

$expdp dumpfile=users.dmp directory=dump tablespaces=users logfile=users.log

$impdp dumpfile=users.dmp directory=dump remap_tablespace=users:tbs


4. Transporting tablespace from one database to another database:

Coming Soon...

------------------------------------------------

Tables

$expdp system/manager tables=scott.emp,scott.dept directory=dump
dumpfile=emp_dept.dmp logfile=emp_dept.log

$impdp system/manager directory=dump dumpfile=emp_dept.dmp 
logfile=new.log remap_schema=scott:u1


Schemas

$expdp system/manager schemas=scott,u1 directory=dump
dumpfile=mydump.dmp logfile=mylog.log


Database

$expdp system/manager full=y directory=dump
dumpfile=fulldb.dmp logfile=fulldb.log
------------------------------------------------

We can change database default block size using datapump.  We can export from a db where block size is 4k and we can import the same into a db where block size is 8k.  

Dump file is platform independent.  We can export from a Windows based oracle installation to Linux.
------------------------------------------------

Transporting whole tablespace as it is

To transport a tablespace from one platform to another, datafiles on different platforms must be in the same endian format (byte ordering).  The pattern for byte ordering in native types is called endianness. There are only two main patterns, big endian and little endian.  

Big endian means the most significant byte comes first, and little endian means the least significant byte comes first.

One of the fastest way to transfer data from one db to other is through transportable tablespaces.

$expdp dumpfile=users.dmp directory=dump transport_tablespaces=users

copy dump file "users.dmp" and datafile "users01.dbf" to the destination.

$impdp sys/manager dumpfile=users.dmp directory=imp
transport_datafiles='/u01/test/tbs01.dbf'

While doing this we must make sure the "endian format" of the Operating System is same at source and destination.

There are 2 types of endian formats available:
1. little
2. big

To know which platform supports which endian format use the following view:

select * from v$transportable_platform;

select platform_name,endian_format from v$transportable_platform;

Endian means "format of the file organizing in OS at OS level".

Dump file is platform independent but datafile is not.

The v$database data dictionary view also adds two columns:

select name,platform_id,platform_name from v$database;

If the source and destination platforms are not the same then we can convert the datafile using RMAN.  As we know dump file is platform independent and we only need to convert datafile.

RMAN> CONVERT TABLESPACE users TO PLATFORM 'platform_name';

Cross platform concept came from 10g.

Tablespace to be transported must be self contained. It should not depend on others.

exec dbms_tts.transport_set_check('USERS','TBS');

The above package dbms_tts checks weather this tablespace is self contained or not.  If there are any violations are inserted into a temporary table.

select * from transport_set_violations;



Note: Some more options to come.