OraPL

Thursday, 14 July 2011

Control file backup and recovery

Control File backup and Recovery:

Control file is repository of Oracle Database. Every information about Database structure is stored here. This can be called as Metadata of Database. Control file stores SCN information (Checkpoint SCN for consistent opening of DB), Data file information, REDO information, RMAN setting information (if configured),Archive log mode ,DBID,DBNAME, Temporary Table space information. By now you would have understood that Control file is actually everything that makes Database working and without control file there will not be any database.

All of the above information makes it very crucial to have your Control file intact. And prepare flawless strategy of Backup and Recovery of Control file. Given proper consideration and well thought planning about safety of Control file makes life of DBA easy. Following are scenarios of Control file Backup and recovery using RMAN utility:

Environment:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

PL/SQL Release 10.2.0.1.0 - Production

"CORE 10.2.0.1.0 Production"

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

In scenario 1, I have used RMAN with Recovery Catalog database connectivity.

C:\>Rman Target sys/seed@seed_54 catalog rman_user/rman_user@dupdb

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 13 18:36:57 2011

connected to target database: SEED (DBID=837440993)

connected to recovery catalog database

RMAN> Show all;

starting full resync of recovery catalog

full resync complete

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'D:\bkup\seed\SEED_BKUP_%U.BKP';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'D:\bkup\seed\SEED_BKUP_%U.BKP';

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 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFSEED.ORA'; # default

These two Control file backup specific configuration(Configuration setting Yellow Colored) makes it sure that whenever Database is Backed up then current control file goes along with Backup.

Secondly , Whenever we make change in Database , eg. Tablespace added, Tablespace Dropped (2 changes in Database), Then two backup of control file goes automatically to 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F'; location.

Now I shutdown the database and remove the Control files to demonstrate Recovery of Control file.

After removing Control files(s) on OS level when I try to open the Database, I get following error:

SQL> Startup

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 230689668 bytes

Database Buffers 373293056 bytes

Redo Buffers 7135232 bytes

ORA-00205: error in identifying control file, check alert log for more info

Our database now is in NOMOUNT stage.

Now to recover it I connect to RMAN

RMAN> exit

Recovery Manager complete.

Scenario 1 :-

Control file is lost but we have Recovery Catalog Database connectivity and we have all the backup piece(s) in tact and valid. Backup piece(s) are at their default location (mentioned in RMAN configuration).

C:\>Rman Target sys/seed@seed_54 catalog rman_user/rman_user@dupdb

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 13 18:56:43 2011

connected to target database: seed (not mounted)

connected to recovery catalog database

We list out Backup Piece of Control file :

RMAN> List backup of controlfile;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

46 Full 1.03M DISK 00:00:01 11-JUL-11

BP Key: 54 Status: AVAILABLE Compressed: YES Tag: TAG20110711T110624

Piece Name: D:\BKUP\SEED\SEED_BKUP_01MH5P1G_1_1.BKP

Control File Included: Ckp SCN: 662627 Ckp time: 11-JUL-11

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

47 Full 6.80M DISK 00:00:01 11-JUL-11

BP Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20110711T110627

Piece Name: D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110711-00

Control File Included: Ckp SCN: 662633 Ckp time: 11-JUL-11

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

53 Full 6.80M DISK 00:00:01 12-JUL-11

BP Key: 61 Status: AVAILABLE Compressed: NO Tag: TAG20110712T162012

Piece Name: D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110712-00

Control File Included: Ckp SCN: 759685 Ckp time: 12-JUL-11

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

336 Full 6.80M DISK 00:00:03 13-JUL-11

BP Key: 337 Status: AVAILABLE Compressed: NO Tag: TAG20110713T175948

Piece Name: D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110713-00

Control File Included: Ckp SCN: 806881 Ckp time: 13-JUL-11

Now! We have seen Backup piece(s) containing Control file. All we need to do is Restore the Control File. As database is connected to Recovery Catalog, So not much problem is there. Comparatively easy. All we need to do is Restore control from Auto backup , Mount the Database and Recover database and in end Open Database in RESETLOGS mode.

