ORACLE数据备份和恢复

数据泵形式

数据泵操作 expdp

#登录oracle服务器, 一般默认是oracle用户
#登录后先创建数据存放地址 , 进入sql界面
mkdir -p /app/oradata
sql / as sysdba
#定义数据库路径
CREATE DIRECTORY dump_dir AS '/app/oradata';

#路径读写权限授权给将导出导入的用户,例mdata用户
grant read,write on directory DUMP_DIR to base;
#退出sql界面
exit

#导出数据, 例mdata用户
expdp base/test201988 directory=DUMP_DIR dumpfile=base-20201218.dmp

数据泵操作impdp

#导入服务器按找如上的方式配置数据存放路径, 并将PRODUCT_PARAM-20201218.dmp文件放入指定数据位置
mkdir -p /app/oradata
sql / as sysdba

#定义数据库路径
CREATE DIRECTORY dump_dir AS '/app/oradata';

#路径读写权限授权给将导出导入的用户,例base用户
grant read,write on directory DUMP_DIR to base;

#退出sql界面
exit

#导入
#remap_schema指定原用户和目标用户的对应关系, 一致时可忽略
#remap_tablespace指定原用户所在表空间和目标用户所在表空间的对应关系, 一致时可忽略
#例原用户base, 表空间base, 目标用户basepro,表空间basepro
impdp basepro/test201988 directory=DUMP_DIR dumpfile=base-20201218.dmp remap_schema=base:basepro remap_tablespace=base:basepro table_exists_action=replace

编写备份脚本

#!/bin/bash
source /app/oracle/.bash_profile

ORACLE_SID="orcl"
ORACLE_PASS="test201988"
ORACLE_IP="127.0.0.1:1521"
ORACLE_DATA="admin acctcenter acctdayend asset common convert credit easyflow flow inreach mdata riskstandard oauth"

current_date=$(date "+%Y%m%d)
current_time=$(date "+%Y%m%d-%H%M%S")

function exportData() {
local ORACLE_USER=$1
expdp system/system@${ORACLE_IP}/${ORACLE_SID} directory=DUMP_DIR dumpfile=${ORACLE_USER}-${current_date}-${current_time}.dmp schemas=${ORACLE_USER} logfile=${ORACLE_USER}-${current_date}-${current_time}.log
}

cd /app/oradata
for data in ${ORACLE_DATA}; do
exportData $data
done

mkdir -p ./${current_date}
tar -czf ./${current_date}/expdp-${current_date}-${current_time}.tar.gz *-${current_date}-${current_time}.dmp *-${current_date}-${current_time}.log
rm -rf *-${current_date}-${current_time}.dmp *-${current_date}-${current_time}.log

find . -type d -ctime +7 -name "[0-9][0-9]*[0-9]" |xargs rm -rf

恢复脚本

需将tar包解压到数据库路径DUMP_DIR下
#!/bin/bash
source /app/oracle/.bash_profile
ORACLE_SID="orcl"
ORACLE_PASS="test201988"
ORACLE_IP="127.0.0.1:1521"
ORACLE_DATA="admin acctcenter acctdayend asset common convert credit easyflow flow inreach mdata riskstandard oauth"

current_date=$(date "+%Y%m%d)
#手动填写,或调整为外部传参
current_time=$(date "+%Y%m%d-%H%M%S")

function importData() {
local ORACLE_USER=$1
impdp system/system@${ORACLE_IP}/${ORACLE_SID} directory=DUMP_DIR dumpfile=${ORACLE_USER}-${current_date}-${current_time}.dmp table_exists_action=replace
}

for data in ${ORACLE_DATA}; do
importData $data
done

远程导入导出形式

配置环境变量

mkdir -p /app/loan/oracle/backup
vi ~/.bash_profile
export ORACLE_BASE=/app/loan/oracle;
export ORACLE_HOME=/app/loan/oracle;
export ORACLE_SID=orcl;
export PATH=$ORACLE_HOME/bin:$PATH;

source ~/.bash_profile

#上传oracle导入导出工具包到/app/loan/oracle下解压
unzip instantclient-tools-linux.x64-12.2.0.1.0.zip

#重命名为bin
mv instantclient_12_2 bin

编写备份脚本

#!/bin/bash
source /app/loan/.bash_profile

ORACLE_SID="orcl"
ORACLE_PASS="test201988"
ORACLE_IP="127.0.0.1:1521"
ORACLE_DATA="admin acctcenter acctdayend asset common convert credit easyflow flow inreach mdata riskstandard oauth"

current_date=$(date "+%Y%m%d)
current_time=$(date "+%Y%m%d-%H%M%S")

function exportData() {
mkdir -p ./$current_date
local ORACLE_USER=$1
exp ${ORACLE_USER}/${ORACLE_PASS}@${ORACLE_IP}/${ORACLE_SID} file=./$current_date/${ORACLE_USER}-$current_time.dmp
}

cd /app/loan/oracle/backup
for data in ${ORACLE_DATA}; do
exportData $data
done

恢复脚本

#恢复前请手动truncate所有的表

#SELECT 'TRUNCATE TABLE '|| table_name || ';' FROM USER_TABLES ORDER BY TABLE_NAME;
#!/bin/bash
source /app/loan/.bash_profile
ORACLE_SID="orcl"
ORACLE_PASS="test201988"
ORACLE_IP="127.0.0.1:1521"
ORACLE_DATA="admin acctcenter acctdayend asset common convert credit easyflow flow inreach mdata riskstandard oauth"

current_date=$(date "+%Y%m%d)
#手动填写,或调整为外部传参
current_time=$(date "+%Y%m%d-%H%M%S")

function importData() {
local ORACLE_USER=$1
imp ${ORACLE_USER}/${ORACLE_PASS}@${ORACLE_IP}/${ORACLE_SID} file=./$current_date/${ORACLE_USER}-$current_time.dmp \
fromuser=${ORACLE_USER} touser=${ORACLE_USER} ignore=y
}

cd /app/loan/oracle/backup
for data in ${ORACLE_DATA}; do
importData $data
done