oracle数据备份脚本

unix 命令行下的oracle数据备份脚本，创建临时存储过程，读取user_tab_columns表中，各个字段的属性，灵活的配置生成数据的格式

gentbdata.sh：

#!/bin/ksh

fun_expdata () {

echo EXP ">>" TABLE[ $4 ] EXPFILE[ $6 ] CONDITION[ $5 ] SID[ $1/$2@$3 ]

$ORACLE_HOME/bin/sqlplus -S $1/$2@$3>/dev/null <<ORA
    ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
    set trim on
    set trimspool on
    set serveroutput on
    set linesize 32767
    set heading off
    set feedback off
    set pagesize 0
    set verify off
    spool ${6}
    DECLARE
        p_table     varchar2(100) := '$4';
        p_condition varchar2(1024) := '$5';

        --

        l_column_list       VARCHAR2(32767);
        l_value_list         VARCHAR2(32767);
        l_query             VARCHAR2(32767);
        l_cursor             NUMBER;
        ignore         NUMBER;
        l_insertline1           varchar2(32767);
        l_insertline2           varchar2(32767);
        cmn_file_handle       UTL_FILE.file_type;

        --

        FUNCTION get_cols(p_table VARCHAR2)
        RETURN VARCHAR2
        IS
          l_cols VARCHAR2(32767);
          CURSOR l_col_cur(c_table VARCHAR2) IS
                  SELECT column_name
                  FROM   user_tab_columns
                  WHERE   table_name = upper(c_table)
                  ORDER BY column_id;
        BEGIN
          l_cols := null;
          FOR rec IN l_col_cur(p_table)
          LOOP
            l_cols := l_cols || rec.column_name || ',';
          END LOOP;
          RETURN substr(l_cols,1,length(l_cols)-1);
        END;

        --

        FUNCTION get_query(p_table IN VARCHAR2)
        RETURN VARCHAR2
        IS
          l_query VARCHAR2(32767);
            CURSOR l_query_cur(c_table VARCHAR2) IS
              SELECT 'decode('||column_name||',null,''null'','||
                    decode(data_type,'VARCHAR2','''''''''||'||column_name ||'||'''''''''
                    ,'CHAR','''''''''||'||column_name ||'||'''''''''
                    ,'DATE','''TO_DATE(''''''||to_char('||column_name||',''YYYY-MM-DD HH24:MI:SS'')||'''''', ''''YYYY-MM-DD HH24:MI:SS'''')'''
                    ,column_name
                    ) || ')' column_query
                FROM user_tab_columns
              WHERE table_name = upper(p_table)
              ORDER BY column_id;
        BEGIN
          l_query := 'SELECT ';
          FOR rec IN l_query_cur(p_table)
          LOOP
            l_query := l_query || rec.column_query || '||'',''||';
          END LOOP;
          l_query := substr(l_query,1,length(l_query)-7);
          RETURN l_query || ' FROM ' || p_table || ' ' || p_condition;
        END;

        --

    BEGIN
        l_column_list   := get_cols(p_table);
        l_query         := get_query(p_table);
        l_cursor := dbms_sql.open_cursor;
        DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.native);
        DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_value_list, 32767);
        ignore := DBMS_SQL.EXECUTE(l_cursor);

        --

        LOOP
          IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
            DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_value_list);
            l_insertline1:='insert into '||upper(p_table)||' ('||l_column_list||')';
            l_insertline2:=' values ('||l_value_list||');';
            DBMS_OUTPUT.put_line(l_insertline1);
            DBMS_OUTPUT.put_line(l_insertline2);
          ELSE
            EXIT;
          END IF;
        END LOOP;
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    /
    spool off
    exit
ORA

}

#==========================================================================#
#  Copyright (C), 2011-2020, huateng                                       #
#  Version    : 2.0                                                        #
#  Description: export data which format is SQL through sqlplus            #
#  File       : gendata.sh                                                 #
#  Author     :                                                            #
#  Date       : 2015/02/13                                                 #
#  History:                                                                #
#==========================================================================#
#=================================START====================================#

DBUSER="$DBUSER"
DBPWD="$DBPWD"
DBNAME="$DBNAME"
TABLENAME=""
CONDITION="where 1=1"
FILENAME=".dat"

case $# in
0|4)
    echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
    echo "FORMAT:gendata.sh tablename condition filename user passwd sid"
    echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
    exit
    ;;

