oracle 高级复制手记

最新推荐文章于 2024-09-14 19:55:29 发布

ciqi9447

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量78

点赞数

文章标签：数据库网络

查看参数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);