1. 初台化参数设置(源/目标):
a) global_names和job_queue_processes:
SQL> alter system setglobal_names=true;
System altered.
SQL> alter system setjob_queue_processes=20;
System altered.
b) global_name设置为带后缀的名称:
源站点:
SQL> alterdatabase rename global_name to ORCL1.COM;
Databasealtered.
目标站点:
SQL> alterdatabase rename global_name to ORCL2.COM;
Databasealtered.
c) 配置tnsnames(源/目标):
ORCL132 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =rhel132)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL131 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =rhel131)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
d) 创建用户(源/目标):
创建管理用户
SQL> createuser repadmin identified by repadmin default tablespace users temporarytablespace temp;
SQL> executedbms_defer_sys.register_propagator('repadmin');
SQL> grantexecute any procedure to repadmin;
SQL> executedbms_repcat_admin.grant_admin_any_repgroup('repadmin');
SQL> executedbms_repcat_admin.grant_admin_any_schema(username =>'"REPADMIN"');
SQL> grantselect any dictionary to repadmin;
SQL> grantcomment any table to repadmin;
SQL> grantlock any table to repadmin;
创建使用用户
SQL> createuser gp identified by gp;
SQL> grantdba to gp;
SQL> conngp/gp
SQL> createtable t(x int primary key);
e) 配置dblink(源/目标)
源库:
SQL> connrepadmin/repadmin
SQL> create database link ORCL2.COMconnect to REPADMIN identified by REPADMIN using 'ORCL132';
目标库:
SQL> connrepadmin/repadmin
SQL> create database link ORCL1.COM connect toREPADMIN identified by REPADMIN using 'ORCL131';
2. 创建复制组(源)
SQL> executedbms_repcat.create_master_repgroup('rep');
3. 加入复制对象(源)
SQL>executedbms_repcat.create_master_repobject(sname=>'gp',oname=>'t',type=>'table',use_existing_object=>true,gname=>'rep',copy_rows=>false);
SQL> selectsname,oname,status,gname from dba_repobject where gname='REP';
SNAME ONAME STATUS GNAME
---------------------------------------- ---------- --------------------
GP T VALID REP
4. 对复制对象启动复制支持(源)
SQL>execute dbms_repcat.generate_replication_support('gp','t','table');
SQL> selectsname,oname,status,gname from dba_repobject where gname='REP';
SNAME ONAME STATUS GNAME
---------------------------------------- ---------- --------------------
GP T VALID REP
GP T$RP VALID REP
GP T$RP VALID REP
5. 添加复制节点(源)
SQL> executedbms_repcat.add_master_database(gname=>'rep',master=>'orcl2.com',use_existing_objects=>true,copy_rows=>false,propagation_mode=>'synchronous');
SQL> columnmasterdef format a10
SQL> columnmaster format a10
SQL> columndblink format a25
SQL> columngname format a12
SQL> selectgname,dblink,masterdef,master from sys.dba_repsites where gname='REP';
GNAME DBLINK MASTERDEF MASTER
------------------------------------- ---------- ----------
REP ORCL1.COM Y Y
REP ORCL2.COM N Y
6. 启动复制(源)
SQL>execute dbms_repcat.resume_master_activity('rep',true);
7. 测试一下:
SQL> insertinto gp.t values(1);
1 row created.
SQL> commit;
Commitcomplete.
目标库查询一下:
SQL> conngp/gp
Connected.
SQL> select* from t;
X
----------
1