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/.*/~&/././.!~&;!PRompt!~&/./././' | sed 's/~/prompt!prompt Deleting /' | sed 's/~/delete from /' | sed 's/~/prompt Loading /' > ${filename}_tmp11.sqlcat $DIR_TO_MAKE | grep -v "^ *#" | sed 's/ //g' | tr A-Z a-z > ${filename}_tmp21.sqlcp ${filename}_tmp21.sql ${filename}_tmp31.sqlsed 's/./=/g' ${filename}_tmp31.sql > ${filename}_tmp32.sqlsed 's/^/prompt ===========/' ${filename}_tmp32.sql > ${filename}_tmp33.sqlpaste -d! ${filename}_tmp11.sql ${filename}_tmp33.sql > ${filename}_tmp51.sqlsed 's/^/prompt!@@/' ${filename}_tmp21.sql > ${filename}_tmp22.sqlsed 's/ *$/.dat;!prompt!commit;!prompt Done./' ${filename}_tmp22.sql > ${filename}_tmp52.sqlpaste -d! ${filename}_tmp51.sql ${filename}_tmp52.sql > ${filename}_tmp53.sqlecho "---------------------------------------------" > ${filename}_tmp61.sqlecho "-- Compose Shell author is andy --" >> ${filename}_tmp61.sqlecho "-- Created by andy on 2014/11/13, 10:14:30 --" >> ${filename}_tmp61.sqlecho "---------------------------------------------" >> ${filename}_tmp61.sqlecho "spool ${filename}.log" >> ${filename}_tmp61.sqlecho "set define off" >> ${filename}_tmp61.sqlcat ${filename}_tmp61.sql ${filename}_tmp53.sql > ${filename}_tmp62.sqlecho "spool off" >> ${filename}_tmp62.sqlecho "set define on" >> ${filename}_tmp62.sqlcat ${filename}_tmp62.sql | tr '!' '/n' > ${filename}.sqlrm -f ${filename}_tmp11.sqlrm -f ${filename}_tmp21.sql ${filename}_tmp22.sqlrm -f ${filename}_tmp31.sql ${filename}_tmp32.sql ${filename}_tmp33.sqlrm -f ${filename}_tmp51.sql ${filename}_tmp52.sql ${filename}_tmp53.sqlrm -f ${filename}_tmp61.sql ${filename}_tmp62.sqlcat $DIR_TO_MAKE | grep -v "^ *#" | sed 's/ //g' | tr A-Z a-z | while read LINEdo if [ "$LINE" = "" ] then continue; fi echo $LINE echo "======== gendata $LINE ========" gendata.sh $LINE ""donecd ..

上一篇：bzoj 1770: [Usaco2009 Nov]lights 燈

下一篇：Vector与Stack源码分析