Link to home
Start Free TrialLog in
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABANFlag for Austria

asked on

ORA-12560: TNS: Fehler bei Protokolladapter under Oracle 10g Release 2

Hi!

I am new to Oracle installations, starting with this job last month and learning. I have successfully installed the application database on one machine, the Oracle Identity Management (OIM) and the Oracle Application Server (OAS) on a second machine, both running under Windows Server 2003 SP 2.

Background:
==========
I install Oracle 10g Release 2 (10.1.2). At first, I had several sessions where I installed, run into problems, deinstalled and try again. Now, all the software (DB on machine1, OIM & OAS on machine2) have been installed.

To run Oracle Forms, it is necessary to install WebUtil. During this installation, I follow the installation notes (http://www.oracle.com/technology/products/forms/htdocs/webutil/readme.html#install) and the web_util.pdf document of August 2005 for WebUtil 1.0.6. In the first installations, I did not realise to follow these documents and could not start the Forms of the application (FRM-92101), so I try to follow the steps over there.

In the "2.4 Configuring Your Database for WebUtil" of the "Oracle® Forms Developer WebUtil Users Guide Release 1.0.6", it is asked to create a user webutil for the installation steps. Here, I run into problems.

Problem:
=======
I want to log to SQL*Plus, but get the error message

ORA-12560: TNS: Fehler bei Protokolladapter
(same as ORA-12560: TNS: Protocol adapter error)

When I start the web-based application server console (http://pco199.inf.beko.local:18100/emd/console/targets), I have following listing:

Application Servers
Name                                                   Status Farm                 Oracle Home
OracleASoim1.pco199.inf.beko.local     | |      Infrastructure   C:\ProgramFiles\OraHome_1
                                                               V     database is
                                                                       unavailable

The infrastructure database (called OraMDR) is unavailable, although installed ("Start > All Programs > Oracle - OAS1 > Application Development > SQL*Plus" exists).

When I try to login over SQL*Plus, I get following messages:

=============================================================
C:\WINDOWS>sqlplus

SQL*Plus: Release 10.1.0.4.2 - Production on Mo Jul 9 13:10:29 2007

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

Benutzernamen eingeben: dbsnmp
Kennwort eingeben:
ERROR:
ORA-12560: TNS: Fehler bei Protokolladapter
=============================================================
In the Log File: redirected output/errors, I found following error:

=============================================================
[&]
07/07/09 11:26:47 Stop process
 --------
 Syntax error on line 218 of c:/programfiles/orahome_1/apache/apache/conf/httpd.conf:
 Cannot load c:/programfiles/orahome_1/apache/apache/modules/apachemodulephp4.dll into server: (126) The specified module could not be found:  
 --------
 07/07/09 11:32:34 Start process
 --------
 [Mon Jul 09 11:32:35 2007] [warn] pid file c:/programfiles/orahome_1/apache/apache/logs/httpd.pid overwritten -- Unclean shutdown of previous Apache run?
 --------
 07/07/09 11:36:10 Restart process
 --------
 --------
 07/07/09 12:27:12 Restart process
=============================================================
 

The tnsnames.ora is like this:
=============================================================
# tnsnames.ora Network Configuration File: C:\ProgramFiles\OraHome_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

REP_PCO199_OAS1.INF.BEKO.LOCAL =
  (ADDRESS = (PROTOCOL = tcp)(HOST = pco199)(PORT = 1950))


EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pco199.inf.beko.local)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PLSExtProc)
    )
  )

Elba =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pco206.inf.beko.local)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Elba)
    )
  )

OraMDR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pco199.inf.beko.local)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = OraMDR)
    )
  )
=============================================================

Note that I have on the 1st machine (PCO206) the application database "Elba", on the 2nd machine (PCO199) the inventory database 'OraMDR'. I added both entries manually.

I appreciate any help. Please let me know if you want any log files etc. to solve the problem.
Avatar of jwahl
jwahl
Flag of Austria image
are you able to connect to the database with the following?

sqlplus user/password@dbname

as dbname use the alias from your tnsnames.ora (elba, oramdr).
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Hi jwahl!

Thank you for your reply!

When I enter the SQL*Plus command, I get the following response:

=========================================================
C:\ProgramFiles>sqlplus dbsnmp/Dbsnmp_01@OraMDR
SQL*Plus: Release 10.1.0.4.2 - Production on Mo Jul 9 14:29:00 2007
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS: Listener kennt den Service aktuell nicht, der in dem
Connect-Deskriptor angefordert wird

