Oracle Database backup - Data Pump and RMAN

RMAN Process - From START to FINISH

2008-09-26T05:55:00.000-07:00

To illustrate it, Let us explain it with an example. Let us backup our PRODUCTION database using RMAN.

C$>rman
rman>connect target /
rman>backup database;

That's it.

Let us explian the above example.

RMAN makes the bequeath connection to the target database that we have set up in our env. This means it checks the ORACLE_SID for an instance name and then spawns a server process at that instance, logging is as SYSDBA user. This connects us as the internal database user SYS. RMAN immediately spawns the channel processes that will use to perform the backup. In this case we are using default settings, so only one channel is allocated.

We are not using I/O slaves, so the process allocates memory in the PGA.

Next RMAN compilesa call to sys.dbms_rcvman to request the database schematic information from the target database control file, starting with a determination of the target database version. It gathers version information from the control files along with control file information itself like type of control file, current sequence number, when it was created etc.

Becuse this is a full backup, RMAN requests information for each datafile in the database and determines if any files are offline. RMAN ignores all disk affinity information and concentartes on compiling the list of files for inclusion in the backupset.

After the list is compiled, RMAN is ready to begin the backup process itself. To guarantee consistency, it the builds a snapshot control files, if one already exists it overwrites it with a new one.

After this RMAN creates a call to the DBMS_BACKUP_RESTORE package to create a backup piece. The backup piece will be built in the default file location.

RMAN has the file list, so it can allocate the memory buffers for performing the read from DISK, with 20 files RMAN allocates input buffer size 128 MB . 4 per file for total memory utilzation of 10 MB for input buffers. RMAN will only allocate 4 output buffers each of size 1 MB. This brings the total memory utilization 14MB for the backup.

After the memory is allocated, RMAN initilize the BACKUP PIECE. backup piece will be given unique name that guarnatees uniqueness.

RMAN then determines if there will be enough space for backup to be successful. It does by using a pessimistic algorithm that assumes that backup will be the same size as sum of the size of all the datafiles. Due to null compression this will not be the case.

Once the backup piece is initiated, then the channel process can begin the database backup process.
a) If you are using spfile, it backs it up automatically to the backupset,
b) RMAN then will backup the current control file to the backupset,
This control file backup is automatic whenever the SYSTEM tablespace is backed up, this behaviour is changed if you have CONTROL FILE AUTOBABKUP turned on.
c) Now its time to begin the datafile reads to pull data blocks into the memory. The channel process does this by doing a read-ahead on the disk and pulling several blocks into memeory at the same time. then memory-to-memory write from input buffer to output buffer occurs.

During this write, RMAN determines if the block has ever been initialized, or block header information is still zeroed out. If it is an unused block, the write to the output buffer never occur and the block is discarded. If the block has been used, RMAN performs the checksum on the block. If the header and footer of the block did not match, RMAN indicates a corrupt block and aborts the backup. If the block has been initialized and it passes the checksum, then the block is written into output buffer.

Once the output buffers fills its capacity, we dump the buffer to the backup file location.

RMAN buffers are being filled up with blocks from all the datafiles so there is no order to the blocks in the dump file.

While the blocks are being written out to backup piece, the status of the backup is being polled by the RMAN shadow process. It checks in on the RPCs at the target and passes the information to v$session_lonops for your review.

Once every block in the datafile has been read into an output buffer and its status determines, then RMAN completes the file backup by writing the datafile header out to the backup piece.

After all the files have their file headers written to the backup piece, RMAN makes a final call to sys.dbms_backup_restore which writes backup information to the control files. This information includes the name of the backup piece, the checkpoint SCN at the time it started and the time it completed.

And that it.

What is auxiliary Database?

2008-09-26T05:51:00.000-07:00

The auxiliary database refers to the instance that will become host to restored files from the target database in the event of a TSPITR, a duplication operation or the creation of a standby database using RMAN backups.

How to use recovery catalog?

2008-09-26T05:44:00.001-07:00

To use a recovery catalog, you first connect from RMAN to the target database. Then you make a second NET connection to the recovery catalog from within RMAN,

Rman> connect target /
rman> connect catalog rman/password@rcat

The connect string you pass to the catalog is for the user that owns the RMAN catalog.

Unlike the target the connection to the catalog is not SYSDBA

There are two package inside the RECOVERY CATALOG

DBMS_RCVMAN and dbms_rcvcat.

Recovery Catalog

2008-09-26T05:41:00.000-07:00

Recovery Catalog is a repository for metadata about RMAN backups. You can say that recovery catalog is a merely a copy of pertinent information out of control file that RMAN requires for backup and recovery purpose.

How to create RMAN catalog in 10g

2008-09-24T11:22:00.000-07:00

Create a rcat - recovery catalog database with database configuration assistant

C:\> sqlplus SYS/pass@rcat AS SYSDBA

SQL> CREATE SMALLFILE TABLESPACE catalog_tbs DATAFILE ‘C:\ORACLE\10.2\datafile\cat_tbs.dbf’ SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

SQL> CREATE USER rcat IDENTIFIED BY rcat DEFAULT TABLESPACE catalog_tbs QUOTA UNLIMITED ON catalog_tbs;

SQL> GRANT recovery_catalog_owner TO rcat;

SQL> Grant connect, resource to rcat;

SQL> exit

C:\> rman catalog rcat/rcat@rcat

RMAN> create catalog;

RMAN> exit

C:\>rman target orcl catalog rcat/rcat@rcat

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 24 21:21:27 2008

connected to target database: RCAT (DBID=376666761)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> report schema;

RMAN - Basics for Beginners

2008-09-24T11:20:00.000-07:00

WHAT IS RMAN ?

Recovery Manager is a tool that: manages the process of creating backups and also manages the process of restoring and recovering from them.

WHY USE RMAN ?

No extra costs …Its available free
RMAN introduced in Oracle 8 it has become simpler with newer versions and easier than user managed backups
Proper security
You are 100% sure your database has been backed up.
Its contains detail of the backups taken etc in its central repository
Facility for testing validity of backups also commands like crosscheck to Check the status of backup.
Faster backups and restores compared to backups without RMAN
RMAN is the only backup tool which supports incremental backups.
Oracle 10g has got further optimized incremental backup which has resulted in improvement of performance during backup and recovery time
Parallel operations are supported
Better querying facility for knowing different details of backup
No extra redo generated when backup is taken..compared to online backup without RMAN which results in saving of space in hard disk
RMAN an intelligent tool
Maintains repository of backup metadata
Remembers backup set location
Knows what need to backed up
Knows what is required for recovery
Knows what backup are redundant

UNDERSTANDING THE RMAN ARCHITECTURE

An oracle RMAN comprises of RMAN EXECUTABLE This could be present and fired even through client side TARGET DATABASE This is the database which needs to be backed up
RECOVERY CATALOG Recovery catalog is optional otherwise backup details are stored in target database controlfile
It is a repository of information queried and updated by Recovery Manager
It is a schema or user stored in Oracle database
One schema can support many databases
It contains information about physical schema of target database datafile and archive log, backup sets and pieces

Recovery catalog is a must in following scenarios

In order to store scripts
For tablespace point in time recovery

Media Management Software

Media Management software is a must if you are using RMAN for storing backup in tape drive directly.

Backups in RMAN

Oracle backups in RMAN are of the following type

RMAN complete backup OR RMAN incremental backup

These backups are of RMAN proprietary nature

IMAGE COPY

Its again a kind of backup. The advantage of uing Image copy is its not in RMAN proprietary format.

Backup Format

RMAN backup is not in oracle format but in RMAN format. Oracle backup comprises of backup sets and it consists of backup pieces. Backup sets are logical entity. In oracle 9i it gets stored in a default location.

There are two type of backup sets

Datafile backup sets,
Archivelog backup sets

One more important point of data file backup sets is it do not include empty blocks. A backup set would contain many backup pieces. A single backup piece consists of physical files which are in RMAN proprietary format.

You can go to RMAN prompt by just typing rman. RMAN executable is present in ORACLE_HOME/bin location.

bash-2.05$ rman

RMAN>

You can use target connect to connect to database. The database it will connect to depends on the environment variable ORACLE_HOME.

RMAN> connect target

connected to target database: ORCL (DBID=1156435946)

Alternatively you can use “rman TARGET SYS/oracle@test NOCATALOG” to connect to the RMAN of “test” instance.

Here we will be using target database control file to store all the information required for RMAN, like backupsets and backup image information etc.

Backup Database:

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mountedTotal System Global Area 1258291200 bytes

Fixed Size 1978336 bytes
Variable Size 318771232 bytes
Database Buffers 922746880 bytes
Redo Buffers 14794752 bytes

RMAN> backup database;

Starting backup at 23-JUL-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/dy/oracle/product/oradata/orcl/system01.dbf
input datafile fno=00003 name=/dy/oracle/product/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/dy/oracle/product/oradata/orcl/example01.dbf
input datafile fno=00002 name=/dy/oracle/product/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/dy/oracle/product/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-JUL-07
channel ORA_DISK_1: finished piece 1 at 23-JUL-07
piece handle=/dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/

o1_mf_nnndf_TAG20070723T031355_3b8zv57d_.bkp tag=TAG20070723T031355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-JUL-07
channel ORA_DISK_1: finished piece 1 at 23-JUL-07
piece handle=/dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/

o1_mf_ncsnf_TAG20070723T031355_3b8zy7xr_.bkp tag=TAG20070723T031355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 23-JUL-07
Creating RMAN Catalog

You can also create a repository for RMAN to store all this information. Repository will be just another small database which can store the catalog information. Creating a catalog is a 3 step process

1) Create database which will hold the catalog. Else you can use the existing database also. All you need is to create a seperate tablespace for holding the information about RMAN catalog.

2) Create RMAN tablespace and RMAN user

SQL> create tablespace rman_tbs datafile ‘/dy/oracle/product/db10g/dbf/rman01.dbf’ size 500M EXTENT MANAGEMENT LOCAL segment SPACE MANAGEMENT AUTO ;

Tablespace created.

SQL> create user rman identified by rman
2 default tablespace rman_tbs
3 TEMPORARY TABLESPACE TEMPTS1
4 QUOTA UNLIMITED ON rman_tbs account unlock;

User created.

After creating user, you need to grant RECOVERY_CATALOG_OWNER role to that user.

3) Create RMAN catalog.

bash-2.05$ rman catalog rman/rman@test

connected to recovery catalog database

RMAN> create catalog

recovery catalog created

For registering the database, you need to get connected to database as well as catalog at the same time. Here is how you can do.

RMAN> connect target

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

The above error is because the database we connected to is not found in the catalog database. We can register the database in catalog.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Registering database will also resynch the information present in the target database control file and catalog database. Since we have taken 1 backup early, it will synchup that information with RMAN catalog. We can check the same using LIST BACKUP command at RMAN prompt.

RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
18 Full 573.48M DISK 00:01:26 23-JUL-07
BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20070723T031355
Piece Name: /dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/

o1_mf_nnndf_TAG20070723T031355_3b8zv57d_.bkp
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 562487 23-JUL-07 /dy/oracle/product/oradata/orcl/system01.dbf
2 Full 562487 23-JUL-07 /dy/oracle/product/oradata/orcl/undotbs01.dbf
3 Full 562487 23-JUL-07 /dy/oracle/product/oradata/orcl/sysaux01.dbf
4 Full 562487 23-JUL-07 /dy/oracle/product/oradata/orcl/users01.dbf
5 Full 562487 23-JUL-07 /dy/oracle/product/oradata/orcl/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
19 Full 6.80M DISK 00:00:05 23-JUL-07
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20070723T031355
Piece Name: /dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/

o1_mf_ncsnf_TAG20070723T031355_3b8zy7xr_.bkp
Control File Included: Ckp SCN: 562487 Ckp time: 23-JUL-07
SPFILE Included: Modification time: 23-JUL-07

RMAN Command line

Backing up the Controlfile and Spfile

The control file can be automatically backed up after each RMAN backup and database structure change as a way to protect the RMAN repository (when we are not using a seperate catalog for RMAN).
RMAN> configure controlfile autobackup on;

Backing up control file

RMAN> backup current controlfile;

Creating Image copy of all datafiles in database

These are the image copies and are stored in ORACLE format and not in RMAN format. Backupsets and backuppieces are stored in internal RMAN format. Hence these image copies can be used for manual restore and recovery as well.

RMAN> backup as copy database;

Creating backupsets of all datafiles in database

If you specify BACKUP AS BACKUPSET, then RMAN stores its backups in backup sets. A backup set, consisting of one or more backup pieces, contains the physical file data being backed up. This backupset is written in a format that only RMAN can access. Only RMAN can create and restore backup sets. Backup sets can be written to disk or tape, and they are the only type of backup which RMAN can use to write backups to tape.

RMAN> backup as backupset database;

Backup individual tablespace as backupsets

RMAN> backup tablespace system, HTMLDB;

Backup individual tablespace as image copies

RMAN> backup as copy tablespace system;

Backup individual files as image copies

RMAN> backup as copy datafile ‘/dy/oracle/product/db10g/dbf/system01.dbf’;

Backup individual files as Backupsets

RMAN> backup datafile ‘/dy/oracle/product/db10g/dbf/system01.dbf’;

Backup archivelogs

We can backup the archive logs according to the output of some search condition. Example we want to backup only those archivelogs which starts with “ARCH_616814159_”.

RMAN> backup acrchivelog like ‘%ARCH_616814159_%’;

Copy archivelogs from some time stamp.

Suppose we want to copy the archivelogs of last 2 days, then we can use the following commands.

RMAN> BACKUP ARCHIVELOG from time ’sysdate-2′;

Backup all archivelog files

RMAN> Backup archivelog all;

Backup archivelog between some time.

RMAN> BACKUP ARCHIVELOG FROM TIME ‘SYSDATE-30′ UNTIL TIME ‘SYSDATE-7′;

Specifying copies while backing up.

RMAN> backup copies 2 datafile ‘/dy/oracle/product/db10g/dbf/cs_tbs01.dbf’;

Remember that copies option cannot be used with image copies. It can be used only with backupsets.

Giving tags to backups

RMAN> BACKUP TAG ‘weekly_full_db_bkup’ DATABASE MAXSETSIZE 100M;

Backing up backupsets
RMAN> BACKUP BACKUPSET ALL;

Backup imagecopies

RMAN> Backup as copy backupset all;

List Imagecopies

RMAN> list copy;

List Backupsets

RMAN> list backup;

Restoring and Recovering the database

Use the RESTORE and RECOVER commands for RMAN restore and recovery of physical database files.

RMAN> STARTUP FORCE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

Recovering Current Tablespaces

RMAN> SQL ‘ALTER TABLESPACE users OFFLINE’;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
SQL ‘ALTER TABLESPACE users ONLINE;

Recovering Current Datafiles

RMAN> SQL ‘ALTER DATABASE DATAFILE 7 OFFLINE’;
RESTORE DATAFILE 7;
RECOVER DATAFILE 7;
SQL ‘ALTER DATABASE DATAFILE 7 ONLINE’;

Recovering Individual Data Blocks

RMAN can recover individual corrupted datafile blocks. When RMAN performs a complete scan of a file for a backup, any corrupted blocks are listed in V$DATABASE_BLOCK_CORRUPTION. Corruption is usually reported in alert logs, trace files or results of SQL queries. Use BLOCKRECOVER to repair all corrupted blocks:

RMAN> BLOCKRECOVER CORRUPTION LIST;

You can also recover individual blocks, as shown in this example:
RMAN> BLOCKRECOVER DATAFILE 7 BLOCK 233, 235 DATAFILE 4 BLOCK 101;

Managing RMAN repository

As you know that if you do not use a recovery catalog, then control file of the target database is used as RMAN repository and eventually after some time the control file records for RMAN information will get overwritten.
Set this initialization parameter in the parameter file of the target database to determine how long records are kept:

CONTROL_FILE_RECORD_KEEP_TIME =

You can configure a retention policy to be used by RMAN to determine which backups are considered obsolete. This allows you to remove files from the repository that are no longer needed to meet your retention requirements. This policy can be based on a recovery window (the maximum number of days into the past for which you can recover) or redundancy (how many copies of each backed-up file to keep).

Two Parameters are used to set retention policies.

You can specify the days days between the current time and the earliest point of recoverability, this is called RECOVERY WINDOW. RMAN does not consider any full or level 0 incremental backup as obsolete if it falls within the recovery window.
Alternatively the REDUNDANCY parameter will instruct to store the number of copies of backup in RMAN repository. If the number of backups for a specific datafile or control file exceeds the REDUNDANCY setting considers the extra backups as obsolete.

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

This will make the REDUNDANCY setting to 3. Meaning that it will at max store 3 copies of backups and ikmages of datafile. Any more images or backups are consider obsolete.

When we run the below command

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

it will configure new retention policy based on the RECOVERY WINDOW of 7 days. This means that all the backups which falls outside this window will be considered obsolute. So in this case you need to have backup scheduled every week to have atleast 1 valid backup.

Remember that at any point of time, only one policy can be active. It can be either REDUNDANCY or RECOVERY WINDOW.

When you change the retention policy to another one, it will suspend the previous policy as shown below.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Cross checking the available backups

RMAN> CROSSCHECK BACKUP;

Crosscheck is needed when an archivelog file or backup is manually removed, i.e., not deleted by RMAN. This command ensures that data about backups in the recovery catalog or control file is synchronized with corresponding data on disk or in the media management catalog. The CROSSCHECK command operates only on files that are recorded in the recovery catalog or the control file.

The CROSSCHECK command does not delete any files that it is unable to find, but updates their repository records to EXPIRED. Then, you can run DELETE EXPIRED to remove the repository records for all expired files as well as any existing physical files whose records show the status EXPIRED.

If some backup pieces or copies were erroneously marked as EXPIRED, for example, because the media manager was misconfigured, then after ensuring that the files really do exist in the media manager, run the CROSSCHECK BACKUP command again to restore those files to AVAILABLE status.

