Configuration Guide to Integrate Oracle Database 10.2.0.4 and SQLSERVER using Oracle Database Gateway for ODBC (dg4odbc)

 

Configuration Guide to Integrate Oracle Database 10.2.0.4  and  SQLSERVER using Oracle Database Gateway for ODBC (dg4odbc)

 

Oracle® Database Gateway Installation and Configuration Guide 11g Release 2 (11.2)

 

Certification Matrix for Database Gateway for ODBC Updated: March 2010

 

Product	Platform	O/S Version	Oracle	Non- Oracle	Notes
Database Gateway for ODBC 11.2	Windows x86 64 bit	Microsoft Windows Server 2003 Standard 64 Bit	{10.2.0.3 + Patch 5965763} 10.2.0.4 11.1 11.2	Microsoft SQL Server 2005	Requires minimum of ODBC v3.0 standard compliant drivers

 

Certified Configurations

For the latest certified configuration refer to the OTN Web site:

http://www.oracle.com/technology/products/gateways/pdf/certmatrix.pdf

 

Installing Oracle Database Gateway for ODBC

Step Through the Oracle Universal Installer

 

Screen	Response
Oracle Universal Installer: Welcome	Click Next.
Oracle Universal Installer: Specify Home Details	Specify a name for the installation in the Name field. You can also choose not to edit the default setting of the Name field of the Specify Home Details screen. The Path field in the Specify Home Details screen is where you specify the destination for your installation. You need not edit the path specification in the Path field. The default setting for this field points to ORACLE_HOME. After you set the fields in the Specify Home Details screen as necessary, click Next to continue. After loading the necessary information from the installation, the Oracle Universal Installer displays the Available Products screen.
Oracle Universal Installer: Available Product Components	a. Select Oracle Database Gateway for ODBC 11.2. b. Click Next.
Oracle Universal Installer: Summary	The Installation Summary screen enables you to review a tree list of options and components for this installation. Click Install to start installation.
Oracle Net Configuration Assistant: Welcome	Click Cancel.
Oracle Net Configuration Assistant:	Click Yes.
Oracle Universal Installer: Configuration Tools	Click Exit.
Exit	The final screen of the Oracle Universal Installer is the End of Installation screen. Click Exit to exit the installer.

 

 

The gateway is now installed.

 

 

 

When the Oracle Universal Installer confirms that the installation is complete, verify that the installation procedure was successful. To do this, read the contents of the installation log file, which is located in the

C:\Program Files\Oracle\Inventory\logs directory.

 

Installing ODBC driver for the non-Oracle system

 

1. Open Microsoft ODBC Manager from Control Panel > Administrative Tools

 

 

 

 

2. Configure System DSN For SQLSERVER

 

 

 

 

Click On ADD

 

 

 

 

 

 

 

 

 

 

3. The end product should be a valid System DSN. You may in the future click on the DSN name and click the Configure button to change the definition if you like. I did this when I wanted to switch between databases. A small warning here on re-configuration of the DSN: you will need to drop and re-create the database link (shown later) to activate the DSN. Click OK to exit the DSN administrator.

 

 

 

Configuring Oracle Database Gateway for ODBC

 

After installing the gateway and the ODBC driver for the non-Oracle system, perform the following tasks to configure Oracle Database Gateway for ODBC:

 

1. Configure the Gateway Initialization Parameter File

2. Configure Oracle Net for the Gateway

3. Configure the Oracle Database for Gateway Access

4. Create Database Links

 

1.            Configure the Gateway Initialization Parameter File

Perform the following tasks to configure the gateway initialization file:

1. Create the Initialization Parameter File

2. Set the Initialization Parameter Values

 

Create the Initialization Parameter File

 

You must create an initialization file for your Oracle Database Gateway for ODBC . Oracle supplies a sample initialization file, initdg4odbc.ora. The sample file is stored in the ORACLE_HOME\hs\admin directory.To create an initialization file for the ODBC gateway, copy the sample initialization file and rename it to initsid.ora, where sid is the system identifier(SID) you want to use for the instance of the non-Oracle system to which the gateway connects.

 

Set the Initialization Parameter Values

 

HS_FDS_CONNECT_INFO = dsn_value

HS_FDS_TRACE_LEVEL = 0

where dsn_value is the name of the system DSN defined in the Microsoft Windows ODBC Data Source Administrator.

 

2.         Configure Oracle Net for the Gateway

 

The gateway requires Oracle Net to communicate with the Oracle database. After configuring the gateway, perform the following tasks to configure Oracle Net to work with the gateway:

1. Configure Oracle Net Listener for the Gateway

2. Stop and Start the Oracle Net Listener for the Gateway

 

Syntax of listener.ora File Entries

LISTENER=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=host_name)

(PORT=port_number))

 

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=gateway_sid)

(ORACLE_HOME=oracle_home_directory)

(PROGRAM=dg4odbc)

)     

)

 

Stop and Start the Oracle Net Listener for the Gateway

 

You must stop and restart the Oracle Net Listener to initiate the new settings, as follows:

 

1. From the Start menu, select Settings, Control Panel and then select Services.

2. Select the Oracle Net Listener service for the gateway.

3. If the service is already running, click Stop to stop it.

4. Click Start to start or restart the service.

 

3.         Configure the Oracle Database for Gateway Access

 

Edit tnsname.ora in oracle 10 g Home

D:\Oracle\Ora10G\NETWORK\ADMIN

 

Configuring tnsnames.ora

 

Edit the tnsnames.ora file to add a connect descriptor for the gateway. The following is a syntax of the Oracle Net entry using the TCP/IP protocol:

 

connect_descriptor=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=host_name)

(PORT=port_number)

)

(CONNECT_DATA=

(SID=gateway_sid))

(HS=OK)

)

 

 

4.         Create Database Links

Any Oracle client connected to the Oracle database can access an ODBC data source through the gateway. The Oracle client and the Oracle database can reside on different machines. The gateway accepts  connections only from the Oracle database.

 

To access the ODBC data source, you must create a database link. A public database

link is the most common of database links.

SQL> CREATE PUBLIC DATABASE LINK dblink CONNECT TO

2 "user" IDENTIFIED BY "password" USING 'tns_name_entry';

 

After the database link is created you can verify the connection to the ODBC data

source, as follows:

SQL> SELECT * FROM DUAL@dblink;

Read more...

Oracle SQL Developer 3.0 Early Adopter 1

  

Oracle SQL Developer 3.0 Early Adopter 1

Oracle SQL Developer 3.0 EA 1 has come up with some major changes . The list of new features are

1. DBMS Scheduler
2. DBA Functionality
3. Files and Version Control
4. Migrations
5. PDF
6. PL/SQL Support
7. Query Builder
8. Schema Browser
9. SQL Plus Commands
10. Spatial
11. Tuning
12. Unit Testing
13. Unloading and Uploading

The DBA functionality Module is really good to play with .

ð     Ability to review and tune db config and edit initation parameters

ð     Create and prioritize consumer groups and manage resource plans

ð     Define audit settings and manage profiles, roles and users

Define and manage archive logs, control files, datafiles, redo logs, rollback segments and tablespaces

Read more...