Benutzernamen eingeben:
C:\ProgramFiles>sqlplus dbsnmp/Dbsnmp_01@Elba

SQL*Plus: Release 10.1.0.4.2 - Production on Mo Jul 9 14:32:06 2007

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

ERROR:
ORA-12170: TNS: Zeit Überschreitung bei Verbindung
(Translation: Timeout during connection)

Benutzernamen eingeben: dbsnmp
Kennwort eingeben:
ERROR:
ORA-12560: TNS: Fehler bei Protokolladapter
(Translation: Protocol adapter error)

Benutzernamen eingeben:
("Enter user name' in English)
=========================================================

The message would read in English 'ORA-12514: TNS: listener could not resolve the service current, that requests a connect descriptor', or something similar to that.

Thank you in advance for your contributions!
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

I do get some error messages in the HTTP_Server log file (C:\ProgramFiles\OraHome_1\Apache\Apache\logs\error_log.1183982400):

[&]
[Mon Jul 09 14:22:51 2007] [warn] [client 172.16.1.38] [ecid: 1183983771:172.16.1.38:1632:5984:10,0] MOD_OC4J_0184: Failed to find an
  oc4j process for destination: OC4J_SECURITY

[Mon Jul 09 14:22:51 2007] [error] [client 172.16.1.38] [ecid: 1183983771:172.16.1.38:1632:5984:10,0] MOD_OC4J_0145: There is no oc4j
  process (for destination: OC4J_SECURITY) available to service request.

[Mon Jul 09 14:22:51 2007] [error] [client 172.16.1.38] [ecid: 1183983771:172.16.1.38:1632:5984:10,0] MOD_OC4J_0119: Failed to get an
  oc4j process for destination: OC4J_SECURITY

[Mon Jul 09 14:22:51 2007] [error] [client 172.16.1.38] [ecid: 1183983771:172.16.1.38:1632:5984:10,0] MOD_OC4J_0013: Failed to call destination:
  OC4J_SECURITY's service() to service the request.

[Mon Jul 09 14:22:51 2007] [warn] [client 172.16.1.38] [ecid: 1183983771:172.16.1.38:1632:2056:29,0] MOD_OC4J_0184: Failed to find an
  oc4j process for destination: OC4J_SECURITY

[Mon Jul 09 14:22:51 2007] [error] [client 172.16.1.38] [ecid: 1183983771:172.16.1.38:1632:2056:29,0] MOD_OC4J_0145: There is no oc4j
  process (for destination: OC4J_SECURITY) available to service request.

[Mon Jul 09 14:22:51 2007] [error] [client 172.16.1.38] [ecid: 1183983771:172.16.1.38:1632:2056:29,0] MOD_OC4J_0119: Failed to get an
  oc4j process for destination: OC4J_SECURITY

[Mon Jul 09 14:22:51 2007] [error] [client 172.16.1.38] [ecid: 1183983771:172.16.1.38:1632:2056:29,0] MOD_OC4J_0013: Failed to call destination:
  OC4J_SECURITY's service() to service the request.
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

&aaaand one more error message, this time in the ...

C:\ProgramFiles\OraHome_1\NETWORK\listener.log:
[&]
05-JUL-2007 14:44:30 * (CONNECT_DATA=(SID=OraMDR)(CID=(PROGRAM=C:\ProgramFiles\OraHome_1\perl\5.6.1\bin\MSWin32-x86\perl.exe)(HOST=PCO199)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.4.3)(PORT=2148)) * establish * OraMDR * 0
05-JUL-2007 14:48:41 * service_update * oramdr * 0
05-JUL-2007 14:48:53 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=OraMDR.inf.beko.local)(CID=(PROGRAM=C:\ProgramFiles\OraHome_1\bin\emagent.exe)(HOST=PCO199)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.4.3)(PORT=2290)) * establish * OraMDR.inf.beko.local * 0
05-JUL-2007 14:48:56 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=OraMDR.inf.beko.local)(CID=(PROGRAM=C:\ProgramFiles\OraHome_1\bin\emagent.exe)(HOST=PCO199)(USER=OraUser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.4.3)(PORT=2293)) * establish * OraMDR.inf.beko.local * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
 TNS-12537: TNS:connection closed
  TNS-12560: TNS:protocol adapter error
   TNS-00507: Connection closed
    32-bit Windows Error: 109: Unknown error
