-
need help creating db links
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
-
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
-
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.
-
Hi
check for the value in global_name
select * from global_name;
Nagesh
-
select * from global_domain; resulted in ->
GLOBAL_NAME
------------------
ORA2.LOCALDOMAIN
What do I do/check next?
-
Re: need help creating db links
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.
Ganesh
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|