Tuesday 22 March 2016

Can I Store Datapump dumpfiles in ASM diskgroup?




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


I have 2 DISKS (i.e, DATA & NEWDATA)

ASMCMD [+] > ls
DATA/
NEWDATA/

SQL> alter diskgroup data add directory '+DATA/Dataset';
Diskgroup altered.

ASMCMD [+] > cd DATA
ASMCMD [+DATA] > ls
ASM/
ASMDB/
Dataset/                                           <---- // Created using above command
MYASM/
ORCL/
ORCL1/




Step 2:
 Go to DB Instance

SQL> create or replace directory datapump_asm as '+DATA/Dataset/';
Directory created.

SQL> grant read , write on directory datapump_asm to system;
Grant succeeded.

SQL> create or replace directory logfile as '/u01/';
Directory created.

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

NOTE:   ‘Nikhil’ is user  &  NIK_EMP is a Table


Step 3: Doing Export using datapump

SQL> ! expdp system/sys directory=datapump_asm dumpfile=testASM.dmp schemas=nikhil.nik_emp logfile=logfile:abc.log;

Export: Release 11.2.0.1.0 - Production on Tue Mar 22 00:17:27 2016

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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=datapump_asm dumpfile=testASM.dmp schemas=nikhil.nik_emp logfile=logfile:abc.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 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
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  +DATA/dataset/testasm.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 00:19:58


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
----------- --------- ----------
                322 22-MAR-16                 159744



No comments:

Post a Comment