05-JUL-2007 14:49:11 * service_died * oramdr * 12547
TNS-12547: TNS:lost contact
[&]
ASKER CERTIFIED SOLUTION
Avatar of jwahl
jwahl
Flag of Austria image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

OK, I admire multilingual people! Fine, dass du Deutsch kannst. :-> But maybe, others who read this discussion would appreciate the translation. ;-)

I copied, as you suggested, the tnsnames.ora file from the ORACLE_HOME directory (the first one added to the ProgramFiles directory) to the second home directory. For convenience, here are the contents of this file:

# tnsnames.ora Network Configuration File: C:\ProgramFiles\OraHome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORAMDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pco199.inf.beko.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OraMDR.inf.beko.local)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pco199.inf.beko.local)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PLSExtProc)
    )
  )

I immediately tried to login with SQL^Plus without restarting any process:

C:\ProgramFiles\OraHome_2>sqlplus

SQL*Plus: Release 10.1.0.4.2 - Production on Mo Jul 9 14:50:40 2007

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

Benutzernamen eingeben: dbsnmp
Kennwort eingeben:
ERROR:
ORA-12560: TNS: Fehler bei Protokolladapter


Benutzernamen eingeben: ^C

==============
Do I have to restart any server after this change in tnsnames.ora?


Notice:
=====
On the Application Server Control (browser), I see the following:

==============================================================================
Application Server: OracleASoim1.pco199.inf.beko.local
&Name                                                              Status Start Time
   HTTP_Server                                                    (up)    Jul 9, 2007 11:32:34 AM
   OC4J_SECURITY                                            (down) Unavailable
   OracleASoim1.pco199.inf.beko.local_DAS    (down) N/A
   OracleASoim1.pco199.inf.beko.local_DIP         (up)    N/A
   OracleASoim1.pco199.inf.beko.local_LDAP  (down) N/A
   Single Sign-On:orasso                                   (down) N/A
   Managment                                                      (up)    Jul 9, 2007 11:32:58 AM
Avatar of jwahl
jwahl
Flag of Austria image
usually you don't have to restart any services after changing the tnsnames.ora.

the difference seems to be is in the service_name: OraMDR.inf.beko.local vs. OraMDR.

you should determine the correct service name. is there a tool for creating the tnsnames automatically, such as NetEasyConfig or NetAssistant?

SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Hallo!

I have on the first machine (PCO206) the application database 'Elba' running, and on the second machine (PCO199) the OIM & the OAS. On the second machine is also the OraMDR database that is installed by the OAS installation for the tool data.

I assume that *remote* is the second machine (PCO199) and make following changes to the listener.ora file (C:\ProgramFiles\OraHome_1\NETWORK\ADMIN):

# listener.ora Network Configuration File: C:\ProgramFiles\OraHome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\ProgramFiles\OraHome_1)
      (PROGRAM = extproc)
    )
# 20070709 SAE: New SID_DESC added:
    (SID_DESC =
      (SID_NAME = OraMDR)
      (ORACLE_HOME = C:\ProgramFiles\OraHome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = pco199.inf.beko.local)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
    )
  )

I stop and start the listener:
C:\ProgramFiles\OraHome_2>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 09-JUL-2007 16:26
:52

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

Verbindung mit (ADDRESS=(PROTOCOL=tcp)(PORT=1521)) wird aufgebaut
Der Befehl wurde erfolgreich ausgef]hrt.

C:\ProgramFiles\OraHome_2>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 09-JUL-2007 16:26:55

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

tnslsnr wird gestartet: Bitte warten...

TNSLSNR for 32-bit Windows: Version 10.1.0.4.2 - Production
Log messages written to C:\ProgramFiles\OraHome_2\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pco199.inf.beko.local)(PORT=1521)))

