关于streams replication的原理本文就不做介绍了,相关文章可以在OTN和metalink上找到。本文主要介绍通过streams replication和transparent gateway实现
Oracle向SQL Server的数据同步的配置过程。主要参考了metalink上的文章,并在期间开了4个SR解决发现的问题(并发现新bug一个)。
一、测试环境:
Oracle DB Server: 9205, SID: ORA92, Host: ORATEST(192.168.111.22), schema: orarep
SQL Server: 2000, DB: pubs, Host: SQLTEST,(192.168.111.11) schema: sqlrep
Oracle 透明网关:10gR2
二、透明网关的配置
将Oracle transparent gateway 10gR2 for MS SQL Server安装装在SQL Server服务器上。
1. 配置服务器Yangcl上的$ORACLE_HOME etworkadmin下的listener.ora如下:
# listener.ora Network Configuration File: C:oracleproduct10.2.0 g_1
etworkadminlistener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:oracleproduct10.2.0 g_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = mssql)
(ORACLE_HOME = C:oracleproduct10.2.0 g_1)
(PROGRAM = tg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sqltest)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
其中黑体部分尤为重要。SID_NAME自己取一个,但要和后面保持一致!
2. 配置服务器Yangcl上的$ORACLE_HOME g4msqladmin下的init.ora如下:
HS_FDS_CONNECT_INFO=SQLTEST.pubs
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
其中,HS_FDS_CONNECT_INFO的格式为.
3. 配置服务器Huanged上的$ORACLE_HOME etworkadmin下的tnsnames.ora,添加如下条目:
MSSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.11)(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = mssql)
)
(HS = OK)
)
Host为MS SQL Server的host名字,SID和上面保持一致。
4. 在服务器Yangcl上启动listener:
Lsnrctl start
5. 在Oracle上创建到SQL Server的DB Link
conn orarep/orarep
create database link mssql connect to sqlrep identified by sqlrep using ‘mssql';
6. 测试连接
Select * from employee@mssql;
有结果则表示网关连接正确,否则根据错误提示调整设置。
三、Steams Replication的配置
1. 确保Oracle运行在归档模式下
Conn / as sysdba
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 192
Next log sequence to archive 194
Current log sequence 194
--否则修改为归档模式
1)SHUTDOWN IMMEDIATE;
2)STARTUP MOUNT;
3)ALTER DATABASE ARCHIVELOG;
4)ALTER DATABASE OPEN;
2. 在Oracle上创建测试表
conn orarep/orarep
create table rep_test1 (a number primary key, b char(20));
create table rep_test2 (a number(9,2) primary key, b varchar2(20), c date);
为表增加补充日志组:
ALTER TABLE rep_test1 ADD SUPPLEMENTAL LOG GROUP lg_test1_pk (a) ALWAYS;
ALTER TABLE rep_test2 ADD SUPPLEMENTAL LOG GROUP lg_test2_pk (a) ALWAYS;
3. 在SQL Server上创建测试表
注意:SQL Server对应的数据库上创建与Oracle上用户名相同的用户,并且一定要大写,与login id对应。创建表和字段也一定要大写!
Use pubs
create table REP_TEST1 (A int primary key not null, B char(20))
create table REP_TEST2 (A DECIMAL(9,2) primary key not null, B varchar(20), C datetime)
go
创建完毕后,要检查表和owner:
在oracle上执行
SQL> select owner, table_name, column_name from all_tab_columns@mssql where owner=’ORAREP’;
OWNER TABLE_NAME COLUMN_NAME
--------------- ------------------ -----------------
ORAREP ORA_TEST1 A
ORAREP ORA_TEST1 B
ORAREP ORA_TEST2 A
ORAREP ORA_TEST2 B
ORAREP ORA_TEST2 C
结果必须全部为大写!
附:SQL Server与Oracle的数据类型对应表
--也可以直接在Oracle上通过以下方式创建:
declare
nr binary_integer;
begin
nr := dbms_hs_passthrough.execute_immediate@mssql
('create table REP_TEST1 (A int primary key not null, B char(20))');
nr := dbms_hs_passthrough.execute_immediate@mssql
(' create table REP_TEST2 (A DECIMAL(9,2) primary key not null, B varchar(20), c datatime)');
end;
/
4. 在Oracle上创建streams管理帐号
Connect / as sysdba
grant connect, resource, dba, select_catalog_role
to STRMADMIN identified by STRMADMIN;
--赋予相关包的权限:
grant execute on DBMS_APPLY_ADM to STRMADMIN;
grant execute on DBMS_AQADM to STRMADMIN;
grant execute on DBMS_CAPTURE_ADM to STRMADMIN;
grant execute on DBMS_FLASHBACK to STRMADMIN;
grant execute on DBMS_PROPAGATION_ADM to STRMADMIN;
grant execute on DBMS_STREAMS_ADM to STRMADMIN;
--赋予相关系统权限:
begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privile
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9650775/viewspace-920350/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9650775/viewspace-920350/