2011年8月17日 星期三

Oracle 10G 覆寫至 SQL SERVER 2008(一)(Streams Replication )

好久沒發新文章了~~最近在研究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 gateway10gR2

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.ora如下:

如果沒有此檔案可以自己創立一個~~

#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)

)

HostMS SQL Serverhost名字,SID和上面保持一致。

4. SQL SERVER 上啟動listener

Lsnrctl start

5. Oracle上創建到SQL ServerDB 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 ServerOracle的數據類型對應表

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 ServerDB 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;

/

沒有留言:

張貼留言