---How to create Datapump Export Dumps within ASM diskgroups
col username for a20
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
--LOGIN TO GRID USER:
[grid@srv1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/db_1
The Oracle base has been changed from /u01/app/grid to /u01/app/oracle
[grid@srv1 ~]$ asmcmd
2022-06-29 20:36:10.319
login as: grid
grid@192.168.56.71's password:
ASMCMD> ls -lrth
State Type Rebal Name
MOUNTED EXTERN N OCRDISK1/
MOUNTED EXTERN N FRA/
MOUNTED EXTERN N DATA/
ASMCMD> cd DATA/
ASMCMD> ls
ORCL/
RAC/
ASMCMD> mkdir EXPDP
ASMCMD> ls -lrth
Type Redund Striped Time Sys Name
N EXPDP/
N ORCL/
N RAC/
1). Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA/EXPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;
Grant succeeded.
2). Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/home/oracle/backup/expdp';
Directory created.
---SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;
3. Create logfile directory in filesystem.
cd /home/oracle/backup
mkdir expdp
chmod -R 755 expdp
$ sqlplus "/as sysdba"
SQL>
col property_value format a30
col OWNER form a20
col DIRECTORY_NAME form a25
col DIRECTORY_PATH form a55
select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where directory_name in ('DATAPUMP_LOG_DIR','DATAPUMP_ASM_DIR');
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------------------- ------------------------- -------------------------------------------------------
SYS DATAPUMP_LOG_DIR /home/oracle/backup/expdp
SYS DATAPUMP_ASM_DIR +DATA/EXPDP
---FOR SCHEMA:
vi schema_export_in_ASM.par
user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:schema_export_in_ASM.log
schemas=schema1,schema2
exclude=statistics
parallel=6
compression=all
---cluster=n ---- 11g
--FOR TABLE
vi table_export_in_ASM.par
user_id="sys/oracle_4U"
directory=DATAPUMP_ASM_DIR
dumpfile=test19c.dmp
logfile=DATAPUMP_LOG_DIR:table_export_in_ASM.log
tables=metals.test
parallel=2
nohup expdp parfile=table_export_in_ASM.par &
or
nohup expdp \"sys/oracle_4U as sysdba\" PARFILE=table_export_in_ASM.par &
expdp \'/ as sysdba\' tables=test directory=DATAPUMP_ASM_DIR logfile=DATAPUMP_LOG_DIR:schema_export_in_ASM.log
or
expdp system/oracle_4U@orlc1 PARFILE=table_export_in_ASM.par
tail -f nohup.out
or
tail -f schema_export_in_ASM.log
COPY from ASMD disk (DATA/EXPDP/test19c.dmp) to local filesystem (/tmp/test19c.dmp)
ASMCMD> cd EXPDP/
ASMCMD> cp test19c.dmp /tmp
SCP to development server to any location of your choice
scp /tmp/test19c.dmp oracle@srv1:/tmp
Then again cp from localfile system to ASM disk (DATA/IMPPDP/test19c.dmp)
ASMCMD> cp /tmp/test19c.dmp '+DATA/IMPDP/'
copying /tmp/test19c.dmp -> +DATA/IMPDP/test19c.dmp
ASMCMD> pwd
+DATA/EXPDP
ASMCMD> cd ..
ASMCMD> cd IMPDP/
ASMCMD> pwd
+DATA/IMPDP
ASMCMD> ls -lrth
Type Redund Striped Time Sys Name
DUMPSET UNPROT COARSE JUN 29 23:00:00 N test19c.dmp => +DATA/ASM/DUMPSET/test19c.dmp.281.1108683391
----ON Development Server---
IMPORT (IMPDP)
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
--LOGIN TO GRID USER:
[grid@srv1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/db_1
The Oracle base has been changed from /u01/app/grid to /u01/app/oracle
[grid@srv1 ~]$ asmcmd
2022-06-29 20:36:10.319
login as: grid
grid@dev password:
ASMCMD> cd DATA
ASMCMD> mkdir IMPDP
ASMCMD> pwd
+DATA
ASMCMD> ls -lrth
Type Redund Striped Time Sys Name
N EXPDP/
N ORCL/
N RAC/
Create logfile directory in filesystem.
cd /home/oracle/backup
mkdir impdp
chmod -R 755 impdp
cd /home/oracle/backup/impdp
1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR1 as '+DATA/IMPDP';
Directory created.
---SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;
[oracle@srv1 impdp]$ sqlplus "/as sysdba"
col property_value format a30
col OWNER form a20
col DIRECTORY_NAME form a25
col DIRECTORY_PATH form a55
select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where directory_name in ('DATAPUMP_LOG_DIR1','DATAPUMP_ASM_DIR1');
2). Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR1 as '/home/oracle/backup/impdp';
Directory created.
---SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;
$vi import_schema_export_in_ASM.par
user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR1
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:import_schema_export_in_ASM.log
schemas=schema1,schema2
parallel=6
cluster=n ---- 11g
$vi import_table_export_in_ASM.par
directory=DATAPUMP_ASM_DIR1
dumpfile=test19c.dmp
logfile=DATAPUMP_LOG_DIR1:import_table_export_in_ASM.log
tables=metals.test
parallel=2
nohup impdp \"sys/oracle_4U as sysdba\" PARFILE=import_table_export_in_ASM.par &
tail -f nohup.out
or
tail -f import_table_export_in_ASM.par
[oracle@srv1 impdp]$ tail -f nohup.out
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Wed Jun 29 23:37:48 2022 elapsed 0 00:00:08
Import: Release 19.0.0.0.0 - Production on Wed Jun 29 23:38:30 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "sys/******** AS SYSDBA" PARFILE=import_table_export_in_ASM.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "METALS"."TEST" 5.062 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
