3

I'm trying to create a database link between this two DBs.

My local DB is 11.2.0.3.

SQL> select * from global_name;


GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MYDB.DOMAINNAME


SQL> select name, value from v$parameter where name in ('db_name', 'db_domain', 'global_names');
NAME                 VALUE
-------------------- ----------------------------------------
db_domain
global_names         TRUE
db_name              mydb

The remote DB is 11.2.0.2 configured as follows:

SQL> select * from global_name;


GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
myremotedb

SQL> select name, value from v$parameter where name in ('db_name', 'db_domain', 'global_names');

NAME                 VALUE
-------------------- ----------
db_domain
global_names         FALSE
db_name              myremotedb

I want to create a DBLINK in the local database to the remote database.

I have my tnsnames (mydb's machine) configured as follows:

REMOTE-DATABASE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.1.2)(Port = 1521))
    )
    (CONNECT_DATA =
      (SID = myremotedb)
    )
  )

Then, when I try to create a database link in my local database, since global_names parameter is set to true, oracle appends the local domain name to the database link name

SQL> create database link myremotedb@myuser connect to myuser identified by mypasswd using 'REMOTE-DATABASE';


Enlace con la base de datos creado.


SQL> select db_link from user_db_links;


DB_LINK
------------------------------
MYREMOTEDB.DOMAINNAME@MYUSER

and if I try to use it, it fails

SQL> select * from dual@MYREMOTEDB@MYUSER;
select * from dual@MYREMOTEDB@MYUSER
                   *
ERROR en línea 1:
ORA-02085: database link MYREMOTEDB.DOMAINNAME@MYUSER connects to MYREMOTEDB




SQL> select * from dual@MYREMOTEDB.DOMAINNAME@MYUSER;
select * from dual@MYREMOTEDB.DOMAINNAME@MYUSER
                   *
ERROR en línea 1:
ORA-02085: database link MYREMOTEDB.DOMAINNAME@MYUSER connects to MYREMOTEDB

How can I create this database link in this scenario?

The remote db doesn't have a db_domain. How can I force oracle to create a DBLINK with no domain name?

Regards!

0
0

Can you show us the content of your sqlnet.ora file ?
The domain name should be listed there :

NAMES.DEFAULT_DOMAIN = domain.com.xxx

In case you don't have one , create a new sqlnet.ora file using the netca tool. - after you create that file edit it and add the entry with you domain name!

0
0

Excellent ... yes it is working after global_name=FALSE.

I am testing uploading expdp dump file from onprim to AWS RDS..

**** SCRIPT STARTED Session: NKIM3@LIMSDEV3(3) 4/1/2019 4:18:41 PM ****
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object       => 'DATA_PUMP_DIR',
source_file_name              => 'expdp_msrt_md_201904011535_file01.dmp',
destination_directory_object  => 'DATA_PUMP_DIR',
destination_file_name         => 'expdp_msrt_md_201904011535_file01.dmp', 
destination_database          => 'to_rds2' 
);
END;
==>
PL/SQL block executed
--------------------------------------------------------------------------------
**** SCRIPT ENDED 4/1/2019 4:19:48 PM ****
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-1

Simply at local db set

alter system set global_Names = FALSE;

Then your DB link works.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.