1、Export/Import的用處
Oracle Export/Import工具用於在數據庫之間傳遞數據。
Export從數據庫中導出數據到dump文件中
Import從dump文件中到入數據導數據庫中
下面是一般使用他們的情況
（1）、兩個數據庫之間傳送數據
同一個版本的Oracle Server之間
不同版本的Oracle Server之間
同種OS之間
不同種OS之間
（2）、用於數據庫的備份和恢復
（3）、從一個SCHEMA傳送到另一個SCHEMA
（4）、從一個TABLESPACE傳送到另一個TABLESPACE
2、DUMP文件
EXPORT到出的是二進制格式的文件，不可以手工編輯，否則會損壞數據。
該文件在Oracle支持的任何平台上都是一樣的格式，可以在各平台上通用。
DUMP文件在IMPORT時采用向上兼容方式，就是說ORALCE7的DUMP文件可以導入
到Oracle8中，但是版本相差很大的版本之間可能有問題。
3、EXPORT/IMPORT過程
EXPORT導出的DUMP文件包含兩種基本類型的數據
- DDL (Data Dictionary Language)
- Data
DUMP文件包含所有重新創建Data Dictionary的DDL語句，基本上是可以讀的格式
。
但是應該注意的是，千萬不要用文本編輯器編輯之，Oracle說不支持這樣做的。
下面列出的是DUMP文件中包括的Oracle對象，分為TABLE/USER/FULL方式，有些對
象
只是在FULL方式下才有（比如public synonyms, users, roles, rollback segm
ents等）
Table mode User Mode Full Database Mode
---------------------- ---------------------- ----------------------
---
Table definitions Table definitions Table definitions
Table data Table data Table data
Owner's table grants Owner's grants Grants
Owner's table indexes Owner's indexes Indexes
Table constraints Table constraints Table constraints
Table triggers &nbs
備份例子1：導出備份數據
#!/bin/bash
#=============================================#
# &n
BACKUPDIR=/oradata/backup; export BACKUPDIR
USER_PASSWD=dbuser/Oracle
RESULTFILE=/export/home/Oracle/log/result.log
cd $BACKUPDIR
[NextPage]
exp $USER_PASSWD log=$(date '+%Y%m%d').log file=$(date '+%Y%m%d').dmp owner=dbuser
buffer=4096000 rows=n
if [ $? != 0 ]
then
echo "`date` backup exp fail" >> $RESULTFILE
exit
fi
rm -f tables.txt
sqlplus -s $USER_PASSWD </dev/null
set heading off;
set term off;
set echo off;
set pagesize 0;
set linesize 1000;
set trimspool on;
set trimout on;
set feedback off;
set colsep |;
spool tables.txt;
select table_name from user_tables;
spool off;
exit;
EOF
for table in $(cat tables.txt)
do
exp $USER_PASSWD log=${table}_$(date '+%Y%m%d').log file
=${table}_$(date '+%Y%m%d').dmp tables=$table direct=y
if [ $? != 0 ]
then
echo "`date` backup exp $table fail" >> $RESULTFILE
exit
fi
done
compress -f *.dmp
echo "`date` backup succeed" >> $RESULTFILE
備份例子2：聯機日備份數據
#!/usr/local/bin/bash
first_msg()
{
echo "******************************************************"
echo " "
echo "The batch process BACKUP_DAILY is starting at [ 'date' ]"
echo "Excuting by LogName = [ $LOGNAME ]"
echo " "
}
final_msg()
{
echo " "
echo "The batch process BACKUP_DAILY completed at [ 'date' ]"
echo "Excuting by LogName = [ $LOGNAME ]"
echo ">>>>> Please Make
#********************************
# Main *
#********************************
USER_PASSWD=system/system
ARCHIVE_DEST=/appl/Oracle/oradata/orafe/arch
DEVICE=/dev/rmt/ctape1
TRC_FILE=$TRC/Z_JOB_BACKUP_DAILY
TMP_SQL=$TRC/backup_daily_tmp.sql
BACKUP_DEST=${BACKUP_PATH}/$(date '+%Y%m%d')
SUCCESS=0
FAIL=1
first_msg
mkdir -m 777 -p $BACKUP_DEST
if [ ! -d $BACKUP_DEST ]
then
echo "$BACKUP_DEST create fail"
exit
fi
#*******************************
# backup archive log *
#*******************************
echo "=========================================="
echo "Backup archive log begin on [ 'date' ]"
echo "alter system archive log stop;" > $TMP_SQL
sqlplus -s $USER_PASSWD <start $TMP_SQL
EOF
if [ $? != 0 ]
then
echo "alter archive log stop fail"
exit
fi
cd $ARCHIVE_DEST
FILE_QT='ls|grep -c "arch*"'
FILE='ls|grep "arch*"'
echo "alter system archive log start;" > $TMP_SQL
sqlplus -s $USER_PASSWD <start $TMP_SQL
EOF
if [ $? != 0 ]
then
echo "alter archive log start fail"
exit
fi
if [ $FILE_QT != 0 ]
then
mv -f $FILE $BACKUP_DEST
cd $BACKUP_DEST
compress -f *
tar -cvf $DEVICE *
if [ $? != 0 ]
then
echo "tar to tape fail"
exit
fi
fi
echo "Backup archive log end on [ 'date' ]"
echo "=========================================="
final_msg
備份例子3：聯機全庫備份數據
#!/usr/local/bin/bash
first_msg()
{
echo "******************************************************"
echo " &nbs
final_msg()
{
echo " "
echo "The batch process JOB_BACKUP_FULL completed at [ 'date' ]"
echo "Excuting by LogName = [ $LOGNAME ]"
echo ">>>>> Please Make Sure That It Has Been Done Successfully, "
echo ">>>>> Otherwise You Must Re-do It."
echo " "
echo "******************************************************"
}
#**************************
#* backup_begin *
#**************************
backup_begin()
{
echo "alter tablespace $1 begin backup;" > $TMP_SQL
sqlplus -s $USER_PASSWD <start $TMP_SQL
EOF
if [ $? != 0 ]
then
return $FAIL
fi
return $SUCCESS
}
#**************************
#* backup_end *
#**************************
backup_end()
{
echo "alter tablespace $1 end backup;" > $TMP_SQL
sqlplus -s $USER_PASSWD <start $TMP_SQL
EOF
if [ $? != 0 ]
then
return $FAIL
fi
return $SUCCESS
}
#********************************
# Main *
#********************************
USER_PASSWD=system/system
BACKUP_DEST=$BACKUP_PATH/full
DEVICE=/dev/rmt/ctape1
BACKUP_CFG=$TRC/backup.cfg
TRC_FILE=$TRC/BACKUP_FULL
TMP_SQL=$TRC/backup_full_tmp.sql
SUCCESS=0
FAIL=1
first_msg
rm -f $BACKUP_CFG
sqlplus -s $USER_PASSWD </dev/null
set heading off;
set term off;
set echo off;
set page
rm -f $TRC_FILE
if [ ! -f $BACKUP_CFG ]
then
echo "備份配置文件缺失" >$TRC_FILE
echo "$BACKUP_CFG not found"
exit
fi
mkdir -m 777 -p $BACKUP_DEST
if [ ! -d $BACKUP_DEST ]
then
echo "備份目錄創建失敗" >$TRC_FILE
echo "$BACKUP_DEST create fail"
exit
fi
cd $BACKUP_DEST
rm -f *
#*******************************
# backup control file *
#*******************************
echo "=========================================="
echo "正在備份控制文件" > $TRC_FILE
echo "Backup control begin on [ 'date' ]"
echo "alter database backup controlfile to '$BACKUP_DEST/control.ctl';" > $TMP_SQL
sqlplus -s $USER_PASSWD <start $TMP_SQL
EOF
if [ $? != 0 ]
then
echo "備份控制文件失敗" > $TRC_FILE
echo "Backup control file fail"
exit
fi
echo "Backup control end on [ 'date' ]"
#*******************************
# backup tablespaces *
#*******************************
TABLESPACES='cut -d= -f1 $BACKUP_CFG|uniq'
for tablespace in $TABLESPACES
do
echo "=========================================="
echo "正在備份$tablespace" > $TRC_FILE
echo "Backup $tablespace begin on [ 'date' ]"
backup_begin $tablespace
if [ $? != 0 ]
then
echo "備份$tablespace失敗" > $TRC_FILE
echo "Turn on backup option of $tablespace fail"
exit
fi
DATAFILES='awk -v var=$tablespace -F = '$1==var {print $2}' $BACKUP_CFG'
for datafile in $DATAFILES
do
#**************************
# tar files *
#**************************
echo "=========================================="
echo "正在備份至磁帶" > $TRC_FILE
echo "tar to tape on [ 'date' ]"
cd $BACKUP_DEST
tar -cvf $DEVICE *
if [ $? != 0 ]
then
echo "備份至磁帶失敗" > $TRC_FILE
echo "tar to tape fail"
exit
fi
echo "tar to tape on [ 'date' ]"
echo "=========================================="
echo "全備份已完成" > $TRC_FILE
final_msg
