Can't create Database Link to remote DB in Oracle-DB

Question

We have a CRM system in our company, which uses an Oracle 11g database. It is developed by a third party vendor.

We do not have access to the server which runs the CRM system. But nevertheless, we have working DBA login data available to us (SYS user). It consists of:

• server IP: 172.1.2.3
• port: 1521
• SID: abc
• user: sys
• password: *

We can use this to access the DB with Oracle SQL Developer 3.1 (Connections >> Properties)

Now parts of the data must be copied out of the CRM-database into an other Oracle database, which resides on another server.

To my understanding, I'd need to create a database link in my target database. I tried something like this:

CREATE PUBLIC DATABASE LINK xxx CONNECT TO sys IDENTIFIED BY ***** USING 'MYTNSENTRY'

My tnsnames.ora is as follows:

MYTNSENTRY =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.1.2.3)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = abc)
 )
)

.... and my listener.ora look like this:

MYLISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=172.1.2.3)(PORT=1521))
      ))
SID_LIST_MYLISTENER=
  (SID_LIST=    
    (SID_DESC=
      (SID_NAME=MYTNSENTRY)
      (ORACLE_HOME=C:\somepath) # path to Oracle home of target DB
      (PROGRAM=extproc)))

Is PROGRAM=extproc the right choice? There are a couple of other programs to pick. I couldn't even start the listener with lsnrctl because it could not "verify the user" or something. Ironically, the listener-setup and database link to a MS SQL server work smoothly.

Now despite lacking some vital information about the CRM DB system, one can still connect to the DB in SQL Developer. Shouldn't it also be possible to make a connection between two Oracle DBs? Please help me with the setup and the creation of the database link.

----- EDIT: --------

Alex Poole's hint helped me get it to work. I used

  show parameters service_names;

to get the full service name. It has the form abc.def, with def being the domain. Thusly, I added the domain name to the TNS alias in tnsnames.ora:

MYTNSENTRY =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.1.2.3)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = abc.def)
 )
)

The connection can be tested with tnsping MYTNSENTRY on the command prompt of the target DB server. The tnsnames.ora is local. However, I deleted all changes to the "local" listener.ora, since the listener indeed resides on the CRM server.

The SQL command is mostly unchanged, but now the connection works:

CREATE PUBLIC DATABASE LINK xxx CONNECT TO some_user IDENTIFIED BY ***** USING 'MYTNSENTRY'

Answer 1

Not only should you NOT connect as SYS unless necessary, you CANNOT connect as SYS over a database link.

Answer 2

You've said the SID is abc, but in your tnsnames.ora you've got the SERVICE_NAME in the CONNECT_DATA section. They are not always the same thing - see this question, or this Ask Tom entry. You haven't actually said what error you're getting, but just changing that to SID = might make a difference.

The listener.ora, and indeed the listener, are on the server that hosts the CRM database, not on the one that hosts your 'target' database. As you can connect from SQL Developer that is apparently already configured. The tnsnames.ora does need to be local.

But if you do know the service_name for the CRM database you can skip that and use the EZCONNECT syntax to define everything in the link:

CREATE PUBLIC DATABASE LINK xxx
CONNECT TO non-sys IDENTIFIED BY *****
USING '//172.1.2.3:1521/service_name';

Check your SQL Developer configuration to see if that is already using the service name, rather than SID, and if not you'll need to discover it. If you had access to the CRM server you could use lsnrctl to find the service names that are registered, but as you don't seem to you will need to connect to the database and run show parameters service_names or select value from v$parameter where name = 'service_names';.

You need more privileges to create a public link than a private one, and public is potentially less secure as it exposes your CRM database to anyone on your target one. So I'd only make it public if really needed, and either way connect to a read-only account if you're able to create one.

Also note that if your target database has global_names set to true the database link name has to match the remote service name.