OracleEXP和IMP用法和介绍（3）

下列关键字仅用于可传输的表空间
TRANSPORT_TABLESPACE 导入可传输的表空间元数据 (N)
TABLESPACES 将要传输到数据库的表空间
DATAFILES 将要传输到数据库的数据文件
TTS_OWNERS 拥有可传输表空间集中数据的用户

备份例子1：导出备份数据

#!/bin/bash

#=============================================#
#   如果脚本用于crontab，下面Oracle环境变量必须设置 #
#=============================================#
ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/product/8.1.7
ORACLE_SID=oradb6
ORACLE_TERM=ansi
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
NLS_LANG=American_America.ZHS16GBK
NLS_DATE_FORMAT=YYYYMMDDHH24MISS
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
TMPDIR=/var/tmp
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_TERM ORA_NLS33 NLS_LANG
export NLS_DATE_FORMAT LD_LIBRARY_PATH TMPDIR PATH
#============================================#

BACKUPDIR=/oradata/backup; export BACKUPDIR
USER_PASSWD=dbuser/oracle

RESULTFILE=/export/home/oracle/log/result.log

cd $BACKUPDIR

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 ; Sure That It Has Been Done Successfully, "
echo ">>>>> Otherwise You Must Re-do It."
echo " "
echo "*****************************************************"
}

#********************************
# 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