oracle數據備份腳本

2019-11-11 02:59:56

字體：大中小

來源：轉載

供稿：網友

unix 命令行下的Oracle數據備份腳本，創建臨時存儲過程，讀取user_tab_columns表中，各個字段的屬性，靈活的配置生成數據的格式

gentbdata.sh：

#!/bin/kshfun_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 exitORA}#==========================================================================## 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 $# in0|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 ;;esacfun_expdata $DBUSER $DBPWD $DBNAME $TABLENAME "$CONDITION" $FILENAMEexit#=================================END======================================#

根據備份列表，調用上面的腳本，生產備份數據

gendbdata.sh：

#/bin/kshif [ $# != 2 ]then echo "FORMAT: gen.sh outputfile listfile!" exit 1;fifilename=$1DIR_TO_MAKE="../$2"STAMP=`date +%Y%m%d%H%M%S`mkdir $STAMPcd $STAMPcat $DIR_TO_MAKE | grep -v "^ *#" | tr a-z A-Z | sed 's/ //g' | sed 's/.*/~&/././.!~&;!