1)
    TABLENAME=$1
    FILENAME=${1}$FILENAME
    ;;

2)
    TABLENAME=$1
    FILENAME=${1}$FILENAME
    CONDITION=`echo $2 | sed  "s/\'/\'\'/g"`
    ;;

3)
    TABLENAME=$1
    CONDITION=`echo $2 | sed  "s/\'/\'\'/g"`
    FILENAME=${3}$FILENAME
    ;;

5)
    TABLENAME=$1
    CONDITION=`echo $2 | sed  "s/\'/\'\'/g"`
    FILENAME=${3}$FILENAME
    DBUSER=$4
    DBPWD=$5
    ;;

6)
    TABLENAME=$1
    CONDITION=`echo $2 | sed  "s/\'/\'\'/g"`
    FILENAME=${3}$FILENAME
    DBUSER=$4
    DBPWD=$5
    DBNAME=$6
    ;;
esac

fun_expdata $DBUSER $DBPWD $DBNAME $TABLENAME "$CONDITION" $FILENAME

exit

#=================================END======================================#

根据备份列表，调用上面的脚本，生产备份数据

gendbdata.sh：

#/bin/ksh

if [ $# != 2 ]
then
    echo "FORMAT: gen.sh outputfile listfile!"
    exit 1;
fi

filename=$1
DIR_TO_MAKE="../$2"

STAMP=`date +%Y%m%d%H%M%S`
mkdir $STAMP
cd $STAMP

cat $DIR_TO_MAKE | grep -v "^ *#" | tr a-z A-Z | sed  's/ //g' | sed 's/.*/~&\.\.\.!~&;!prompt!~&\.\.\./' | sed 's/~/prompt!prompt Deleting /' | sed 's/~/delete from /' | sed 's/~/prompt Loading /' > ${filename}_tmp11.sql
cat $DIR_TO_MAKE | grep -v "^ *#" | sed 's/ //g' | tr A-Z a-z > ${filename}_tmp21.sql
cp ${filename}_tmp21.sql ${filename}_tmp31.sql
sed 's/./=/g' ${filename}_tmp31.sql > ${filename}_tmp32.sql
sed 's/^/prompt ===========/' ${filename}_tmp32.sql > ${filename}_tmp33.sql

paste -d! ${filename}_tmp11.sql ${filename}_tmp33.sql > ${filename}_tmp51.sql

sed 's/^/prompt!@@/' ${filename}_tmp21.sql > ${filename}_tmp22.sql
sed 's/ *$/.dat;!prompt!commit;!prompt Done./' ${filename}_tmp22.sql > ${filename}_tmp52.sql

paste -d! ${filename}_tmp51.sql ${filename}_tmp52.sql > ${filename}_tmp53.sql

echo "---------------------------------------------" >  ${filename}_tmp61.sql
echo "-- Compose Shell author is andy            --" >> ${filename}_tmp61.sql
echo "-- Created by andy on 2014/11/13, 10:14:30 --" >> ${filename}_tmp61.sql
echo "---------------------------------------------" >> ${filename}_tmp61.sql
echo "spool ${filename}.log"                         >> ${filename}_tmp61.sql
echo "set define off"                                >> ${filename}_tmp61.sql

cat ${filename}_tmp61.sql ${filename}_tmp53.sql > ${filename}_tmp62.sql

echo "spool off"                                     >> ${filename}_tmp62.sql
echo "set define on"                                 >> ${filename}_tmp62.sql
cat ${filename}_tmp62.sql | tr '!' '\n' > ${filename}.sql

rm -f ${filename}_tmp11.sql
rm -f ${filename}_tmp21.sql ${filename}_tmp22.sql
rm -f ${filename}_tmp31.sql ${filename}_tmp32.sql ${filename}_tmp33.sql
rm -f ${filename}_tmp51.sql ${filename}_tmp52.sql ${filename}_tmp53.sql
rm -f ${filename}_tmp61.sql ${filename}_tmp62.sql

cat $DIR_TO_MAKE | grep -v "^ *#" | sed 's/ //g' | tr A-Z a-z | while read LINE
do
    if [ "$LINE" = "" ]
    then
        continue;
    fi
    echo $LINE
    echo "======== gendata $LINE ========"
    gendata.sh $LINE ""
done

cd ..