Printable View
hi folks,
i am trying to set up a very simple streams flow from one db1 to db2. when i am creating a private dblink from db1 to db2 with the stream-admin user, the dblink is showing as not active.
in the init.ora file for both db1 and db2 i have kept the global_names = TRUE and i have kept the dblink name as db2.
can anyone plzz tell me where am i going wrong.
thanks and regards,
Prasenjit
Hi Prasenjit,
1. Check the network connection between the two databases by "tnsping"
2. Check the alias in the tnsnames.ora file which used in the dblink creation
Regards,
Nir
hi Nir,
thanks for the reply - sorry i didn't mention - i checked the connectivity with the following :
C:\Documents and Settings\121570>tnsping uii.world
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 21-SEP-20
04 13:07:50
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
C:\ORANT\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 172.18.34.133)(PORT = 1521))) (CONNECT_DATA = (SID = UII) (SERVER = DEDI
CATED)))
OK (60 msec)
it seems to be fine.
thanks and regards,
Prasenjit
Hi Prasenjit,
Send us please the error message you've got when you use the db link.
Nir
hi,
actually after doing the Stream Admin set-up on both the db1(pdsource) and db2(uii.world), i wanted to test the dblink thru OEM, where i am getting this.
but Nir, one thing i want to mention here - don't know whether it is relevant or not.
i have set up the db "pdsource" in my pc yes'day thru the OEM - the TNS entry for this comes like
PDSOURCE.TCSKOLKATA.CO.IN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TCS031301)(PORT = 1521))
)
(CONNECT_DATA =
(SID= PDSOURCE)
(SERVER = DEDICATED)
)
)
(cudn't undstand why this TCSKOLKATA part came - is it bcos of some network settings???????)
but for db2 (uii.world) - the TNS entry is like this
UII.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.34.133)(PORT = 1521))
)
(CONNECT_DATA =
(SID = UII)
(SERVER = DEDICATED)
)
)
anything to do with this???
thanks and regards,
Prasenjit
another thing, i am creating this dblink with stream-admin user and it is a private dblink - that's what the setup demands i guess - what if i create it as public with my schema user of "pdsource" ??? - but don't know whether the streams will work or not.
actually was referring to the doc -
[URL=http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96571/man_prep.htm#43906]
thanks and regards,
Prasenjit
check your sqlnet.ora parameter names.default_domain value..
and post your db link creation script, does the domain name appended
in your dblink name? You can also post errorcodes as what nir_s have asked.;)
hi,
checked the sqlnet.ora
NAMES.DEFAULT_DOMAIN = tcskolkata.co.in
SQL> create database link UII.WORLD
2 connect to strmadmin
3 identified by strmadminpd
4 using '(DESCRIPTION =
5 (ADDRESS_LIST =
6 (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.34.133)(PORT = 1521))
7 )
8 (CONNECT_DATA =(SERVICE_NAME=UII.WORLD))
9 )';
Database link created.
SQL> select * from [email protected]d;
select * from [email protected]d
*
ERROR at line 1:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor
this is what i am getting.
thanks and regards.
can use just use your database alias in your tnsnames.ora instead
of that whole description in that USING statement?
what is the db link created when you query DB_LINK in either user_db_links or dba_db_links?
you seems to be confused between db link name and db alias name.
i dropped the dblink and created it afresh with the connector description as below - i got the following result - cudn't undstand from where did UII.US.ORACLE.COM emerge ?????????
SQL> drop database link uii.world;
Database link dropped.
SQL> create database link UII.WORLD connect to strmadmin identified by strmadmi
npd using 'UII.WORLD';
Database link created.
SQL> select * from [email protected]d;
select * from [email protected]d
*
ERROR at line 1:
ORA-02085: database link UII.WORLD connects to UII.US.ORACLE.COM
SQL> select * from user_db_links;
DB_LINK
--------------------------------------------------------------------------------
USERNAME PASSWORD
------------------------------ ------------------------------
HOST
--------------------------------------------------------------------------------
CREATED
---------
UII.WORLD
STRMADMIN STRMADMINPD
UII.WORLD
21-SEP-04
SQL>
thanks and regards.
"you seems to be confused between db link name and db alias name."
db alias names default to the db name if no other name is mentioned for that during the db creation. so in my case, pdsource is both the db name and db alias name.
dblink name is the name of the db link that i create between "pdsource" and "uii.world" - which i have kept as 'uii.world" keeping the global_names parameter in the pfile = true.
am i wrong somewhere??????
end of the day for me guys ...keep me posted with any progress..i'll reengage tmrw morn. bye
hey prasen999,
I'm just wondering of where this tnsnames, sqlnet.ora that you are referring to. I'm not sure if you're aware that when creating db links, the tnsnames, sqlnet that is being dealt with is the one located between two servers(source and destination) and not the client. Please correct me guys if im wrong with this.
While creating DBlink between any 2 servers the important thing to note after keeping the global_names=true is to keep the global_names parameter like either 'world' or 'us.oracle.com' or what ever u like on the both servers as same which is not in your case.So use the Alter database command and keep the global_names same on both the servers and then create the fresh DBlinks.Then only u can see the link as active.
exactly, and that is why I want prasen999 to check his sqlnet.ora parameter default_domain. Because it is the one appended to the db link name after creating the dblink.
______________________
prasen999
"cudn't undstand from where did UII.US.ORACLE.COM emerge ?????????"
_______________________
yes , that is true that in sqlnet.ora the default domain in the 2 servers are different - so does changing the sqlnet.ora default domain to be the same in both the dbs solve the issue?
thanks and regards
Thats probably because the global name is UII.US.ORACLE.COM.
Check what ur global name of the database is.
Do a "select * from global_name;" and rename the global name.
guys, having a doubt - i see through OEM that my database is PDSOURCE.TCSKOLKATA.CO.IN whereas it is UII.WORLD - and that's bcoz my domain name in the sqlnet.ora is TCSKOLKATA.CO.IN in one case whereas it WORLD in the other.
so can one of u plzz list me the steps of what i have to do get that domain in both the cases to be the same.
plzz bear my ignorance.
thanks and regards.
i think that will involve a recreation of the control file of PDSOURCE.
No, you are not going to recreate the control file.Quote:
Originally posted by prasen999
i think that will involve a recreation of the control file of PDSOURCE.
Hold on for a while I will give you the work-around.
considering you're not going to touch global_names.Quote:
Originally posted by prasen999
yes , that is true that in sqlnet.ora the default domain in the 2 servers are different - so does changing the sqlnet.ora default domain to be the same in both the dbs solve the issue?
thanks and regards
I will give you a work around just to simplify my view and to work things out.
omit the value of default_domain in SQLNET.ORA in both servers, or might as well rename SQLNET.ORA to SQLNET.BAK
edit the tnsnames of both servers(please post which server did you create your dblink).
1st server: tnsnames.ora(also include tnsnames of 2nd server)
PDSOURCE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TCS031301)(PORT = 1521))
)
(CONNECT_DATA =
(SID= PDSOURCE)
(SERVER = DEDICATED)
)
)
2nd server: tnsnames.ora tnsnames.ora(also include tnsnames of 1nd server)
UII =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.34.133)(PORT = 1521))
)
(CONNECT_DATA =
(SID = UII)
(SERVER = DEDICATED)
)
)
Then recreate your db link like(assuming you are creating db link from 2nd server):
create database link YOURDBLINK_UII
connect to strmadmin
identified by strmadminpd
using PDSOURCE
removing the sqlnet.ora default_domain value, i am not being able to login
C:\>sqlplus strmadmin/strmadminpd@pdsource
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 22 13:38:04 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve service name
moreover, the box having the uii db has other dbs also which are in use by the team - so it wont be prudent to tamper with that.
thanks and regards.
that's because you didn't change the tnsnames entry from
PDSOURCE.TCSKOLKATA.CO.IN to PDSOURCE.
"moreover, the box having the uii db has other dbs also which are in use by the team - so it wont be prudent to tamper with that."
your client connection to your server is different with your server/server connection. As long as the listener is up and the tnsnames, sqlnet in client is well configured, then it should just be fine.
Anyway, if you decide not to touch your tnsnames, sqlnet in your server try creating the db link like this(assuming you are creating your db in UI.WORLD):
create database link YOURDBLINK_UII
connect to strmadmin
identified by strmadminpd
using PDSOURCE.TCSKOLKATA.CO.IN
Instead of strugling further follow the steps.
1.Alter database rename global_names to xxx
on both the servers.
2.create database link abcd.xxx connect to user_name identified by password using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = your host )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = database_sid)
)
)';
guys, sorry to say .. but now i am thoroughly confused - i have done something for which the link seems to work .. i am pasting what i had done -
i checked the sqlnet.ora of the other server and saw that default_domnin = tcskolkata.co.in
so i let the default_domain in my box to be tcskolkata.co.in
then as per reydp's suggestion .. i changed the tnsnames connect-desc riptor to pdsource.world
then according to akhadar i changed the global_name of both the databases to pdsource (but here the domain that got appended was different)
then i recreated the database link - which seemed to work fine.
this i executed from [email protected]d
SQL> select * from uii.ar_classes@pdsource_uii;
DEP COURSE
--- ----------
DESCRIPTION
------------------------------------------------------------------------------
MAX_STUDENTS CURRENT_STUDENTS NUM_CREDITS ROOM_ID CREDITS_ALLOWED
------------ ---------------- ----------- ---------- ---------------
PHY 1
OPTICS
100 75 9 100 9
BUT INTERESTINGLY IN THE OEM THE DATABASE LINK STILL TELLS ME NOT ACTIVE.
hey guys, can u give me a good info regarding these things -
when i am creating a link between 2 databases how does global_name, global_names, domain come into the picture and play a part
thanks and regards
It was really my bad akhadar,Quote:
Originally posted by akhadar
Instead of strugling further follow the steps.
1.Alter database rename global_names to xxx
on both the servers.
2.create database link abcd.xxx connect to user_name identified by password using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = your host )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = database_sid)
)
)';
Though I mention earlier that without touching global_names, the preceeding post that i have made is for prasen999 to understand what is the usual oracle behaviour for db link creation when global_names is not used.
prasen999,
You are close, it will also help you if you just read documents for proper and easier understanding.