ORACLE DATA PUMP (EXPORT / IMPORT SCHEMS) Starting from version 11g and higher

SQL>
ssh -X root@ocp
mkdir -p /u03/oradata/datapump/dumps
mkdir -p /u03/oradata/datapump/logs
chown -R oracle:dba /u03/oradata/datapump/dumps
chown -R oracle:dba /u03/oradata/datapump/logs

ssh -X oralce@ocp
sqlplus / as sysdba

Show current datapump directories

SQL>
set linesize 200;
set pagesize 0;
col directory_name format a30;
col directory_path format a60;
select directory_name, directory_path from dba_directories;

It recommends to delete default DATA_PUMP_DIR

SQL> DROP DIRECTORY DATA_PUMP_DIR;

Create new one

SQL> CREATE DIRECTORY  dpdumps  as '/u03/oradata/datapump/dumps';
SQL> CREATE DIRECTORY  dplogs   as '/u03/oradata/datapump/logs';

Delegate permissions to this directories to user scott

SQL> GRANT READ, WRITE ON DIRECTORY dpdumps TO scott;
SQL> GRANT READ, WRITE ON DIRECTORY dplogs TO scott;

If we need to grant export for all schemas

SQL> GRANT READ, WRITE ON DIRECTORY dpdumps TO PUBLIC;
SQL> GRANT READ, WRITE ON DIRECTORY dplogs TO PUBLIC;
quit

Export using parameter file

nohup expdp scott/tiger parfile=exoprt_schema_name.config &

Some times we need to set SID of the database

nohup expdp scott/tiger@SID parfile=exoprt_schema_name.config &

Export without parameter file by one command

nohup expdp scott/tiger job_name=scott_export_job_01 dumpfile=dpdumps:scott_YYYYMMDD.dmp logfile=dplogs:scott_YYYYMMDD  &

EXPORT FULL DATABASE

SQL> grant "DATAPUMP_IMP_FULL_DATABASE" to "LINKEDUP";
SQL> grant "DATAPUMP_EXP_FULL_DATABASE" to "LINKEDUP";
expdp linknedup/linkedup DUMPFILE=expfull_linkedup1.dmp TRANSPORTABLE=ALWAYS FULL=Y
SQL> grant IMPORT FULL DATABASE to "LINKEDUP";
SQL> grant "IMP_FULL_DATABASE" to "LINKEDUP";
SQL> grant "EXP_FULL_DATABASE" to "LINKEDUP";
SQL> grant EXPORT FULL DATABASE to "LINKEDUP";
exp linkedup/linkedup FULL=y

 

Expdp to the NFS example

Mount NSF directory

vi /etc/fstab
192.168.1.10:/data /backup nfs     rw,hard,tcp,vers=3,bg,nointr,rsize=32768,wsize=32768,nolock,actimeo=0 0 0
mkdir /backup
mount 192.168.1.10:/data /backup
mkdir /backup/orcl

1) Set Oracle invironment ORACLE_SID=database_name, from which we need to export dump file;

export ORACLE_SID=ORCL 

env |grep SIDORCL

2) Set Oracle environment NLS_LANG=AMERICAN_RUSSIA.AL32UTF8;

export NLS_LANG=AMERICAN_RUSSIA.AL32UTF8

3) Check and set directory for dump

To know parameter of DATA_PUMP_DIR

SQL> select directory_name, directory_path from dba_directories;
SQL> create or replace directory EXPORT_META as '/backup/orcl';
SQL> GRANT read, write on directory EXPORT_META TO SYSTEM;
SQL> select * from dba_directories where directory_name = 'EXPORT_META';

4) Allow user system to make an export

SQL> grant "DATAPUMP_EXP_FULL_DATABASE" to "SYSTEM";

5) Run dump export using

expdp system/pa$$w0rd@db_name full=Y directory=EXPORT_META dumpfile=fulldumpDBNAME.dmp logfile=expdpDBNAME.log

6) Check log file

cat /backup/orcl/expdpDBNAME.log

7) Compress dump file (c-create, v-verbose, f-file) 

tar cvzf /backup/orcl/expdpDBNAME.tar.gz /backup/orcl/expdpDBNAME.log

 

Troubleshooting

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1

 

CAUSE

column name format a31;
select name, bytes/1024/1024 Mb from v$sgainfo;...
Streams Pool Size                               0 Yes 

Data Pump uses Advanced Queuing (AQ) internally, so it can handle job stops/starts/re-starts.
However, AQ is using the Streams Pool, where the database currently has the STREAMS_POOL_SIZE set to ZERO

 

SOLUTION

Backup current initialization configuration

SQL> create pfile='/tmp/pfile1.txt' from spfile;

Change parameter  STREAMS_POOL_SIZE

SQL> ALTER SYSTEM SET STREAMS_POOL_SIZE = 150M;

Alternatively, turn on Automatic Shared Memory management (ASMM) by setting SGA_TARGET or MEMORY_TARGET.

Remove failed export and log files

rm -rf /backup/orcl/fulldumpDBNAME.dmp
rm -rf /backup/orcl/expdpDBNAME.log

Re-run export 

expdp system/pa$$w0rd@db_name full=Y directory=EXPORT_META dumpfile=fulldumpDBNAME.dmp logfile=expdpDBNAME.log

  

UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 264 bytes of shared memory ("shared pool","unknown object","CCUR^7f265e37","kglob")
ORA-06512: at "SYS.KUPV$FT_INT", line 2904
ORA-06512: at "SYS.KUPC$QUE_INT", line 572
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1

UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","SQLA","tmp")
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4310
ORA-06512: at line 1

 

CAUSE

SQL> show parameter target...
memory_max_target                    big integer 0

 

SOLUTION (DB restart required)

Backup current configuration

SQL> create pfile='/tmp/pfile2.txt' from spfile;

Change target parameters  

SQL> ALTER SYSTEM SET memory_max_target = 8g scope=spfile;
SQL> ALTER SYSTEM SET memory_target = 8g scope=spfile;
SQL> shu imediate
SQL> startup

Remove failed export and log files

rm -rf /backup/orcl/fulldumpDBNAME.dmp
rm -rf /backup/orcl/expdpDBNAME.log

Re-run export 

expdp system/pa$$w0rd@db_name full=Y directory=EXPORT_META dumpfile=fulldumpDBNAME.dmp logfile=expdpDBNAME.log

  

If something wrong with memory settings use this commands to start DB and recreate spfile

SQL> startup mount pfile='/tmp/pfileX.txt'';
SQL> create spfile from pfile;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open;

 

If you need set back previous settings after export

SQL> ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
SQL> ALTER SYSTEM SET memory_max_target = 0 scope=spfile;
SQL> ALTER SYSTEM SET memory_target = 0 scope=spfile;
SQL> shu imediate
SQL> startup