Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Introduction
In the previous post of “11gR2 ASM – Inside story – Part2″, we have seen how to restore a diskgroup after a failure.
In this part 3, we will create datapump export dumpsets within ASM diskgroup.
The procedure is valid for 10g Oracle database,

We should follow the steps below:
1- Create a directory from ASM.
2- Create a directory object in the database.
3- Create log file directory. The datapump log file cannot be stored within ASM.
4- Run the datapump export.
5- Verify the file within the ASM diskgroup.

1- Create a directory from ASM:

From ASM instance:

SQL> ALTER DISKGROUP DATA_REPORTING ADD DIRECTORY '+DATA_REPORTING/oraw/dumpsets';

Diskgroup altered.

2- Create a directory object in the database:

From database instance:

SQL>  CREATE DIRECTORY DUMP_SETS AS '+DATA_REPORTING/oraw/dumpsets';

Directory created.

SQL>
SQL> create user wissem identified by wissem;

User created.

SQL> grant read,write on directory DUMP_SETS to wissem;

Grant succeeded.

SQL> grant EXP_FULL_DATABASE to wissem;

Grant succeeded.

SQL> 

SQL> create table wissem.dump (ID number);

Table created.

SQL> alter user wissem quota 1M on USERS;

User altered.

SQL> insert into wissem.dump values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>

3- Create log file directory:

SQL> CREATE DIRECTORY LOGFILE_DEST AS '/tmp';

Directory created.

SQL> 

SQL> grant read,write on directory LOGFILE_DEST to wissem;

Grant succeeded.

SQL>

4- Run the datapump export:

[oracle@wissem ~]$ expdp wissem/wissem directory=DUMP_SETS dumpfile=wissem_dump.dmp TABLES=DUMP LOGFILE=LOGFILE_DEST:wissem_dump.dmp

Export: Release 11.2.0.1.0 - Production on Sat Mar 5 05:56:57 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "WISSEM"."SYS_EXPORT_TABLE_01":  wissem/******** directory=DUMP_SETS dumpfile=wissem_dump.dmp TABLES=DUMP LOGFILE=LOGFILE_DEST:wissem_dump.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "WISSEM"."DUMP"                             5.007 KB       1 rows
Master table "WISSEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for WISSEM.SYS_EXPORT_TABLE_01 is:
  +DATA_REPORTING/oraw/dumpsets/wissem_dump.dmp
Job "WISSEM"."SYS_EXPORT_TABLE_01" successfully completed at 05:57:04

[oracle@wissem ~]$

5- Verify the file within the ASM diskgroup:

You can verify the file is created within the ASM diskgroup by query the view v$ASM_FILE where TYPE=’DUMPSET’.