Data migration between Oracle and SQL

oracle| data
Advanced Data Migration

A lot of time, to the heterogeneous database data migration or extraction, if the Oracle data extraction in SQL, through ODBC, OLE DB and other means, to extract data from the Oracle SQL, mostly through a transparent gateway to achieve.

In the process of heterogeneous data extraction, it is best to use SQL92 standard syntax to write SQL code, while paying attention to the conversion relationship of data types between different databases, such as Date type of Oracle, datetime with SQL, etc.



A transparent gateway configuration for Oracle

Oracle installation (9i), transparent GATEWAY for MSSQL selected, in the Oracle home directory \ Bin, There is a Tg4msql.exe program, which is a transparent gateway program, and in the home directory also has a tgrmsql directory, oracle_home\tg4msql\ The Inittg4msql.ora in the Admin directory is required to be configured to connect SQL in Oracle.



Figure 2.1 Tg4msql

1 How do I configure a transparent gateway? Open Inittg4msql.ora:

#

# HS init parameters

# Xzh represents the SQL service name, and pubs represents the SQL database to access

#

hs_fds_connect_info= "Server=xzh;database=pubs"

Hs_fds_trace_level=off

Hs_fds_recovery_account=recover

Hs_fds_recovery_pwd=recover

2 Listening Configuration Oracle_home\network\admin\listiner.ora

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 highlight code is to add a new section to the listening file, Global_dbname, Sid_name can be arbitrary, progrm must point to Tg4msql as shown in Figure 2.1.

3 Configuration of Local Service files Oracle_home\network\admin\tnsnames.ora

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)

)

Use SQL Local Service name is Tg4msql, you can write arbitrarily, sid must be specified in the Listiner.ora of the Sid_name,port also must be in the listener specified port, host, etc. should conform to the same. So far, we've roughly configured the transparent gateway, and it's convenient to use a database chain if you want to access SQL.

4 Create login account in SQL Xzh password Xzh, use to access the pubs database.

CREATE DATABASE LINK SQL CONNECT to Xzh identified by Xzh

USING ' Tg4msql '

The using ' Tg4msql ' here is the local service name configured in Tnsnames.ora, and SQL is the name of the database chain we'll be referencing later.

Sql>select * from Sales@sql

If there is data returned, indicating that our access to the SQL database has been completed, please use it boldly, but not allow DDL operations on remote heterogeneous databases.

Sql>create TABLE all_users@tg4msql Frin all_users;

*

ERROR is on line 1th:

ORA-02021: DDL operations are not allowed on remote databases



Two SQL Access Oracle methods

1 through the rowset function OpenDataSource

OpenDataSource (provider_name, init_string)

SELECT *

From OpenDataSource (

' Msdaora ',

' Data source=xzh.oracle; User Id=pos; Password=pos '). Pos. A0325

MSDAORA is an OLE DB for Oracle driver that initializes a string that specifies the local service name, user name, and password. You then reference the data in the table to the server. User name, table name. Note that there must be four parts, the user name and table name must be capitalized.

SELECT * into PUBS. Dbo. A0325 from

OpenDataSource (

' Msdaora ',

' Data source=xzh.oracle; User Id=pos; Password=pos '). Pos. A0325

--Import the A0325 of POS mode in Oracle into the pubs database in SQL.

2 referencing Oracle data through a linked server in SQL

--View linked servers that already exist

Select*from sysservers

--Adding linked servers to SQL

EXEC sp_addlinkedserver

@server = ' ORCL ',--ORCL is a linked server name in SQL

@srvproduct = ' Oracle ',--oracle fixed

@provider = ' Msdaora ',--msdaora fixed

@datasrc = ' xzh.oracle '--datasrc Local Service name



EXEC sp_dropserver ' ORCL '--delete linked server



In this case, it is definitely not possible to access Oracle now, as there is no user name/password to sign in to Oracle

EXEC sp_addlinkedsrvlogin ' ORCL ', false, ' sa ', ' pos ', ' POS '

The SA is a SQL local login account, Pos/pos is an Oracle login account, but this statement does not help us to achieve the goal.

SELECT * from ORCL ... Pos. A0325

Or four parts, notice ibid, why not, I have been looking for solutions, and finally found through the SQL statement can not be resolved, only open SQL Enterprise Manager.



Figure 2.2 Configuring the remote account number for Oracle



Figure 2.3 Setting up a remote login account

SELECT * from ORCL ... Pos. A0325--this time it's OK.

SELECT * from OPENQUERY (ORCL, ' select * from POS. A0325 ')

3 OPENROWSET functions using SQL

SELECT a.*

From OPENROWSET (' Msdaora ',

' Xzh.oracle '; ' POS '; ' Pos ', Pos. A0325) as a order by a.id



There are some places to use aliases to quote, please note.



4 How to use ODBC

SELECT a.*

From OPENROWSET (' msdasql ', ' orcl_odbc '; POS '; ' POS ',

Pos. A0325) as A

ORDER BY a.ID



ORCL_ODBC is an Oracle ODBC data source, creating ODBC I don't need to say that all of the above code is passed in SQL QUERY ANALYZE.



Three summary

About SQL access to Oracle there are four ways to do it through ODBC and linked server way more difficult, opponents novice will be troublesome, using OLE DB for ORACL driver, there are OPENDATASOURCE and OPENROWSET functions can be used, are very convenient , notes like to use the latter, to explain, in the data access aspect, these four kinds of aspects are quite efficient.