Verbindung mit (ADDRESS=(PROTOCOL=tcp)(PORT=1521)) wird aufgebaut
STATUS des LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.4.2 - Produ
ction
Startdatum                09-JUL-2007 16:26:55
Uptime                    0 Tage 0 Std. 0 Min. 0 Sek.
Trace-Ebene               off
Sicherheit                ON: Local OS Authentication
SNMP                      OFF
Log-Datei des Listener    C:\ProgramFiles\OraHome_2\network\log\listener.log
Zusammenfassung Listening-Endpunkte...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pco199.inf.beko.local)(PORT=1521)))
Der Listener unterstützt keine Services
Der Befehl wurde erfolgreich ausgeführt.
C:\ProgramFiles\OraHome_2>
C:\ProgramFiles\OraHome_2>sqlplus scott/tiger@OraMDR
SQL*Plus: Release 10.1.0.4.2 - Production on Mo Jul 9 16:35:44 2007

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

ERROR:
ORA-12514: TNS: Listener kennt den Service aktuell nicht, der in dem
Connect-Deskriptor angefordert wird


Benutzernamen eingeben: scott
Kennwort eingeben:
ERROR:
ORA-12560: TNS: Fehler bei Protokolladapter


Benutzernamen eingeben:
SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jwahl
jwahl
Flag of Austria image
i think "Radeberger Pilsner" is only available in Germany ... Poster sae1962at seems to be from Austria ....?
Avatar of schwertner
schwertner
Flag of Antarctica image
HMMMM I am speaking about "Radeberger Pilsner", not about the asker ... :-)))
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Well, I am from Austria but not drinking beer or wine. ;-)))

I undone the changes on PCO199 & did it on PCO206, the machine that has the application database 'Elba'. I stopped & started the listeners on both machines.

When I try to login over SQL*Plus on PCO206, I get the following reply:
C:\ProgramFiles>sqlplus scott/tiger@Elba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 9 17:36:21 2007

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

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist


Enter user-name:

When I try on the other machine (PCO199), it will, of course, not work either (if it doesn't run, it doesn't):
C:\ProgramFiles>sqlplus scott/tiger@Elba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 9 17:35:28 2007

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

ERROR:
ORA-12154: TNS:Angegebener Connect-Bezeichner konnte nicht aufgelöst werden

Enter user-name:


*-)

I searched the contents of alert_elba.log on PCO206:
[&]
Thu Jul 05 16:57:55 2007
Database Characterset is WE8MSWIN1252
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=18, OS id=176
Thu Jul 05 16:58:17 2007
Completed: alter database open
Thu Jul 05 16:58:19 2007
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Jul 06 08:59:42 2007
MMNL absent for 56595 secs; Foregrounds taking over
Fri Jul 06 08:59:42 2007
MMNL absent for 56595 secs; Foregrounds taking over
MMNL absent for 56599 secs; Foregrounds taking over
MMNL absent for 56599 secs; Foregrounds taking over




Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Oh, I forgot to write a last sentence to the alert_elba.log contents:

The database Elba seems not to be running till Friday morning.
Avatar of schwertner
schwertner
Flag of Antarctica image
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
means that the instance is not running.

Go to Administrative Tools ---> Processes and make sure that
the Listener and the Instance are up.

Also connect as SYS:

SQL>connect sys/password@some_instance as sysdba

If you get connection to an idle instance, then bounce the database:

SQL>startup

But for external connections the listener has to run, the firewall has to be off.
The last is very important.
Avatar of schwertner
schwertner
Flag of Antarctica image
O, mein Gott! Das ist eine gute Nachricht ...!
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

As another step to find the solution to my problem, I restarted following services on the remote machine (PCO206):

OracleDBConsoleELBA
OracleOraDb10g_home1iSQL*Plus
OracleOraDb10g_home1TNSListener
OracleServiceELBA

Then, I tried again to login:

C:\ProgramFiles>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 9 17:54:38 2007

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

Enter password:

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

SQL>

On the machine that has the OAS (PCO199), I tried to connect to Elba but got the ORA-12560: TNS error message. The alert_oramdr.log shows that the 'tool database' OraMDR is running.
SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

I restarted the machine that has the application database 'Elba' on it (PCO206) again, and it runs fine. Could login over SQL*Plus.

