Listener service for SQL server

[Mydeen]

we need help on creating the DB link for Microsoft SQL server from Oracle
Currently we have established the ODBC connection on both server to reach it.

Here the issue is we don't know how to do the changes in listerner.ora file as its
configured with ASM. Please help us the sample to do that.

Current file listener:
-- Linux Database

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent


Need to add the SQL server servcies, we don't know how to add and where to add
        (SID_DESC =
      (SID_NAME = MySQL)
      (ORACLE_HOME = /u01/app/oracle/product/18.0.0.0/dbhome_1)
      (PROGRAM = dg4odbc)
    )

[ErmanArslansOracleBlog]

You can add them to a "listener I" (IFILE) file and then add the IFILE to your listener ora. Once done, you can reload your listener to make your additional configuration be activated.

IFILES entries are actually pointers to the files for additional entries.

For example, in listener.ora, an additional entry such as  ->

IFILE=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener_ifile.ora

However; if you want a dedicated listener , then it is a different story.
I have written several blog posts, which includes listener configurations, IFILES and modification on listener parameters.

An example : https://ermanarslan.blogspot.com/2016/09/exadata-ebs-on-exadata-migration-2-tips.html

Also I have a dedicated blog post for Oracle to SQL Server dblink ->

https://ermanarslan.blogspot.com/2016/01/rdbms-oracle-to-sqlserver-dblink.html

So, I suggest you to read my blog (by making searches with your keywords) and check the related information to go further.

[Mydeen]

Please help us add to add it in the file as the below SQL listener. ODBC created and tested

(SID_DESC =
(SID_NAME = MySQL)
(ORACLE_HOME = /u01/app/oracle/product/18.0.0.0/dbhome_1)
(PROGRAM = dg4odbc)
)

[ErmanArslansOracleBlog]

Okay.
As you know, you need to install the  Database Gateway for ODBC first.
You need to install it into an ORACLE HOME and the ORACLE_HOME value that you specify in your listener.ora (in SID_DESC) must match the location where you have installed your gateway to.

Modifying this Grid related listener files is not something that I recommend, as the listener configurations in GRID Home uses Dynamic service registration. That 's why you don't see e SID_LIST there.

However, what you want to do actually is to have a static one there.

I didn't test it yet, but you can test it on your own risk.

Just add the below lines to your GRID listener.ora, reload the listener and see.

Do this on a TEST instance first!

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = MySQL)
(ORACLE_HOME = /u01/app/oracle/product/18.0.0.0/dbhome_1)
(PROGRAM = dg4odbc)
)
)

[Mydeen]

Yeah we have tried  for the same but entries listener server is down. so we have changed the listener as back to old one.

And we have created new one listener under /u01/app/oracle/product/18.0.0.0/dbhome_1/network/admin
(which is not there earlier). All service running but newly created service showing as unknown. reaming service are ready state.
All these task done under oracle 18C in Oracle cloud.

[ErmanArslansOracleBlog]

Hmm.. Probably a RAC agent overwrites that file periodically..

So can you use that SQL Server service for your DB Link? In other words; can you use your SQL Server DB link at the moment?

[Mydeen]

Yes tried but its shows below error

[ErmanArslansOracleBlog]

Please check this MOS note -> ORA-28545 Connecting from Oracle Database to non-Oracle databases using Oracle Database Gateways (Doc ID 2169496.1)