Home » RDBMS Server » Server Administration » error database link from 10g to 11g
error database link from 10g to 11g [message #452589] Thu, 22 April 2010 22:25 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
balv
Messages: 7
Registered: January 2010
Junior Member
BlackSwan wrote on Fri, 23 April 2010 10:33
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]


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 #452712 is a reply to message #452653] Fri, 23 April 2010 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
perhaps you should while logged onto V11 system do the following

lsnrctl start


post result from command above
Re: error database link from 10g to 11g [message #452716 is a reply to message #452712] Fri, 23 April 2010 09:59 Go to previous messageGo to next message
balv
Messages: 7
Registered: January 2010
Junior Member
BlackSwan wrote on Fri, 23 April 2010 21:02
perhaps 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 #452717 is a reply to message #452716] Fri, 23 April 2010 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener.  This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action: 
//  - Wait a moment and try to connect a second time.
//  - Check which services are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SERVICE_NAME parameter in the connect descriptor of the
//    net service name used specifies a service known by the listener.
//  - If an easy connect naming connect identifier was used, check that
//    the service name specified is a service known by the listener.
//  - Check for an event in the listener.log file.


From V11 system doing following command

lsnrctl status

post results back here
Re: error database link from 10g to 11g [message #452720 is a reply to message #452717] Fri, 23 April 2010 10:34 Go to previous messageGo to next message
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 #452722 is a reply to message #452720] Fri, 23 April 2010 10:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Is V11 DB really online?

from V11 system open command window
CUT lines below & PASTE into command window

set
sqlplus
/ as sysdba
select * from v$version;
exit

CUT results from command window & PASTE all back here

Re: error database link from 10g to 11g [message #452726 is a reply to message #452722] Fri, 23 April 2010 10:50 Go to previous messageGo to next message
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 #452728 is a reply to message #452726] Fri, 23 April 2010 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Hmmm.......

post content of following files

C:\app\balv\product\11.2.0\dbhome_1\network\admin\listener.ora

& I am not sure where to find it but post content of "hosts" file
Re: error database link from 10g to 11g [message #452733 is a reply to message #452728] Fri, 23 April 2010 11:49 Go to previous messageGo to next message
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 #452734 is a reply to message #452733] Fri, 23 April 2010 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I am not sure where to find it but post content of "hosts" file from V11 system

Many thing appear to be correct.


post the last 100 - 200 lines from following file

c:\app\balv\diag\tnslsnr\balv-PC\listener\alert\log.xml

On V11 DB is up & online along with listener.
The problem is the listener does not know the DB exists.
The DB should auto-register with listener every minute.

Does any type of Operating System Virtualization exist on V11 system?
Re: error database link from 10g to 11g [message #452736 is a reply to message #452734] Fri, 23 April 2010 12:27 Go to previous messageGo to next message
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)
Re: error database link from 10g to 11g [message #496111 is a reply to message #452734] Thu, 24 February 2011 10:12 Go to previous messageGo to next message
ssellers
Messages: 1
Registered: February 2011
Junior Member
I am experiencing the same issues and my v11 db *is* on a virtualized os. Do you have particular insight into special considerations for virtualized os's and/or documentation that could help me troubleshoot the problem. I am not the dba that owns the db (which means I don't have access to anything on the db server) but am very interested in getting our 10g dblink to our 11g db functional.

Thank you.
Re: error database link from 10g to 11g [message #496116 is a reply to message #496111] Thu, 24 February 2011 10:29 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Previous Topic: Migrating Oracle 9i to 10g
Next Topic: Data File/Table Relation
Goto Forum:
  


Current Time: Wed May 08 20:17:14 CDT 2024