Thursday, October 1, 2009

Create Oracle Database Link access SQL Server

Just finished a project: customer A use Oracle Database 10G, customer B user SQL Server 2005.
Need read SQL Server from Oracle Database.

Here is how:

Request: Microsoft Windows 32 bit OS
Memory Great than 2 Giga-Byte

1. First Download oracle for windows from otn.oracle.com

http://www.oracle.com/technology/software/products/database/index.html
Oracle Database 10g Release 1 (10.1.0.1.0) for Microsoft Windows

2. Download Oracle Database 10g Release 1 patch 10.1.0.5 for Microsoft Windows
3. Uncompress files
4. Start Installation
5. “Oracle Transparent Gateway for Microsoft SQL Server” has to bee install

(Manual attached: b14270.pdf)

6. Create a Database for convert (SID name = ora)
7. Notice the “Database Character Set” to
8. Create a Listener for connection
9. Setup MS SQL Server (Host Name and Database Name)
10. create a user in SQL Server for Oracle connection (User Name: cyx Password:testora)
11. Create a user in Oracle Server for operation (User Name: cyx Password: cyx)
12. Setup 4 configure files:

tnsnames.ora /listener.ora /inittg4msql.ora /tg4msql_tx.sql
(Files attached)

13. Create Database Link in Oracle:

create database link msql connect to "cyx" identified by "testora" using 'tg4msql';

14. Create Mirror Table in Oracle:

create table t2008_test
as
select *
from t2008_geo@msql;

15. Export Mirror Talbe by using command “exp”:

set ORACLE_SID=ora
exp cyx/cyx file=test.dmp table=t2008_test

PS:For save some memory and 3 services has been set to Manual:
OracleCSService /OracleORACLE_HOMETNSListener /OracleServiceORA
Please start these 3 services before your operation.


For More info and screen shoot please left commit and let me know.

2 comments:

  1. Hello, I enjoy reading all of your post. I wanted
    to write a little comment to support you.

    ReplyDelete
  2. Thank you my friend.

    ReplyDelete