RMAN> Restore Controlfile;

Starting restore at 13-JUL-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: reading from backup piece D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110713-00

channel ORA_DISK_1: restored backup piece 1

piece handle=D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110713-00 tag=TAG20110713T175948

channel ORA_DISK_1: restore complete, elapsed time: 00:00:05

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL01.CTL

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL02.CTL

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL03.CTL

Finished restore at 13-JUL-11

RMAN> Alter Database Mount;

database mounted

released channel: ORA_DISK_1

released channel: ORA_DISK_2

RMAN> Recover Database;

Starting recover at 13-JUL-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 1 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO02.LOG

archive log thread 1 sequence 2 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG

archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO02.LOG thread=1 sequence=1

archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG thread=1 sequence=2

media recovery complete, elapsed time: 00:00:03

Finished recover at 13-JUL-11

RMAN> Alter Database Open Resetlogs;

database opened

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

Now! Control file is recovered and Database is opened . Restart the database and take backup. Recovering Control file using Recovery Catalog Database is pretty simple and straight forward. We have all the information required for Restoration of File.

Scenario 2:

Control file as well as Recovery Catalog Database is lost. Backup piece(s) are at their default location and available.

To demonstrate I delete all the Control File from OS using OS utility. Now when I try to open the Database , I get following error (Same like scenario 1):

SQL> startup

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 247466884 bytes

Database Buffers 356515840 bytes

Redo Buffers 7135232 bytes

ORA-00205: error in identifying control file, check alert log for more info

Now problem is, When I connect to RMAN using Catalog Database, Due to unavailability of Database, I get following error:

C:\>rman target sys/seed@seed_54 catalog rman_user/rman_user@dupdb

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 14 15:13:18 2011

connected to target database: seed (not mounted)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04004: error from recovery catalog database: ORA-12518: TNS:listener could not hand off client connection.

Now! I start RMAN without Recovery Catalog Database.

C:\>rman target sys/seed@seed_54 nocatalog

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 14 15:47:33 2011

connected to target database: seed (not mounted)

using target database control file instead of recovery catalog

Unlike scenario 1, When I try to see backup piece(s) containing Control File(s), I get following message.

RMAN> list backup of controlfile;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of list command at 07/14/2011 18:31:48

ORA-01507: database not mounted

It is understandable; Database is not in MOUNT stage so we cannot see the Backup information from Control file. As we are unable to see Backup information, cannot proceed with restoration of Control File. If we use any of “RESTORE CONTROLFILE” Command, than statement fails.

RMAN> restore controlfile;

Starting restore at 14-JUL-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/14/2011 18:39:15

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> restore controlfile from autobackup;

Starting restore at 14-JUL-11

using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20110714

channel ORA_DISK_1: looking for autobackup on day: 20110713

channel ORA_DISK_1: looking for autobackup on day: 20110712

channel ORA_DISK_1: looking for autobackup on day: 20110711

channel ORA_DISK_1: looking for autobackup on day: 20110710

channel ORA_DISK_1: looking for autobackup on day: 20110709

channel ORA_DISK_1: looking for autobackup on day: 20110708

channel ORA_DISK_1: no autobackup in 7 days found

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/14/2011 18:39:28

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Even if, We SET proper FORMAT of Control File auto backup using following RUN Block, We fail.

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';

4> RESTORE CONTROLFILE;

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 15-JUL-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/15/2011 10:36:18

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';

4> RESTORE CONTROLFILE FROM AUTOBACKUP;

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 15-JUL-11

using channel ORA_DISK_1

autobackup search outside recovery area not attempted because DBID was not set

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/15/2011 10:36:32

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

There is no problem within RUN block, It is just that Oracle is not aware of Backup Piece location of the Database. Here we need to provide the DBID of the Database. In case we have lost Control file and Recovery Catalog Database connectivity, we must know the DBID of Database. Because using DBID of the Database, Oracle comes to know the where about of Database Backup Piece(s) and once it knows the location of backup piece(s), it uses them for restoration and recovery.