RMAN> CROSSCHECK COPY;

This will validate the image copies.

Deleting the backups

The DELETE command removes RMAN backups and copies from DISK marks the records in control file as DELETED or removes the records from the recovery catalog (if you use a catalog).

RMAN> DELETE BACKUPSET 101, 102, 103;

RMAN> delete controlfilecopy ‘%ctl’; # Pattern search is allowed.

RMAN> delete archivelog until sequence 20;

This will delete all the archives from the oldest one till the sequence we have specified.

RMAN> delete backup of tablespace system;

RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE DISK;

List commands

RMAN> list backup;

RMAN> list copy;

RMAN> list incarnation of database;

RMAN> list expired backupset;

RMAN> list expired copy;

RMAN> list backup of tablespace sysaux;

RMAN> list copy of datafile 3;

Reporting in RMAN

RMAN> report need backup;

Reports which database files need to be backed up to meet a configured or specified retention policy

RMAN> report unrecoverable;

Reports which database files require backup because they have been affected by some NOLOGGING operation such as a direct-path insert

RMAN> report need backup recovery window of 7 days;

Displays objects requiring backup to satisfy a recovery window-based retention policy.

Suppose in the above command we want to skip a perticular tablespace like perfstat then we can use the below command.

RMAN> report need backup recovery window of 2 days database skip tablespace perfstat;

RMAN> report need backup redundancy 3;

Displays objects requiring backup to satisfy a redundancy-based retention policy.

RMAN> report need backup days 7;

Displays files that require more than n days’ worth of archived redo log files for recovery.

RMAN> report need backup incremental 7;

Displays files that require application of more than n incremental backups for recovery.

RMAN> report need backup redundancy 2 datafile 3;

Gives report of files with less than 2 redundant backups.

RMAN> report need backup tablespace htmldb;

Report of files that must be backed up to satisfy current retention policy for this tablespace (htmldb)

RMAN> report need backup incremental 2;

Report of files that need more than 2 incrementals during recovery

RMAN> report need backup device type disk;

Report of files that must be backed up to satisfy current retention policy for database

RMAN> report obsolete;
Shows backups that are obsolete according to the current retention policy.

You can add the options RECOVERY WINDOW and REDUNDANCY with this command as given below.

RMAN> report obsolete recovery window of 3 days;

RMAN> report obsolete redundancy 2;

RMAN> report schema;

This command lists and displays information about the database files.

RMAN> report schema at time ’sysdate - 14′;

This command gives report on schema 14 days ago.

RMAN> report schema at SCN 10000;

This gives report on schema at scn 10000.

RMAN> report schema at sequence 55 thread 1;

Gives report of schema at sequence 55.

RMAN configuration

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/dy/oracle/product/db10g/dbs/snapcf_test.f’; # default

We can change each of these parameters as per our requirements.

You can return any setting to its default value by using CONFIGURE… CLEAR

RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;

RMAN> CONFIGURE RETENTION POLICY CLEAR;

By default, RMAN sends all backups to an operating system specific directory on disk. So default setting for DEVICE TYPE is DISK. You can configure to make backups by default on tape or any other device as given below.

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;

You can configure backup sets or image copies as the default for a perticular device type, using either of the following commands:

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # Default becomes image copies
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # Default becomes uncompressed

You can configure RMAN to use compressed backupsets by default on a particular device type, by using the CONFIGURE DEVICE TYPE command with the BACKUP TYPE TO COMPRESSED BACKUPSET option, as shown in the following examples.

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;

To disable compression you can use below command

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO BACKUPSET;

You can configure the parallelism for a device type.

RMAM> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

You can turn on/off controlfile autobackup using

RMAN> configure controlfile autobackup off;

RMAN> configure controlfile autobackup on;

We can configure RMAN channel to write backups and images in a specific format.

The following command configures RMAN to write disk backups to the /backup directory

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;

%t is replaced with a four byte time stamp,
%s with the backup set number, and
%p with the backup piece number.

You can also configure format for controlfile autobackup as well.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE deviceSpecifier TO ’string’;

For example, you can run the following command:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘?/oradata/cf_%F’;

Here %F will be replaced by following

c-IIIIIIIIII-YYYYMMDD-QQ, where:

IIIIIIIIII stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF

You can clear the format using following command.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

ASH Report using OEM

2008-09-24T07:28:00.000-07:00

Run ASH Report

Specify the time period for the report.

Start Date
		(Example: 12/15/03)

Start Time

AMPM

End Date
		(Example: 12/15/03)

End Time

AMPM

Report Results

ASH Report For ORCL/orcl

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
ORCL	1193379843	orcl	1	10.2.0.3.0	NO	SACHCHIDA-PC

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
2	584M (100%)	360M (61.6%)	184M (31.5%)	4.0M (0.7%)

	Sample Time	Data Source
Analysis Begin Time:	24-Sep-08 10:22:32	V$ACTIVE_SESSION_HISTORY
Analysis End Time:	24-Sep-08 10:27:32	V$ACTIVE_SESSION_HISTORY
Elapsed Time:	5.0 (mins)
Sample Count:	11
Average Active Sessions:	0.04
Avg. Active Session per CPU:	0.02
Report Target:	None specified

ASH Report

Top Events

Top User Events

Event	Event Class	% Activity	Avg Active Sessions
db file sequential read	User I/O	54.55	0.02
CPU + Wait for CPU	CPU	36.36	0.01

Top Background Events

Event	Event Class	% Activity	Avg Active Sessions
os thread startup	Concurrency	9.09	0.00

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
db file sequential read	54.55	"1","27757","1"	9.09	file#	block#	blocks
		"1","60501","1"	9.09
		"3","3834","1"	9.09

Load Profile

Top Service/Module

Service	Module	% Activity	Action	% Action
SYS$USERS	Admin Connection	72.73	UNNAMED	72.73
	emagent.exe	18.18	UNNAMED	18.18
SYS$BACKGROUND	UNNAMED	9.09	UNNAMED	9.09

Back to Load Profile
Back to Top

Top Client IDs

Client ID	% Activity	Avg Active Sessions	User	Program	Service
SYS@Sachchida-PC@Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US	72.73	0.03	SYS	OMS	SYS$USERS

Back to Load Profile
Back to Top

Top SQL Command Types

'Distinct SQLIDs' is the count of the distinct number of SQLIDs with the given SQL Command Type found over all the ASH samples in the analysis period

SQL Command Type	Distinct SQLIDs	% Activity	Avg Active Sessions
PL/SQL EXECUTE	1	72.73	0.03
INSERT	1	9.09	0.00
SELECT	1	9.09	0.00

Back to Load Profile
Back to Top

Top SQL

Top SQL Statements

SQL ID	Planhash	% Activity	Event	% Event	SQL Text
8u809k64x3nzd		72.73	db file sequential read	45.45	begin DBMS_WORKLOAD_REPOSITORY...
		72.73	CPU + Wait for CPU	27.27	begin DBMS_WORKLOAD_REPOSITORY...
3kmkqn10x3m0x	1983056714	9.09	db file sequential read	9.09	INSERT INTO DBSNMP.MGMT_SNAPSH...
cp5caasd2udnw	3838994914	9.09	CPU + Wait for CPU	9.09	/* OracleOEM */ SELEC...

Top SQL using literals

No data exists for this section of the report.

Complete List of SQL Text

