3

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'
Emil Laine
  • 41,598
  • 9
  • 101
  • 157
user748261
  • 151
  • 1
  • 2
  • 6
  • The TNSNames file you have that entry from, thats from the TNSNames file on the 'other' (non-CRM) oracle database, right? I'm not sure you have to do anything to the listener on the target DB. I've never had to, at least. As best I know, the target DB handles an incoming DB link like any other connection (but I may be wrong about that) – moleboy Apr 18 '12 at 20:43
  • I'm also really sure you don't want to create the DB link as the sys user (or, at least, I wouldn't...if only for security's sake) – moleboy Apr 18 '12 at 20:45
  • Agreed. `SYS` is *very* special in Oracle and should be treated with extreme care/caution. – Adam Hawkes Apr 18 '12 at 20:46
  • I'll echo the don't do this in `sys`. You need to quote your password; it's not an object it's a string. – Ben Apr 18 '12 at 20:58
  • Yes, the tnsnames.ora is from the "target" DB. I need to reference a service name in my "create database link" SQL statement. Since I have no information on what is running on the CRM-server, I intend to create my own Listener/TNS alias which provides a service. That listener shall then be used to access the remote (=CRM) database. Thanks for the hint on the security issues with the sys user, I'll use another user. – user748261 Apr 18 '12 at 21:44

2 Answers2

6

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.

Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • you advise to use **'//172.1.2.3:1521/abc.domain'**. How do I look up what the .domain information is in my case? I'd have to check if there is any non-admin user yet who can see all the tables we need. I am reluctant to toy with user privilege modification on a system I do not fully understand, though. – user748261 Apr 18 '12 at 23:21
  • @user748261 - wel I meant the service name really, I was guessing the format. I've updated the answer with some links and more info. Please add the actual error you are seeing to your question though, as I'm shooting in the dark a bit! – Alex Poole Apr 19 '12 at 07:53
  • I have edited my original posting. You may have shot in the dark, but you were right :-). I hope my edit will make clear how the solution worked. – user748261 Apr 19 '12 at 09:01
1

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

DCookie
  • 42,630
  • 11
  • 83
  • 92
  • My initial thought was to not get additional problems with lack of privileges while testing. But since it even has restrictions on it, I guess I'll change it even for testing. – user748261 Apr 18 '12 at 23:48