RMAN> set dbid=837440993

executing command: SET DBID

Now after providing , DBID procedure of Control file recovery is simple. But unlike scenario 1, We cannot simply use “RECOVER CONTROLFILE” or “RECOVER CONTROLFILE FROM AUTOBACKUP” . Both commands produce error.

RMAN> Restore Controlfile;

Starting restore at 15-JUL-11

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/15/2011 10:45:20

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> Restore Controlfile from Autobackup;

Starting restore at 15-JUL-11

using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20110715

channel ORA_DISK_1: looking for autobackup on day: 20110714

channel ORA_DISK_1: looking for autobackup on day: 20110713

channel ORA_DISK_1: looking for autobackup on day: 20110712

channel ORA_DISK_1: looking for autobackup on day: 20110711

channel ORA_DISK_1: looking for autobackup on day: 20110710

channel ORA_DISK_1: looking for autobackup on day: 20110709

channel ORA_DISK_1: no autobackup in 7 days found

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/15/2011 10:45:32

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

To Restore control file in such situation, We need to provide FORMAT of Control File AUTOBACKUP.

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';

4> RESTORE CONTROLFILE FROM AUTOBACKUP;

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 15-JUL-11

using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20110715

channel ORA_DISK_1: looking for autobackup on day: 20110714

channel ORA_DISK_1: autobackup found: D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_c-837440993-20110714-02

channel ORA_DISK_1: control file restore from autobackup complete

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL01.CTL

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL02.CTL

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL03.CTL

Finished restore at 15-JUL-11

As we can see that, using RUN block our restoration of Control file is successful.

RMAN> Alter Database Mount;

database mounted

released channel: ORA_DISK_1

RMAN> Recover database;

Starting recover at 15-JUL-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=154 devtype=DISK

starting media recovery

archive log thread 1 sequence 3 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO03.LOG

archive log thread 1 sequence 4 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG

archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO03.LOG thread=1 sequence=3

archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG thread=1 sequence=4

media recovery complete, elapsed time: 00:00:05

Finished recover at 15-JUL-11

RMAN> Alter database open Resetlogs;

database opened

It is pretty simple and straight. All you need to provide DBID and location of Control File backup piece.

The procedure of recovering Control file from its default AUTOBACK FORMAT location or any other location where user manually has transferred is simple. All we need to do is SET CONTOLFILE AUTOBACKUP FORMAT to user defined location. Eg. In scenario 2 I used following RUN block

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F'; ß Default Location

4> RESTORE CONTROLFILE FROM AUTOBACKUP;

5> }

In case, User has transferred backup piece(s) to any other location lets say D:\bkup and Then all we need to do is change the RUN block eg.

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup \SEED_CTL_FILE_%F'; ß changed Location

4> RESTORE CONTROLFILE FROM AUTOBACKUP;

5> }

Any comment/Query/Correction is most welcome....

Friday, 3 June 2011

Oracle Unique and Priamry constraint.

I am trying to add unique key constraints on SCOTT.EMP.ENAME column and getting error:

ALTER TABLE EMP ADD

CONSTRAINT EMP_UK UNIQUE (ENAME) ENABLE NOVALIDATE;

Error starting at line 5 in command:

ALTER TABLE EMP ADD

CONSTRAINT EMP_UK UNIQUE (ENAME) NOVALIDATE

Error report:

SQL Error: ORA-02299: cannot validate (SCOTT.EMP_UK) - duplicate keys found

02299. 00000 - "cannot validate (%s.%s) - duplicate keys found"

*Cause: an alter table validating constraint failed because the table has

duplicate key values.

*Action: Obvious

This is understandable. I have duplicate values in ENAME columns and when I am saying to create a Unique constraint on this column, Oracle tries to create a Unique Index, and fails even we are saying Enable and No-validate existing values.

Solution 1 : The reason of failure is Oracle create Unique Index for a Unique constraints. If Oracle is unable to create such Index, We can try to support Uniqueness of values using Non-Unique Index by creating a Index on ENAME column.

