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
very good explained, very helpful