Configure the transparent gateway to access a second SQL server database.


In a previous post I explained the parameters that I used when connecting Oracle to SQL Server, now I have received another request from the developers, they need to access tables from another database in the same server.

These are the steps to configure a second database using Oracle Transparent Gateway:

1st.- Create a new init file in the SQL Server host.
2nd.- Edit the listener.ora file in the SQL Server host.
3rd.- Add an entry to the tnsnames.ora file in the Oracle database server.
4rd.- Create a database link.


1st.-
Create a new init file in the SQL Server host:
We should already have the init file for the first database (in my case sqldev):
$ORACLE_HOME\tg4<brand>\admin\init.ora
The absolut path is:
D:\oracle\product\10.2.0\tg_1\tg4msql\admin\initsqldev.ora

Now we copy it and rename the copy with the new DB name (in this case hhrr): inithhrr.ora
Then edit the file and update the HS_FDS_CONNECT_INFO parameter with the new database name
HS_FDS_CONNECT_INFO=sqldev01.hhrr (sqldev01=servername)

2nd.- Edit the listener.ora file in the SQL Server host:
D:\oracle\product\10.2.0\tg_1\network\admin\listener.ora

In the SID_LIST_LISTENER section we have to add a new entry (in blue)

SID_LIST_LISTENER_TG =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\tg_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = sqldev)
(ORACLE_HOME = D:\oracle\product\10.2.0\tg_1)
(PROGRAM=tg4msql)
)
(SID_DESC =
(SID_NAME = hhrr)
(ORACLE_HOME = D:\oracle\product\10.2.0\tg_1)
(PROGRAM=tg4msql)
)

)

Now we need to reload the listener:
lsnrctl reload listener_tg <== check your listener name

3rd.- Add an entry to the tnsnames.ora file in the Oracle database server:

vi $ORACLE_HOME/network/admin/tnsnames.ora

SQLDEV01_HHRR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sqldev01)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hhrr)
)
(HS = OK)
)

4rd.- Create a database link:

create database link sqldev01_hhrr connect to <sqls_user> identified by <passwd> using ‘SQLDEV01_HHRR’;

and test it:
select * from dual@sqldev01_hhrr

we are done!

Advertisements
This entry was posted in Oracle Server, Schema and tagged , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s