error database link from 10g to 11g [message #452589] |
Thu, 22 April 2010 22:25 |
balv
Messages: 7 Registered: January 2010
|
Junior Member |
|
|
I created a database link from a database 11g to 10g and it OK
but if reverse (I want select data 11g from 10g) with similary way then had an error returned:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Specific:
On database 11g:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
)
On database 10g:
listener:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
TNSNAME
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLBALV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclbalv.alibaba.vn)
)
)
When I select
SQL> select tenuser from tbluser@db_link_orclbalv.anhnt;
select tenuser from tbluser@db_link_orclbalv.anhnt
then met that error
And with bd_link created by:
create public database link db_link_orclbalv.anhnt connect to user identified by pass using 'orclbalv';
thanks!
Sincerely!
|
|
|
Re: error database link from 10g to 11g [message #452591 is a reply to message #452589] |
Thu, 22 April 2010 22:33 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
from V10 system try to access V11 system using sqlplus
CUT whole sqlplus session showing the SQL & the error.
PASTE results back here
Then from V11 system issue following command
lsnrctl service
post tail end of listener.log file from V11 showing failure from V10
[Edit BY Ram Typo]
[Updated on: Thu, 22 April 2010 23:08] by Moderator Report message to a moderator
|
|
|
Re: error database link from 10g to 11g [message #452653 is a reply to message #452591] |
Fri, 23 April 2010 03:26 |
balv
Messages: 7 Registered: January 2010
|
Junior Member |
|
|
BlackSwan wrote on Fri, 23 April 2010 10:33from V10 system try to access V11 system using sqlplus
CUT whole sqlplus session showing the SQL & the error.
PASTE results back here
Then from V11 system issue following command
lsnrctl service
post tail end of listener.log file from V11 showing failure from V10
[Edit BY Ram Typo]
ON 10g
Enter user-name: user/pass@orclbalv
ERROR:
ORA-12541: TNS:no listener
ON 11g
C:\Users\balv>lsnrctl service
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-APR-2010 15:15
:51
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521))
)
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error
|
|
|
|
Re: error database link from 10g to 11g [message #452716 is a reply to message #452712] |
Fri, 23 April 2010 09:59 |
balv
Messages: 7 Registered: January 2010
|
Junior Member |
|
|
BlackSwan wrote on Fri, 23 April 2010 21:02perhaps you should while logged onto V11 system do the following
lsnrctl start
post result from command above
Sorry I didn't know Listener on 11g automatic stoped.
I started it again then check:
On 11g
C:\Users\balv>lsnrctl service
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-APR-2010 21:41
:39
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
On 10g
Enter user-name: user/pass@orclbalv
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
|
|
|
|
Re: error database link from 10g to 11g [message #452720 is a reply to message #452717] |
Fri, 23 April 2010 10:34 |
balv
Messages: 7 Registered: January 2010
|
Junior Member |
|
|
Check on 11g:
C:\Users\balv>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-APR-2010 22:29
:19
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 23-APR-2010 21:53:49
Uptime 0 days 0 hr. 35 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\balv\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\balv\diag\tnslsnr\balv-PC\listener\alert\log.xml
Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Moreover,I checked so
SQL> SELECT * FROM GLOBAL_NAME;
GLOBAL_NAME
ORCLBALV.ALIBABA.VN
SQL> show parameter service_name;
NAME TYPE VALUE
service_names string orclbalv.alibaba.vn
SQL> show parameter global_names;
NAME TYPE VALUE
global_names boolean FALSE
And on 10g
C:\Documents and Settings\Administrator>tnsping orclbalv
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 23-APR-2010 22:04:32
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.
1.2)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orclbalv.alibaba.vn)))
OK (20 msec)
C:\Documents and Settings\Administrator>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 23 22:04:52 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: user/pass@orclbalv
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
[Updated on: Fri, 23 April 2010 10:44] Report message to a moderator
|
|
|
|
Re: error database link from 10g to 11g [message #452726 is a reply to message #452722] |
Fri, 23 April 2010 10:50 |
balv
Messages: 7 Registered: January 2010
|
Junior Member |
|
|
C:\Users\balv>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 23 22:46:33 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
|
|
|
|
Re: error database link from 10g to 11g [message #452733 is a reply to message #452728] |
Fri, 23 April 2010 11:49 |
balv
Messages: 7 Registered: January 2010
|
Junior Member |
|
|
yes! all content I showed on the first topic:
listener.ora on 11g
# listener.ora Network Configuration File: C:\app\balv\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\app\balv\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\app\balv\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
)
ADR_BASE_LISTENER = C:\app\balv
and tnsname.ora on 10g
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orclbalv =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orclbalv.alibaba.vn)
)
)
Thank you very much!
|
|
|
|
Re: error database link from 10g to 11g [message #452736 is a reply to message #452734] |
Fri, 23 April 2010 12:27 |
balv
Messages: 7 Registered: January 2010
|
Junior Member |
|
|
<msg time='2010-04-23T23:00:41.112+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>23-APR-2010 23:00:41 * ping * 0
</txt>
</msg>
<msg time='2010-04-23T23:13:59.839+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>System parameter file is C:\app\balv\product\11.2.0\dbhome_1\network\admin\listener.ora
</txt>
</msg>
<msg time='2010-04-23T23:14:00.352+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>Log messages written to c:\app\balv\diag\tnslsnr\balv-PC\listener\alert\log.xml
</txt>
</msg>
<msg time='2010-04-23T23:14:00.361+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>Trace information written to c:\app\balv\diag\tnslsnr\balv-PC\listener\trace\ora_2344_2920.trc
</txt>
</msg>
<msg time='2010-04-23T23:14:00.371+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>Trace level is currently 0
</txt>
</msg>
<msg time='2010-04-23T23:14:00.381+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>
</txt>
</msg>
<msg time='2010-04-23T23:14:00.649+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>Started with pid=2344
</txt>
</msg>
<msg time='2010-04-23T23:14:00.739+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
</txt>
</msg>
<msg time='2010-04-23T23:14:00.785+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521)))
</txt>
</msg>
<msg time='2010-04-23T23:14:02.538+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>Listener completed notification to CRS on start
</txt>
</msg>
<msg time='2010-04-23T23:14:02.540+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
</txt>
</msg>
<msg time='2010-04-23T23:30:12.782+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>23-APR-2010 23:30:12 * ping * 0
</txt>
</msg>
<msg time='2010-04-23T23:30:45.298+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>23-APR-2010 23:30:45 * (CONNECT_DATA=(SERVICE_NAME=orclbalv.alibaba.vn)(CID=(PROGRAM=C:\oracle\product\10.2.0\db_1\bin\sqlplus.exe)(HOST=BALV-56E67EF52D)(US ER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.3)(PORT=1190)) * establish * orclbalv.alibaba.vn * 12514
</txt>
</msg>
<msg time='2010-04-23T23:30:45.339+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
</txt>
</msg>
<msg time='2010-04-23T23:32:27.895+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>23-APR-2010 23:32:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=balv))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
</txt>
</msg>
<msg time='2010-04-23T23:32:49.540+07:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='BALV-PC'
host_addr='fe80::9ddf:3e49:fb30:6e7f%21'>
<txt>23-APR-2010 23:32:49 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=balv))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * services * 0
</txt>
</msg>
V11 setup on Window 7 professional
V10 setup on Window XP professional(Vitual Machine)
|
|
|
|
|