need help creating db links

[padawin]

Hi all,
I have 2 dbs ora1 and ora2 on the same linux box (host = linbox) running 8.1.6.
ora1 service name = ora1
ora1 user = scott
ora2 service name = ora2
ora2 user = mary

If I want to create a public db link from mary's account (create public db link privilege already granted to mary) to scott's schema to acces the emp table. I tried the following:
SQL> create public database link ora1.linbox connect to scott identified by tiger using 'ora1';
Is this correct?

When I try to access the emp table I get the following error:
SQL> select count(*) from scott.emp@ora1.linbox;
ORA-12154: TNS: could not resolve service name

My tnsnames.ora file has the following entries:
ORA1.LINBOX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = linbox)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora1)
)
)

Am I doing something majorly wrong? Also how would scott grant select on emp to mary (user in anohter db)? Thanks

[akhadar]

Do the following

1. Make the global_names parameter as false on both the databases.

2.SQL> create public database link ora1 connect to scott identified by tiger using 'ora1';

Hope this will solve your problem

[padawin]

global_names param is set to false on both dbs and I tried the create db link with ora1 using 'ora1' as suggested above but still having the same probs.

SQL> select * from scott.emp@ora1;
ORA-12154:TNS could not resolve service name
SQL> select * from scott.emp@ora1.linbox;
ORA-02019:connection description for remote database not found

Using LSNRCTL status I can see the listener is listening for both ora1 and ora2.

My tnsnames.ora file:

ORA2.LINBOX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = linbox)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora2)
)
)

ORA1.LINBOX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = linbox)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora2)
)
)

My sqlnet.ora file:
NAMES.DEFAULT_DOMAIN= localdomain
NAMES.DIRECTORY_PATH= (TNSNAMES)

My listener.ora file:
SID_LIST_ORA1_LIST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora1.linbox)
(ORACLE_HOME = /u01/apps/oracle/product/8.1.6)
(SID_NAME = ora1)
(SID_DESC =
(GLOBAL_DBNAME = ora2.linbox)
(ORACLE_HOME = /u01/apps/oracle/product/8.1.6)
(SID_NAME = ora2)
)
)

ORA1_LIST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = linbox)((PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)

init.ora params for ora1 db:
db_name = "ora1"
db_domain = linbox
instance_name = ora1
service_names = ora1
remote_login_passwordfile = exclusive
global_names = false

init.ora params for ora2 db:
db_name = "ora2"
db_domain = linbox
instance_name = ora2
service_names = ora2
remote_login_passwordfile = exclusive
global_names = false

Both dbs are on the same machine and I want to be able to access on db from another. Are there any other parameters I need to consider? It seems like it should be a very simple thing but I'm stumped.

[gnagesh]

Hi

check for the value in global_name

select * from global_name;

[padawin]

select * from global_domain; resulted in ->

GLOBAL_NAME
------------------
ORA2.LOCALDOMAIN

What do I do/check next?

[agganesh]

Originally posted by padawin
Hi all,
I have 2 dbs ora1 and ora2 on the same linux box (host = linbox) running 8.1.6.
ora1 service name = ora1
ora1 user = scott
ora2 service name = ora2
ora2 user = mary

If I want to create a public db link from mary's account (create public db link privilege already granted to mary) to scott's schema to acces the emp table. I tried the following:
SQL> create public database link ora1.linbox connect to scott identified by tiger using 'ora1';
Is this correct?

When I try to access the emp table I get the following error:
SQL> select count(*) from scott.emp@ora1.linbox;
ORA-12154: TNS: could not resolve service name

My tnsnames.ora file has the following entries:
ORA1.LINBOX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = linbox)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora1)
)
)

Am I doing something majorly wrong? Also how would scott grant select on emp to mary (user in anohter db)? Thanks

I have got exactly the same error message when I tried some time back.
I have replaced the connection string with the full address and it worked so you replace 'ora1' with the exact address in tnsnames.ora file.