Oracle Datapump_EXPORT

How to Copy files from ASM to filesystem and filesystem to ASM

Copy files from ASM to filesystem
---------------------------------
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd

ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/EXPDP
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
ASMCMD> cp dumpfile1.dmp dumpfile1.dmp dumpfile1.dmp /oracle/backup/testdb/expdp
copying +FRA/TESTDB/EXPDP/dumpfile1.dmp -> /oracle/backup/testdb/expdp/dumpfile1.dmp
copying +FRA/TESTDB/EXPDP/dumpfile2.dmp -> /oracle/backup/testdb/expdp/dumpfile2.dmp
copying +FRA/TESTDB/EXPDP/dumpfile3.dmp -> /oracle/backup/testdb/expdp/dumpfile3.dmp
ASMCMD> exit

$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp

Copy files from filesystem to ASM
---------------------------------
$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp

$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd

ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/IMPDP
ASMCMD> ls
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile1.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile1.dmp -> +FRA/TESTDB/EXPDP/dumpfile1.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile2.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile2.dmp -> +FRA/TESTDB/EXPDP/dumpfile2.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile3.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile3.dmp -> +FRA/TESTDB/EXPDP/dumpfile3.dmp
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp

Schema Refresh using ASM Diskgroups

How to create Datapump Export Dumps within ASM diskgroups

Schema Status
-------------
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;


EXPORT (EXPDP)

$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir EXPDP

1. Create a directory in ASM. 
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/EXPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;

2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/expdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;

3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir expdp
$ chmod -R 755 expdp
$ cd expdp
$pwd
/oracle/backup/testdb/expdp
$ df -h /oracle/backup/testdb/expdp -- or
$df -gt /oracle/backup/testdb/expdp

$ sqlplus "/as sysdba"

set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/expdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/expdp

Where DATAPUMP_ASM_DIR is a ASM based directory
and   DATAPUMP_LOG_DIR is on a filesystem.

Here, Export is written to ASM based dictionary,
      but log is written to another directory which is on filesystem.

We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).

$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

:wq

$nohup expdp parfile=schema_export_in_ASM.par &

$tail -f nohup.out or

$tail -f schema_export_in_ASM.log

=======================================================================

IMPORT (IMPDP)

Schema Status
-------------
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;

$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir IMPDP

1. Create a directory in ASM. 
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/IMPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;

2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/impdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;

3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir impdp
$ chmod -R 755 impdp
$ cd impdp
$pwd
/oracle/backup/testdb/impdp

$ sqlplus "/as sysdba"

set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/impdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/impdp

Where DATAPUMP_ASM_DIR is a ASM based directory
and   DATAPUMP_LOG_DIR is on a filesystem.

Here, Export is written to ASM based dictionary,
      but log is written to another directory which is on filesystem.

We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).

$vi import_schema_export_in_ASM.par

user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:import_schema_export_in_ASM.log
schemas=schema1,schema2
parallel=6
cluster=n  ---- 11g

If required,
REMAP_SCHEMA=source_schema1:destination_schema1,source_schema2:destination_schema2
REMAP_TABLESPACE=source_tablespace1:destination_tablespace1,source_tablespace2:destination_tablespace2

:wq

$nohup impdp parfile=import_schema_export_in_ASM.par &

$tail -f nohup.out

$tail -f import_schema_export_in_ASM.log


Schema Status
-------------
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;  

Longops DATAPUMP

DATAPUMP STATUS
---------------

 set pages 50000 lines 32767
col OPNAME for a10
col UNITS for a10
col TARGET_DESC for a10
col OWNER_NAME for a10
col STATE for a10
col JOB_MODE for a10
col JOBNAME for a20
col MESSAGE for a30
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select sid,serial#,sofar,totalwork,units,target_desc,start_time,last_update_time,
round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,
message
from gv$session_longops sl,gv$datapump_job dp
where sl.opname=dp.job_name and sofar != totalwork
/

Currently Active DataPump Operations
---------------------------------------
set pages 9999 lines 300

