查看参数global_names
show parameter global_names
select * from global_name;
alter system set global_names=true;
或alter system set global_names=true scope=both;
修改参数:
alter database rename global_name to sdga.com;
alter database rename global_name to wzk.com;
建立repadmin用户,并授予相应权限
create user repadmin identified by repadmin default tablespace users temporary tablespace temp;
execute dbms_defer_sys.register_propagator('repadmin');
grant execute any procedure to repadmin;
execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');
grant comment any table to repadmin;
grant lock any table to repadmin;
grant select any dictionary to repadmin;
在主体定义站点的repadmin用户下建立dblink
CREATE PUBLIC DATABASE LINK "sdga.com" connect to repadmin identified by repadmin
USING '(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS= (PROTOCOL = TCP)(Host = 192.168.0.5)(Port = 1521)))(CONNECT_DATA =
(SID = repli)(SERVER = DEDICATED)))';
create database link "sdga.com@repli" connect to repadmin identified by repadmin
USING '(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS= (PROTOCOL = TCP)(Host = 192.168.0.5)(Port = 1521)))(CONNECT_DATA =
(SID = repli)(SERVER = DEDICATED)))';
5. 在主体站点的repadmin用户下建立dblink
CREATE PUBLIC DATABASE LINK "wzk.com" connect to repadmin identified by repadmin
USING '(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS= (PROTOCOL = TCP)(Host = 192.168.11.25)(Port = 1521)))(CONNECT_DATA =
(SID = main)(SERVER = DEDICATED)))';
create database link "wzk.com@wzk" connect to repadmin identified by repadmin
USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS= (PROTOCOL = TCP)(Host =
192.168.11.25)(Port = 1521)))(CONNECT_DATA = (SID = main)(SERVER = DEDICATED)))';
验证:
SQL> select * from global_name@sdga.com;
SQL> select * from global_name@wzk.com;
6. 在主体定义站点:
在repadmin用户下:
execute dbms_repcat.create_master_repgroup('repbbb'); --创建组
execute dbms_repcat.create_master_repobject(sname=>'scott',oname=>'dept',type=>'table',use_existing_object=>true,gname=>'repbbb',copy_rows=>false); --将scott用户下的dept表加入到组里面,如果有多个表,都按照这个格式来做
execute dbms_repcat.generate_replication_support('scott','dept','table'); -- 加入的每一个表也要这么做
SQL> select gname, master, status from dba_repgroup where gname='repbbb';
GNAME M STATUS
------------------------------ - ---------
REP Y QUIESCED
SQL> select sname,oname,status,gname from dba_repobject where gname='repbbb';
SNAME ONAME STATUS
------------------------------ ------------------------------ ----------
GNAME
------------------------------
SCOTT dept VALID
REP
SCOTT dept$RP VALID
REP
SCOTT dept$RP VALID
REP
7. 在主体站点:
execute dbms_repcat.add_master_database(gname=>'repbbb',master=>'sdga.com',use_existing_objects=>true, copy_rows=>false, propagation_mode => 'synchronous');
column masterdef format a10
column master format a10
column dblink format a25
column gname format a12
select gname, dblink, masterdef MASTERDEF, master MASTER from sys.dba_repsites where gname='repbbb';
select sname,oname,status,gname from dba_repobject where gname='repbbb';
8 . 修改repbbb的状态为可用
execute dbms_repcat.resume_master_activity('repbbb',true);
这个同步复制就建立完毕了,可以使用了
如果没建立好,可用下面的删除相关的
9. 删除repbbb组
exec dbms_repcat.drop_master_repgroup( gname =>'repbbb',drop_contents =>false,all_sites =>true);
10. 删除主体站点
exec dbms_repcat.remove_master_databases(gname =>'repbbb' ,master_list =>'sdga.com');
10. 11. 将repbbb组改为暂停使用,一方便添加别的表进入这个组
EXECUTE dbms_repcat.suspend_master_activity(gname=>'repbbb');