SQL Id	SQL Text
3kmkqn10x3m0x	INSERT INTO DBSNMP.MGMT_SNAPSHOT_SQL( SNAP_ID, SQL_ID, HASH_VALUE, CHILD_NUMBER, ELAPSED_TIME, EXECUTIONS) SELECT :B2 , SQL_ID, HASH_VALUE, -1, ELAPSED_TIME, EXECUTIONS FROM V$SQLAREA WHERE EXECUTIONS > :B1 AND PARSING_USER_ID NOT IN (SELECT USER_ID FROM DBA_USERS WHERE USERNAME IN ('SYS', 'SYSMAN', 'DBSNMP'))
8u809k64x3nzd	begin DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); end;
cp5caasd2udnw	/* OracleOEM / SELECT TO_CHAR(CAST(md.end_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, md.user_wait_time_pct, md.db_time_ps db_time_users, md.cpu_time_ps db_cpu_users, DECODE(:1, 'TRUE', md.host_cpu_usage_pct, NULL) host_cpu_usage_pct, wcd.users userio_users, :2 max_cpu_cnt FROM (SELECT DISTINCT wait_class_id FROM v$event_name WHERE wait_class = 'User I/O' AND :3 = 'TRUE') wcn, (SELECT wait_class_id, intsize_csec, end_time, time_waited / intsize_csec users FROM v$waitclassmetric_history WHERE end_time >= SYSDATE - 15/(6024)) wcd, (SELECT intsize_csec, end_time, SUM(CASE WHEN metric_name = 'Database Wait Time Ratio' THEN value ELSE 0 END) user_wait_time_pct, SUM(CASE WHEN metric_name = 'Database Time Per Sec' THEN value / 100 ELSE 0 END) db_time_ps, SUM(CASE WHEN metric_name = 'CPU Usage Per Sec' THEN value / 100 ELSE 0 END) cpu_time_ps, SUM(CASE WHEN metric_name = 'Host CPU Utilization (%)' THEN value ELSE 0 END) host_cpu_usage_pct FROM v$sysmetric_history WHERE metric_name IN ('Database Wait Time Ratio', 'Database Time Per Sec', 'CPU Usage Per Sec', 'Host CPU Utilization (%)') AND group_id = 2 AND end_time >= SYSDATE - 15/(60*24) GROUP BY intsize_csec, end_time) md WHERE wcn.wait_class_id = wcd.wait_class_id AND wcd.intsize_csec = md.intsize_csec AND wcd.end_time = md.end_time AND :4 != 'BASIC' ORDER BY md.end_time ASC

Top PL/SQL Procedures

'PL/SQL entry subprogram' represents the application's top-level entry-point(procedure, function, trigger, package initialization or RPC call) into PL/SQL.
'PL/SQL current subprogram' is the pl/sql subprogram being executed at the point of sampling . If the value is 'SQL', it represents the percentage of time spent executing SQL for the particular plsql entry subprogram

PLSQL Entry Subprogram	% Activity	PLSQL Current Subprogram	% Current
SYS.DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT#1	63.64	UNKNOWN_PLSQL_ID <9278,17>	36.36
		SQL	27.27
DBSNMP.MGMT_RESPONSE.GET_METRIC_CURS	9.09	SQL	9.09

Top Sessions

'# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity.
'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event
For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the 'Top Sessions running PQs' section for such statistics.

Sid, Serial#	% Activity	Event	% Event	User	Program	# Samples Active	XIDs
143, 346	72.73	db file sequential read	45.45	SYS	OMS	5/300 [ 2%]	2
		CPU + Wait for CPU	27.27			3/300 [ 1%]	1
139, 16	18.18	CPU + Wait for CPU	9.09	DBSNMP	emagent.exe	1/300 [ 0%]	0
		db file sequential read	9.09			1/300 [ 0%]	1
162, 1	9.09	os thread startup	9.09	SYS	ORACLE.EXE (CJQ0)	1/300 [ 0%]	0

Top Blocking Sessions

No data exists for this section of the report.

Top Sessions running PQs

No data exists for this section of the report.

Top Objects/Files/Latches

Top DB Objects

With respect to Application, Cluster, User I/O and buffer busy waits only.

Object ID	% Activity	Event	% Event	Object Name (Type)	Tablespace
9087	36.36	db file sequential read	36.36	SYS.WRH$_OSSTAT_NAME (TABLE)	SYSAUX
335	9.09	db file sequential read	9.09	SYS.KOTTD$ (TABLE)	SYSTEM
9640	9.09	db file sequential read	9.09	DBSNMP.MGMT_SNAPSHOT (TABLE)	SYSAUX

Top DB Files

With respect to Cluster and User I/O events only.

File ID	% Activity	Event	% Event	File Name	Tablespace
3	45.45	db file sequential read	45.45	C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF	SYSAUX
1	9.09	db file sequential read	9.09	C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF	SYSTEM

Top Latches

No data exists for this section of the report.

Activity Over Time

Analysis period is divided into smaller time slots
Top 3 events are reported in each of those slots
'Slot Count' shows the number of ASH samples in that slot
'Event Count' shows the number of ASH samples waiting for that event in that slot
'% Event' is 'Event Count' over all ASH samples in the analysis period

Slot Time (Duration)	Slot Count	Event	Event Count	% Event
10:22:32 (28 secs)	1	os thread startup	1	9.09
10:25:00 (1.0 min)	3	db file sequential read	3	27.27
10:26:00 (1.0 min)	7	CPU + Wait for CPU	4	36.36
		db file sequential read	3	27.27

End of Report

RMAN Recovery - All data files or tables using OEM

2008-09-24T07:17:00.000-07:00

Perform Recovery

Whole Database Recovery

		Restore all datafiles
		Specify Time, SCN or log sequence. The backup taken at or prior to that time will be used. No recovery will be performed in this operation.

Object Level Recovery

Object Type

Operation Type

Flashback Existing Tables

Flashback Dropped Tables

	Encryption

Host Credentials

To perform recovery, supply operating system login credentials to access the target database.

* Username

* Password

		Save as Preferred Credential

Overview

Restore and/or recover the entire database or selected objects
Restore files to a new location
Recover tablespaces to a point-in-time based on a timestamp, system change number (SCN), or log sequence number
Recover datafile data blocks that are marked as corrupted, or based on datafile block IDs or tablespace block addresses
Flashback database or tables to a specific system change number (SCN) or timestamp

Export/Import Usage on Data Incompatible with a Previous Release

2008-09-23T06:57:00.001-07:00

When you export data to a previous release, data that is incompatible with the previous release either is not exported at all or is exported with the loss of some features.

In general, if you must export data to a previous release, then first remove as many incompatibilities with the previous release as possible before you export the data.

To upgrade a database using the Export/Import utilities, complete the following steps:

Export data from the current database using the Export utility shipped with the current database. See the current Oracle Database Utilities documentation for information about using the Export utility on the current database.

To ensure a consistent export, make sure the current database is not available for updates during and after the export. If the current database is available to users for updates after the export, then, prior to making the current database available, put procedures in place to copy the changes made in the current database to the new database after the import is complete.
Install the new Oracle Database software. Installation is operating system specific. Installation steps for Oracle Database are covered in your operating system-specific Oracle documentation.
If the new database has the same name as the current database, then shut down the current database before creating the new database.
Create the new database.
Start SQL*Plus in the new Oracle Database environment.
Connect to the database instance as a user with SYSDBA privileges.
Start an Oracle Database instance using STARTUP.
Optionally, you can change the storage parameters from the source database.

You can pre-create tablespaces, users, and tables in the new database to improve space usage by changing storage parameters. When you pre-create tables using SQL*Plus, either run the database in the original database compatibility mode or make allowances for the specific data definition conversions that occur during import. When items have been pre-created, specify one of the following options:
- TABLE_EXISTS_ACTION=APPEND for Data Pump Import
- IGNORE=Y for original Import
Note:
If the new database is created on the same computer as the source database, and you do not want to overwrite the source database data files, then you must pre-create the tablespaces and specify one of the following options when you import:
- REUSE_DATAFILES=N for Data Pump Import
  
  Optionally, consider using the REMAP_DATAFILE, REMAP_TABLESPACE and REMAP_TABLE options so that references to the old names in the dump file set are remapped to new, non-colliding names.
- DESTROY=N for original Import.
Use the Import utility of the new database to import the objects exported from the current database. Include one of the following parameters to save the informational and error messages from the import session to a file:
- The LOGFILE parameter for Data Pump Import
- The LOG parameter for original ImportAfter the import, check the import log file for information about which imports of which objects completed successfully and, if there were failures, which failed.
- Use further Import scenarios or SQL scripts that create the database's objects to clean up incomplete imports (or possibly to start an entirely new import).
  
  Note:
  If a Data Pump Export or Import job encounters a fatal error, then the job can be restarted after the condition inducing the failure is corrected. The job then continues automatically from the point of failure.
- If changes are made to the current database after the export, then make sure those changes are propagated to the new database prior to making it available to users. See Step 1 for more information.

Export and Import Requirements for Downgrades

2008-09-23T06:56:00.000-07:00

The Oracle Database release to which you downgrade can be one release older, at the most. For example, if the current database is Oracle Database 11g Release 1 (11.1), then Oracle Database 10g Release 2 (10.2) is the only release supported for the downgrade.

Exporting Data From Release 11.1 and Importing Into Earlier Releases

Export From	Import To	Export Version to Use	Import Version to Use
Release 11.1	Release 10.2	Data Pump Export Release 11.1 with `VERSION=10.2`	Data Pump Import Release 10.2
Release 10.2	Release 10.1	Data Pump Export Release 10.2 with `VERSION=10.1`	Data Pump Import Release 10.1

To obtain a downward compatible dump file:

With Data Pump Export, use the current version's Data Pump Export utility, but set the VERSION parameter to the target older version.

Data Pump Import cannot read dump file sets created by a database version that is newer than the current database version, unless those dump file sets were created with the VERSION parameter set to the version of the target database. Therefore, the best way to perform a downgrade is to perform your Data Pump export with the VERSION parameter set to the version of the target database.

With original Export, run an older version of Export (exp) to produce a dump file that is compatible with the database version to which you want to downgrade. Then, run the target's original Import utility.

Original Export dump files are not downward compatible with the Import utilities of previous Oracle Database releases. That is, exported data cannot be imported by the Import utilities of previous Oracle Database releases. For example, an Oracle9i Export dump file cannot be imported by an Oracle8i Import utility, and an Oracle Database 10g export dump file cannot be imported by an Oracle9i Import utility. The following tables provide specific examples.

Export and Import Requirements for Upgrades

2008-09-23T06:54:00.000-07:00

Dump files created by one of the Export utilities can be imported into all future releases of Oracle Database. For example, an Oracle8 original Export dump file can be imported by the Oracle8i, Oracle9i, and Oracle Database 10g original Import utility.

Exporting Data From Releases Earlier Than 11.1 and Importing Into Release 11.1

Export From	Import To	Export Version to Use	Import Version to Use
Release 10.2	Release 11.1	Data Pump Export Release 10.2	Data Pump Import Release 11.1
Release 10.1	Release 11.1	Data Pump Export Release 10.1	Data Pump Import Release 11.1
Release 9.2	Release 11.1	Original Export Release 9.2	Original Import Release 11.1
Release 8.1.7	Release 11.1	Original Export Release 8.1.7	Original Import Release 11.1
Release 8.0.6	Release 11.1	Original Export Release 8.0.6	Original Import Release 11.1
Release 7.3.4	Release 11.1	Original Export Release 7.3.4	Original Import Release 11.1

TABLE_EXISTS_ACTION

2008-09-21T15:09:00.000-07:00

Default: SKIP (Note that if CONTENT=DATA_ONLY is specified, the default is APPEND, not SKIP.)

Purpose

Tells Import what to do if the table it is trying to create already exists.

Syntax and Description

TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}

The possible values have the following effects:

SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
APPEND loads rows from the source and leaves existing rows unchanged.
TRUNCATE deletes existing rows and then loads rows from the source.
REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

The following considerations apply when you are using these options:

When you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.
When you use SKIP, APPEND, or TRUNCATE, existing table-dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. For REPLACE, the dependent objects are dropped and re-created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.
When you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action.

The existing table is loaded using the external tables access method because the external tables feature honors active constraints and triggers. However, be aware that if any row violates an active constraint, the load fails and no data is loaded.

If you have data that must be loaded, but may cause constraint violations, consider disabling the constraints, loading the data, and then deleting the problem rows before reenabling the constraints.
When you use APPEND, the data is always loaded into new space; existing space, even if available, is not reused. For this reason, you may wish to compress your data after the load.

Restrictions

TRUNCATE cannot be used on clustered tables or over network links.

Example

The following is an example of using the TABLE_EXISTS_ACTION parameter. You can create the expfull.dmp dump file used in this example by running the example provided for the Export FULL parameter.

> impdp hr/hr TABLES=employees DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp
TABLE_EXISTS_ACTION=REPLACE

SQLFILE

2008-09-21T15:08:00.000-07:00

Purpose

Specifies a file into which all of the SQL DDL that Import would have executed, based on other parameters, is written.

Syntax and Description

SQLFILE=[directory_object:]file_name

The file_name specifies where the import job will write the DDL that would be executed during the job. The SQL is not actually executed, and the target system remains unchanged. The file is written to the directory object specified in the DIRECTORY parameter, unless another directory_object is explicitly specified here. Any existing file that has a name matching the one specified with this parameter is overwritten.

Note that passwords are commented out in the SQL file. For example, if a CONNECT statement is part of the DDL that was executed, it will be commented out and the schema name will be shown but the password will not. In the following example, the dashes indicate that a comment follows, and the hr schema name is shown, but not the password.

-- CONNECT hr

Therefore, before you can execute the SQL file, you must edit it by removing the dashes indicating a comment and adding the password for the hr schema (in this case, the password is also hr), as follows:

CONNECT hr/hr

Example

The following is an example of using the SQLFILE parameter. You can create the expfull.dmp dump file used in this example by running the example provided for the Export FULL parameter.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp
SQLFILE=dpump_dir2:expfull.sql

A SQL file named expfull.sql is written to dpump_dir2.

SKIP_UNUSABLE_INDEXES

2008-09-21T15:07:00.002-07:00

Purpose

Specifies whether or not Import skips loading tables that have indexes that were set to the Index Unusable state (by either the system or the user).

Syntax and Description

SKIP_UNUSABLE_INDEXES={y | n}

If SKIP_UNUSABLE_INDEXES is set to y, then the import job does not load tables that have indexes that were previously set to an Index Unusable state. Other tables, not previously set Unusable, continue to be updated as rows are inserted.

This parameter enables you to postpone index maintenance on selected index partitions until after row data has been inserted. You then have the responsibility to rebuild the affected index partitions after the import.

If SKIP_UNUSABLE_INDEXES is set to n (the default) and an index in the Unusable state is encountered, the load of that table or partition will fail.

Example

The following is an example of using the SKIP_UNUSABLE_INDEXES parameter. You can create the expfull.dmp dump file used in this example by running the example provided for the Export FULL parameter.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp LOGFILE=skip.log
SKIP_UNUSABLE_INDEXES=y

SCHEMAS

2008-09-21T15:07:00.001-07:00

Purpose

Specifies that a schema-mode import is to be performed.

Syntax and Description

SCHEMAS=schema_name [,...]

If you have the IMP_FULL_DATABASE role, you can use this parameter to perform a schema-mode import by specifying a single schema other than your own or a list of schemas to import. First, the schemas themselves are created (if they do not already exist), including system and role grants, password history, and so on. Then all objects contained within the schemas are imported. Nonprivileged users can specify only their own schemas. In that case, no information about the schema definition is imported, only the objects contained within it.

Example

The following is an example of using the SCHEMAS parameter. You can create the expdat.dmp file used in this example by running the example provided for the Export SCHEMAS parameter.

> impdp hr/hr SCHEMAS=hr,oe DIRECTORY=dpump_dir1 LOGFILE=schemas.log
DUMPFILE=expdat.dmp

The hr and oe schemas are imported from the expdat.dmp file. The log file, schemas.log, is written to dpump_dir1.

REUSE_DATAFILES

2008-09-21T15:05:00.002-07:00

Purpose

Specifies whether or not the import job should reuse existing datafiles for tablespace creation.

Syntax and Description

REUSE_DATAFILES={y | n}

If the default (n) is used and the datafiles specified in CREATE TABLESPACE statements already exist, an error message from the failing CREATE TABLESPACE statement is issued, but the import job continues.

If this parameter is specified as y, a warning is issued and the existing datafiles are reinitialized. Be aware that specifying Y can result in a loss of data.

Example

The following is an example of using the REUSE_DATAFILES parameter. You can create the expfull.dmp dump file used in this example by running the example provided for the Export FULL parameter.

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp LOGFILE=reuse.log
REUSE_DATAFILES=Y

This example reinitializes datafiles referenced by CREATE TABLESPACE statements in the expfull.dmp file.

REMAP_TABLESPACE

2008-09-21T15:05:00.001-07:00

Purpose

Remaps all objects selected for import with persistent data in the source tablespace to be created in the target tablespace.

Syntax and Description

REMAP_TABLESPACE=source_tablespace:target_tablespace

Multiple REMAP_TABLESPACE parameters can be specified, but no two can have the same source tablespace. The target schema must have sufficient quota in the target tablespace.

Note that use of the REMAP_TABLESPACE parameter is the only way to remap a tablespace in Data Pump Import. This is a simpler and cleaner method than the one provided in the original Import utility. In original Import, if you wanted to change the default tablespace for a user, you had to perform several steps, including exporting and dropping the user, creating the user in the new tablespace, and importing the user from the dump file. That method was subject to many restrictions (including the number of tablespace subclauses) which sometimes resulted in the failure of some DDL commands.

By contrast, the Data Pump Import method of using the REMAP_TABLESPACE parameter works for all objects, including the user, and it works regardless of how many tablespace subclauses are in the DDL statement.

Example

The following is an example of using the REMAP_TABLESPACE parameter.

> impdp hr/hr REMAP_TABLESPACE='tbs_1':'tbs_6' DIRECTORY=dpump_dir1 PARALLEL=2
JOB_NAME=cf1n02 DUMPFILE=employees.dmp NOLOGFILE=Y

REMAP_SCHEMA

2008-09-21T15:04:00.001-07:00

Purpose

Loads all objects from the source schema into a target schema.

Syntax and Description

REMAP_SCHEMA=source_schema:target_schema

Multiple REMAP_SCHEMA lines can be specified, but the source schema must be different for each one. However, different source schemas can map to the same target schema. The mapping may not be 100 percent complete, because there are certain schema references that Import is not capable of finding. For example, Import will not find schema references embedded within the body of definitions of types, views, procedures, and packages.

This parameter requires the IMP_FULL_DATABASE role.

If the schema you are remapping to does not already exist, the import operation creates it, provided the dump file set contains the necessary CREATE USER metadata and you are importing with enough privileges. For example, the following Export commands would create the dump file sets with the necessary metadata to create a schema, because the user SYSTEM has the necessary privileges:

> expdp SYSTEM/password SCHEMAS=hr
> expdp SYSTEM/password FULL=y

If you do not have enough privileges to perform an import that creates dump files containing the metadata necessary to create a schema, then you must create the target schema before performing the import operation. This is because the dump files do not contain the necessary information for the import to create the schema automatically.

If the import operation does create the schema, then after the import is complete, you must assign it a valid password in order to connect to it. The SQL statement to do this, which requires privileges, is:

SQL> ALTER USER [schema_name] IDENTIFIED BY [new_pswd]

When you remap a schema, it is possible to constrain the rows loaded into the destination table by specifying a query. However, Data Pump Import performs remapping before applying any queries. This means that by the time the query is applied, the schema has already been remapped. Therefore, to constrain the rows that are loaded, the query must be applied to the target schema table rather than to the source schema table.

Example

Suppose that you execute the following Export and Import commands to remap the hr schema into the blake schema:

> expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp


> impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
REMAP_SCHEMA=hr:scott

In this example, if user scott already exists before the import, then the Import REMAP_SCHEMA command will add objects from the hr schema into the existing scott schema. You can connect to the scott schema after the import by using the existing password (without resetting it).

If user scott does not exist before you execute the import operation, Import automatically creates it with an unusable password. This is possible because the dump file, hr.dmp, was created by SYSTEM, which has the privileges necessary to create a dump file that contains the metadata needed to create a schema. However, you cannot connect to scott on completion of the import, unless you reset the password for scott on the target database after the import completes.

REMAP_DATAFILE

2008-09-21T15:03:00.001-07:00

Purpose

Changes the name of the source datafile to the target datafile name in all SQL statements where the source datafile is referenced: CREATE TABLESPACE, CREATE LIBRARY, and CREATE DIRECTORY.

Syntax and Description

REMAP_DATAFILE=source_datafile:target_datafile

Remapping datafiles is useful when you move databases between platforms that have different file naming conventions. The source_datafile and target_datafile names should be exactly as you want them to appear in the SQL statements where they are referenced. Oracle recommends that you enclose datafile names in quotation marks to eliminate ambiguity on platforms for which a colon is a valid file specification character.

You must have the IMP_FULL_DATABASE role to specify this parameter.

Example

The following is an example of using the REMAP_DATAFILE parameter.

> impdp hr/hr FULL=y DIRECTORY=dpump_dir1 DUMPFILE=db_full.dmp
REMAP_DATAFILE='DB1$:[HRDATA.PAYROLL]tbs6.f':'/db1/hrdata/payroll/tbs6.f'

This example remaps a VMS file specification (DR1$:[HRDATA.PAYROLL]tbs6.f) to a UNIX file specification, (/db1/hrdata/payroll/tbs6.f) for all SQL DDL statements during the import. The dump file, db_full.dmp, is located in the directory object, dpump_dir1.

Using Directory Objects When Automatic Storage Management Is Enabled

2008-09-21T15:01:00.001-07:00

If you use Data Pump Export or Import with Automatic Storage Management (ASM) enabled, you must define the directory object used for the dump file so that the ASM disk-group name is used (instead of an operating system directory path). A separate directory object, which points to an operating system directory path, should be used for the log file. For example, you would create a directory object for the ASM dump file as follows:

SQL> CREATE or REPLACE DIRECTORY dpump_dir as '+DATAFILES/';

Then you would create a separate directory object for the log file:

SQL> CREATE or REPLACE DIRECTORY dpump_log as '/homedir/user1/';

To enable user hr to have access to these directory objects, you would assign the necessary privileges, for example:

SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir TO hr;
SQL> GRANT READ, WRITE ON DIRECTORY dpump_log TO hr;

You would then use the following Data Pump Export command:

> expdp hr/hr DIRECTORY=dpump_dir DUMPFILE=hr.dmp LOGFILE=dpump_log:hr.log

Using Substitution Variables in Oracle Data Pump

2008-09-21T15:00:00.001-07:00

Instead of, or in addition to, listing specific filenames, you can use the DUMPFILE parameter during export operations to specify multiple dump files, by using a substitution variable (%U) in the filename. This is called a dump file template. The new dump files are created as they are needed, beginning with 01 for %U, then using 02, 03, and so on. Enough dump files are created to allow all processes specified by the current setting of the PARALLEL parameter to be active. If one of the dump files becomes full because its size has reached the maximum size specified by the FILESIZE parameter, it is closed, and a new dump file (with a new generated name) is created to take its place.

If multiple dump file templates are provided, they are used to generate dump files in a round-robin fashion. For example, if expa%U, expb%U, and expc%U were all specified for a job having a parallelism of 6, the initial dump files created would be expa01.dmp, expb01.dmp, expc01.dmp, expa02.dmp, expb02.dmp, and expc02.dmp.

For import and SQLFILE operations, if dump file specifications expa%U, expb%U, and expc%U are specified, then the operation will begin by attempting to open the dump files expa01.dmp, expb01.dmp, and expc01.dmp. If the dump file containing the master table is not found in this set, the operation expands its search for dump files by incrementing the substitution variable and looking up the new filenames (for example, expa02.dmp, expb02.dmp, and expc02.dmp). The search continues until the dump file containing the master table is located. If a dump file does not exist, the operation stops incrementing the substitution variable for the dump file specification that was in error. For example, if expb01.dmp and expb02.dmp are found but expb03.dmp is not found, then no more files are searched for using the expb%U specification. Once the master table is found, it is used to determine whether all dump files in the dump file set have been located.

Maximizing Data Pump Job Performance

2008-09-21T14:58:00.000-07:00

To improve throughput of a job, you can use the PARALLEL parameter to set a degree of parallelism that takes maximum advantage of current conditions. For example, to limit the effect of a job on a production system, the database administrator (DBA) might wish to restrict the parallelism. The degree of parallelism can be reset at any time during a job. For example, PARALLEL could be set to 2 during production hours to restrict a particular job to only two degrees of parallelism, and during nonproduction hours it could be reset to 8. The parallelism setting is enforced by a master process, which allocates work to be executed to a set of worker processes that perform the data and metadata processing within an operation. These worker processes operate in parallel.

What Happens During Execution of a Data Pump Job?

2008-09-21T14:57:00.000-07:00

Data Pump jobs use a master table, a master process, and worker processes to perform the work and keep track of progress.

Coordination of a Job

For every Data Pump Export job and Data Pump Import job, a master process is created. The master process controls the entire job, including communicating with the clients, creating and controlling a pool of worker processes, and performing logging operations.

Tracking Progress Within a Job

While the data and metadata are being transferred, a master table is used to track the progress within a job. The master table is implemented as a user table within the database. The specific function of the master table for export and import jobs is as follows:

For export jobs, the master table records the location of database objects within a dump file set. Export builds and maintains the master table for the duration of the job. At the end of an export job, the content of the master table is written to a file in the dump file set.
For import jobs, the master table is loaded from the dump file set and is used to control the sequence of operations for locating objects that need to be imported into the target database.

The master table is created in the schema of the current user performing the export or import operation. Therefore, that user must have sufficient tablespace quota for its creation. The name of the master table is the same as the name of the job that created it. Therefore, you cannot explicitly give a Data Pump job the same name as a preexisting table or view.

For all operations, the information in the master table is used to restart a job.

The master table is either retained or dropped, depending on the circumstances, as follows:

Upon successful job completion, the master table is dropped.
If a job is stopped using the STOP_JOB interactive command, the master table is retained for use in restarting the job.
If a job is killed using the KILL_JOB interactive command, the master table is dropped and the job cannot be restarted.
If a job terminates unexpectedly, the master table is retained. You can delete it if you do not intend to restart the job.

How Does Data Pump Access Data?

2008-09-21T14:56:00.000-07:00

Data Pump supports two access methods to load and unload table row data: direct path and external tables. Because both methods support the same external data representation, data that is unloaded with one method can be loaded using the other method. Data Pump automatically chooses the fastest method appropriate for each table.

Data Pump also uses functionality provided in the DBMS_METADATA PL/SQL package to handle all operations involving metadata, including complete extraction, transformation, and re-creation of all database object definitions.

Direct Path Loads and Unloads

The Oracle database has provided direct path unload capability for export operations since Oracle release 7.3 and a direct path API for OCI since Oracle8i. Data Pump technology enhances direct path technology in the following ways:

Support of a direct path, proprietary format unload.
Improved performance through elimination of unnecessary conversions. This is possible because the direct path internal stream format is used as the format stored in the Data Pump dump files.
Support of additional datatypes and transformations.

Data Pump uses direct path load and unload when the structure of a table allows it.

In the following circumstances, Data Pump cannot use direct path loading:

A global index on multipartition tables exists during a single-partition load. This includes object tables that are partitioned.
A domain index exists for a LOB column.
A table is in a cluster.
A table has an active trigger.
A table has fine-grained access control enabled in insert mode.
A table contains BFILE columns or columns of opaque types.
A referential integrity constraint is present.
A table contains VARRAY columns with an embedded opaque type.

If any of these conditions exist for a table, Data Pump uses external tables rather than direct path to move the data for that table.

External Tables

The Oracle database has provided an external tables capability since Oracle9i that allows reading of data sources external to the database. As of Oracle Database 10g, the external tables feature also supports writing database data to destinations external to the database. Data Pump provides an external tables access driver (ORACLE_DATAPUMP) that reads and writes files. The format of the files is the same format used with the direct path method. This allows for high-speed loading and unloading of database tables. Data Pump uses external tables as the data access mechanism in the following situations:

Loading and unloading very large tables and partitions in situations where parallel SQL can be used to advantage
Loading tables with global or domain indexes defined on them, including partitioned object tables
Loading tables with active triggers or clustered tables
Loading and unloading tables with encrypted columns
Loading tables with fine-grained access control enabled for inserts
Loading tables that are partitioned differently at load time and unload time

DBA_DATAPUMP_JOBS View and USER_DATAPUMP_JOBS View

2008-09-21T14:55:00.000-07:00

DBA_DATAPUMP_JOBS View and USER_DATAPUMP_JOBS View
The DBA_DATAPUMP_JOBS and USER_DATAPUMP_JOBS views identify all active Data Pump jobs, regardless of their state, on an instance (or on all instances for Real Application Clusters). They also show all Data Pump master tables not currently associated with an active job. You can use the job information to attach to an active job. Once you are attached to the job, you can stop it, change its parallelism, or monitor its progress. You can use the master table information to restart a stopped job or to remove any master tables that are no longer needed.

Column	Datatype	Description
OWNER_NAME	VARCHAR2(30)	User who initiated the job (valid only for `DBA_DATAPUMP_JOBS)`
JOB_NAME	VARCHAR2(30)	User-supplied name for the job (or the default name generated by the server)
OPERATION	VARCHAR2(30)	Type of job
JOB_MODE	VARCHAR2(30)	Mode of job
STATE	VARCHAR2(30)	State of the job
DEGREE	NUMBER	Number of worker processes performing the operation
ATTACHED_SESSIONS	NUMBER	Number of sessions attached to the job

DBA_DATAPUMP_SESSIONS View

2008-09-21T14:54:00.001-07:00

The DBA_DATAPUMP_SESSIONS view identifies the user sessions that are attached to a job. The information in this view is useful for determining why a stopped operation has not gone away.

The DBA_DATAPUMP_SESSIONS View

Column	Datatype	Description
OWNER_NAME	VARCHAR2(30)	User who initiated the job.
JOB_NAME	VARCHAR2(30)	User-supplied name for the job (or the default name generated by the server).
SADDR	RAW(4) (RAW(8) on 64-bit systems)	Address of session attached to the job. Can be used with `V$SESSION` view.

Default Locations for Dump, Log, and SQL Files

2008-09-21T14:53:00.001-07:00

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 /usr/apps/datafiles.

SQL> CREATE DIRECTORY dpump_dir1 AS '/usr/apps/datafiles';

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.

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. Then, when you are using Export or Import, you specify the directory object with the DIRECTORY parameter.

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_dir1 TO hr;

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.

Data Pump Export and Import use the following order of precedence to determine a file's location:

If a directory object is specified as part of the file specification, then the location specified by that directory object is used. (The directory object must be separated from the filename by a colon.)
If a directory object is not specified for a file, then the directory object named by the DIRECTORY parameter is used.
If a directory object is not specified, and if no directory object was named by the DIRECTORY parameter, then the value of the environment variable, DATA_PUMP_DIR, is used. This environment variable is defined using operating system commands on the client system where the Data Pump Export and Import utilities are run. The value assigned to this client-based environment variable must be the name of a server-based directory object, which must first be created on the server system by a DBA. For example, the following SQL statement creates a directory object on the server system. The name of the directory object is DUMP_FILES1, and it is located at '/usr/apps/dumpfiles1'.
```
SQL> CREATE DIRECTORY DUMP_FILES1 AS '/usr/apps/dumpfiles1';
```
Then, a user on a UNIX-based client system using csh can assign the value DUMP_FILES1 to the environment variable DATA_PUMP_DIR. The DIRECTORY parameter can then be omitted from the command line. The dump file employees.dmp, as well as the log file export.log, will be written to '/usr/apps/dumpfiles1'.
```
%setenv DATA_PUMP_DIR DUMP_FILES1
%expdp hr/hr TABLES=employees DUMPFILE=employees.dmp
```
If none of the previous three conditions yields a directory object and you are a privileged user, then Data Pump attempts to use the value of the default server-based directory object, DATA_PUMP_DIR. It is important to understand that Data Pump does not create the DATA_PUMP_DIR directory object; it merely attempts to use its value when a privileged user has not provided a directory object using any of the mechanisms previously described. This default directory object must first be created by a DBA. Do not confuse this with the client-based environment variable of the same name.

Original Export and Import Versus Data Pump Export and Import

2008-09-21T14:51:00.001-07:00

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 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.
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.

Oracle Data Pump New Featurs

2008-09-21T14:47:00.000-07:00

1.The ability to specify the maximum number of threads of active execution operating on behalf of the Data Pump job. This enables you to adjust resource consumption versus elapsed time. See PARALLEL for information about using this parameter in export. See PARALLEL for information about using this parameter in import. (This feature is available only in the Enterprise Edition of Oracle Database 10g.)

2.The ability to restart Data Pump jobs. See START_JOB for information about restarting export jobs. See START_JOB for information about restarting import jobs.

3.The ability to detach from and reattach to long-running jobs without affecting the job itself. This allows DBAs and other operations personnel to monitor jobs from multiple locations. The Data Pump Export and Import utilities can be attached to only one job at a time; however, you can have multiple clients or jobs running at one time. (If you are using the Data Pump API, the restriction on attaching to only one job at a time does not apply.) You can also have multiple clients attached to the same job. See ATTACH for information about using this parameter in export. See ATTACH for information about using this parameter in import.

4.Support for export and import operations over the network, in which the source of each operation is a remote instance. See NETWORK_LINK for information about using this parameter in export. See NETWORK_LINK for information about using this parameter in import.

5.The ability, in an import job, to change the name of the source datafile to a different name in all DDL statements where the source datafile is referenced. See REMAP_DATAFILE.

6.Enhanced support for remapping tablespaces during an import operation. See REMAP_TABLESPACE.

7.Support for filtering the metadata that is exported and imported, based upon objects and object types. For information about filtering metadata during an export operation, see INCLUDE and EXCLUDE. For information about filtering metadata during an import operation, see INCLUDE and EXCLUDE.

8. Support for an interactive-command mode that allows monitoring of and interaction with ongoing jobs. See Commands Available in Export's Interactive-Command Mode and Commands Available in Import's Interactive-Command Mode.

9.The ability to estimate how much space an export job would consume, without actually performing the export. See ESTIMATE_ONLY.

10. The ability to specify the version of database objects to be moved. In export jobs, VERSION applies to the version of the database objects to be exported. See VERSION for more information about using this parameter in export.

In import jobs, VERSION applies only to operations over the network. This means that VERSION applies to the version of database objects to be extracted from the source database. See VERSION for more information about using this parameter in import.

11.Most Data Pump export and import operations occur on the Oracle database server. (This contrasts with original export and import, which were primarily client-based.) See Default Locations for Dump, Log, and SQL Files for information about some of the implications of server-based operations.

What New Features Do Data Pump Export and Import Provide?

2008-09-21T14:45:00.000-07:00

The new Data Pump Export and Import utilities (invoked with the expdp and impdp commands, respectively) have a similar look and feel to the original Export (exp) and Import (imp) utilities, but they are completely separate. Dump files generated by the new Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility.

Oracle recommends that you use the new Data Pump Export and Import utilities because they support all Oracle Database 10g features, except for XML schemas. Original Export and Import support the full set of Oracle database release 9.2 features. Also, the design of Data Pump Export and Import results in greatly enhanced data movement performance over the original Export and Import utilities.

The following are the major new features that provide this increased performance, as well as enhanced ease of use:

* The ability to specify the maximum number of threads of active execution operating on behalf of the Data Pump job. This enables you to adjust resource consumption versus elapsed time. See PARALLEL for information about using this parameter in export. See PARALLEL for information about using this parameter in import. (This feature is available only in the Enterprise Edition of Oracle Database 10g.)
* The ability to restart Data Pump jobs. See START_JOB for information about restarting export jobs. See START_JOB for information about restarting import jobs.
* The ability to detach from and reattach to long-running jobs without affecting the job itself. This allows DBAs and other operations personnel to monitor jobs from multiple locations. The Data Pump Export and Import utilities can be attached to only one job at a time; however, you can have multiple clients or jobs running at one time. (If you are using the Data Pump API, the restriction on attaching to only one job at a time does not apply.) You can also have multiple clients attached to the same job. See ATTACH for information about using this parameter in export. See ATTACH for information about using this parameter in import.
* Support for export and import operations over the network, in which the source of each operation is a remote instance. See NETWORK_LINK for information about using this parameter in export. See NETWORK_LINK for information about using this parameter in import.
* The ability, in an import job, to change the name of the source datafile to a different name in all DDL statements where the source datafile is referenced. See REMAP_DATAFILE.
* Enhanced support for remapping tablespaces during an import operation. See REMAP_TABLESPACE.
* Support for filtering the metadata that is exported and imported, based upon objects and object types. For information about filtering metadata during an export operation, see INCLUDE and EXCLUDE. For information about filtering metadata during an import operation, see INCLUDE and EXCLUDE.
* Support for an interactive-command mode that allows monitoring of and interaction with ongoing jobs. See Commands Available in Export's Interactive-Command Mode and Commands Available in Import's Interactive-Command Mode.
* The ability to estimate how much space an export job would consume, without actually performing the export. See ESTIMATE_ONLY.
* The ability to specify the version of database objects to be moved. In export jobs, VERSION applies to the version of the database objects to be exported. See VERSION for more information about using this parameter in export.

In import jobs, VERSION applies only to operations over the network. This means that VERSION applies to the version of database objects to be extracted from the source database. See VERSION for more information about using this parameter in import.
* Most Data Pump export and import operations occur on the Oracle database server. (This contrasts with original export and import, which were primarily client-based.) See Default Locations for Dump, Log, and SQL Files for information about some of the implications of server-based operations.

Data Pump Components

2008-09-21T14:43:00.000-07:00

Oracle Data Pump is made up of three distinct parts:

The command-line clients, expdp and impdp
The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)
The DBMS_METADATA PL/SQL package (also known as the Metadata API)

The Data Pump clients, expdp and impdp, invoke the Data Pump Export utility and Data Pump Import utility, respectively. They provide a user interface that closely resembles the original export (exp) and import (imp) utilities.

The expdp and impdp clients use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, using the parameters entered at the command-line. These parameters enable the exporting and importing of data and metadata for a complete database or subsets of a database.

All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the server. This means that the data base administrator (DBA) must create directory objects.

When data is moved, Data Pump automatically uses either direct path load (or unload) or the external tables mechanism, or a combination of both. When metadata is moved, Data Pump uses functionality provided by the DBMS_METADATA PL/SQL package. The DBMS_METADATA package provides a centralized facility for the extraction, manipulation, and resubmission of dictionary metadata.

The DBMS_DATAPUMP and DBMS_METADATA PL/SQL packages can be used independently of the Data Pump clients.

Data Pump - Operating on Specific Objects

2008-09-21T14:40:00.002-07:00

Ever had a need to export only certain procedures from one user to be recreated in a different database or user? Unlike the traditional export utility, Data Pump allows you to export only a particular type of object. For instance, the following command lets you export only procedures, and nothing else--no tables, views, or even functions:

expdp bob/iclaim directory=DPDATA1 dumpfile=expprocs.dmp include=PROCEDURE

To export only a few specific objects--say, function FUNC1 and procedure PROC1--you could use

expdp bob/iclaim directory=DPDATA1 dumpfile=expprocs.dmp
 include=PROCEDURE:\"=\'PROC1\'\",FUNCTION:\"=\'FUNC1\'\"

This dumpfile serves as a backup of the sources. You can even use it to create DDL scripts to be used later. A special parameter called SQLFILE allows the creation of the DDL script file.

impdp bob/iclaim directory=DPDATA1 dumpfile=expprocs.dmp sqlfile=procs.sql

This instruction creates a file named procs.sql in the directory specified by DPDATA1, containing the scripts of the objects inside the export dumpfile. This approach helps you create the sources quickly in another schema.

Using the parameter INCLUDE allows you to define objects to be included or excluded from the dumpfile. You can use the clause INCLUDE=TABLE:"LIKE 'TAB%'" to export only those tables whose name start with TAB. Similarly, you could use the construct INCLUDE=TABLE:"NOT LIKE 'TAB%'" to exclude all tables starting with TAB. Alternatively you can use the EXCLUDE parameter to exclude specific objects.

Expdp and impdp help

expdp help=y

Similarly, impdp help=y will show all the parameters in DPI.

While Data Pump jobs are running, you can pause them by issuing STOP_JOB on the DPE or DPI prompts and then restart them with START_JOB. This functionality comes in handy when you run out of space and want to make corrections before continuing.

Data Pump Import

2008-09-21T14:40:00.001-07:00

Data import performance is where Data Pump really stands out, however. To import the data exported earlier, we will use

impdp bob/abc123 directory=dpdata1 dumpfile=expCASES.dmp job_name=cases_import

The default behavior of the import process is to create the table and all associated objects, and to produce an error when the table exists. Should you want to append the data to the existing table, you could use TABLE_EXISTS_ACTION=APPEND in the above command line.

As with Data Pump Export, pressing Control-C on the process brings up the interactive mode of Date Pump Import (DPI); again, the prompt is Import>.

Data Pump Parallel Operation

2008-09-21T14:38:00.000-07:00

You can accelerate jobs significantly using more than one thread for the export, through the PARALLEL parameter. Each thread creates a separate dumpfile, so the parameter dumpfile should have as many entries as the degree of parallelism. Instead of entering each one explicitly, you can specify wildcard characters as filenames such as:

expdp bob/abc123 tables=CASES directory=DPDATA1
 dumpfile=expCASES_%U.dmp parallel=4 job_name=Cases_Export

Note how the dumpfile parameter has a wild card %U, which indicates the files will be created as needed and the format will be expCASES_nn.dmp, where nn starts at 01 and goes up as needed.

In parallel mode, the status screen will show four worker processes. (In default mode, only one process will be visible.) All worker processes extract data simultaneously and show their progress on the status screen.

It's important to separate the I/O channels for access to the database files and the dumpfile directory filesystems. Otherwise, the overhead associated with maintaining the Data Pump jobs may outweigh the benefits of parallel threads and hence degrade performance. Parallelism will be in effect only if the number of tables is higher than the parallel value and the tables are big.

Data Pump Export Monitoring

2008-09-21T14:37:00.000-07:00

Export Monitoring

While Data Pump Export (DPE) is running, press Control-C; it will stop the display of the messages on the screen, but not the export process itself. Instead, it will display the DPE prompt as shown below. The process is now said to be in "interactive" mode:

Export>

This approach allows several commands to be entered on that DPE job. To find a summary, use the STATUS command at the prompt:

Export> status
Job: CASES_EXPORT
 Operation: EXPORT                        
 Mode: TABLE                         
 State: EXECUTING                     
 Degree: 1
 Job Error Count: 0
 Dump file:  /u02/dpdata1/expCASES.dmp
     bytes written =  2048

Worker 1 Status:
 State: EXECUTING                     
 Object Schema: DWOWNER
 Object Name: CASES
 Object Type: TABLE_EXPORT/TBL_TABLE_DATA/TABLE/TABLE_DATA
 Completed Objects: 1
 Total Objects: 1
 Completed Rows: 4687818

Remember, this is merely the status display. The export is working in the background. To continue to see the messages on the screen, use the command CONTINUE_CLIENT from the Export> prompt.

Data Pump Export

2008-09-21T14:34:00.000-07:00

Data Pump Export

The new utility is known as expdp to differentiate it from exp, the original export. In this example, we will use Data Pump to export a large table, CASES, about 3GB in size. Data Pump uses file manipulation on the server side to create and read files; hence, directories are used as locations. In this case, we are going to use the filesystem /u02/dpdata1 to hold the dump files.

create directory dpdata1 as '/u02/dpdata1';
grant read, write on directory dpdata1 to bob;

Next, we will export the data:

expdp bob/abc123 tables=CASES directory=DPDATA1
 dumpfile=expCASES.dmp job_name=CASES_EXPORT

Let's analyze various parts of this command. The userid/password combination, tables, and dumpfile parameters are self-explanatory. Unlike the original export, the file is created on the server (not the client). The location is specified by the directory parameter value DPDATA1, which points to /u02/dpdata1 as created earlier. The process also creates a log file, again on the server, in the location specified by the directory parameter. By default, a directory named DPUMP_DIR is used by this process; so it can be created instead of the DPDATA1.

Note the parameter job_name above, a special one not found in the original export. All Data Pump work is done though jobs. Data Pump jobs, unlike DBMS jobs, are merely server processes that process the data on behalf of the main process. The main process, known as a master control process, coordinates this effort via Advanced Queuing; it does so through a special table created at runtime known as a master table. In our example, if you check the schema of the user ANANDA while expdp is running you will notice the existence of a table CASES_EXPORT, corresponding to the parameter job_name. This table is dropped when expdp finishes.

Oracle 10g data pump

2008-09-21T14:33:00.000-07:00

Oracle 10g data pump the newer and faster sibling of the export/import toolkit in Oracle Database 10g, designed to speed up the process many times over.

Data Pump reflects a complete overhaul of the export/import process. Instead of using the usual SQL commands, it provides proprietary APIs to load and unload data significantly faster. In my tests, I have seen performance increases of 10-15 times over export in direct mode and 5-times-over performance increases in the import process. In addition, unlike with the export utility, it is possible to extract only specific types of objects such as procedures.

Oracle Data Pump

2008-09-21T14:32:00.000-07:00

Oracle Data Pump Oracle Data Pump is a feature of Oracle Database 11g that enables very fast bulk data and metadata movement between Oracle databases. Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp) as well as a Web-based Oracle Enterprise Manager interface.
	Data Pump Export and Import utilities are typically much faster than the original Export and Import Utilities. A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is 15-45 times fast than original Import.
	Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary or involuntary.
	Data Pump jobs support fine-grained object selection. Virtually any type of object can be included or excluded in a Data Pump job.
	Data Pump supports the ability to load one instance directly from another (network import) and unload a remote instance (network export).