Oracle 高级复制
三台数据库的global_name如下:
Orcl16.com 192.168.100.16 (主数据库,即数据源)
Orcl8.com 192.168.100.8 (复制数据库)
Dtgdb.com 192.168.100.150(复制数据库)
三台数据库初始化参数的设置:
Global_names = true
Job_queue_process 的值要大于0
Open_links = 4 或更大 视连接数来定
要求:三个数据库的scott模式表,索引的数据和结构都必须相同
应用程序用户:scott
可用以下命令在每个站点上面修改工作组
ALTER DATABASE RENAME global_name TO orcl16.Com;
建立主站点:orcl16.com
CONNECT SYSTEM/xl1100@100.16 ------用system用户链接100.16
CREATE USER repadmin IDENTIFIED BY repadmin; ------建立复制管理员repadmin账号
BEGIN ------分配权限给repadmin用户
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;
/
GRANT DBA TO repadmin
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.16 ---------用repadmin 用户登录100.16
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24', ----------添加PURGE 的任务SYSDATE + 1/24 间隔为一个小时
delay_seconds => 0);
END;
/
设置orcl8.com
CONNECT SYSTEM/xl1100@100.8
CREATE 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.8
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;
/
设置dtgdb
CONNECT SYSTEM/xl1100@100.150
CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;
/
GRANT DBA TO repadmin
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.150
BEGIN
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.16
CREATE PUBLIC DATABASE LINK orcl8.com USING 'orcl8'; ----------后面的orcl8为在TNS文件中配置的名字
CREATE PUBLIC DATABASE LINK dtgdb.com USING 'dtgdb';
CONNECT repadmin/repadmin@100.16
CREATE 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.8
CREATE PUBLIC DATABASE LINK orcl16.com USING 'orcl16';
CREATE PUBLIC DATABASE LINK dtgdb.com USING 'dtgdb';
CONNECT repadmin/repadmin@100.8
CREATE 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.150
CREATE PUBLIC DATABASE LINK orcl16.com USING 'orcl16';
CREATE PUBLIC DATABASE LINK orcl8.com USING 'orcl8';
CONNECT repadmin/repadmin@100.150
CREATE 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.16
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;
/
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.8
BEGIN
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.150
BEGIN
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.16
BEGIN
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 is
BEGIN
declare
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;
begin
dbms_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;
----删除JOBS
select job,what from user_jobs; ----------查询job numbers
EXECUTE Dbms_Job.Remove(2); ------括号内填写刚查询到的job numbers
EXECUTE Dbms_Job.Remove(3);
-- 删除REPADMIN用户,注意:必须先删除站点下隶属于该repadmin用户的jobs和组mygrp 才能删除用户(就是必须先执行上面的删除命令才能用下面的命令)
connect system/xl1100@100.16
EXECUTE 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.8
connect system/xl1100@100.150
EXECUTE 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.16
DROP PUBLIC DATABASE LINK orcl8.com;
DROP PUBLIC DATABASE LINK dtgdb.com;
connect system/xl1100@100.8
DROP PUBLIC DATABASE LINK orcl16.com;
DROP PUBLIC DATABASE LINK dtgdb.com;
connect system/xl1100@100.150
DROP 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);
关于高级复制的相关内容还很多,比如高级复制中冲突的解决方法等等,以后可以一起讨论.
Orcl16.com 192.168.100.16 (主数据库,即数据源)
Orcl8.com 192.168.100.8 (复制数据库)
Dtgdb.com 192.168.100.150(复制数据库)
三台数据库初始化参数的设置:
Global_names = true
Job_queue_process 的值要大于0
Open_links = 4 或更大 视连接数来定
要求:三个数据库的scott模式表,索引的数据和结构都必须相同
应用程序用户:scott
可用以下命令在每个站点上面修改工作组
ALTER DATABASE RENAME global_name TO orcl16.Com;
建立主站点:orcl16.com
CONNECT SYSTEM/xl1100@100.16 ------用system用户链接100.16
CREATE USER repadmin IDENTIFIED BY repadmin; ------建立复制管理员repadmin账号
BEGIN ------分配权限给repadmin用户
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;
/
GRANT DBA TO repadmin
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.16 ---------用repadmin 用户登录100.16
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24', ----------添加PURGE 的任务SYSDATE + 1/24 间隔为一个小时
delay_seconds => 0);
END;
/
设置orcl8.com
CONNECT SYSTEM/xl1100@100.8
CREATE 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.8
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;
/
设置dtgdb
CONNECT SYSTEM/xl1100@100.150
CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;
/
GRANT DBA TO repadmin
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.150
BEGIN
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.16
CREATE PUBLIC DATABASE LINK orcl8.com USING 'orcl8'; ----------后面的orcl8为在TNS文件中配置的名字
CREATE PUBLIC DATABASE LINK dtgdb.com USING 'dtgdb';
CONNECT repadmin/repadmin@100.16
CREATE 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.8
CREATE PUBLIC DATABASE LINK orcl16.com USING 'orcl16';
CREATE PUBLIC DATABASE LINK dtgdb.com USING 'dtgdb';
CONNECT repadmin/repadmin@100.8
CREATE 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.150
CREATE PUBLIC DATABASE LINK orcl16.com USING 'orcl16';
CREATE PUBLIC DATABASE LINK orcl8.com USING 'orcl8';
CONNECT repadmin/repadmin@100.150
CREATE 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.16
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;
/
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.8
BEGIN
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.150
BEGIN
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.16
BEGIN
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 is
BEGIN
declare
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;
begin
dbms_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;
----删除JOBS
select job,what from user_jobs; ----------查询job numbers
EXECUTE Dbms_Job.Remove(2); ------括号内填写刚查询到的job numbers
EXECUTE Dbms_Job.Remove(3);
-- 删除REPADMIN用户,注意:必须先删除站点下隶属于该repadmin用户的jobs和组mygrp 才能删除用户(就是必须先执行上面的删除命令才能用下面的命令)
connect system/xl1100@100.16
EXECUTE 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.8
connect system/xl1100@100.150
EXECUTE 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.16
DROP PUBLIC DATABASE LINK orcl8.com;
DROP PUBLIC DATABASE LINK dtgdb.com;
connect system/xl1100@100.8
DROP PUBLIC DATABASE LINK orcl16.com;
DROP PUBLIC DATABASE LINK dtgdb.com;
connect system/xl1100@100.150
DROP 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 阅读(1549) 评论(0) 编辑 收藏 举报