Oracle备份

登录oracle数据库,创建指定备份目录，也可使用oracle自身dump目录

[oracle@d9f745cef345 /]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 31 17:48:41 2023

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create   directory dum_date_dir as  '/opt/backup' ;

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
/opt/oracle/product/112010/db_1/ccr/state

SYS                            DATA_PUMP_DIR
/opt/oracle/admin/orcl/dpdump/

SYS                            DUM_DATE_DIR
/opt/backup


OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            XMLDIR
/ade/b/2125410156/oracle/rdbms/xml

创建备份目录

$ mkdir -p /opt/backup && chmod 777 /opt/backup

脚本

#!/bin/bash
#oracle变量设置
#oracle实例名
#可以sqlplus / as sysdba登录后select instance_name from v$instance;查询
#也可以（su - oracle后执行 export |grep ORACLE_SID填入输出的值）
export ORACLE_SID=test;
#oracle 安装目录
#（su - oracle后执行 export |grep ORACLE_HOME填入输出的值）
export ORACLE_HOME=/opt/oracle/product/11.2.0/db;
#设置环境变量信息
export PATH=$ORACLE_HOME/bin:$PATH;

#获取当前日期
DATE=`date +%Y%m%d`
#与oracle中指定的备份路径一致
local_backup_dir="/opt/backup";
BACKUP_DIR=${local_backup_dir}/${DATE}
mkdir -p  $BACKUP_DIR 

#设置输出文件
OUTPUT_FILE=/opt/backup/${DATE}_export_status.log

# 查询所有用户的用户名
USERS=$(sqlplus -s /nolog <<EOF
connect / as sysdba
set feedback off
set pagesize 0
set term off
select username from dba_users WHERE account_status='OPEN' AND username NOT IN ('SYS','SYSTEM','DBSNMP');
exit;
EOF
)

# 导出每个用户的数据
for USER in $USERS
do
  #注意此处要用\转义  同时directory参数可通过select * from dba_directories;查询
  expdp \'/ as sysdba\'  directory=DUM_DATE_DIR dumpfile=${USER}_${DATE}.dmp logfile=${USER}_${DATE}.log schemas=$USER exclude=STATISTICS
   #获取命令的退出状态码
  EXIT_STATUS=$?

  if [ $EXIT_STATUS -eq 0 ]
  then
    echo "Export for user $USER completed successfully." >> $OUTPUT_FILE
    mv $local_backup_dir/${USER}_${DATE}.dmp $BACKUP_DIR
    mv $local_backup_dir/${USER}_${DATE}.log $BACKUP_DIR
    mv $OUTPUT_FILE  $BACKUP_DIR
  else
    echo "Export for user $USER failed with error code $EXIT_STATUS." >> $OUTPUT_FILE
  fi
done

#删除48小时以外的备份数据文件
#-mtime +1表示修改时间大于1天的文件，即48小时以外的文件，为什么不是24小时？因为n只能为整数比1大的就是2，即48小时
#-mtime +0 同理为24小时以外的文件
#-mtime -1表示修改时间为小于1天的文件，即24小时以内的文件
find /opt/backup -mtime +1 -type d -exec rm -rf {} \;

计划任务执行

chmod 777 /opt/shell/backup_oracle.sh
su - oracle  #切换oracle用户执行脚本
crontab  -e
0 1 * * * /opt/shell/backup_oracle.sh