shareplex的部署安装已经在上一贴已做说明,详细请参考:[原创]rhel+oracle11g+shareplex+exp/imp部署测试(上)[原创],下面演示以exp/imp为初始化数据手段模拟shareplex不停机迁移数据,由于exp/imp是可以跨平台的,此方案可用于跨平台数据迁移,但只限于oracle数据库之间哦~~
1.源端和目标端清除配置
ora_clean system/oracle
2.源端和目标端配置shareplex
ora_setup
3.源端目标端启动shareplex
sp_cop &
4.目标端停止post队列
sp_ctrl (shareplex02:2100)> show
Process Source Target State PID
---------- ------------------------------------ ------------ -------------------- ------
sp_ctrl (shareplex02:2100)> stop post
5.编辑规则集
sp_ctrl (shareplex01:2100)> copy config ORA_config to ora_exp
sp_ctrl (shareplex01:2100)> list config
File Name State Datasource
-------------------------------------------------- ---------- ---------------
ora_con Inactive o.orcl
Last Modified At: 27-Oct-14 18:49 Size: 136
ORA_config Inactive o.SOURCE_SID
Last Modified At: 27-Oct-14 17:10 Size: 151
ora_exp Inactive o.SOURCE_SID
Last Modified At: 28-Oct-14 13:07 Size: 151
sp_ctrl (shareplex01:2100)>
编辑规则集文件ora_exp:
datasource:o.orcl
#source tables target tables routing map
expand scott.% scott.% shareplex02@o.orcl
expand hr.% hr.% shareplex02@o.orcl
6.生效规则集:
sp_ctrl (shareplex01:2100)> activate config ora_exp
sp_ctrl (shareplex01:2100)> show
Process Source Target State PID
---------- ------------------------------------ ------------ -------------------- ------
Capture o.orcl Running 6736
Read o.orcl Running 6741
Export shareplex01 shareplex02 Running 6762
sp_ctrl (shareplex01:2100)>
sp_ctrl (shareplex02:2100)> show
Process Source Target State PID
---------- ------------------------------------ ------------ -------------------- ------
Import shareplex01 shareplex02 Running 7172
Post o.orcl-shareplex01 o.orcl Stopped by user
sp_ctrl (shareplex02:2100)>
7.生产库查询当前的scn:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1386140
SQL>
8.源端生产库基于flashback_scn exp/expdp数据:
exp system/oracle compress=n owner=scott,hr file='/oracle/app/oracle/expimp/sp.dmp' \
log='/oracle/app/oracle/expimp/sp.log' flashback_scn=1386140
10.目标端导入数据:
imp system/oracle file='/oracle/app/oracle/expimp/sp.dmp' fromuser=scott,hr touser=scott,hr
11.目标端reconcile重复数据:
reconcile queue shareplex01 for o.orcl-o.orcl scn 1386140
queue名可以通过qstatus查询到,如果没有要删除的重复数据,则此进程处于挂起状态,是正常的,直接退出就可以了。
12.目标端disable trigger\constraints\job
先用以下脚本生成sql,然后执行:
select 'alter trigger '||owner||'.'||trigger_name||' disable;' from dba_triggers where owner in ('SCOTT','HR');
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' from dba_constraints where constraint_type in ('R') and owner in ('SCOTT','HR');
13.目标端启动post队列:
sp_ctrl (shareplex02:2100)> start post
sp_ctrl (shareplex02:2100)> show
Process Source Target State PID
---------- ------------------------------------ ------------ -------------------- ------
Import shareplex01 shareplex02 Running 7172
Post o.orcl-shareplex01 o.orcl Running 7779
14.测试验证:
如果源端的表在目标端不存在,开启post的时候会报错。并且报错之后post进程自动停止。这时候可在目标端创建空表,然后开启post进程,但有可能
数据不会完全同步过去,需要手动repair或者copy表。