oracle自动备份同步

database

oracle的简单导入导出工具一般有 exp/imp 跟 expdp/impdp。

  • exp无法导出空表结构
  • exp可以导出long类型的数据，expdp不可以。long类型现在在慢慢被淘汰中。
  • exp是客户端工具，expdp是服务器端工具，expdp还可以并发，exp不可以

使用exp导出而不是expdp的情况是无法直接访问数据库主机，也无法在建立dblink，这个时候只能使用exp导出。大部分情况还是用 expdp/impdp。exp无法导出空表不注意的话是很麻烦的，曾经做同步就因为这个原因导致应用无法使用，虽然网上也有一些解决办法，但是都需要对源库做一些变更，这对生产服务器来说还是很麻烦的，毕竟操作了就有风险要负责任。下面是用expdp/impdp自动同步生产跟测试库的范例。

环境

源库跟目的库的Oracle大版本要一致，并且数据库SID要一样。
本文测试环境：

  • 源库操作系统 windows server 2008 R2 x64
    源库数据库版本 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
  • 目的库操作系统 win7 x64 win10 x64 windows server 2008 R2 x64 均测试成功
    目的库数据库版本 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

查看版本：

1
select banner from sys.v_$version;

源库导出

在源库机器(假定SID名为ORCL)执行以下操作：

1. 打开CMD，用sysdba登录数据库。

有些操作系统安装了不止一个数据库实例，为了确保操作的是源库，在CMD命令行中先执行一次set ORACLE_SID=ORCL：

1
2
set ORACLE_SID=ORCL
sqlplus / as sysdba

先检查一下登录的数据库：

1
2
3
4
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl

2. 新建directory（首次导出才要）。

1
2
3
SQL> create directory dump_dir as 'D:\dump';  
SQL> grant all on directory dump_dir to test; 
SQL> select * from dba_directories;

3. 执行导出操作。

此操作再CMD中执行，不是登录到SQLPLUS里面。可用Ctrl+C退出SQLPLUS。

1
expdp system/manager@ORCL directory=dump_dir dumpfile=expdp_orcl_full.dmp logfile=expdp_orcl_full.log FULL=y;

导出完成后，在D:\dump里面就能看到文件了，然后拷贝到目标机器的director路径里面。

目的库导入

首次导入同源库导出1、2步骤操作，这里省略。
因为目的库已经有各种用户对象了，不确定这些已存在对象会不会影响导入结果，所以这边先进行删除对象操作，然后再进行导入操作，最后重新编译一下一些存储过程等。

1.删除对象

先创建删除对象的脚本，CMD下执行：

1
sqlplus system/manager@ORCL @d:\orcl_build_drop_sql.sql

其中，orcl_build_drop_sql.sql 如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- d:\orcl_build_drop_sql.sql
set feedback off heading off verify off trimspool off  
set pagesize 0 linesize 100  
define v_spool ='d:\orcl_drop_objects.sql'  
spool &v_spool  
  SELECT 'DROP PACKAGE BODY '|| OWNER ||'.'|| OBJECT_NAME ||';'  FROM ALL_OBJECTS WHERE OBJECT_TYPE='PACKAGE BODY' AND OWNER IN (('DBUSER1','DBUSER2'))
    union all
  SELECT 'DROP PACKAGE '|| OWNER ||'.'|| OBJECT_NAME ||';'  FROM ALL_OBJECTS WHERE OBJECT_TYPE='PACKAGE' AND OWNER IN (('DBUSER1','DBUSER2'))
    union all
  SELECT 'DROP PROCEDURE '|| OWNER ||'.'|| OBJECT_NAME ||';'  FROM ALL_OBJECTS WHERE OBJECT_TYPE='PROCEDURE' AND OWNER IN (('DBUSER1','DBUSER2'))
    union all
  SELECT 'DROP FUNCTION '|| OWNER ||'.'|| OBJECT_NAME ||';'  FROM ALL_OBJECTS WHERE OBJECT_TYPE='FUNCTION' AND OWNER IN (('DBUSER1','DBUSER2'))
    union all
  SELECT 'DROP SEQUENCE '|| SEQUENCE_OWNER ||'.'|| SEQUENCE_NAME ||';'  FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER IN (('DBUSER1','DBUSER2'))
    union all
  SELECT 'DROP VIEW '|| OWNER ||'.'|| VIEW_NAME ||';'  FROM ALL_VIEWS WHERE OWNER IN (('DBUSER1','DBUSER2'))
    union all
  SELECT 'DROP TABLE '|| OWNER ||'.'|| TABLE_NAME ||' CASCADE CONSTRAINTS PURGE;'  FROM ALL_TABLES WHERE OWNER IN (('DBUSER1','DBUSER2'))
    union all
  SELECT 'exit ' from dual;

spool off  

exit

创建的删除脚本在 d:\orcl_build_drop_sql.sql，在CMD中执行脚本删除对象：

1
sqlplus system/krcs@KRCS @d:\krcs_drop_objects.sql

2.导入操作

执行导入操作：

1
impdp system/manager@ORCL directory=dump_dir dumpfile=expdp_orcl_full.dmp logfile=impdp_orcl_full.log FULL=y;