I admit again that I am a beginner. :(( I didn't even know that there is a Windows Firewall to switch off. When you stated yesterday that it is very important to switch the firewall off, I thought that I didn't install one!!! Shame on me!

Now, I can ping the PCO206. But tnsping will not work on Elba:
=============================================================================
C:\ProgramFiles\OraHome_2>ping pco206.inf.beko.local

Pinging pco206.inf.beko.local [172.16.1.54] with 32 bytes of data:

Reply from 172.16.1.54: bytes=32 time<1ms TTL=128
Reply from 172.16.1.54: bytes=32 time<1ms TTL=128
Reply from 172.16.1.54: bytes=32 time<1ms TTL=128
Reply from 172.16.1.54: bytes=32 time<1ms TTL=128

Ping statistics for 172.16.1.54:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

C:\ProgramFiles\OraHome_2>sqlplus sys/<...>@Elba

SQL*Plus: Release 10.1.0.4.2 - Production on Di Jul 10 10:18:35 2007

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

ERROR:
ORA-12154: TNS:Angegebener Connect-Bezeichner konnte nicht aufgelbst werden


Benutzernamen eingeben: ^C
C:\ProgramFiles\OraHome_2>tnsping Elba

TNS Ping Utility for 32-bit Windows: Version 10.1.0.4.2 - Production on 10-JUL-2
007 10:19:02

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

Parameterdateien benutzt:
C:\ProgramFiles\OraHome_2\network\admin\sqlnet.ora

TNS-03505: Name konnte nicht aufgelbst werden

C:\ProgramFiles\OraHome_2>
----------------------------------------------------------------------------------------------------------

I think there is something missing in the sqlnet.ora file. Is it?
=============================================================================
# sqlnet.ora Network Configuration File: C:\ProgramFiles\OraHome_2\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)

Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Very interesting to me! I searched on PCO199, the machine hosting OIM & OAS, the string "*sqlplus*" and found the same files in the two Oracle homes, C:\ProgramFiles\OraHome_1 &  C:\ProgramFiles\OraHome_2. I never intended to have duplicates on this machine, but I worry how this happened, as I have made a default OIM & OAS installation. Is there probably a clue for problems? Note that although the names of the objects are the same, their sizes differ.

Examples:
==============================================================================
C:\>dir /s *sqlplus*
 Volume in drive C has no label.
 Volume Serial Number is 984A-6861

 Directory of C:\Program Files\Oracle\Inventory\Components

2007-07-05  14:20    <DIR>          oracle.rdbms.sqlplus
               0 File(s)              0 bytes

 Directory of C:\Program Files\Oracle\Inventory\Patches

2007-07-05  15:49    <DIR>          oracle.rdbms.sqlplus
               0 File(s)              0 bytes

 Directory of C:\ProgramFiles\OraHome_1

2007-07-05  14:11    <DIR>          sqlplus
               0 File(s)              0 bytes

 Directory of C:\ProgramFiles\OraHome_1\BIN

2005-10-10  06:45           688,128 sqlplus.exe
2005-10-10  06:45           749,568 sqlplusw.exe
               2 File(s)      1,437,696 bytes

[...]
 Directory of C:\ProgramFiles\OraHome_2

2007-07-05  15:41    <DIR>          sqlplus
               0 File(s)              0 bytes

 Directory of C:\ProgramFiles\OraHome_2\bin

2005-06-25  08:16           684,032 sqlplus.exe
2005-06-25  08:17           741,376 sqlplusw.exe
               2 File(s)      1,425,408 bytes

[...]
C:\>
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Notice: I found out that I can connect to Elba over iSQLPlus without problems. The connection problems (TNS errors etc.) arise only when I try to connect over SQL*Plus.
SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

I made the sqlnet.ora file look like you proposed and tried SQL*Plus from PCO199 again in order to reach the application database 'Elba':

================================
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\WINDOWS>sqlplus sysman/Elba_01@Elba

SQL*Plus: Release 10.1.0.4.2 - Production on Di Jul 10 12:25:37 2007

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

ERROR:
ORA-12154: TNS:Angegebener Connect-Bezeichner konnte nicht aufgelöst werden


Benutzernamen eingeben:
------------------------------------

How is it possible to could not resolve service name?

I searched the net for this error message and found out that one reason is to correct the init.ora file (http://www.dba-oracle.com/t_ora_12154_tns_resolve_service_name.htm). I made following modifications:
PCO206 "Elba":
=================================
[...]
# replace "clustdb" with your database name
# 20070710 SAE: Changed db_name to database name 'OraMDR'.
db_name = OraMDR
[...]
# 20070710 SAE: Changed currently to the local address, as global address not available yet.
db_domain = inf.beko.local      # global database name is db_name.db_domain
----------------------------------------

PCO199 "OraMDR":
=================================
[...]
# replace "clustdb" with your database name
# 20070710 SAE: Changed db_name to database name 'OraMDR'.
db_name = OraMDR
[...]
# 20070710 SAE: Changed currently to the local address, as global address not available yet.
db_domain = inf.beko.local      # global database name is db_name.db_domain
----------------------------------------

According to this site, I also modified the database with

ALTER DATABASE RENAME global_name TO Elba.inf.beko.local;

These modifications did not resolve my problems. But I didn't undo them.
Avatar of schwertner
schwertner
Flag of Antarctica image
Why you did this? Why you do not ask us before doing this?
Making dangerous experiments you will do things worse.

The plan to fix the things.
Go to the application DB 'Elba'
1. Check if you have registered service by the listener:

c:\>lsnrctls services

If not, publish the LISTENER.ORA file.
It will need some small corrections.
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Well, I have to proceed with the installation. This is my third try (started the 5th June). I hope that this hint I followed didn't do something bad to the whole system. But the original iinformation was something like ELBA. ... .ORACLE.COM.

OK. Thank you for this hint. For sure, you will get points for XE, as this hint shows that the listener does not have registered services. Thank you very much!

C:\WINDOWS>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 10-JUL-2007 15:39
:00

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

Verbindung mit (ADDRESS=(PROTOCOL=tcp)(PORT=1521)) wird aufgebaut
Der Listener unterst]tzt keine Services
Der Befehl wurde erfolgreich ausgef]hrt.

C:\WINDOWS>
Avatar of schwertner
schwertner
Flag of Antarctica image
So, this is typical Oracle bug. As said to fix it
read and follow the posting
ID:19444939Author:schwertnerDate:07.09.2007 at 04:27PM
Avatar of schwertner
schwertner
Flag of Antarctica image
Of course you have to do this on the Oracle server machine, not at the client site.
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

OK, now I have undone the changes I have made on the client, i.e. PCO199. I wondered that I hadn't made the changes you requested on the 9. July. On the server (PCO206, where the application database 'Elba' is running), I made this changes on 1732. You can see it above under 07.09.2007 at 04:36PM CEST, ID: 19445494.

Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Here the listener file on PCO206, the 'Elba' host. I hope that it is OK.

======================================================================
# listener.ora Network Configuration File: C:\ProgramFiles\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\ProgramFiles\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
# 20070709 SAE: New SID_DESC added (ExpertsExchange):
    (SID_DESC =
      (SID_NAME = Elba)
      (ORACLE_HOME = C:\ProgramFiles\oracle\product\10.2.0\db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pco206.inf.beko.local)(PORT = 1521))
    )
  )

Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

