好久沒發新文章了~~最近在研究ORACLE 與 SQL SERVER 之間的Replication!!
今天來講Oracle 10G 覆寫至 SQL SERVER 2008這一部份!!!
大概步驟是:
1.SQL SERVER 機器上安裝Oracle transparent gateway
目的是要讓Oracle 與 SQL SERVER 溝通
2.Oracle 機器上設定Steams Replication
目的是要把Oracle 的異動覆寫至 SQL SERVER上
一、測試環境：
Oracle DB Server: 10.2.0.1.0 , SID: ORCL, Host:oracle_10g(172.16.19.61), schema: MEMBER
SQL Server: 2003, DB: MEMBER, Host: SQLTEST,(172.16.19.60) schema: Oracle_rep
Oracle transparent gateway：10gR2
SQL SERVER 及Oracle 機器上的Host File都先設定好
EXP:
172.16.19.60 mssql
172.16.19.61 Oracle_10g
二、Oracle transparent gateway的配置
安裝檔Oracle官方網站都有提供~可以免費下載
將Oracle transparent gateway 10gR2 for MS SQL Server安裝裝在SQL Server服務器上。
1. 配置SQL SERVER 上的$ORACLE_HOME\network\admin下的listener.ora如下：
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\tg_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\tg_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = mssql)
(ORACLE_HOME = C:\oracle\product\10.2.0\tg_1)
(PROGRAM = tg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.19.60)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
其中黑體部分尤為重要。SID_NAME自己取一個，但要和後面保持一致！
2. 配置SQL SERVER上的$ORACLE_HOME\tg4msql\admin下的init
如果沒有此檔案可以自己創立一個~~
#HS_FDS_CONNECT_INFO=MSSQL.Oracle_rep
HS_FDS_CONNECT_INFO="SERVER=172.16.19.60;DATABASE=Oracle_rep"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_INFO的格式為
HS_FDS_CONNECT_INFO可以打IP或是你的機器名稱~~但是格式有點不一樣!
我這邊是用IP!!
3. 配置Oracle上的$ORACLE_HOME\network\admin下的tnsnames.ora，添加如下條目：
mssql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =mssql)(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = mssql)
)
(HS = OK)
)
Host為MS SQL Server的host名字，SID和上面保持一致。
4. 在SQL SERVER 上啟動listener：
Lsnrctl start
5. 在Oracle上創建到SQL Server的DB Link
conn member/member
create database link mssql connect to member identified by "member" using 'mssql';
6. 測試連接
select sysdate from dual@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 member/member
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上用戶名相同的用戶(MEMBER)，並且一定要大寫，與login id對應。創建表和字段也一定要大寫！
Use Oracle_rep
create table MEMBER.REP_TEST1 (A int primary key not null, B char(20))
create table MEMBER.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='MEMBER';
OWNER TABLE_NAME COLUMN_NAME
--------------- ------------------ -----------------
MEMBER ORA_TEST1 A
MEMBER ORA_TEST1 B
MEMBER ORA_TEST2 A
MEMBER ORA_TEST2 B
MEMBER ORA_TEST2 C
結果必須全部為大寫！
附：SQL Server與Oracle的數據類型對應表
| Microsoft Sql Server | Oracle | Comment |
| BINARY | RAW | - |
| BIT | NUMBER(3) | - |
| CHAR | CHAR | - |
| DATETIME | DATE | Fractional parts of a second are truncated |
| DECIMAL | NUMBER(p[,s]) | - |
| FLOAT | FLOAT(49) | - |
| IMAGE | LONG RAW | - |
| INTEGER | NUMBER(10) | NUMBER range is -2,147,483,647 to 2,147,483,647 |
| MONEY | NUMBER(19,4) | - |
| NCHAR | CHAR | - |
| NTEXT | LONG | - |
| NVARCHAR | VARCHAR2 | - |
| NUMERIC | NUMBER(p[,s]) | - |
| REAL | FLOAT(23) | - |
| SMALL DATETIME | DATE | The value for seconds is returned as 0 |
| SMALL MONEY | NUMBER(10,4) | - |
| SMALLINT | NUMBER(5) | NUMBER range is -32,767 to 32,767 |
| TEXT | LONG | - |
| TIMESTAMP | RAW | - |
| TINYINT | NUMBER(3) | - |
| VARBINARY | RAW | - |
| VARCHAR | VARCHAR2 | - |
--也可以直接在Oracle上通過以下方式創建：
declare
nr binary_integer;
begin
nr := dbms_hs_passthrough.execute_immediate@mssql
('create table MEMBER.REP_TEST1 (A int primary key not null, B char(20))');
nr := dbms_hs_passthrough.execute_immediate@mssql
(' create table MEMBER.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(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'strmadmin',
grant_option => FALSE);
end;
/
begin
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'strmadmin',
grant_option => FALSE);
end;
/
--創建到SQL Server的DB Link：
Connect STRMADMIN/STRMADMIN
create database link mssql
connect to sqlrep identified by sqlrep
using 'mssql';
--測試連接是否可用：
select * from rep_test1@mssql;
--確定SQL Server的表上的主鍵是否可用：
select owner, constraint_name, constraint_type from all_constraints@mssql where table_name in ('REP_TEST1','REP_TEST2');
5. 創建streams隊列、捕捉和表規則
Connect STRMADMIN/STRMADMIN
--創建streams隊列（隊列的默認名字為STREAMS_QUEUE）：
/* Create queue: following creates and starts the streams queue. */
/* Default queue_name is 『STREAMS_QUEUE』 */
execute DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_name => 'queue_for_reptest');
--在Oracle上創建捕捉和表規則
Connect STRMADMIN/STRMADMIN
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES
(
Table_name=>'MEMBER.REP_TEST1',
streams_type=>'capture',
streams_name=>'REPTEST_CAPTURE',
queue_name=> 'strmadmin.queue_for_reptest'
);
end;
/
begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES
(
Table_name=>'MEMBER.REP_TEST2',
streams_type=>'capture',
streams_name=>'REPTEST_CAPTURE',
queue_name=> 'strmadmin.queue_for_reptest'
);
end;
/