3.重新编译无效对象

oracle自带脚本$ORACLE_HOME/rdbms/admin/utlrp.sql可重新编译无效对象。

1
sqlplus system/krcs@KRCS @D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlrp.sql

汇总所有操作写成批处理自动执行

1.源库定时导出并发送

在源库机器新建一个任务计划，每天执行以下批处理。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
@echo off  
color 0b & cls  
echo [%date:~0,10% %time%] 设置备份文件存放文件夹...  
echo.
set "localexpdpdir=d:\dump"  
:: 备份服务器
set "bk_server=\\192.168.1.1\g$"  
set "bk_server_full=\\192.168.1.1\g$\数据"
:: 测试服务器1
set "bk_server2=\\192.168.1.2\e$\data"
:: 测试服务器2
set "bk_server3=\\192.168.1.3\c$\temp"
  
echo [%date:~0,10% %time%] 是否存在2天前备份文件，若存在则删除...  
echo.
forfiles /p %localexpdpdir% /d -2 /c "cmd /c echo deleting @file ... && del /f /q /a @path"
  
echo [%date:~0,10% %time%] 设置备份文件名
echo.
set name=%date:~0,4%%date:~5,2%%date:~8,2%
set name=%name%
::set name=20161210

echo [%date:~0,10% %time%] 是否存在同名文件，若存在则删除同名文件...  
echo.
echo [%date:~0,10% %time%] ===========================================================
echo.
if exist %localexpdpdir%\orcl_full_%name%.dmp del %localexpdpdir%\orcl_full_%name%.dmp
if exist %localexpdpdir%\orcl_%name%.dmp del %localexpdpdir%\orcl_%name%.dmp
echo [%date:~0,10% %time%] 开始备份.....    
echo.
::select * from dba_directories;
:: 备份两种,一种全库，一种按部分用户
expdp system/manager@orcl directory=dump_dir dumpfile=orcl_%name%.dmp logfile=orcl_%name%.log schemas=dbuser1,dbuser2
expdp system/manager@orcl directory=dump_dir dumpfile=orcl_full_%name%.dmp logfile=orcl_full_%name%.log FULL=y
echo [%date:~0,10% %time%] 备份完毕!
echo.

echo [%date:~0,10% %time%] ===========================================================
echo [%date:~0,10% %time%] 拷贝备份到192.168.1.1
echo.
net use O: /del
net use O: \\192.168.1.1\g$ "psw****" /user:"Administrator"

:: 每周二拷贝全量备份，其余日期拷贝选定用户的备份，减少备份服务器的空间压力
if "%date:~13%"=="二" (
copy %localexpdpdir%\orcl_full_%name%.dmp %bk_server_full%\orcl_full_%name%.dmp /y
) else (
copy %localexpdpdir%\orcl_%name%.dmp %bk_server%\orcl_%name%.dmp /y
)

echo [%date:~0,10% %time%] 删除备份服务器上31天前的全量备份
echo.
forfiles /p "o:" /d -31 /c "cmd /c echo deleting @file ... && del /f /q /a @path"
echo [%date:~0,10% %time%] 删除备份服务器上7天前的选定用户数据的备份
echo.
forfiles /p "o:\数据" /d -7 /c "cmd /c echo deleting @file ... && del /f /q /a @path"

echo [%date:~0,10% %time%] ===========================================================

::echo 拷贝备份到192.168.1.2
::net use O: /del
::net use O: \\192.101.1.2\e$\data "psw****" /user:"Administrator"
::
::copy %localexpdpdir%\orcl_full_%name%.dmp %bk_server2%\orcl_full_%name%.dmp /y
::echo 删除备份服务器上2天前的备份
::forfiles /p "o:\" /d -2 /c "cmd /c echo deleting @file ... && del /f /q /a @path"

echo [%date:~0,10% %time%] ===========================================================

echo [%date:~0,10% %time%] 拷贝备份到192.168.1.3
echo.
net use O: /del
net use O: \\192.168.1.3\c$\temp "psw****" /user:"Administrator"

echo [%date:~0,10% %time%] 删除备份服务器192.168.1.3上2天前的备份
echo.
forfiles /p "o:\" /d -2 /c "cmd /c echo deleting @file ... && del /f /q /a @path"
copy %localexpdpdir%\orcl_full_%name%.dmp %bk_server3%\orcl_full_%name%.dmp /y
echo [%date:~0,10% %time%] 拷贝备份到192.168.1.3完成
echo.
echo [%date:~0,10% %time%] ===========================================================
echo [%date:~0,10% %time%] 结束

goto :eof

::rem 计算指定天数之前的日期
::set DaysAgo=5
::rem 假设系统日期的格式为yyyy-mm-dd
::call :DateToDays %date:~0,4% %date:~5,2% %date:~8,2% PassDays
::set /a PassDays-=%DaysAgo%
::call :DaysToDate %PassDays% DstYear DstMonth DstDay
::set DstDate=%DstYear%%DstMonth%%DstDay%
::echo %DaysAgo%天的日期是%DstDate%
::pause
::goto :eof