Create index scott.ename_uk on scott.emp(ename) compute statistics;

SELECT index_name,uniqueness FROM USER_INDEXES WHERE table_NAME='EMP';

INDEX_NAME UNIQUENESS

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

PK_EMP UNIQUE

ENAME_UK NONUNIQUE

Now! A Non-Unique Index is present on ENAME column, so when we proceed with constraint creation statement, it is successful.

SELECT index_name,uniqueness FROM USER_INDEXES WHERE table_NAME='EMP';

ALTER TABLE EMP succeeded.

Inserting a duplicate values fails:

Insert into emp(empno,ename) values (1111,'WARD')

Error starting at line 4 in command:

Insert into emp(empno,ename) values (1111,'dd')

Error report:

SQL Error: ORA-00001: unique constraint (SCOTT.EMP_UK) violated

00001. 00000 - "unique constraint (%s.%s) violated"

*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.

For Trusted Oracle configured in DBMS MAC mode, you may see

this message if a duplicate entry exists at a different level.

*Action: Either remove the unique restriction or do not insert the key.

Solution 2:

In solution 1, we create a Non-Unique index on ENAME column separately. There is another option, in which we have no need to create a index separately. In this we will mention USING INDEX clause in ADD CONSTRAINT statement.

Alter table emp add

Constraint emp_uk unique(ENAME) using index (create index emp_uk on emp(ename)) enable novalidate;

Unique Index and Non-Unique index creation.

Whenever, We create a table and add either Unique constraint or a Primary key constraint and we do not specify any Index in constraint addition statement (USING INDEX Clause) than along with constraint definition and an Index is created which is by default a Unique index.

E.g

SQL> Create table Scott.t3 as select level id1 from dual connect by level<=12;

Table created.

SQL> Alter Table scott.t3 add constraint t3_pk primary key(id1);

Table altered.

SQL> select index_name,uniqueness from user_indexes where index_name='T3_PK';

INDEX_NAME UNIQUENES

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

T3_PK UNIQUE

SQL> alter table t3 drop constraint T3_PK;

Table altered.

SQL> select index_name,uniqueness from user_indexes where index_name='T3_PK';

no rows selected

SQL> ALTER TABLE T3 ADD CONSTRAINT T3_PK PRIMARY KEY(ID1) USING INDEX(CREATE INDEX T3_PK ON T3(ID1));

Table altered.

SQL> select index_name,uniqueness from user_indexes where index_name='T3_PK';

INDEX_NAME UNIQUENES

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

T3_PK NONUNIQUE

In second , Alter table statement, We have guided Oracle to create a constraint using a Index. Now this time Oracle has not created Unique Index.

Any comment/Question is welcome....

Thursday, 26 May 2011

Export/Import using ExpDP in Oracle 10g Part1

This is my first blog. In this blog, I have written about Oracle's ExpDP/ImpDP (data pump) utility.
This utility is a lot different from earlier Export/Import utility(Exp/Imp).

This is not a complete list. More will come on the later part(s) of the series.

Environment:-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Machine:- <>.<>.<.>.54 X86 Windows 2003 Server

Machine:- <>.<>.<.>.82 X86 bit     RedHat Linux 5

User : Scott/Test_user/Rman_User

Directories:
Instead of creating a new directory, I have used DATA_PUMP_DIR which is created by default when you install Oracle software. The location of this directory is :

On windows
<OraclePath>10.2.0\admin\<SID>\dpdump\

On Unix/Linux (Linux 5 in this case)

<mount point>/oracle/admin/<SID>/dpdump/

In case you want to use a new directory,You can use following procedure.

On Windows:
Create directory on the drive. eg. E:\DP_DIR
Login to database: Connect to SYS user or any user with DBA priv(s). and execute following create statement.

1: Create directory DPDIR AS 'D:\DP_DIR'

This will create a directory. Check DBA_DIRECTORIES.

2: GRANT READ,WRITE ON DIRECTORY DPDIR TO SCOTT
To check the priv(s) granted on this directory to user Scott: Connect to Scott and run following query

