Nethence Newdoc Olddoc Lab Your IP BBDock  


Warning: those guides are mostly obsolete, please have a look at the new documentation.

UnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigs

Oracle Datapump
 
 
Introduction
Datapumps should be executed while the database is up and running. 10g expdp and 11g impdp binaries are compatible. That means you can import a 10g into a 11g database.
 
Note. if you need to proceed through the network (TNS/listener), you might have to use the good old export/import utilities instead.
 
 
Configuration
Check you've got the binaries in your path,
which impdp
which expdp
 
Connect with sys as sysdba to prepare the database,
@/u01/product/11.2.0/ora1120/rdbms/admin/catexp.sql
desc dbms_datapump 
 
Create some dedicated user and give it some rights,
create user oper identified by PASSWORD;
grant connect, resource, exp_full_database to oper;
Note. those three roles are mandatory.
 
Create a folder for the datapumps,
mkdir -p /u03/datapump/dbname
create a logicial folder in Oracle and authorize the user to write in it,
create directory datapump as '/u03/datapump/dbname';
grant read, write on directory datapump to oper;
 
Check you've got some user table and something in it,
sqlplus USERNAME/PASSWORD
select * from table1;
select * from table2;
 
 
Deploy the scripts
Assuming that's in your path,
cd ~/
mkdir -p bin
cd bin
 
Deploy the datapump export script,
cat > expdp.ksh <<EOF9
#!/bin/ksh
 
fusage() {
cat <<EOF9
 
Usage: ${0##*/} dbname
 
EOF9
exit 1
}
 
fmain() {
ORACLE_SID=$1
export ORACLE_SID
 
date0=`date +%s`
expdp oper/PASSWORD full=y directory=datapump \
dumpfile=$date0.$ORACLE_SID.full.dp \
logfile=$date0.$ORACLE_SID.full.dp.log \
2>&1 | grep ^ORA-
}
 
[[ -z $1 ]] && fusage
fmain $@
EOF9
Note. view possible arguments,
help=y  
Note. you can simply estimate the execution time,
estimate_only=true
Note. you can limit resulting file sizes,
filesize=2G
 
Deploy the datapump import table script,
cat > impdp_table.ksh <<EOF9
#!/bin/ksh
 
fusage() {
cat <<EOF9
 
Usage: ${0##*/} dbname dumpfile tablename
 
EOF9
exit 1
}
 
fmain() {
ORACLE_SID=$1
export ORACLE_SID
 
date0=`date +%s`
impdp system/PASSWORD tables=$3 \
directory=datapump \
dumpfile=$2 \
logfile=$date0.import.log \
2>&1 | grep ^ORA-
}
 
[[ -z $1 ]] && fusage
[[ -z $2 ]] && fusage
[[ -z $3 ]] && fusage
fmain $@
EOF9
Note. we're using the 'system' user to import data
Note. view possible arguments,
help=y
 
 
Datapump export
Proceed with a datapump export,
expdp.ksh dbname
Note. only prints output in case of errors
Note. see what's going on during the datapump,
tail -F /u03/datapump/dbname/1277586271.dbname.full.dp.log
 
 
Datapump import
For the purpose of this tutorial, drop some table,
. oraenv
sqlplus USERNAME/PASSWORD
select * from table1;
drop table table1;
Note. you need to drop the table before importing it
 
Import a precise table,
cd /u03/datapump/dbname
ls -ltr
impdp_table.ksh dbname 1277586271.dbname.full.dp USERNAME.table1
cat 1277587138.import.log
Note. during the datapump import, eventually check its progress,
select * from dba_datapump_jobs;
select sid, serial#, sofar, totalwork, start_time, elapsed_seconds from v$session_longops;
 
Check the table is back in its pre-export state,
sqlplus USERNAME/PASSWORD
select * from table1;
 

(obsolete, see the new doc)