代码语言：javascript复制
#!/bin/bash
# author:院长
#
clear
#[ $(id -u) != "777" ] && echo "错误: 你必须用oracle用户执行备份脚本，且在oracle逻辑目录下" && exit 1
#
#oracle变量设置
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_home2
export ORACLE_SID=bpas2
export ORACLE_TERM
export PATH=$PATH.$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH.$ORACLE_HOME/lib:/usr/lib:/usr/local/lib
export NLS_LANG=AMERICAN_AMERICA.UTF8
#以上代码为Oracle数据库运行账号oracle的系统环境变量设置，必须添加，否则crontab任务计划不能执行。
#部分导出（分区表导出）备份30天的数据
cd /home/oracle/backup
fun1()
{
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TRAFFIC_MONITOR_DET.dmp tables=E_TRAFFIC_MONITOR_DET query='"where comm_time > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=S_KPI_DATA.dmp tables=S_KPI_DATA query='"where CREATE_DATE > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TMNL_COMM_ANALY_STATS.dmp tables=E_TMNL_COMM_ANALY_STATS query='"where STAT_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=I_MASTER_ORA_DATA.dmp tables=I_MASTER_ORA_DATA query='"where START_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TMNL_LOGON.dmp tables=E_TMNL_LOGON query='"where CONN_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TRAFFIC_PORT_STATS.dmp tables=E_TRAFFIC_PORT_STATS query='"where COMM_DATE > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=HIS_KPI_CONFIG_PM.dmp tables=HIS_KPI_CONFIG_PM query='"where CREATE_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=S_WARNING_INFO.dmp tables=S_WARNING_INFO query='"where OCCURE_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TMNL_COMM_HOUR_ANALY.dmp tables=E_TMNL_COMM_HOUR_ANALY query='"where STAT_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TRAFFIC_DIFF_STATS.dmp tables=E_TRAFFIC_DIFF_STATS query='"where CREATE_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_UNENCRYPTED_TMNL.dmp tables=E_UNENCRYPTED_TMNL query='"where CREATE_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_U_TERMINAL_STATS.dmp tables=E_U_TERMINAL_STATS query='"where COMM_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TRAFFIC_PROTOCOL.dmp tables=E_TRAFFIC_PROTOCOL query='"where COMM_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_SERVER_COMM_DATA.dmp tables=E_SERVER_COMM_DATA query='"where COMM_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_SOCKET_INFO.dmp tables=E_SOCKET_INFO query='"where REC_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_S_SQL_STATS.dmp tables=E_S_SQL_STATS query='"where CURR_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TMNL_COMM_DATA.dmp tables=E_TMNL_COMM_DATA query='"where COMM_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TMNL_COMM_DURA_STATS.dmp tables=E_TMNL_COMM_DURA_STATS query='"where STAT_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TRAFFIC_AVG_STATS.dmp tables=E_TRAFFIC_AVG_STATS query='"where CREATE_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=E_TRAFFIC_MONITOR.dmp tables=E_TRAFFIC_MONITOR query='"where START_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=I_MASTER_WEB_DATA.dmp tables=I_MASTER_WEB_DATA query='"where BEGIN_TIME > sysdate - 30"'
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=S_WARNING_HANDLE_INFO.dmp tables=S_WARNING_HANDLE_INFO query='"where OCCURE_TIME > sysdate - 30"'
}
fun1
#全量导出
fun2()
{
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=FULL.dmp tables=E_SQL_RISK_ROLE,E_PARA_CONFIG,S_METRIC_INFO,E_UNSAFTY_PORT_CONFIG,E_SERVICES_MONITOR,S_EXAMPLE_INFO,S_EQU_INFO,C_CLUST_NODE,E_VERSION_MANAGE,S_RESOURCE_INSTANCE_INFO,BASE_ROLE_RESOURCE,BASE_USER_NEW,E_MID_WARE_MONITOR,BASE_USER_ROLE,S_RESOURCE_INFO,C_CLUSTER,SYS_TEMP_FBT,SI_EMAIL_SEND_LOG,LOG_BAUPTAB_INFO,T_TOPU_INFO,T_STAT_TABLESPACE_RESULT,T_STAT_DATA_RESULT,BASE_SYS_APPROVE,E_S_SQL_MODEL,D_LOOPHOLE_INFO,S_VERSION_MONITOR,S_MONITOR_TASK,S_EQU_INSTANCE_INFO,E_UNSAFTY_RELATION,E_EVALUATE_RULE,E_EVALUATE_RESULT,E_EVALUATE_REPORT,E_EVALUATE_ENTIRETY_RULE,E_EVALUATE_ENTIRETY_RESULT,D_LOOPHOLE_DET,D_KNOWLEDGE_BASE_LOG,D_KNOWLEDGE_BASE,D_HOST_STATUS_ROLE,D_HOST_STATUS_ANAY,U_UK_CHECK,U_U2_CHECK,U_U1_CHECK,U_MAC_STATUS,U_MAC_ENCRYPT,U_LOG_UKEY_DATA_INFO,U_LOG_UKEY_DATA,U_LINK_STATUS,R_WS_RCA_FK_CTRLINFO_SIGN,R_WS_RCA_FK_CTRLINFO,R_WS_RCA_CTRLINFO_SIGN,R_WS_RCA_CTRLINFO,COM_AUDIT_TRAIL,BASE_NO_PERMISSION_LOG,BASE_SYS_LOG,BASE_OPERATION_LOG,BASE_SAFE_WARNING,VW_SWITCH_METRIC,VW_KPI_INSTANCE,SI_ADMIN_EMAIL_LOG,S_EQU_INFO_BAK,TEMPLATE,BASE_USER_ORG,BASE_ORG_RULES,T_CHECK_DATA_RESULT,T_WARN_INFO,T_WARN_DETAIL,T_WARNING_HANDLE_INFO,T_SYS_RELI_INFO,T_SECURITY_STATICTIS,T_RESOURCE_UPLOAD,T_RESOURCE_STATICTIS,T_REGION,T_MET_PARAM,T_MASTER_EQU_INFO,T_MASTER_DEC,T_HEALTH_STATUS,T_HEALTH_INFO,T_HEALTH_DETAIL_INFO,T_GATHER_ANALYSIS,T_EQU_INFO,T_EQU_CONFIG,T_ECONOMY_RELIABILITY,T_COLLECT_INFO,S_MIDDLEWARE_MONIT,MIDDEL_WEB_MENU,METRIC_TYPE_CONFIG,C_COLLECTION_USER,E_WHITE_VISIT_INFO,E_WHITE_APP_INFO,E_PORT_LIST,A_SENS_DATA,E_SENS_THRESH,E_SENS_INFO,S_MONITOR,BASE_USER_LOGIN_HIS,BASE_USER_CFG,E_CLENT_BINDING,L_USER_OP_LOG,SI_ADMIN_EMAIL,SI_BUS_ALARM_SEND_CONDITION,SI_BUS_ALARM_CONDITION,SI_BUS_ALARM_USER,SI_BUS_ALARM_RULE,I_MASTER_INSTANCE,E_CLUST_METRIC_SOURCE,E_CLUST_FAULT_DURA,S_TYPE_METRIC_INFO,E_TRAFFIC_DIO_STATS,I_MASTER_CONFIG,I_MASTER_MONITOR,E_CLUST_METRIC_STAT,E_SYS_PACK_INFO,E_CLUST_NODE_STATUS,E_TMNL_COMM_HOUR_DURA,E_TMNL_MONITOR,E_TMNL_ONLINE_STAT,T_COLLE_METRIC,S_DB_METRIC_STATISTIC,TB_MENU,S_SYS_RELY,S_EQU_CONFIG,S_MASTER_EQU_INFO,S_MASTER_DEC,S_RESOURCE_STATICTIS,S_REGION,S_WARNING_INFO_STATISTICS,C_SMART_CHART,S_USER_LOGON,C_WEAKPASS_DIC,E_TMNL_CURRENT_STATISTIC,E_VERSION_INFO,C_CLUSTER_MONITOR_COLLECT,C_CLUSTER_MONITOR,S_TBS_GROW_DAY,S_TBS_GROW_MONTH,C_WEAKPASS_LOG,E_HEALTH_INFO,TEST_SQL_STATS,E_HEALTH_DETAIL_INFO,E_HEALTH_STATUS,S_RESOURCE_EQU_INFO,C_WEAKPASS_RES,C_WEAKUSER_DIC,TT_EXEC_TASK,TT_CONFIG_TASK,TT_QUEUE_TERM_DETAIL_EM,TT_QUEUE_TERM_DETAIL_PN,E_DB_METRIC,E_SERVER_PORT_DOC,E_TMNL_CURRENT_INFO,C_TEST2,C_TEST1,C_TEST,C_SERIES,C_OPTION,C_AXIS,E_WHITE_LIST_INFO,E_DB_MONITOR,E_UNSAFTY_MONITOR,E_MENU_INFO,A_S_TRANS_DATA_SQL,TT_QUEUE_TERM_DETAIL,TT_QUEUE_TERM,TT_SCHEME_DETAIL,TT_SCHEME,TT_FUN,TT_TYPE,BASE_SERVICE_URL,BASE_SERVICE_MAINTENANCE,BASE_SERVICE_DEFEND,E_CLUST_METRIC,A_S_TRANS_DATA_SUMY,A_TRANS_DATA,A_WEB_DATA,A_AGENT_DATA,P_CODE_SORT,A_S_SQL_DATA,A_APP_DATA,A_ERROR_DATA,A_S_TRANS_DATA_CHILD,A_S_TRANS_DATA_INFO,A_METRIC_NAME,TRANINFO,BASE_EXCEPEDIT,BASE_TARGET,BASE_SOFTWARE,BASE_DEVICE,BASE_EXCEPTRACTION,E_NODE_FAULT_DURA,E_CLUST_STATUS,E_CLUST_NODE_FAULT,E_CLUST_FAULT,S_EQU_PERF_STATS,S_HIS_WARNING_INFO,BASE_SHORTCUT,BASE_ROLES,BASE_RCV_MESSAGE,BASE_SEND_MESSAGE,BASE_USER,BASE_POLICY,BASE_POLICY_RELA,BASE_RESOURCES,BASE_ORG,BASE_ONLINE,BASE_EXCEPTION,BASE_COUNTRY,BASE_CODE_KIND,BASE_CODE,BASE_CANT_TYPE,BASE_CANT,BASE_MESSAGE,P_CODE
}
fun2
#这些存错过程、job、序列对象等可以写成一条语句。但是担心那一天我自己忘记了备份的是什么所以分开写的。
#
#导出存储过程
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=p.dmp include=procedure
#导出job
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=j.dmp include=job
#导出序列对象
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=s.dmp include=SEQUENCE
#导出视图
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=v.dmp include=VIEW
#导出函数
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=f.dmp include=function
#导出触发器
expdp ami/Xnjc_201703# directory=MY_DUMP dumpfile=t.dmp include=trigger
#配置一个变量（后面的.zip文件以时间命名）
backtime=`date %Y-%m-%d-%H-%M-%S`
#按表备份.dmp文件太多所以压缩一下。也减少空间。
#由于上面写了cd /home/oracle/backup 在这里面备份的。所以压缩/home/oracle/backup这个目录里面的所有.dmp文件并生成.zip压缩包到当前目录
zip ${backtime}.zip *
#移动.zip压缩包到/home/oracle/back里面。这个back目录就是存放压缩完成之后的压缩包。
mv ${backtime}.zip /home/oracle/back
#删除备份目录也就是oracle数据库识别到的逻辑目录的.dmp文件。（按表备份的.dmp文件比较多，29个。已经压缩了所以删除掉）
rm -rf /home/oracle/backup/*
#为节约磁盘空间，删除/home/oracle/back 7天以前备份的.zip文件
find /home/oracle/back -type f -mtime 7 -name "*.zip"|xargs rm -rf #mmin分钟 #mtime天计划任务：为避免数据库压力，设定在每天凌晨1点执行脚本进行备份。一个root用户、一个oracle用户。
代码语言：javascript复制[root@oracle ~]$ crontab -u oracle -e
00 01 * * * /home/oracle/backups.sh
[oracle@oracle ~]$ crontab -e
00 01 * * * /home/oracle/backups.sh重启计划任务。用root用户重启
代码语言：javascript复制[root@oracle ~]# /etc/init.d/crond restart
