Oracle 高级复制
三台数据库的global_name如下:Orcl16.com 192.168.100.16 (主数据库,即数据源)Orcl8.com 192.168.100.8 (复制数据库)Dtgdb.com 192.168.100.150(复制数据库)三台数据库初始化参数的设置:Global_names = trueJob_queue_process 的值要大于0Open_links = 4 或更大 视连接数来定要求:三个数据库的scott模式表,索引的数据和结构都必须相同应用程序用户:scott可用以下命令在每个站点上面修改工作组ALTER DATABASE RENAME global_name TO orcl16.Com; 建立主站点:orcl16.comCONNECT SYSTEM/xl1100@100.16 ------用system用户链接100.16CREATE USER repadmin IDENTIFIED BY repadmin; ------建立复制管理员repadmin账号BEGIN ------分配权限给repadmin用户 DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ( username => 'repadmin');END;/GRANT DBA TO repadminGRANT COMMENT ANY TABLE TO repadmin;GRANT LOCK ANY TABLE TO repadmin;GRANT SELECT ANY DICTIONARY TO repadmin;BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR ( username => 'repadmin');END;/BEGIN DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP ( username => 'repadmin', privilege_type => 'receiver', list_of_gnames => NULL);END;/CONNECT repadmin/repadmin@100.16 ---------用repadmin 用户登录100.16BEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE ( next_date => SYSDATE, interval => 'SYSDATE + 1/24', ----------添加PURGE 的任务SYSDATE + 1/24 间隔为一个小时 delay_seconds => 0);END;/设置orcl8.comCONNECT SYSTEM/xl1100@100.8CREATE USER repadmin IDENTIFIED BY repadmin;BEGIN DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ( username => 'repadmin');END;/GRANT COMMENT ANY TABLE TO repadmin;GRANT LOCK ANY TABLE TO repadmin;GRANT SELECT ANY DICTIONARY TO repadmin;BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR ( username => 'repadmin');END;/BEGIN DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP ( username => 'repadmin', privilege_type => 'receiver', list_of_gnames => NULL);END;/CONNECT repadmin/repadmin@100.8BEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE ( next_date => SYSDATE, interval => 'SYSDATE + 1/24', delay_seconds => 0);END;/设置dtgdbCONNECT SYSTEM/xl1100@100.150CREATE USER repadmin IDENTIFIED BY repadmin;BEGIN DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ( username => 'repadmin');END;/GRANT DBA TO repadminGRANT COMMENT ANY TABLE TO repadmin;GRANT LOCK ANY TABLE TO repadmin;GRANT SELECT ANY DICTIONARY TO repadmin;BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR ( username => 'repadmin');END;/BEGIN DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP ( username => 'repadmin', privilege_type => 'receiver', list_of_gnames => NULL);END;/CONNECT repadmin/repadmin@100.150BEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE ( next_date => SYSDATE, interval => 'SYSDATE + 1/24', delay_seconds => 0);END;/建立数据库链路(可用select * from global_name@orcl8.com检查数据链路是否通)100.16:CONNECT SYSTEM/xl1100@100.16CREATE PUBLIC DATABASE LINK orcl8.com USING 'orcl8'; ----------后面的orcl8为在TNS文件中配置的名字CREATE PUBLIC DATABASE LINK dtgdb.com USING 'dtgdb';CONNECT repadmin/repadmin@100.16CREATE DATABASE LINK orcl8.com CONNECT TO repadmin IDENTIFIED BY repadmin;CREATE DATABASE LINK dtgdb.com CONNECT TO repadmin IDENTIFIED BY repadmin;100.8:CONNECT SYSTEM/xl1100@100.8CREATE PUBLIC DATABASE LINK orcl16.com USING 'orcl16';CREATE PUBLIC DATABASE LINK dtgdb.com USING 'dtgdb';CONNECT repadmin/repadmin@100.8CREATE DATABASE LINK orcl16.com CONNECT TO repadmin IDENTIFIED BY repadmin;CREATE DATABASE LINK dtgdb.com CONNECT TO repadmin IDENTIFIED BY repadmin;100.150:CONNECT SYSTEM/xl1100@100.150CREATE PUBLIC DATABASE LINK orcl16.com USING 'orcl16';CREATE PUBLIC DATABASE LINK orcl8.com USING 'orcl8';CONNECT repadmin/repadmin@100.150CREATE DATABASE LINK orcl16.com CONNECT TO repadmin IDENTIFIED BY repadmin;CREATE DATABASE LINK orcl8.com CONNECT TO repadmin IDENTIFIED BY repadmin;建立jobs （定时执行PUSH任务）CONNECT repadmin/repadmin@100.16BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH ( destination => 'orcl8.com', interval => 'SYSDATE + (1/144)', next_date => SYSDATE, parallelism => 1, execution_seconds => 1500, delay_seconds => 1200);END;/BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH ( destination => 'dtgdb.com', interval => 'SYSDATE + (1/144)', next_date => SYSDATE, parallelism => 1, execution_seconds => 1500, delay_seconds => 1200);END;/CONNECT repadmin/repadmin@100.8BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH ( destination => 'orcl16.com', interval => 'SYSDATE + (1/144)', next_date => SYSDATE, parallelism => 1, execution_seconds => 1500, delay_seconds => 1200);END;/BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH ( destination => 'dtgdb.com', interval => 'SYSDATE + (1/144)', next_date => SYSDATE, parallelism => 1, execution_seconds => 1500, delay_seconds => 1200);END;/CONNECT repadmin/repadmin@100.150BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH ( destination => 'orcl16.com', interval => 'SYSDATE + (1/144)', next_date => SYSDATE, parallelism => 1, execution_seconds => 1500, delay_seconds => 1200);END;/BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH ( destination => 'orcl8.com', interval => 'SYSDATE + (1/144)', next_date => SYSDATE, parallelism => 1, execution_seconds => 1500, delay_seconds => 1200);END;/建立主工作组CONNECT repadmin/repadmin@100.16BEGIN DBMS_REPCAT.CREATE_MASTER_REPGROUP ( gname => 'mygrp'); ----------创建名为mygrp的组名END;/BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => ' mygrp ', ----------组名 type => 'TABLE', ----------类型(此处类型为表) oname => 'emp', ----------表（为sname用户下的要参加复制的表） sname => 'scott', ----------用户（参加复制的用户） use_existing_object => TRUE, copy_rows => FALSE);END;/ ----------有多少个需要参加复制的表就要写多少条以上的语句BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => 'mygrp', type => 'TABLE', oname => 'dept', sname => 'scott', use_existing_object => TRUE, copy_rows => FALSE);END;/BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => 'mygrp', type => 'TABLE', oname => 'a', sname => 'scott', use_existing_object => TRUE, copy_rows => FALSE);END;/BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => 'mygrp', type => 'TABLE', oname => 'bonus', sname => 'scott', use_existing_object => TRUE, copy_rows => FALSE);END;/BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => 'mygrp', type => 'TABLE', oname => 'salgrade', sname => 'scott', use_existing_object => TRUE, copy_rows => FALSE);END;/添加复制站点BEGIN DBMS_REPCAT.ADD_MASTER_DATABASE ( gname => 'mygrp', master => 'orcl8.com', use_existing_objects => TRUE, copy_rows => FALSE, propagation_mode => 'ASYNCHRONOUS');END;/BEGIN DBMS_REPCAT.ADD_MASTER_DATABASE ( gname => 'mygrp', master => 'dtgdb.com', use_existing_objects => TRUE, copy_rows => FALSE, propagation_mode => 'ASYNCHRONOUS');END;/启用复制支持BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'scott', --------------用户名 oname => 'emp', --------------参与复制的表名 type => 'TABLE', --------------类型 min_communication => TRUE); END;/------------以上内容有多少参与复制的表就要写多少条BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'scott', oname => 'dept', type => 'TABLE', min_communication => TRUE); END;/BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'scott', oname => 'a', type => 'TABLE', min_communication => TRUE); END;/BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'scott', oname => 'bonus ', type => 'TABLE', min_communication => TRUE); END;/BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'scott', oname => ' salgrade ', type => 'TABLE', min_communication => TRUE); END;/―――以上执行完后 可以通过select * from dbs_repcatlog;查看同步情况DBA_REPCATLOG 视图中管理请求的几种状态：1) READY：表示请求准备被执行。如果长时间处于该状态，可以手动执行DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN 存储过程来执行请求。2) AWAIT_CALLBACK：这种状态只会出现在主体定义站点上，表示正在等待其它的主站点执行请求并且返回结果。3) ERROR：表示请求执行错误4) DO_CALLBACK：这种状态只会出现在非主体定义站点上，表示要通知主体定义站点请求执行的结果。以下脚本用于解决由于网络问题造成jobs失败次数超过16次状态变为broken后JOBS失效的问题(每个站点都要运行)create or replace procedure change_job_broken isBEGINdeclare CURSOR my_broken_jobs is ------建立一个游标 SELECT job FROM user_jobs WHERE broken = 'Y'; ------检测user_jobs的broken状态是否有为"Y"的 begin FOR broken_job IN my_broken_jobs LOOP BEGIN dbms_job.broken(broken_job.job, FALSE); -------如果broken状态为"Y"则通过此行将其改为"N" EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; END;end;/然后可以在制作一个JOBS让以上脚本每天运行一次variable n number;begindbms_job.submit(:n,'change_job_broken;',sysdate,'sysdate+1');commit;dbms_job.run(:n);commit;end; /开始复制BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'mygrp'); END;/停止复制SQL>EXECUTE Dbms_Repcat. SUSPEND_MASTER_ACTIVITY (gname => 'mygrp');所有工作完成后 启动复制 然后在各个站点上面 用select * from dba_repgroup;检查站点状态 状态为NORMAL时表示该站点正常。相关视图" DBA_REPSITES ----复制站点情况" DBA_REPGROUP -----复制组情况" DBA_REPOBJECT ----复制对象情况" DBA_REPCATLOG ---同步情况日志删除复制在主站点上面执行CONNECT repadmin/repadmin@100.16-- 停掉高级复制EXECUTE Dbms_Repcat.Suspend_Master_Activity(gname=>'MYGRP');-- 删除复制组EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'EMP', 'TABLE');EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'a', 'TABLE');EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'dept', 'TABLE');EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'bonus', 'TABLE');EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'salgrade', 'TABLE'); EXECUTE Dbms_Repcat.Remove_Master_Databases('MYGRP', 'orcl8.com')；----删除主站点上面的组EXECUTE Dbms_Repcat.Remove_Master_Databases('MYGRP', 'dtgdb.com');在所有要删除的站点上面执行--删除私有数据链路CONNECT repadmin/repadmin@100.16;EXECUTE Dbms_Repcat.Drop_Master_Repgroup('MYGRP');DROP DATABASE LINK orcl8.com;DROP DATABASE LINK dtgdb.com;CONNECT repadmin/repadmin@100.8;EXECUTE Dbms_Repcat.Drop_Master_Repgroup('MYGRP');DROP DATABASE LINK orcl16.com;DROP DATABASE LINK dtgdb.com;CONNECT repadmin/repadmin@100.150;EXECUTE Dbms_Repcat.Drop_Master_Repgroup('MYGRP');DROP DATABASE LINK orcl16.com;DROP DATABASE LINK orcl8.com;----删除JOBSselect job,what from user_jobs; ----------查询job numbersEXECUTE Dbms_Job.Remove(2); ------括号内填写刚查询到的job numbersEXECUTE Dbms_Job.Remove(3);-- 删除REPADMIN用户，注意：必须先删除站点下隶属于该repadmin用户的jobs和组mygrp 才能删除用户（就是必须先执行上面的删除命令才能用下面的命令）connect system/xl1100@100.16EXECUTE Dbms_Defer_Sys.Unregister_Propagator(username=>'REPADMIN');EXECUTE Dbms_Repcat_Admin.Revoke_Admin_Any_Schema(username=>'REPADMIN');DROP USER repadmin CASCADE;connect system/xl1100@100.8connect system/xl1100@100.150EXECUTE Dbms_Defer_Sys.Unregister_Propagator(username=>'REPADMIN');EXECUTE Dbms_Repcat_Admin.Revoke_Admin_Any_Schema(username=>'REPADMIN');DROP USER repadmin CASCADE;--删除公用的数据链路connect system/xl1100@100.16DROP PUBLIC DATABASE LINK orcl8.com;DROP PUBLIC DATABASE LINK dtgdb.com;connect system/xl1100@100.8DROP PUBLIC DATABASE LINK orcl16.com;DROP PUBLIC DATABASE LINK dtgdb.com;connect system/xl1100@100.150DROP PUBLIC DATABASE LINK orcl16.com;DROP PUBLIC DATABASE LINK orcl8.com;备注：每次运行完repcat 包以后都应该执行一次commit，因为某些rep 的存储过程是不会自动commit 的，同时这也是一个troubleshooting，一般的rep 脚本都会较快的返回结果，如果一条命令之后长时间没有结果返回，那么很可能是上面的命令没有commit，取消掉当前的命令，然后作一次commit，再重新执行，一般都能够解决问题。强制删除复制组Sql>Truncate table system.def$_aqcall;Sql>Exec dbms_repcat.drop_master_repgroup(gname=>'mygrp',all_sites=>true);关于高级复制的相关内容还很多，比如高级复制中冲突的解决方法等等，以后可以一起讨论.posted on 2007-10-23 14:01 mjgforever 阅读(1578) 评论(0) 收藏 举报
浙公网安备 33010602011771号