| Db link did not work between 2 dbs [message #381789] |
Mon, 19 January 2009 21:42  |
trantuananh24hg
Messages: 743
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 #381794 is a reply to message #381790] |
Mon, 19 January 2009 22:32  |
trantuananh24hg
Messages: 743
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 |
trantuananh24hg
Messages: 743
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 #381838 is a reply to message #381827] |
Tue, 20 January 2009 01:30 |
trantuananh24hg
Messages: 743
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
