Home » Server Options » Streams & AQ » Db link did not work between 2 dbs (Source db: WindowsXP/Oracle10.2.0.4. Downstream DB: Linux x86/Oracle10.2.0.1)
Db link did not work between 2 dbs [message #381789] Mon, 19 January 2009 21:42 Go to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!

I've got one problem. When I configured initialization parameters, streamadmin user, queue,.. and then, I created one database link from source to downstream db, but I can not selected any data from db_link.

strmadmin@META> create database link dbstream
  2  connect to strmadmin identified by tuananhtran
  3  using 'stream';

Database link created.

strmadmin@META> select * from dual@dbstream;
select * from dual@dbstream
                   *
ERROR at line 1:
ORA-02085: database link DBSTREAM connects to STREAM.REGRESS.RDBMS.DEV.US.ORACLE
.COM


strmadmin@META> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\>


Of course, the sid in tnsnames.ora works normally

C:\>cat %ORACLE_HOME%\network\admin\tnsnames.ora
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\meta\network
\admin\tnsnames.ora
# Generated by Oracle configuration tools.

VNP66 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 190.10.10.66)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = vnp)
    )
  )

TINHCUOC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 190.10.10.67)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = tinhcuoc)
    )
  )

RATING2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 190.10.10.51)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = RATING.COM.VN)
      (INSTANCE_NAME = RATING2)
    )
  )

RATING1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 190.10.10.51)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = RATING.COM.VN)
      (INSTANCE_NAME = RATING1)
    )
  )

RATING =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 190.10.10.52)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 190.10.10.54)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = rating.com.vn)
    )
  )

META =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tuananhtran)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = meta)
    )
  )
STREAM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.149.33.8)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stream)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
C:\>tnsping stream

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 20-JAN-2
009 10:39:52

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
c:\oracle\product\10.2.0\meta\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 10.149.33.8)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stream)))
OK (20 msec)

C:\>

2 initialization parameters global_name in source/downstream database was set to be TRUE.
Do you suggest me something to solve this?
Thank you!
Re: Db link did not work between 2 dbs [message #381790 is a reply to message #381789] Mon, 19 January 2009 22:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If default domain is not specified, REGRESS.RDBMS.DEV.US.ORACLE
.COM will the default.
Set a value for db_domain.
Since you have global_names=true, you need to have a value for global_name. If nothing is specified, it will take a domain value of REGRESS.RDBMS.DEV.US.ORACLE.COM.



Re: Db link did not work between 2 dbs [message #381794 is a reply to message #381790] Mon, 19 January 2009 22:32 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Mahesh!

But I set the initialization parameter db_domain in both of source/downstream database.

In source database.

C:\>strmadmin

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 20 11:27:40 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

strmadmin@META> show parameter db_domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      vinaphone.vn

strmadmin@META> select * from dual@stream;
select * from dual@stream
                   *
ERROR at line 1:
ORA-02085: database link STREAM connects to STREAM.REGRESS.RDBMS.DEV.US.ORACLE.C
OM


strmadmin@META>


In downstream database
[oracle@oracle ~]$ sqlplus strmadmin/tuananhtran

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 20 05:40:28 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

strmadmin@STREAM> show parameter db_domain

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_domain                            string                           vinaphone.vn
strmadmin@STREAM> create database link dbmeta 
  2  connect to strmadmin identified by tuananhtran
  3  using 'meta';

Database link created.

strmadmin@STREAM> select * from dual@dbmeta;
select * from dual@dbmeta
                   *
ERROR at line 1:
ORA-02085: database link DBMETA.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to META


strmadmin@STREAM> create database link meta.vinaphone.vn
  2  connect to strmadmin identified by tuananhtran
  3  using 'meta';

Database link created.

strmadmin@STREAM> select * from dual@meta.vinaphone.vn
  2  /
select * from dual@meta.vinaphone.vn
                   *
ERROR at line 1:
ORA-02085: database link META.VINAPHONE.VN connects to META


strmadmin@STREAM> ! tnsping meta

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 20-JAN-2009 05:43:16

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.149.33.17)
(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = meta)))
OK (10 msec)

strmadmin@STREAM> 


May you clarify more?
Thanks again!