3: SELECT * FROM all_tab_privs   WHERE table_name = 'DPDIR';
On Linux/Unix boxes, The procedure is same, but you need to change Owner and Group of folder, you referenced in CREATE DIRECTORY statement.

Case 1: Scott exported his tables M1,M2,M1_Mask from Machine 54 using ExpDP.  Scott of 82 could import tables from Export file of scott from Machine 54 without any hitch.
Export:
ExpDp Scott/tiger@seed_54 partfile=tb1_lvl1.txt
The contents of parfile are
tables=T1,T2,T1_MASK DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT_TB2.DMP LOGFILE=SCOTT_TB2.LOG

Import:

ImpDp Scott/Tiger@seed_82 ParFile=Tb1_lvel_imp.txt

The Content of Partifle are
TABLES=T1,T2,T1_MASK DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT_TB2.DMP LOGFILE=SCOTT_TB2_IMP.LOG

Note:- As we know that the user scott has exported his tables.The default tablespace of scott on 54 is USERs but on 82, the default tablespace of scott is Rman_Tbs. In above import script we have not mentioned any key word, parameter to remap this tablespace. So in this case if USERS tablespace exists in 82 then that USERS tablespace will be used otherwise error (Tablespace Does Not exists) will be shown on screen if we donot use remap_tablespace parameter. now lets run Import Script once again with Rmap_Tablesapce cluase

IMPORT: IMPDP SCOTT/TIGER@SEED_82
Now the changed Parfile contains following:
TABLES=T1,T2,T1_MASK DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT_TB2.DMP LOGFILE=SCOTT_TB2_IMP.LOG REMAP_TABLESPACE=USERS:SCOTT_TBS

Case 2:- Scott's tables imported to another user. Eg. Rman_user of Seed_54
We to export tables of Scott from 82 machine and have to import those tables into Rman_USer Schema of 54 machine.

Export:
  Expdp scott/tiger@seed_82 parfile=abc.txt

  contents of parfile are
  tables=dept,emp directory=data_pump_dir dumpfile=scott_tbl2_exp.dmp logfile=scott_m_log.log
Import
  ImpDp rman_user/rman_user@seed_54 parfile=xyz.txt

  contents of parfile are
  tables=DEPT,EMP DUMPFILE=SCOTT_TBL2_EXP.DMP DIRECTORY=DATA_PUMP_DIR LOGFILE=IMP_SCTT_BL.LOG                 REMAP_SCHEMA=SCOTT:RMAN_USER
Note:- In this case we have exported table(s) (emp,dept) of scott and imported those to RMAN_USER.The default tablespace of Scott on 82 is SCOTT_TBS. and this tablespace does not exits in seed_54 machine. so we got error tablespace does not exits. To troubleshoot this problem,We have two options either create tablespace or use REMAP_TABLESPACE like we used in case1.Finally, after troubleshooting
this error, our parfile looked like following

  tables=DEPT,EMP DUMPFILE=SCOTT_TBL2_EXP.DMP DIRECTORY=DATA_PUMP_DIR LOGFILE=IMP_SCTT_BL.LOG
remap_tablespace=SCOTT_TBS:USERS REMAP_SCHEMA=SCOTT:RMAN_USER

Case 3:- Scott's (emp,dept) tables to exported with data for Deptno=10 only.

Export:
  ExpDp scott/tiger@seed_82 parfile=abc.txt

  contents of parfile are
  tables=dept,emp directory=data_pump_dir dumpfile=scott_tbl2_exp.dmp logfile=scott_m_log.log query='where deptno=10'

Import
  ImpDP test_user/test_user@seed_54 parfile=STIMPQ.TXT
  Contents of parfile are
  tables=DEPT,EMP DUMPFILE=SCOTT_TBL2_EXP.DMP DIRECTORY=DATA_PUMP_DIR LOGFILE=IMP_SCTT_BL.LOG remap_tablespace=SCOTT_TBS:USERS REMAP_SCHEMA=SCOTT:TEST_USER

Continues...