:DateToDays %yy% %mm% %dd% days
setlocal ENABLEEXTENSIONS
set yy=%1&set mm=%2&set dd=%3
if 1%yy% LSS 200 if 1%yy% LSS 170 (set yy=20%yy%) else (set yy=19%yy%)
set /a dd=100%dd%%%100,mm=100%mm%%%100
set /a z=14-mm,z/=12,y=yy+4800-z,m=mm+12*z-3,j=153*m+2
set /a j=j/5+dd+y*365+y/4-y/100+y/400-2472633
endlocal&set %4=%j%&goto :EOF

:DaysToDate %days% yy mm dd
setlocal ENABLEEXTENSIONS
set /a a=%1+2472632,b=4*a+3,b/=146097,c=-b*146097,c/=4,c+=a
set /a d=4*c+3,d/=1461,e=-1461*d,e/=4,e+=c,m=5*e+2,m/=153,dd=153*m+2,dd/=5
set /a dd=-dd+e+1,mm=-m/10,mm*=12,mm+=m+3,yy=b*100+d-4800+m/10
(if %mm% LSS 10 set mm=0%mm%)&(if %dd% LSS 10 set dd=0%dd%)
endlocal&set %2=%yy%&set %3=%mm%&set %4=%dd%&goto :EOF

2.目的库定时导入

在目的库机器新建一个任务计划，每天执行以下批处理(执行时间在源库的定时时间延后2小时)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
@echo off  
color 0b & cls  
echo [%date:~0,10% %time%] 设置导入路径...  
echo.
set "localexpdpdir=c:\temp"
echo %localexpdpdir%
echo.

::set "localexpdpdir=Z:\orcl系统备份\data"
::set "localexpdpdir=Y:"

echo [%date:~0,10% %time%] 设置导入文件名
echo.
set name=%date:~0,4%%date:~5,2%%date:~8,2%
set name=%name%
::set name=20161215

:: 前一日
::call :DateToDays %date:~0,4% %date:~5,2% %date:~8,2% PassDays
::set /a PassDays-=1
::call :DaysToDate %PassDays% DstYear DstMonth DstDay
::set name=%DstYear%%DstMonth%%DstDay%

echo %name%
echo [%date:~0,10% %time%] work begin......
echo.

if exist %localexpdpdir%\orcl_full_%name%.dmp (
echo [%date:~0,10% %time%] 存在%localexpdpdir%\orcl_full_%name%.dmp
echo [%date:~0,10% %time%] 创建删除对象脚本.....
echo.
sqlplus system/manager@orcl @d:\orcl_build_drop_sql.sql
echo [%date:~0,10% %time%] 开始删除对象.....
echo.
sqlplus system/manager@orcl @d:\orcl_drop_objects.sql
echo [%date:~0,10% %time%] 开始导入.....
impdp system/manager@orcl directory=dump_dir dumpfile='orcl_full_%name%.dmp' logfile='orcl_full_%name%.log' TABLE_EXISTS_ACTION=SKIP schemas=dbuser1,dbuser2
echo.
echo [%date:~0,10% %time%] 重新编译无效对象.....
echo.
sqlplus system/manager@orcl @D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlrp.sql
)
::del %localexpdpdir%\sipfbak%name%.dmp)
echo [%date:~0,10% %time%] 导入结束。
echo.

::pause

:: 删除T-2日备份
call :DateToDays %date:~0,4% %date:~5,2% %date:~8,2% PassDays
set /a PassDays-=2
call :DaysToDate %PassDays% DstYear DstMonth DstDay
set aYesDate=%DstYear%%DstMonth%%DstDay%
if exist %localexpdpdir%\orcl_full_%aYesDate%.dmp (
    echo [%date:~0,10% %time%] 删除过期文件orcl_full_%aYesDate%.dmp
    del %localexpdpdir%\orcl_full_%aYesDate%.dmp
)

:DateToDays %yy% %mm% %dd% days
setlocal ENABLEEXTENSIONS
set yy=%1&set mm=%2&set dd=%3
if 1%yy% LSS 200 if 1%yy% LSS 170 (set yy=20%yy%) else (set yy=19%yy%)
set /a dd=100%dd%%%100,mm=100%mm%%%100
set /a z=14-mm,z/=12,y=yy+4800-z,m=mm+12*z-3,j=153*m+2
set /a j=j/5+dd+y*365+y/4-y/100+y/400-2472633
endlocal&set %4=%j%&goto :EOF
 
:DaysToDate %days% yy mm dd
setlocal ENABLEEXTENSIONS
set /a a=%1+2472632,b=4*a+3,b/=146097,c=-b*146097,c/=4,c+=a
set /a d=4*c+3,d/=1461,e=-1461*d,e/=4,e+=c,m=5*e+2,m/=153,dd=153*m+2,dd/=5
set /a dd=-dd+e+1,mm=-m/10,mm*=12,mm+=m+3,yy=b*100+d-4800+m/10
(if %mm% LSS 10 set mm=0%mm%)&(if %dd% LSS 10 set dd=0%dd%)
endlocal&set %2=%yy%&set %3=%mm%&set %4=%dd%&goto :EOF
正在加载中……