[Updated on: Tue, 20 January 2009 01:43] by Moderator

Report message to a moderator

Re: Db link did not work between 2 dbs [message #381797 is a reply to message #381794] Mon, 19 January 2009 23:02 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I think I've got some mistake
In source database, the global_name is
sys@META> show parameter global_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE

sys@META> select * from global_name
  2  /

GLOBAL_NAME
--------------------
META

sys@META>


In downstream database, the global_name is clean
strmadmin@STREAM> show parameter global_name

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
global_names                         boolean                          TRUE
strmadmin@STREAM> select * from global_name
  2  /

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
STREAM.REGRESS.RDBMS.DEV.US.ORACLE.COM


But I can not find the how will I resolve..
Re: Db link did not work between 2 dbs [message #381827 is a reply to message #381789] Tue, 20 January 2009 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://forums.oracle.com/forums/thread.jspa?threadID=452664
http://forums.oracle.com/forums/thread.jspa?threadID=672037

These are the 2 first entries I got when I type REGRESS.RDBMS.DEV.US.ORACLE.COM in Google.
There are about 6788 other ones.

Regards
Michel

Re: Db link did not work between 2 dbs [message #381838 is a reply to message #381827] Tue, 20 January 2009 01:30 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Oh, my dear Michel! I want to kiss you now.

Of course, before I'd posted here, I searched in google, but with "why database link cannot work with db_domain parameter" keyword, and I've read oracle document that matched this keyword till now, very long documentations.

I re-configured some thing which following your pointed topic - OTN's forum. It - database link - retrieved data successfully. Thank you!

I edited sqlnet.ora file & rename global_name.

In source database
C:\>cat %ORACLE_HOME%\network\admin\sqlnet.ora <<EOF
> SQLNET.DEFAULT_DOMAIN=vinaphone.vn
^D
C:\> exit
sys@META> alter database rename global_name
  2  to meta.vinaphone.vn
  3  /

Database altered.

sys@META> select * from global_name
  2  /

GLOBAL_NAME
------------------------------
META.VINAPHONE.VN

sys@META> host lsnrctl reload

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 20-JAN-2009 14:25
:30

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tuananhtran)(PORT=1521))
)
The command completed successfully

sys@META>


In downstream database.
sys@STREAM> ! cat $ORACLE_HOME/network/admin/sqlnet.ora <<EOF
> SQLNET.DEFAULT_DOMAIN=vinaphone.vn
^D
sys@STREAM> ! lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 20-JAN-2009 07:15:00

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.149.33.8)(PORT=1521)))
The command completed successfully

sys@STREAM> alter database rename global_name
  2  to stream.vinaphone.vn
  3  /

Database altered. 

sys@STREAM> select * from global_name
  2  /

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
STREAM.VINAPHONE.VN


Test it.

In source database
sys@META> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - P
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>strmadmin

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 20 14:28:53 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

strmadmin@META> create database link stream
  2  connect to strmadmin identified by tuananhtran
  3  using 'stream';

Database link created.

strmadmin@META> select * from dual@stream;

D
-
X

strmadmin@META>


In downstream database.
sys@STREAM> create database link meta connect to
  2  strmadmin identified by tuananhtran
  3  using 'meta';  

Database link created.

sys@STREAM> select * from dual@meta;

D
-
X

sys@STREAM> drop database link meta;

Database link dropped.

sys@STREAM> conn strmadmin/tuananhtran
Connected.
strmadmin@STREAM> create database link meta 
  2  connect to strmadmin identified by tuananhtran
  3  using 'meta';

Database link created.

strmadmin@STREAM> select * from dual@meta;

D
-
X

strmadmin@STREAM>


Thank you, again!

[Updated on: Tue, 20 January 2009 01:33]

Report message to a moderator

Re: Db link did not work between 2 dbs [message #381845 is a reply to message #381838] Tue, 20 January 2009 01:44 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback and detailed solution.

Regards
Michel

[Updated on: Tue, 20 January 2009 01:45]

Report message to a moderator

Previous Topic: OS compatabilities in streams
Next Topic: Streams data conflict
Goto Forum:
  


Current Time: Thu Mar 28 05:52:53 CDT 2024