備份SCHEMA資訊的指令碼

#!/bin/ksh
##################################################################
#3LPr9&D#:187]J}>]?bVPschema#,187]5DSC;'C{?IEdVC5=/oradata2/masa/export_schema/exportuser.cfgEdVCND# Hg9{2;4fTZ4KEdVCND#ChJv #:
# 1.6TSZRl5X187]CBkP^8DPhR*M,2=P^8D4K4&PASSWDKEY="password";
# 2.51G0TKPPB7>6#,D,HON*/oradata2/masa/export_schema#,Hg9{2;N*4KB7>6#,PhR*=xPPO`S&P^8D
##################################################################

[@more@]

#Rl5X187]IP,SC;'C{#,CBk#,CBkP^8DPhR*M,2=P^8D4K4&
IP="10.110.16.49";
USER="view";
#495DCBkP^8DPhR*M,2=P^8D4K4&
PASSWDKEY="password";
remote_path="/subject10/backup/backup_etl/backup_db_source"; #Rl5X187]B7>6

. /etc/profile
. /oracle/.profile

#187]B7>6
current_dir="/oracle/export_schema"; #51G0TKPPB7>6#,D,HON*/oradata2/masa/export_schema
vss_package_dir="vss_db_source";
temp_file="${current_dir}/tmpfile.txt";
cd ${current_dir};
day_str=`date +%Y%m%d`;

########################################################
#:/J}C{#:begin_export
#Hk?Z2NJ}#:1. 5<3v6TOsKyJtSC;'C{
# 2. 5<
# 3. 5<3v6TOs5DC|C{
########################################################
begin_export()
{
export_user=$1;
export_type=$2;
export_name=$3;
export_dir="${current_dir}/${day_str}/${export_user}/${export_type}";
mkdir -p ${export_dir}
cd ${export_dir}
sSQL="SELECT dbms_metadata.get_ddl('${export_type}', '${export_name}', '${export_user}') || ';' FROM dual;";
sTmpFile="${export_dir}/${export_name}.sql";
sSQL_grant="";
if [ ${export_type} = "TABLE" ]
then
sSQL_grant="select 'GRANT SELECT ON ' || table_name || ' TO ' || grantee || ';' from sys.user_tab_privs where OWNER = upper('${export_user}') and table_name = '${export_name}';";
fi
sqlplus -S '/ as sysdba' << !
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set termout off;
set trimout on;
set trimspool on;
set newpage 0;
set space 0;
set verify off;
set markup html off spool off;
set long 2048576;
SET longc 2048576;
spool ${sTmpFile};
${sSQL}
${sSQL_grant}
spool off;
exit
!

}

########################################################
#:/J}C{#:export_schema_one_type_all
#Hk?Z2NJ}#:1. 5<3vSC;'C{
# 2. 5<3vSC;'6TOs5D@`PM
########################################################
export_schema_one_type_all()
{
export_user=$1;
export_type=$2;
export_dir="${current_dir}/run";
mkdir -p ${export_dir}
cd ${export_dir}
sSQL="select object_name from sys.all_objects a where a.owner = upper('${export_user}') and a.object_type = upper('${export_type}');";
sTmpFile="${export_dir}/${export_user}.${export_type}.sql";
sqlplus -S '/ as sysdba' << !
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set termout off;
set trimout on;
set trimspool on;
set newpage 0;
set space 0;
set verify off;
set markup html off spool off;
set long 2048576;
spool ${sTmpFile};
${sSQL}
spool off;
exit
!
while read tmplistline
do
export_name=`echo "${tmplistline}" | awk '{printf "%s",$1}'`
if [ "-${export_name}" != "-" ]
then
begin_export ${export_user} ${export_type} ${export_name} > ${temp_file}
fi
done < ${sTmpFile}

}

########################################################
#:/J}C{#:export_schema_all
#Hk?Z2NJ}#:1. 5<3vSC;'C{
########################################################
export_schema_all()
{
export_user=$1;

export_schema_one_type_all $export_user "FUNCTION"
#export_schema_one_type_all $export_user "PACKAGE"
export_schema_one_type_all $export_user "PROCEDURE"
#export_schema_one_type_all $export_user "SEQUENCE"
#export_schema_one_type_all $export_user "TABLE"
#export_schema_one_type_all $export_user "TRIGGER"
#export_schema_one_type_all $export_user "VIEW"
#export_schema_one_type_all $export_user "INDEX"

}

########################################################
#:/J}C{#:export_db
#Hk?Z2NJ}#:
########################################################
export_db()
{

export_dir="${current_dir}";
cd ${export_dir}
sTmpFile="${export_dir}/exportuser.cfg";
file_exist=`ls ${sTmpFile} 2>/dev/null |wc -l`;
if [ ${file_exist} = 0 ]
then
sSQL="select upper(username) from ALL_USERS;";
sqlplus -S '/ as sysdba' << !
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set termout off;
set trimout on;
set trimspool on;
set newpage 0;
set space 0;
set verify off;
set markup html off spool off;
set long 2048576;
spool ${sTmpFile};
${sSQL}
spool off;
exit
!
fi

while read tmplistline
do
export_user=`echo "${tmplistline}" | awk '{printf "%s",$1}'`
if [ "-${export_user}" != "-" ]
then
export_schema_all ${export_user}
fi
done < ${sTmpFile}
}

#Vw:/J}5wSC
export_db

#package the data
cd ${current_dir}
package_name="${current_dir}/${day_str}.tar"
tar -cvf ${package_name} ${current_dir}/${day_str}
compress ${package_name}
#mkdir -p ${current_dir}/${vss_package_dir}
#rm -rf ${current_dir}/${vss_package_dir}/*
#mv -f ${current_dir}/${day_str}/* ${current_dir}/${vss_package_dir}
rm -rf ${current_dir}/${day_str}
#rm -f ${vss_package_dir}.jar
#jar cvf ${vss_package_dir}.jar ${vss_package_dir}

#RTOBN*V'3VRl5X187]#,RT<0IO4+ vss ?*
exit 0

ftp -inv ${IP}<user ${USER} ${PASSWDKEY}

cd ${remote_path}
lcd ${current_dir}

bin
put ${day_str}.tar.Z
put ${vss_package_dir}.jar

bye
!
rm -f ${day_str}.tar.Z
rm -f ${vss_package_dir}.jar