I again entered the listner control services command and got following reply:

==========================
C:\WINDOWS>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 10-JUL-2007 16:31:22

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

Verbindung mit (ADDRESS=(PROTOCOL=tcp)(PORT=1521)) wird aufgebaut
Services Übersicht...
Dienst "OraMDR.inf.beko.local" hat 1 Instance(s).
  Instance "oramdr", Status READY, hat 3 Handler für diesen Dienst...
    Handler:
      "D001" eingerichtet:0 abgewiesen:0 aktuell:0 max:1002 Status:ready
         DISPATCHER <machine: PCO199, pid: 2096>
         (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pco199.inf.beko.local)(PORT=1
047))(PRESENTATION=oracle.aurora.server.SGiopServer)(SESSION=RAW))
      "D000" eingerichtet:0 abgewiesen:0 aktuell:0 max:1002 Status:ready
         DISPATCHER <machine: PCO199, pid: 2092>
         (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pco199.inf.beko.local)(PORT=1
046))(PRESENTATION=oracle.aurora.server.GiopServer)(SESSION=RAW))
      "DEDICATED" festgelegt:11 abgelehnt:0 Status:ready
         LOCAL SERVER
Der Befehl wurde erfolgreich ausgeführt.

C:\WINDOWS>
C:\WINDOWS>
C:\WINDOWS>sqlplus sysman/<pwd>@Elba

SQL*Plus: Release 10.1.0.4.2 - Production on Di Jul 10 16:32:59 2007

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

ERROR:
ORA-12154: TNS:Angegebener Connect-Bezeichner konnte nicht aufgelöst werden
Avatar of schwertner
schwertner
Flag of Antarctica image
Ja, mein Herr!

C:/>lsnrctl stop
C:/>lsnrctl start
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Vielen Dank for the reminder! I did it from the Services menu, but I did it again on PCO199 from the command line. (Changes to listener.ora are done on the PCO206.)

