Search code examples
databaseoracle-databaseoracle11glinked-server

Failing to read data from remote Oracle 11g linked database

I have the username and password, full permissions for 2 Oracle databases. When I create a database link from A to B, I see no error:

ALTER SESSION SET CURRENT_SCHEMA = TPMDBO;

CREATE DATABASE LINK link_to_uat
CONNECT TO {UAT username}
IDENTIFIED BY {UAT user password}
USING '{UAT fqdn}:2053/ocrdev';

When I try to run a query against the linked server:

SELECT * FROM TheTable@link_to_uat;

I receive:

ORA-02019: Connection description for remote database not found

I am able to connect to the remote database using the username and password above from Oracle Sql Developer and other tools, but for some reason Oracle just can't figure it out.

This company I'm working for right now locks down all machines, including dev boxes, so I'm unable to modify the TNSNames.ora file.

I tried creating the linked server using:

CREATE DATABASE LINK link_to_uat
CONNECT TO {remote username}
IDENTIFIED BY {remote user\'s password}
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={fqdn})(PORT=2053)))(CONNECT_DATA=SERVER=DEDICATED)(SERVICE_NAME=ocrdev)))';

But apparently that's bad syntax and I get:

ORA-12154: TNS: could not resolve the connect identifier...

All of the actions recommended involve modifying the TNSNames.ora file, which is not possible here in dev prison.

For both the remote and local databases I have admin rights.

Here are screenshots of my connections to the two servers from within Aqua Data Studio:

UAT Localhost

Is there any other way to get around this issue?

Solution

  • Ok, after dinking around with this one for a while it turned out I need to make a PUBLIC linked database. Like so:

    CREATE PUBLIC DATABASE LINK "LINK_TO_PROD"
  CONNECT TO {username} IDENTIFIED BY {password}
  USING '(DESCRIPTION =(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = {prod server})(PORT = 2053)))
    (CONNECT_DATA = (SERVICE_NAME = orcprd)))'