Advanced data migration
?????? Many times, data migration or extraction is to be performed between the heterogeneous database. If Oracle's data is extracted in SQL, you can extract data from Oracle by ODBC, OLEDB, etc. Most of them are achieved by transparent gateways.
?????? In the process of heterogeneous data extraction, it is best to write SQL code using the SQL92 standard, and pay attention to the conversion relationship between data types between different databases, such as Oracle's date type with DATE, SQL with DateTime, etc. .
?
A transparent gateway configuration about Oracle
?????? Oracle installation (9i), select the Transparent Gateway for Mssql, in the Oracle main directory / bin, there is a tg4msql.exe program, which is a transparent gateway program, and also TGRMSQL in the main directory. A directory, the inittg4msql.ora in the oracle_home / tg4msql / admin directory needs to be configured to connect SQL in Oracle.
Figure 2.1 TG4MSQL
1 How to configure transparent gateway? Open inittg4msql.ora:
#
# HS init parameters
# xzh represents the SQL service name, Pubs represents the SQL database to be accessed
#
HS_FDS_CONNECT_INFO = "Server = xzh; database = pubs"
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_RECOVERY_ACCOUNT = Recover
HS_FDS_RECOVERY_PWD = Recover
2 Configuring 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)
???)
?)
?????? Excavation code is to add some parts in the listener 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's local service named TG4MSQL, can be written, the SID must be the SID_NAME specified in Listiner.ora, and Port must be the port, host, etc., which must be specified in the monitor. So far, we have been roughly configured with transparent gateways. If you want to access SQL, you should use the database chain to be convenient.
4 Create a login account XZH password XZh in SQL, use the PUBS database.
Create? Database? Link SQL Connect To Xzh Identified by xzh
Using 'TG4MSQL'
The use of using 'tg4msql' is a local service name in TNSNames.ora, and SQL is the database chain name to be referenced later.
SQL> SELECT * from sales @ SQL
If there is a data returned, it means that the channel we accesses the SQL database has been built, please use it, but do not make DDL operations for the remote heterogeneous database.
SQL> CREATE TABLE All_Users @ TG4MSQL frin all_users;
?????? ??????????????? *
Error is located on the first line:
ORA-02021: Do not allow DDL operations for remote databases
?
Two SQL access to Oracle
1 By the line set function OpenDataSource
?????? OpenDataSource (provider_name, init_string)
??? SELECT? *
From OpenDataSource
???????? 'msdaora',
???????? 'Data Source = xzh.Oracle; user ID = POS; password = pos') .. pos.a0325
?????? Msdaora is the driver of OLEDB for Oracle, initialize the string to designate the local service name, user name, password. Then reference the data in the table to be servers .. username. Name. Note that it must be four parts, and the username and the 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 the POS mode in Oracle into the PUBS database of SQL.
2 2 Oracle's data by the SQL link server
?? - View the already existing link server
?? SELECT * from Sysservers
?? - Add a link server to SQL
?? EXEC sp_addlinkedServer ?? @server = 'orcl', ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?? @srvproduct = 'oracle', - Oracle ??????? fixed
?? @Provider = 'msdaora', ???????????? - MSDara ?? fixed
?? @Datasrc = 'xzh.oracle' ?????????????????? Local service name
??
?? EXEC sp_dropserver 'orcl' ?????? - Delete Link Server
??
?????? In this declaration, if you have access to Oracle now, you don't have username / password for logging in to Oracle.
?????? exec sp_addlinkedsrvlogin 'orcl', false, 'sa', 'POS', 'POS'
SA is SQL local login account, POS / POS is Oracle's login account, but this sentence is not helpful to the purpose of what we have to achieve.
Select * from orcl..pos.a0325
Still four parts, the precautions are the same, why not do it, I have been looking for a solution, and finally I found that I can't solve it through the SQL statement, only open SQL enterprise manager.
Figure 2.2 Configuring the remote account of Oracle
Figure 2.3 Setting the remote login account
SELECT * from Orcl..pos.a0325 ?? - this time finally OK.
Select * from OpenQuery (Orcl, 'SELECT * from pos.a0325')
3 Using SQL OpenRowSet functions
SELECT A. *
From OpenRowSet ('MSDara',
?? 'xzh.Oracle'; 'POS'; 'POS', POS.A0325) AS A ORDER BY A.ID
?
Some places should use alias to reference, please pay attention.
?
4 Ways use ODBC
SELECT A. *
From OpenRowSet ('msdasql', 'orcl_odbc'; 'POS'; 'POS',
?? Pos.a0325) AS? A
Order by a.id
?
ORCL_ODBC is Oracle's ODBC data source, creating ODBCs don't have to say, all of the above code is passed in SQL Query Analyze.
?
Summary
?????? About SQL Access Oracle has four ways, where the ODBC is difficult to do with the link server, the opponent will trouble, when using OLE DB for ORACL driver, OpenDataSource and OpenRowSet functions can be used It is very convenient, notes like to use the latter, see the four aspects, in terms of data access.
?