========================================
C:\WINDOWS>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 10-JUL-2007 16:35:35

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

Verbindung mit (ADDRESS=(PROTOCOL=tcp)(PORT=1521)) wird aufgebaut
Der Befehl wurde erfolgreich ausgeführt.

C:\WINDOWS>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 10-JUL-2007 16:35:41

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

tnslsnr wird gestartet: Bitte warten...

TNSLSNR for 32-bit Windows: Version 10.1.0.4.2 - Production
Log messages written to C:\ProgramFiles\OraHome_2\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pco199.inf.beko.local)(PORT=1521)))

Verbindung mit (ADDRESS=(PROTOCOL=tcp)(PORT=1521)) wird aufgebaut
STATUS des LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.4.2 - Production
Startdatum                10-JUL-2007 16:35:43
Uptime                    0 Tage 0 Std. 0 Min. 2 Sek.
Trace-Ebene               off
Sicherheit                ON: Local OS Authentication
SNMP                      OFF
Log-Datei des Listener    C:\ProgramFiles\OraHome_2\network\log\listener.log
Zusammenfassung Listening-Endpunkte...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pco199.inf.beko.local)(PORT=1521)))
Der Listener unterstützt keine Services
Der Befehl wurde erfolgreich ausgeführt.

C:\WINDOWS>
C:\WINDOWS>
C:\WINDOWS>sqlplus sysman/Elba_01@Elba

SQL*Plus: Release 10.1.0.4.2 - Production on Di Jul 10 16:35:51 2007

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

ERROR:
ORA-12154: TNS:Angegebener Connect-Bezeichner konnte nicht aufgelöst werden


Benutzernamen eingeben:
Avatar of schwertner
schwertner
Flag of Antarctica image
Are you sure that the instance is running?
Is this the name of the instance?

Also this is not good ---->Sicherheit                ON: Local OS Authentication

Need changes in
# sqlnet.ora Network Configuration File: C:\ProgramFiles\OraHome_2\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT, ONAMES, HOSTNAME)

And need restarting of the Listener after this.
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Over "Start > Administrative Tools > Services", I can see the services below (PCO199). OraMDR is the inventory database for OIM & OAS. How can I set the "Sicherheit" (security) to OFF, if this is not good? I haven't changed anything that it is on, so it should be the default (like the firewall is ON Windows by default).

===============================================
Name                                        Status    Startup
------------------------------------ ---------- -------------
OracleCSService                     Started   Automatic
OracleDBConsoleOraMDR       Started   Automatic
OracleJobSchedulerORAMDR                Disabled
OracleOAS1ASControl            Started   Automatic
OracleOAS1ProcessManager Started   Automatic
OracleOAS1TNSListener         Started   Manual
OracleOIM1ASControl              Started   Automatic
OracleOIM1ProcessManager   Started   Automatic
OracleOIM1TNSListener           Started   Automatic
OracleServiceORAMDR           Started   Automatic
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Congratulations! Your help gave me the HINT!

My problem is that I have TWO tnsnames.ora files on the PCO199, where I have OIM & OAS installed!!!

Such a bu_____! (Fill in the blanks)

I never requested Oracle to install it in two directories, but the OIM & the OAS have their own HOME directories, as they could be installed on separate hosts.

===============================================
# tnsnames.ora Network Configuration File:

C:\ProgramFiles\OraHome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pco199.inf.beko.local)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PLSExtProc)
    )
  )

Elba =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pco206.inf.beko.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Elba)
    )
  )

ORAMDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pco199.inf.beko.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OraMDR)
    )
  )
Avatar of jwahl
jwahl
Flag of Austria image
yes, with mr. schwertner's help no oracle problem remains unsolved ... he should get the points and a box of cold "Radeberger Pilsner" ... ;-)
Avatar of Ahmet Ekrem SABAN
Ahmet Ekrem SABAN
Flag of Austria image

ASKER

Great! If you had asked me a directory listing, we would have seen that I have overseen the second tnsnames.ora. But I Mr. Schwertner elaborated alot the case. So, he got also some of the points. :)
Avatar of schwertner
schwertner
Flag of Antarctica image
I will never visit Oesterreich!
Guess why!
Avatar of jwahl
jwahl
Flag of Austria image
It's a pity! We've excellent beer here ...