TTITLE 'Currently Active DataPump Operations'
COL owner_name          FORMAT A06      HEADING 'Owner'
COL job_name            FORMAT A20      HEADING 'JobName'
COL operation           FORMAT A12      HEADING 'Operation'
COL job_mode            FORMAT A12      HEADING 'JobMode'
COL state               FORMAT A12      HEADING 'State'
COL degree              FORMAT 9999     HEADING 'Degr'
COL attached_sessions   FORMAT 9999     HEADING 'Sess'

SELECT
 owner_name
,job_name
,operation
,job_mode
,state
,degree
,attached_sessions
FROM dba_datapump_jobs
/

Currently Active DataPump Sessions
-------------------------------------
set pages 9999 lines 300

TTITLE 'Currently Active DataPump Sessions'
COL owner_name          FORMAT A06      HEADING 'Owner'
COL job_name            FORMAT A20      HEADING 'Job'
COL osuser              FORMAT A12      HEADING 'UserID'

SELECT
 DPS.owner_name
,DPS.job_name
,S.osuser
,S.sid
,S.serial#
,S.status
FROM
 dba_datapump_sessions DPS
,v$session S
WHERE S.saddr = DPS.saddr
/

DBA_DATAPUMP_JOBS - This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.
-----------------
set pages 9999 lines 300

COL owner_name FORMAT a10
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

select * from dba_datapump_jobs where job_name not like 'BIN$%' order by 1,2;
OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- -----------------

DBA_DATAPUMP_SESSIONS - This view give gives the SADDR that assist in determining why a Data Pump session may be having problems.
---------------------
set pages 9999 lines 300

COL owner_name FORMAT a10
COL job_name FORMAT a20

select owner_name,job_name,saddr,sid,serial# from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;

Longops EXPORT

EXPORT STATUS
-------------
jobs
or
ps -ef| grep expdp or ps -ef| grep exp or ps -ef|grep <processid>
or
ls -lrth
tail -f nohup.out
tail -f SOURCE_TABLENAME_EXP.log

cat SOURCE_TABLENAME_EXP.log
(OR)

EXPORT STATUS
-------------

$expdp attach=<JOB_NAME>
username: / as sysdba
EXPDP>STATUS

If required, to kill EXPORT job
CTRL+C
EXPDP>ATTACH=SOURCE_TABLE_EXPORT
EXPDP>STATUS
EXPDP>STOP_JOB=IMMEDIATE (OR) KILL_JOB


(OR)
EXPORT STATUS
-------------
set pages 50000 lines 32767
col UNITS for a5
col TARGET_DESC for a11
col OWNER_NAME for a10
col STATE for a10
col JOB_MODE for a10
col JOB_NAME for a20
col MESSAGE for a30
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select sid,serial#,username,opname,sofar,totalwork,units,target_desc,start_time,last_update_time,
round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,
message
from gv$session_longops sl,gv$datapump_job dp
where sl.opname=dp.job_name and sofar != totalwork and target_desc='EXPORT'
/
(OR)

DBA_DATAPUMP_JOBS - This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.

set pages 50000 lines 32767
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

select * from dba_datapump_jobs where job_name not like 'BIN$%' order by 1,2;
OWNER_NAME JOB_NAME               OPERATION  JOB_MODE   STATE         DEGREE    ATTACHED_SESSIONS
---------- ---------------------- ---------- ---------- ------------- --------- -----------------

DBA_DATAPUMP_SESSIONS - This view give gives the SADDR that assist in determining why a Data Pump session may be having problems.

set pages 50000 lines 32767
COL owner_name FORMAT a10
COL job_name FORMAT a20

select owner_name,job_name,s.saddr,sid,serial# from v$session s, dba_datapump_sessions d
where s.saddr = d.saddr;

select owner_name, job_name, operation, job_mode, state from dba_datapump_jobs;

EXP
---
nohup exp userid=\'/ as sysdba \' file=SOURCE_TABLENAME_EXP.dmp log=SOURCE_TABLENAME_EXP.log tables=OWNER.TABLENAME &