Data migration between Oracle and SQL Server (1)

For a long time, data migration or extraction between heterogeneous databases is required. If Oracle Data is extracted from SQL, You Can Use ODBC, OLEDB, and other methods to extract SQL data from Oracle, most of them are implemented through transparent gateways.

In the process of heterogeneous data extraction, it is best to use the SQL92 standard syntax to write SQL code, and pay attention to the Conversion Relationship between data types between different databases, for example, ORACLE uses DATE for DATE and SQL for Datetime.

1. ORACLE transparent gateway Configuration

When installing ORACLE 9i), select transparent gateway for mssql. In the ORACLE main directory \ bin, There Is A tg4msql.exe program. It is a transparent gateway program, and there is also a directory of tgrmsql in the main directory, inittg4msql In the ORACLE_HOME \ tg4msql \ admin directory. ora requires configuration to connect to SQL in ORACLE.

1. How to configure a transparent gateway? Open inittg4msql. ora:

# HS init parameters

Xzh indicates the SQL Service name, And pubs indicates the SQL database to be accessed.

HS_FDS_CONNECT_INFO="SERVER=xzh;DATABASE=pubs"HS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVER

2 configure oracle_home \ network \ admin \ Listiner. ora for the listener

LISTENER =　(DESCRIPTION_LIST =(DESCRIPTION =　(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1521))　)　(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1527))　))　)SID_LIST_LISTENER =　(SID_LIST =(SID_DESC =　(GLOBAL_DBNAME = xzh.world)　(ORACLE_HOME = D:\oracle\ora92)　(SID_NAME = xzh))(SID_DESC = 　 (GLOBAL_DBNAME = tg4msql) 　 (PROGRAM = tg4msql) 　 (SID_NAME = tg4msql)　 (ORACLE_HOME = D:\oracle\ora92))　)

The highlighted Code adds a new part to the listening file. GLOBAL_DBNAME and SID_NAME can be arbitrary, and PROGRM must point to tg4msql.

3 configure oracle_home \ network \ admin \ TnsNames. ora in the local service file

XZH =　(DESCRIPTION =(ADDRESS_LIST =　(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1521)))(CONNECT_DATA =　(SERVER = DEDICATED)　(SERVICE_NAME = xzh.world))　)TG4MSQL =　(DESCRIPTION =(ADDRESS_LIST =　(ADDRESS = (PROTOCOL = TCP)(HOST = xzh)(PORT = 1527)))(CONNECT_DATA =　(SID = tg4msql))(HS = OK)　)

The local service name of SQL is TG4MSQL, which can be written at will. The SID must be Listiner. the SID_NAME specified in ora must also be the PORT specified in the listener, and the host must be consistent. So far, we have configured the transparent gateway. If you want to access SQL, you need to use the database chain for convenience.

4. Create a Logon account xzh password xzh in SQL to access the PUBS database.

CREATE　DATABASE　LINK sql CONNECT TO xzh IDENTIFIED BY xzhUSING ‘TG4MSQL’

Here, the USING 'tg4msql' is the local service name configured in tnsnames. ora, and the SQL is the database chain name to be referenced later.

SQL>SELECT * FROM sales@sql

If any data is returned, it indicates that the channel for accessing the SQL database has been built. Please use it boldly. However, you do not need to perform DDL operations on remote heterogeneous databases.

SQL>CREATE TABLE all_users@tg4msql FRIN all_users;

ERROR is located in row 1st. ORA-02021: DDL operations on remote databases are not allowed.