Skip to content

Can I Store Datapump dumpfiles in ASM diskgroup?

One of my friend asked me , Can I store Datapump dumpfile in asm diskgroup?. Yes you can. Now we can see How do we create directory and store dumpfile.

Step 1: Go To ASM Instance and Create New Directory.

C:\Documents and Settings\Administrator>set oracle_sid=+asm

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 22 15:27:13 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter diskgroup data add directory '+DATA/dumpset';

Diskgroup altered.

Step 2:  Go to DB Instance

 Create Directory for dumpfile and logfile


Dumpfile Directory (ASM Disk)

SQL> create or replace directory dp_asm as '+DATA/dumpset';

Directory created.

Log file Directory (Local File System).

SQL> create or replace directory logfile as 'C:\azar';

Directory created.

SQL> grant read,write on directory dp_asm to system;

Grant succeeded.

SQL> grant read,write on directory logfile to system;

Grant succeeded.

Step 3: Doing Export using datapump


SQL> $expdp system/Admin123 directory=dp_asm dumpfile=testasm.dmp schemas=scott
logfile=logfile:testasm.log

Export: Release 10.2.0.1.0 - Production on Tuesday, 22 February, 2011 15:35:00

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=dp_asm dump
file=testasm.dmp schemas=scott logfile=logfile:testasm.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  +DATA/dumpset/testasm.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:36:01
SQL>

Step 4: Go to ASM Instance and Check the file created in ASM


SQL> select file_number,creation_date,bytes from v$asm_file where type='DUMPSET';

FILE_NUMBER CREATION_      BYTES
----------- --------- ----------
        283 22-FEB-11     212992

SQL>
One Comment Post a comment
  1. sajid #

    very good explained, very helpful

    September 26, 2017

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: