Solved

Database link to AS400

Posted on 2012-04-11
40
2,278 Views
Last Modified: 2012-04-12
Hello everyone, i configured ODBC for iseries on my oracle machine

i tested to make sure i can connect with isql but when i create a database link i am told

An error was encountered performing the requested operation:
ORA-12541: TNS:no listener
12541. 00000 -  "TNS:no listener"
*Cause:    The connection request could not be completed because the listener
           is not running.
*Action:   Ensure that the supplied destination address matches one of
           the addresses used by the listener - compare the TNSNAMES.ORA entry with
           the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
           go by way of an Interchange). Start the listener on the remote machine.
Vendor code 12541

Open in new window


my tnsname.ora file is as follows

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )



AS400 =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.mycompany.com)(port=1521))
(CONNECT_DATA =
(SID = AS400)
)
(HS=OK)
)

Open in new window


this is my listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(PORT = 1521))
    )
  )


SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=AS400)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/xe/lib:/opt/ibm/iSeriesAccess/lib)
)
)

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=orcl.mycompany.com)(PORT= 1521))
)

DEFAULT_SERVICE_LISTENER = (XE)

Open in new window


my odbc.ini
[root@orcl admin]# cat /etc/odbc.ini
[AS400]
Description     = iSeries Access ODBC Driver
Driver          = iSeries Access ODBC Driver
System          = 192.168.3.5
UserID          = username
Password        = userpass
Naming          = 0
DefaultLibraries  = RAPFILE
Database          =
ConnectionType    = 0
CommitMode        = 2
ExtendedDynamic   = 1
DefaultPkgLibrary = QGPL
DefaultPackage    = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression  = 1
LibraryView           = 0
AllowUnsupportedChar  = 0
ForceTranslation      = 0
Trace           = 1
DSN             = AS400

Open in new window


and odbcinst.ini
[iSeries Access ODBC Driver]
Description             = iSeries Access for Linux ODBC Driver
Driver          = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup           = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
NOTE1           = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
NOTE2           = the following Driver64/Setup64 keywords will provide that support.
Driver64                = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup64         = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading               = 2
DontDLClose             = 1
UsageCount              = 1

[iSeries Access ODBC Driver 64-bit]
Description             = iSeries Access for Linux 64-bit ODBC Driver
Driver          = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup           = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading               = 2
DontDLClose             = 1
UsageCount              = 1

Open in new window

0
Comment
Question by:gnivkor
  • 22
  • 17
40 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37832860
The tnsnames and listener you posted are set up for connecting to another Oracle database.

Below is a link for creating a database link between Oracle and SQL Server but the steps to DB2 should be the same:
http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm
0
 

Author Comment

by:gnivkor
ID: 37832974
the thing is i dont see any difference between the setup in the link you sent me and the setup i have
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37833004
I guess I didn't look at it close enough.

From the oracle database server command prompt what is the results of: lsnrctl status
0
 

Author Comment

by:gnivkor
ID: 37833019
status XE gives me

LSNRCTL> status xe
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.mycompany.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                11-APR-2012 09:09:28
Uptime                    0 days 0 hr. 53 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

Open in new window




status as400 gives me

 status as400
Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=58.11.0.96)(PORT=1521)))
TNS-12535: TNS:operation timed out
 TNS-12560: TNS:protocol adapter error
  TNS-00505: Operation timed out
   Linux Error: 110: Connection timed out

Open in new window


I am not sure why it's trying to look for an external IP address
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37833073
I missed this the first time as well.

In your listener.ora file you have repeated the sections
SID_LIST_LISTENER and LISTENER.

There should only be a single entry with multiple sub entries.

Move the AS400 entry to the SID_LIST of the first entry.

An example of multiple entries can be found in the docs.
http://docs.oracle.com/cd/B14117_01/network.101/b10776/listener.htm
0
 

Author Comment

by:gnivkor
ID: 37833080
i fixed the external ip address by adding the ip of the oracle server to the /etc/hosts
 still not able to create database link
0
 

Author Comment

by:gnivkor
ID: 37833370
yeah, i cannot get it going when i tried to follow the instructions or your link
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37833375
What does your listener.ora file look like now?
0
 

Author Comment

by:gnivkor
ID: 37833451
#listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc))
    (SID_DESC=
(SID_NAME=AS400)
	(ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe)
	(PROGRAM=dg4odbc)
	(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/xe/lib:/opt/ibm/iSeriesAccess/lib)
)
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(PORT = 1521))
    )
  )



DEFAULT_SERVICE_LISTENER = (XE)

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37833510
Syntax looks ok on a quick scan.  What happens when you stop and start the listener?  What does the status show after the restart?
0
 

Author Comment

by:gnivkor
ID: 37833612
when i created a database link it seems that i was linking to my own oracle database instead of the as400
0
 

Author Comment

by:gnivkor
ID: 37833623
the previous listeners.ora did not work, oracle would not start

the code for the one i have working now is

# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=armcdb2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/xe/lib:/opt/ibm/iSeriesAccess/lib)
)
)

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=orcl.mycompany.com)(PORT= 1521))
)
DEFAULT_SERVICE_LISTENER = (XE)

Open in new window




oracle starts with this one... but..when i created a database link it seems that i was linking to my own oracle database instead of the as400
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37833693
>>the code for the one i have working now is

The problem with this one is the syntax is wrong.  Just because the listener starts does not mean it is 'working'.   AS400 isn't being listened for.  Until it is, it will never work.

For the merged one you say wouldn't start, what was the error?
0
 

Author Comment

by:gnivkor
ID: 37833770
for starters, oracle doesnt start when i restart the service, port 1521 and 8080 do not come back up...

when i do a status check for xe and as400 on lsnrctl, i get

LSNRCTL> status xe
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.mycompany.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
LSNRCTL> status as400
Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.59)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
LSNRCTL>

Open in new window



When i check the tnslistener trace log, i get

System parameter file is /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Trace information written to /u01/app/oracle/diag/tnslsnr/orcl/listener/trace/ora_2584_139763294930688.trc
Trace level is currently 0

Started with pid=2584
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.mycompany.com)(PORT=1521)))
TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
  TNS-00515: Connect failed because target host or object does not exist
   Linux Error: 99: Cannot assign requested address
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
[root@orcl bin]# /u01/app/oracle/diag/tnslsnr/orcl/listener/trace/listener.log

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37833976
Is your oracle server named orcl.mycompany.com?
0
 

Author Comment

by:gnivkor
ID: 37834007
yes.. that is the name it resolves to on the network
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37834349
Does this machine use DHCP for it's IP address?

Let's put the listener back to original and at least get it started.  Use the code below and stop/start the listener.

Let me know if it starts or the error if it doesn't.

# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

Open in new window

0
 

Author Comment

by:gnivkor
ID: 37834402
okay, thats where i am at,

my listeners.ora now reflects the the one you just posted. everything started fine
0
 

Author Comment

by:gnivkor
ID: 37834442
by the way, these are the instructions i was following

http://mark.the-fennells.org/?p=410
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37834498
Cool now try this one:

# listener.ora Network Configuration File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
    (SID_DESC=
      (SID_NAME=armcdb2)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/xe/lib:/opt/ibm/iSeriesAccess/lib)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

Open in new window



The only thing that might be sketchy is dg4odbc.  All other referneces to this on Oracle references hsodbc.  I'm afraid I don't know the difference.
0
 

Author Comment

by:gnivkor
ID: 37834525
LSNRCTL> status xe
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.mycompany.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
LSNRCTL> status as400
Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.5)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
LSNRCTL>

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37834531
I would like to see the output of the start command.
0
 

Author Comment

by:gnivkor
ID: 37834538
the one thing that i see very weird is that when i do status as400, its literally pointing to the ip address of the as400 which is 192.168.3.5, i thought it would be pointing to the IP address of the oracle server itself
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37834566
>>when i do status as400

"status somename" is looking for a listener called somename:
http://docs.oracle.com/cd/E11882_01/network.112/e10835/lsnrctl.htm#CIHGHCGJ

Since you don't have a listener called as400, it likely just went out and did a nslookup and went looking for a lisneter on the box called as400.


Still waiting for the start output from the last listener.ora file I posted.
0
 

Author Comment

by:gnivkor
ID: 37834570
actually, i forgot to edit something out, it works now.

when i do status xe, it goes through correctly, when i do status as400, it points to the ip address of my as400 but it gives an error

LSNRCTL> status xe
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.mycompany.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                11-APR-2012 14:44:10
Uptime                    0 days 0 hr. 0 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "as400" has 1 instance(s).
  Instance "as400", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> status as400
Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.5)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
LSNRCTL>

Open in new window



the results of lsnrctl start

LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/orcl/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                11-APR-2012 14:46:32
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "as400" has 1 instance(s).
  Instance "as400", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37834580
>>when i do status as400

Read my last post about "status somename".  I'll wait for a response before posting back.
0
 

Author Comment

by:gnivkor
ID: 37834646
I get it now, understood. thanks, below is the output of start
LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/orcl/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                11-APR-2012 14:46:32
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "as400" has 1 instance(s).
  Instance "as400", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
                                            

Open in new window


I know that odbc settings are working because i can connect to the as400 when i test with isql as400, but when i created a link to the database using

"CREATE PUBLIC DATABASE LINK "as400.mycompany.com" CONNECT TO "username" IDENTIFIED BY mypassword USING 'as400';

Open in new window


after i create the database link and test it,

i get

An error was encountered performing the requested operation:

ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 -  "TNS:could not resolve the connect identifier specified"
*Cause:    A connection to a database or other service was requested using
           a connect identifier, and the connect identifier specified could not
           be resolved into a connect descriptor using one of the naming methods
           configured. For example, if the type of connect identifier used was a
           net service name then the net service name could not be found in a
           naming method repository, or the repository could not be
           located or reached.
*Action:   
           - If you are using local naming (TNSNAMES.ORA file):
           - Make sure that "TNSNAMES" is listed as one of the values of the
           NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA)
           - Verify that a TNSNAMES.ORA file exists and is in the proper
           directory and is accessible.
           - Check that the net service name used as the connect identifier
           exists in the TNSNAMES.ORA file.
           - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
           file.  Look for unmatched parentheses or stray characters. Errors
           in a TNSNAMES.ORA file may make it unusable.
           - If you are using directory naming:
           - Verify that "LDAP" is listed as one of the values of the
           NAMES.DIRETORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA).
           - Verify that the LDAP directory server is up and that it is
           accessible.
           - Verify that the net service name or database name used as the
           connect identifier is configured in the directory.
           - Verify that the default context being used is correct by
           specifying a fully qualified net service name or a full LDAP DN
           as the connect identifier
           - If you are using easy connect naming:
           - Verify that "EZCONNECT" is listed as one of the values of the
           NAMES.DIRETORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA).
           - Make sure the host, port and service name specified
           are correct.
           - Try enclosing the connect identifier in quote marks.
Vendor code 12154

Open in new window

0
 

Author Comment

by:gnivkor
ID: 37834660
this is my tnsname.ora
[root@orcl admin]# vim tnsnames.ora
# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

AS400 =
(DESCRIPTION =
        (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(port =1521))
        (CONNECT_DATA = (SID = AS400)
)
(HS=OK)
)
~

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37834669
Now that as400 is being listened for, on the the tnsnames.ora.

post the results from a command prompt: tnsping as400
0
 

Author Comment

by:gnivkor
ID: 37834678
tnsping results

[root@orcl init.d]# tnsping as400

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 11-APR-2012 15:05:52

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.beyerfarms.com)(port =1521)) (CONNECT_DATA = (SID = AS400)
TNS-12533: TNS:illegal ADDRESS parameters
[root@orcl init.d]#

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37834691
Just saw a typo in your tnsnames.ora file.

Delete your AS400 entry and try this one:
AS400 =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(port =1521))
   (CONNECT_DATA =
      (SID = AS400)
   )
(HS=OK)
)
0
 

Author Comment

by:gnivkor
ID: 37834713
still getting same tnsping error

this is my current tnsnames.ora
# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

AS400 =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(port =1521))
   (CONNECT_DATA =
      (SID = AS400)
   )
(HS=OK)
)

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37834728
Please post the output of the tnsping.
0
 

Author Comment

by:gnivkor
ID: 37834756
[root@orcl init.d]# tnsping as400

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 11-APR-2012 15:23:26

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(port =1521)) (CONNECT_DATA = (SID = AS400))
TNS-12533: TNS:illegal ADDRESS parameters
[root@orcl init.d]#

Open in new window

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37834781
tnsnames.ora is picky... try this one (space in column 1):

AS400 =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(port =1521))
   (CONNECT_DATA =
      (SID = AS400)
   )
      (HS=OK)
   )
0
 

Author Comment

by:gnivkor
ID: 37834836
that one worked... let me try to see if i can connect to as400 now
0
 

Author Comment

by:gnivkor
ID: 37834857
almost there... i created a new link, and tried to select from the link

the response was

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
(unixODBC)(Driver Manager) Data Source Name {I}
ORA-02063: preceding 2 lines from AS400.MYCOMPANY.COM
28500. 00000 -  "connection from ORACLE to a non-Oracle system returned this message:"
*Cause:    The cause is explained in the forwarded message.
*Action:   See the non-Oracle system's documentation of the forwarded
           message.
Error at Line: 1 Column: 19

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37837300
This is quickly getting outside my abilities.  I can help with the Oracle side of things.  This error is coming form outside Oracle.  Not sure how much help I can be once it leaves Oracle.

Just a thought:  Does your sqlnet.ora file have NAMES.DEFAULT_DOMAIN set?  If so, comment it out and retry.

That error is a pretty generic one.  There isn't a lot out there on it.  What I could locate seems ot point to the ODBC driver not being found.  Is dg4odbc installed in Oracle?

If those last couple things do not fix the issue you might want to ask a new question specific to troubleshooting the dg4odbc connection not what the listner is up and running.
0
 

Author Closing Comment

by:gnivkor
ID: 37837530
thank you very much, i will create new post for the separate issue
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now