Home » RDBMS Server » Networking and Gateways » Unsuccessful of establishment of database link from oracle to sql server (Oracle 11.2.0.4.0,Linux 2.6.32-573.el6.x86_64)
Unsuccessful of establishment of database link from oracle to sql server [message #672721] |
Thu, 25 October 2018 07:45 |
|
arin.oradba
Messages: 33 Registered: January 2016 Location: Kolkata
|
Member |
|
|
GOAL :
Require to push data from one schema of oracle database to sql database , so we have to create a database link in oracle database.
Request all of you to help on this matter to solve the issue....
Steps we had followed:
1 > Download the gateway file from OTN.
2> unzip the file (p13390677_112040_Linux-x86-64_5of7.zip) in oracle database server and execute runInstaller
3>Select the Available product components i.e "Oracle Database Gateway for Microsoft sql server 11.2.0.4.0".
4>Enter the details of Microsoft sql server i.e, Host name, port number,sql server instance name, sql server database name.
5>Run the root.sh file from root user and the gateway installation will be complete.
6>Now it is need to add oracle database server ip and port entry in listener.ora file and start the listener
7>Now it is need to add the tns entry in tnsnames.ora file
8>Now we are able to create the database link in oracle database.
Our 2 servers details:
Oracle DB details :
Server IP :- 112.200.20.19
Port :- 1522
Database Name :- XYZS
Schema :- SCHENA1
SQL Server details:
Server IP :- 112.200.20.33
Instance Name :- SCHENA1_XYZ
Dynamic Port :- 55430
Database Name :- DynamicsInstance
Id :- sa
Pwd :- XXX@XXXX
Activities on DATE: 22/10/2018 and DATE: 24/10/2018
DB SERVER IP: 112.200.20.19 and PORT=1522
xxx> select name from v$database;
NAME
---------
XYZ
SYS@MAGMATRNS> Select instance_name from v$instance;
INSTANCE_NAME
----------------
XYZ
SYS@MAGMATRNS> Show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string XYZS
LISTENER ENTRY:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = abcd.vgfd.co.in)(PORT = 1522))
)
)
LISTENER_XYZS=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=abcd.abcdef.co.in)(PORT=1522))
)
)
)
SID_LIST_LISTENER_XYZS=
(sid_list=
(sid_desc=
(sid_name=XYZS)
(ORACLE_HOME=/u01/ddd/oracle/product/11.2.0/dbhome_1)
(program=XYZS)
(envs="LD_LIBRARY_PATH=/u01/ddd/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib")
)
)
ADR_BASE_LISTENER = /u01/ddd/oracle
LISTENER STATUS:
-bash-4.1$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2018 16:02:40
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-OCT-2018 15:58:38
Uptime 0 days 0 hr. 4 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/ddd/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/ddd/oracle/diag/tnslsnr/abcd.abcdef/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=abcd.abcdef.co.in)(PORT=1522)))
Services Summary...
Service "BBBS" has 1 instance(s).
Instance "BBBS", status READY, has 1 handler(s) for this service...
Service "BBBXDB" has 1 instance(s).
Instance "BBBS", status READY, has 1 handler(s) for this service...
Service "DDD" has 1 instance(s).
Instance "DDD", status READY, has 1 handler(s) for this service...
Service "DDDXDB" has 1 instance(s).
Instance "DDD", status READY, has 1 handler(s) for this service...
Service "XYZS" has 1 instance(s).
Instance "XYZS", status READY, has 1 handler(s) for this service...
Service "XYZXDB" has 1 instance(s).
Instance "XYZS", status READY, has 1 handler(s) for this service...
The command completed successfully
-bash-4.1$
TNS ENTRY:
XYZS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = abcd.abcdef.co.in)(PORT = 1522))
(CONNECT_DATA =(SID=XYZS))
(SERVER = DEDICATED)
# (SERVICE_NAME = xyzs)
(HS=OK)
)
-bash-4.1$ tnsping XYZS
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2018 15:47:34
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/ddd/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = abcd.abcdef.co.in)(PORT = 1522)) (CONNECT_DATA =(SID=XYZS)) (SERVER = DEDICATED) (HS=OK))
OK (0 msec)
-bash-4.1$
DATABASE LINK:
SCHEMA1_XYZ is a schema of sql server and password is "XXX@XXXX"
Create public database link DB_LINK1 connect to SCHEMA1_XYZ identified by "XXX@XXXX" using 'XYZS';
ERROR:
HS: Error ORA-28544 or 28546, probable network admin error
HS: Lost RPC connection to remote Agent...
HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=GC-UATPreProdDB.magma.co.in)(PORT=1522))(CONNECT_DATA=(SID=MAGMATRNS))(SERVER=DEDICATED)), NCR status = 28546
Also modify the sqlnet.ora file as follows but same above error occurred:
#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
TRACE_LEVEL_CLIENT = OFF
SQLNET.INBOUND_CONNECT_TIMEOUT = 0
ADR_BASE = /u01/app/oracle
But same Error.
ERROR:
HS: Error ORA-28544 or 28546, probable network admin error
HS: Lost RPC connection to remote Agent...
HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=GC-UATPreProdDB.magma.co.in)(PORT=1522))(CONNECT_DATA=(SID=MAGMATRNS))(SERVER=DEDICATED)), NCR status = 28546
Some Modification done on DATE: 25/10/2018
MODIFY LISTENER ENTRY BY REPLACING sid_name=XYZS to (sid_name=AAA.COM): #AAA.COM is DNS name of sql server .
Previous (sid_name=XYZS)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = abcd.abcdef.co.in)(PORT = 1522))
)
)
LISTENER_XYZS=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=abcd.abcdef.co.in)(PORT=1522))
)
)
)
SID_LIST_LISTENER_XYZS=
(sid_list=
(sid_desc=
(sid_name=AAA.COM)
(ORACLE_HOME=/u01/ddd/oracle/product/11.2.0/dbhome_1)
(program=xyzs)
(envs="LD_LIBRARY_PATH=/u01/ddd/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib")
)
)
ADR_BASE_LISTENER = /u01/ddd/oracle
TNS ENTRY:
XYZS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = abcd.abcdef.co.in)(PORT = 1522))
(CONNECT_DATA =(SID=XYZS))
(SERVER = DEDICATED)
# (SERVICE_NAME = xyz)
(HS=OK)
)
Error:
HS: Error ORA-28544 or 28546, probable network admin error
HS: Lost RPC connection to remote Agent...
HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=GC-UATPreProdDB.magma.co.in)(PORT=1522))(CONNECT_DATA=(SID=MAGMATRNS))(SERVER=DEDICATED)), NCR status = 28546
After modifying TNS ENTRY by replacing the Previous SID=XYZS to SID=AAA.COM:
Previous (SID=XYZS)
After modifying SID_NAME of TNS Entry by DNS name of sql server...
XYZS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = abcd.abcdef.co.in)(PORT = 1522))
(CONNECT_DATA =(SID=AAA.COM))
(SERVER = DEDICATED)
# (SERVICE_NAME = xyz)
(HS=OK)
)
Error :
HS: Unable to establish RPC connection to HS Agent...
HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=abcd.abcdef.co.in)(PORT=1522))(CONNECT_DATA=(SID=AAA.COM))(SERVER=DEDICATED)), NCR error = 65535 Unable to retrieve text of NETWORK/NCR message 65535
Also make the sqlnet.ora file as before:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
#NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
#TRACE_LEVEL_CLIENT = OFF
#SQLNET.INBOUND_CONNECT_TIMEOUT = 0
ADR_BASE = /u01/app/oracle
But Same Error.......
Error :
HS: Unable to establish RPC connection to HS Agent...
HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=abcd.abcdef.co.in)(PORT=1522))(CONNECT_DATA=(SID=AAA.COM))(SERVER=DEDICATED)), NCR error = 65535 Unable to retrieve text of NETWORK/NCR message 65535
Once again Request all of you to help on this matter to solve the issue....
[Updated on: Thu, 25 October 2018 07:49] Report message to a moderator
|
|
|
|
Re: Unsuccessful of establishment of database link from oracle to sql server [message #672737 is a reply to message #672724] |
Fri, 26 October 2018 00:12 |
|
arin.oradba
Messages: 33 Registered: January 2016 Location: Kolkata
|
Member |
|
|
Thank you very much for you response.....
I am sharing the server details for your convenience...
ORACLE DATABASE DETAILS:
Oracle DB details :
Hostname: abcd.abcdef.co.in
Server IP :- 112.200.20.19
Port :- 1522
Database Name :- XYZS
Schema :- SCHENA1
SQL Server or target database details:
SQL Server details:
Hostname: sql2014-db-uat.aaa.com
DNS Name: AAA.com
Server IP :- 112.200.20.33
Instance Name :- SCHENA1_XYZ
Dynamic Port :- 55430
Database Name :- DynamicsInstance
Id :- sa
Pwd :- XXX@XXXX
[Updated on: Fri, 26 October 2018 00:19] Report message to a moderator
|
|
|
Re: Unsuccessful of establishment of database link from oracle to sql server [message #672738 is a reply to message #672721] |
Fri, 26 October 2018 00:22 |
|
arin.oradba
Messages: 33 Registered: January 2016 Location: Kolkata
|
Member |
|
|
There was a mistake of myself to give wrong hostname in LISTENER Entry. I have corrected it.
LISTENER ENTRY:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = abcd.abcdef.co.in)(PORT = 1522))
)
)
LISTENER_XYZS=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=abcd.abcdef.co.in)(PORT=1522))
)
)
)
SID_LIST_LISTENER_XYZS=
(sid_list=
(sid_desc=
(sid_name=XYZS)
(ORACLE_HOME=/u01/ddd/oracle/product/11.2.0/dbhome_1)
(program=XYZS)
(envs="LD_LIBRARY_PATH=/u01/ddd/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib")
)
)
ADR_BASE_LISTENER = /u01/ddd/oracle
[Updated on: Fri, 26 October 2018 00:24] Report message to a moderator
|
|
|
Re: Unsuccessful of establishment of database link from oracle to sql server [message #672781 is a reply to message #672738] |
Fri, 26 October 2018 08:58 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
For base functionality, the listener usually doesn't need a listener.ora file at all. Without it, it will quite happily start with all default values, the oracle databases on the same server will self-register,and all will be right with the world. If the listener has to service heterogeneous (gateway) connections, you will need listener.ora to list them in the SID_LIST section, but you still wouldn't need the LISTENER section. I believe if you had left that out completely and allowed the listener to use the defaults, you would have never had the problem.
BTW, I just noticed that you had configured your listener to use the non-default port of 1522. What's the reasoning for this? Any time you use a non-default value for anything, you need a good justification along with the realization that it will simply complicate everything.
|
|
|
|
|
|
Re: Unsuccessful of establishment of database link from oracle to sql server [message #672875 is a reply to message #672858] |
Tue, 30 October 2018 05:40 |
|
arin.oradba
Messages: 33 Registered: January 2016 Location: Kolkata
|
Member |
|
|
Hi JP, What a great help...This is a new way..I have the no idea about the GATEWAY ORACLE HOME. I did everything in ORACLE_HOME.
Please look at the following and request you to share your knowledge.
Steps to install SQLSERVER gateway files:-
1 > Download the gateway file from OTN.
2> unzip the file (p13390677_112040_Linux-x86-64_5of7.zip) in oracle database server and execute runInstaller
Then one screen come where we have to provide ORACLE_HOME directory path.
Please confirm whether this path should be ORACLE_HOME or ORACLE_GATEWAY_HOME and it can be anything I wish.
Other Question:
How to create listener in the Gateway Oracle Home, with a different port number ?
How to decide the port number?
What should be the SID_NAME in LISTENER ? THE ORACLE DATABASE NAME Or anything?
How to create init<sid>.ora file which you asked to store in Gateway $ORACLE_HOME/dg4msql/admin directory?
Please look and guide me the right way-
Again I am doing the ORACLE GATEWAY INSTALLATION with different ORACLE HOME as describe below path.
/u01/ddd/oracle/product/11.2.0/dbhome_1/ORA_GATEWAY_SQL
2.Provide ORACLE HOME NAME "ORA_GATEWAY_SQL" and provide the following path -
/u01/app/oracle/product/11.2.0/dbhome_1/ORA_GATEWAY_SQL
Once again I admire you knowledge and request you to help me.Thank you very much.
[Updated on: Tue, 30 October 2018 06:00] Report message to a moderator
|
|
|
Re: Unsuccessful of establishment of database link from oracle to sql server [message #672888 is a reply to message #672875] |
Tue, 30 October 2018 12:21 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
When you unzip the Gateway zip file and run the installer, you should give it the location where you want it to create the Oracle Gateway Home. This needs to be different than the Oracle Database Home.
e.g. If your Oracle Database Home is in /u01/app/oracle/product/11.2.0/dbhome , you can install/create the Gateway into /u01/app/oracle/product/11.2.0/gateway . As long as it's a valid (with proper rights) EMPTY directory, other than the Oracle Database Home directory, it will be fine.
I believe (going from memory) that, the installer normally automatically brings up the Net configuration assistant ("netca") to create a new listener. This is only true if you are running in GUI mode. Otherwise it will use the default configuration when running in text (silent) mode. Make sure to create the listener with a different name than the one in the Oracle Database Home.
Port number, that's you and your Sys Admin's choice. If you don't plan on having this Gateway to be used by an external database, you can use "IPC" mode only. This means that all communications are done on the server via inter-process communication. If you choose TCPIP, OTHER databases (other than the one(s) on your current database server) can also use the Gateway.
SID name is your choice, it's just like a database name. When the listener starts the DG4MSQL process to create a connection to the SQL-Server database, it will use the SID name and attempt to find the init<sid>.ora parameter file.
The init<sid>.ora is created by the Installer, (it uses your manual input in the GUI, or parameters that are included in the response file).
Quote:
/u01/ddd/oracle/product/11.2.0/dbhome_1/ORA_GATEWAY_SQL
This puts the Oracle Gateway Home below the Oracle Database Home directory. I wouldn't do that. Keep them separate, see above.
While you're at it, I recommend that you have a read here: https://docs.oracle.com/cd/B28359_01/gateways.111/b31042/installsql.htm
One important thing to remember, once you have installed the Oracle Gateway, anytime that you want to change its configuration, you should make sure that your environment variables are set to the Oracle GATEWAY Home. The easiest way do this is to use "oraenv" from Oracle, and make sure that the Oracle Gateway Home is defined correctly in the "oratab" file. I believe it's found under /etc/oratab - again going from memory.
JP
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 17 14:24:52 CST 2025
|