Geek asks and answers

Unable to create database link for remote database in Oracle-DB

We have a CRM system in our company that uses the Oracle 11g database . It is developed by a third-party vendor.

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

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

We can use this to access the database using Oracle SQL Developer 3.1 (Connections → Properties)

Now parts of the data should be copied from the CRM database to another Oracle database, which is located on another server.

As far as I understand, I 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 looks like this:

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

.... and my listener.ora looks 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 several other programs to choose from. I couldn’t even start the listener with lsnrctl because it couldn’t “verify the user” or anything else. Ironically, the connection between the listener and the database with the MS SQL server works smoothly.

Now, despite the lack of important information about the CRM DB system, you can still connect to the database in SQL Developer. Is it also possible to establish a connection between two Oracle databases? Please help me with setting up and creating a link to the database.

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

Assistant Alex Pool helped me get it to work. I used

  show parameters service_names;

. abc.def, def . , TNS tnsnames.ora:

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

tnsping MYTNSENTRY . Tnsnames.ora . , "local" listener.ora, CRM.

SQL , :

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

, SID - abc, tnsnames.ora SERVICE_NAME CONNECT_DATA. - . , Tom entry. , , SID = .

listener.ora, , , CRM, , . SQL Developer, , -, . tnsnames.ora .

SERVICE_NAME CRM, EZCONNECT, :

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

SQL, , , SID, , . CRM-, lsnrctl , , , , show parameters service_names select value from v$parameter where name = 'service_names';.

, , , CRM , . , , , .

, global_names ​​ true, .

+6

SYS, , SYS .

+1

More articles:


All Articles