Database link from Local Oracle 11g to Oracle Database Cloud Service 11g

Question

We are using Oracle database from Oracle Database Cloud Service, now we are not able to connect Cloud db from Local oracle db.

Somehow we created link using below commnd,

CREATE DATABASE LINK link_1 CONNECT TO "username" 
IDENTIFIED BY "password" USING 'https://x.x.x.oraclecloudapps.com/apex/';

Now once i execute query select * from EMP@link_1 It throws an error

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

with Cause and Actions.

Let me know if some additional configuration/changes required.

Answer 1

That is not how you connect to another database through database links. Database links require SQL*Net connectivity, not HTTP(S).

First you need to enable access, then you can connect using the address of the compute node and the service name for your database.

http://docs.oracle.com/cloud/latest/dbcs_dbaas/CSDBI/GUID-372D4233-9793-47EC-8DFC-AFD6CF32866C.htm#CSDBI3449

Answer 2

Don't get hung-up on "cloud" vs "local" - what you're doing is establishing a TNS network link between two databases. It is nothing more than that.

In order to do this, you will need to have the appropriate tns entries configured in your tnsnames.ora (unless you are using LDAP or you pass in the entire connect string). If you don't know how to do this, the netca application will create one for you using a wizard.

Once you have this tns entry, you can simply create the database link as you listed above, but replace the USING clause value you have with the tns entry alias.

http://docs.oracle.com/cd/E11882_01/network.112/e10835/tnsnames.htm#NETRF007