Tom,
I set the TNS_ADMIN system variable on an XP box to point to one of our Windows 2000 box TNSNAMES.
Our front end is ASP.NET.
SQLPLUS works O.K...but when we try to connect through a web page (using IIS)..we get ORA-12154 error...
Any ideas...

Thanks

"IIS's environment doesn't have the TNS_ADMIN set then.... or you are using the
wrong tns alias in your setup.

create a small IIS routine that just prints out what the tns admin is set to."

When I set up the TNS_ADMIN variable...I went to the system environment variables (on client XP) and added another system variable TNS_ADMIN..with the path for the remote TNSNAMES file on a Windows 2000 box (Database is on HP UNIX box)
So, are you saying the client box needs another entry for the IIS...where would that entry go?

Thanks.

Unsolicted $0.02...

Since IIS runs as a service, it can't see things like mapped drives. IIS does not run in a context like a 'normal user environment'. Typically it runs as 'Local System'. If TNS_ADMIN on the box running IIS is pointing to a location on a mapped drive, IIS would not be able to see it...

-Mark

Rebooted the client machine...still the same error...

Hi Tom,
I come to know that you can use restricted IP's to get into the database thro' SQLPLUS by the use of protocol.ora Where this file should be in the server/Client ,
what parameters i can set in this and uses of each parameter?

Tom

We are using the oracle foms 6.8.8.0 on NT4 with oracle 8.1.7

I have tns entry in my tnsnames.ora file without COMMUNITY and it connects well through Oracle forms /SQL PLUS to Database.

But on the other PC same oracle forms version 6.0.8.8.0 and same tnsnames file without COMMUNITY, but this does not work unless you but the COMMUNITY with x.world

Can you advise me what could be the reason and how can find what exact tnsnames entry should be.

Thanks

I have checked, entry is commented in both the PC in sqlnet.ora file.

anyway follow is the tnsnames.ora's entry on PC

connstr1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST1)(PORT = 1527))
)
(CONNECT_DATA =
(SERVICE_NAME = DB1)
)
)

and on other pc
connstr2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = HOST1)
(Port = 1527)
)
)
(CONNECT_DATA = (SID = DB1)
)
)

Tom

I have installed oracle 8i and 9i on the same hp ux 11i machine.

I want to use the 9i listener to connect to instances on both databases, ie 9i listener can connect to 8i and 9i whereas the 8i might not. This should enable me to maintain one copy of the tnsnames.ora file, am i right so far? I have a tnsnames under the network/admin of the 9i orahome.

But i've found that i still need a copy of the tnsnames in the 8i orahome network/admin dir. I have set my TNS_ADMIN env variable to be:

$ORACLE_HOME/network/admin where the oracle home is the 9i home.

for both the o/s user which owns the 8i orahome and the o/s user which owns the 9i orahome, but i can prove this is not being used by the 8i home because if i remove the tnsnames.ora from the 8i home i cannot use any the database links i have specified. As soon as i copy the tnsnames to the 8i home, my database links work again.

So my question is, what could be stopping the 8i home from using the TNS_ADMIN env ? it exists in the the 8i user profile but seems to be ignored?? Do you know why this could be?

Ok as requested, the following shows

(this is all done from the hp ux 11i server which has 8.1.7.4 and 9.2.1 db installed)

1) i am logged in as the 8i user and i'm in the 8i home net admin directory, I've echoed the 8i home.
2) i echo the TNS_ADMIN to show it is set to the 9i home net admin directory.
3) i use sqlplus to show it uses a tnsnames to connect to remote database
4) i then remove the tnsnames.ora from the 8i net admin dir and attempt the same remote sqlplus login and it fails.


The results i have copied and pasted below:


$
$ echo $ORACLE_HOME
/oracle/app/oracle/product/8.1.7
$ echo $TNS_ADMIN
/oracle/app/oracle/product/9.2.1/network/admin
$ pwd
/oracle/app/oracle/product/8.1.7/network/admin
$ ls -t
tnsnames.ora        sqlnet.log         samples             
shrept.lst          snmp_ro.ora         
cman.ora            names.ora           
namesdrp.sql        snmp_rw.ora         
$ sqlplus opt1/******@op4bo
 
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 9 10:16:14 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
 
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Producti
on
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
$ rm tnsnames.ora
$ sqlplus opt1/******@op4bo
 
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 9 10:16:51 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
ERROR:
ORA-12154: TNS:could not resolve service name
 
 
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
$ 



That is my test, which tells me that the TNS_ADMIN env variable is not being used. Why could this be??

Thanks 

Could it be that the TNS_ADMIN variable is not set up for export?

Try this :-

$ export TNS_ADMIN
$ sh
$ echo $TNS_ADMIN
$ exit

Takmeister

As requested:

$ ls -l $TNS_ADMIN/tnsnames.ora
-rwxrwxrwx 1 ora9 dba 2240 Aug 4 11:20 /oracle/app/oracle/pro
duct/9.2.1/network/admin/tnsnames.ora
$


I also tried the suggestion from the other reply :


$ export TNS_ADMIN
$ sh
$ echo $TNS_ADMIN
/oracle/app/oracle/product/9.2.1/network/admin
$ exit
$


but I'm not sure what this means!?

$
$
$ echo $ORACLE_HOME
/oracle/app/oracle/product/8.1.7
$ echo $TNS_ADMIN
/oracle/app/oracle/product/9.2.1/network/admin
$ sqlplus *****/****@op4bo
 
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 9 16:21:36 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
 
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Producti
on
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
$ rm tnsnames.ora
$ sqlplus *****/****@op4bo
 
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 9 16:22:01 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
ERROR:
ORA-12154: TNS:could not resolve service name
 
 
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
$ cp $TNS_ADMIN/tnsnames.ora .
$ sqlplus *****/****@op4bo
 
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 9 16:22:17 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
 
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Producti
on
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
$
$
$ 

Could you please make sure that you don't have a sqlnet.ora file in 9i home? ( I could see you don't have this file in 8i home)

Tom, I think in this case sqlplus shouldn't connect but...

[mportes@lath09 /home01/mportes]$ mkdir network
[mportes@lath09 /home01/mportes]$ echo $TNS_ADMIN
/home01/mportes/network
[mportes@lath09 /home01/mportes]$ touch $TNS_ADMIN/tnsnames.ora
[mportes@lath09 /home01/mportes]$ sqlplus /

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Aug 9 14:16:00 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

ops$mportes@FCCUAT9I> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
[mportes@lath09 /home01/mportes]$ uname -a
HP-UX lath09 B.11.11 U 9000/800 690359356 unlimited-user license

I was connecting direct without net... sorry my mistake. I cannot reproduce the last "bug" appointed by dxl.

[mportes@lath09 /home01/mportes]$ export TNS_ADMIN=~/net
[mportes@lath09 /home01/mportes]$ mkdir net
[mportes@lath09 /home01/mportes]$ cp $ORACLE_HOME/network/admin/tnsnames.ora net
[mportes@lath09 /home01/mportes]$ sqlplus /@fccuat9i

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Aug 9 16:58:11 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

ops$mportes@FCCUAT9I> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
[mportes@lath09 /home01/mportes]$ echo "" > $TNS_ADMIN/tnsnames.ora
[mportes@lath09 /home01/mportes]$ sqlplus /@fccuat9i

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Aug 9 16:58:42 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve service name


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[mportes@lath09 /home01/mportes]$

no, we have remote_os_authent = false.

What else could cause this behaviour? Is there anyway i can trace whats going on?

I was talking to mportes at that point.  about the OS authentication.  I was pointing out they have it set to true and that is *bad*

for you -- i can only recommend you open a tar with support.  I quite simply *cannot* reproduce.  I don't use HP/UX, i don't know what the equivalent of truss is on that system (strace on linux).  that is what I use to trace "what files are being read"



<b>
[mportes@lath09 /home01/mportes]$ sqlplus /@fccuat9i

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Aug 9 16:58:11 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production


</b>

you just used os authentication to log in "remotely" over the network.



ops$tkyte@ORA9IR2> connect /@ora9ir2
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
<b>that is what happens with remote connections and remote_os_authent=false</b>

ops$tkyte@ORA9IR2> connect /
Connected.

ops$tkyte@ORA9IR2> connect ops$tkyte/foobar@ora9ir2
Connected.
ops$tkyte@ORA9IR2>
<b>ops$tkyte does exist in that database and i am tkyte in the OS</b>

ops$tkyte@ORA9IR2> show parameter remote_os_authent
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     FALSE

<b>remote os authent is false, but..</b>

ops$tkyte@ORA9IR2> alter system set remote_os_authent=true scope=spfile;
 
System altered.
 
ops$tkyte@ORA9IR2> connect / as sysdba
Connected.
ops$tkyte@ORA9IR2> startup force
ORACLE instance started.
 
Total System Global Area  168891480 bytes
Fixed Size                   451672 bytes
Variable Size             100663296 bytes
Database Buffers           67108864 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

ops$tkyte@ORA9IR2> connect /@ora9ir2
Connected.
ops$tkyte@ORA9IR2>

<b>when set to true, i can remotely OS authenticate -- but it is a security hole the size of the moon -- don't do it (and you have it set, you have to have it set</b>

 

Hi Tom, thank you for point it out, but this servers wich have this kind of "security hole" was set up for that. We have that to play -- no concern for.
Well -- thanks though to figure it out.

About strace -- there is one on hp/ux

ops$mportes@FCCUAT9I> !man strace
Reformatting entry. Wait... done

strace(1M) strace(1M)

NAME
strace - write STREAMS event trace messages to standard output

SYNOPSIS
strace [ mod sub pri ] ...

DESCRIPTION
strace gets STREAMS event trace messages from STREAMS drivers and
modules via the STREAMS log driver (strlog(7)), and writes these
messages to standard output. By default, strace without arguments
writes all STREAMS trace messages from all drivers and modules.
strace with command-line arguments limits the trace messages received.

The arguments, which must be specified in groups of three, are:

mod Specifies the STREAMS module identification number from the
streamtab entry.

sub Specifies a subidentification number (often corresponding to
a minor device).

pri Specifies a tracing priority level. strace gets messages of
a level equal to or less than the value specified by pri.
Only positive integer values are allowed.
...

Regards,

dxl,

I'm terrible at communicating my thoughts.  I'll try again :-

In unix, shell variables are available for use in shell scripts, but are not necessarily passed to child processes.  Doing an 'echo $TNS_NAMES' only shows that it is defined, but does not necessarily mean it is passed to any child process - the variable expansion is done by the shell.

$ echo $greeting

$ greeting=hello
$ echo $greeting
hello
$ sh
$ echo $greeting

$ exit
$ export greeting
$ sh
$ echo $greeting
hello
$ exit


I defined a variable called 'greeting' and gave it the value 'hello'.  Then I spawned a child process (actually another shell) with the 'sh' command, and tried to print the value of the 'greeting' variable.  It came out blank, which shows that the parent shell did not pass the value to the child shell.  Then I exit'd out of the child shell; marked the 'greeting' variable for export; spawned a new shell; then printed the 'greeting' variable.


$ echo $TNS_ADMIN

$ TNS_ADMIN=/var/opt/oracle/network
$ echo $TNS_ADMIN
/var/opt/oracle/network
$ sqlplus /

SQL*Plus: Release 3.3.4.0.0 - Production on Wed Aug 11 12:49:48 2004

Copyright (c) Oracle Corporation 1979, 1996.  All rights reserved.

ERROR: ORA-12154: TNS:could not resolve service name


Enter user-name: 
ERROR: ORA-12154: TNS:could not resolve service name


Enter user-name: 
ERROR: ORA-12154: TNS:could not resolve service name


unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
$ export TNS_ADMIN
$ sqlplus /

SQL*Plus: Release 3.3.4.0.0 - Production on Wed Aug 11 12:49:59 2004

Copyright (c) Oracle Corporation 1979, 1996.  All rights reserved.


Connected to:
Oracle7 Server Release 7.3.4.5.0 - Production
With the parallel query option
PL/SQL Release 2.3.4.5.0 - Production


SQL> exit
Disconnected from Oracle7 Server Release 7.3.4.5.0 - Production
With the parallel query option
PL/SQL Release 2.3.4.5.0 - Production
$ 


The above transcripts where from HP-UX 10.20.


[Unsolicited opinion]
The best book I have ever read about shell programming, is called 'The Korn Shell', by David Korn.  I read the first edition in the library, and wanted to buy a copy, but it was out of print.  I have flicked through a copy of 'The Korn Shell' by Anatole Olczak, which belongs to a colleague, and I think it is on a par with David Korn's book.  Note, some systems (eg linux) do not run the ksh shell, but most of the korn shell features were adopted by the posix standard, and so are available in posix compatible shells.

Takmeister
 

PMFJI!
Hey DXL, what Takmeister said makes senses at all. Have you exported the environmet vars? Because echo'ing ther doesn't mean visibility to all (child) process -- as Tak said!

I did here with same version as yours and get no error! Why don't you try export?

We are in the middle of upgrading both our client desktops and server to Oracle 9i. So as to decouple the 2 projects, we confirmed with Support that Oracle 8i client works with Oracle 9i server (on Unix).

So the client desktops (Windows) have a Oracle 8i client (SQL*Net). Server is 9.2.0.4 on Solaris.

We use TNSNAMES.ORA files everywhere as the only naming service. I am noticing the following strange thing. Some of our tnsnames entries are simply 'myname = ...', i.e. the connect string itself has no "." in it.

The 8i client is unable to connect to these tnsnames entries even though the same entry worked fine against the 8i database. By unable to connect, I mean I get a 'TNS 12154: Unable to resolve service name"

When I add a "." to the connect string i.e.
myname.world = ....
it works fine!


Why would this be? Why should the Oracle 8i client's behaviour in resolving the tnsnames entry change because of the server it is trying to connect to?

Lots of client code has this table@mytnsname in it, so it would be a nightmare to go and change everything.

Is there a way for client code to continue to use @myname and have it actually resolve to @myname.world (which I would put in my tnsnames.ora file)

Help! Thanks

I have installed Oracle 10g in one system, it is working fine. now in another system I installed 8i client, with custom option having ODBC drivers, JDBC drivers and SQL Plus. it is not connecting to Server. can you help me pls?

thanks
Pushpendra Singh

Hi Tom
I have a problem wan i connect to oracle enterprise manager
i get error:TNS could not resolve service name.

Thanks.

Hi Tom,

I have two databases (A,B), 9.2.0.8., AIX 5L both.
I have dblink_1 in A pointing to B:
create database link dblink_1 connect to ... identified by ... using 'B';

and I have a procedure in A including
begin
-- do something
select ... from table_1@dblink_1;
-- do something
exception when others then
-- logging errors
end;
This works fine when running the procedure manually, by any application connected to A, etc.

But when I include this procedure in a JOB (created by DBMS_JOB, run periodically) I sometimes face ORA-12154:TNS could not resolve service name.
The owner of the procedure, dblink and job is the same.

I know, 'B' is resolved using TNSNAMES.ORA located in 'A'.
As a workaround I've created the dblink_1 using the "long" connection string, ie.
create database link dblink_1 connect to ... identified by ... using
' (DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ...)(PORT = ...)) )
(CONNECT_DATA = (SERVICE_NAME = ...) )
)';
it works fine also (and always), but we'd like to keep TNSNAMES.

I experienced also, that increasing the JOB_QUEUE_PROCESSES parameter to some higher value, ie. higher than the possible same-time-running jobs, it also could help, but sometimes the error still persists.

And, whenever the job runs into this error, will never recover from it.

Any explanations are welcome.

>do you have more than one tnsnames.ora file on your server
What do you mean more than one?
We have exactly one, accessed via TNS_ADMIN.

Hi Tom,
This is still not the case.
I wrote a job: testing 2 dblinks in a loop of 100 cycles.
The job was run by periods of 30,20,10 and 5 seconds, for approx. 2 hours.
By logging the success and error(always ORA-12154) I received the following failure rates:

TEXT INTERVAL COUNT PCT
ORA-12154: TNS:could not resolve service name 5 130800 48,7
Success 5 137800 51,3
ORA-12154: TNS:could not resolve service name 10 67800 47,75
Success 10 74200  52,25
ORA-12154: TNS:could not resolve service name 20 33200 46,63
Success 20 38000 53,37
ORA-12154: TNS:could not resolve service name 30 25800 55,13
Success 30 21000 44,87

So the "path to TNSNAMES" is not the solution.

Tom:

The above thread applies to unix.

Can you show how to print the value of TNS_ADMIN on windows machine and how to set the value?

also, if it is not set what happens?

SQLPlus on NT does use the TNS_ADMIN variable.  Consider:


C:\Documents and Settings\Thomas Kyte\Desktop>sqlplus 
scott/tiger@ora8i.us.oracle.com

Tom:

what i meant what DOS command do you type to give you the value of that variable in windows?

I could have many tnsnames.ora on my machines. How can i find out which is being used by clients.

C:\Documents and Settings\tkyte><b>set</b>
ADE_SITE=ade_burl
ALLUSERSPROFILE=C:\Documents and Settings\All Users
APPDATA=C:\Documents and Settings\tkyte\Application Data
CLASSPATH=.;C:\Program Files\Java\jre1.6.0_03\lib\ext\QTJava.zip
CommonProgramFiles=C:\Program Files\Common Files
COMPUTERNAME=TKYTE-PC
ComSpec=C:\WINDOWS\system32\cmd.exe
FP_NO_HOST_CHECK=NO
....

C:\Documents and Settings\tkyte><b>echo %OS%</b>
Windows_NT

Tom:

OK great.

I found out that i do not have TNS_ADMIN set.

You said that i do not need it to have set. it will use thedefault for oracle home.

if i have many oracle homes (8i, 9i, etc) how do i know which one is the default being used.

2. Can a client app have its own copy of tnsnames.ora in the application directory? I assume the client should always use the one under oracle home.

Tom

would doing this at the windwoze prompt

c:\set TNS_ADMIN= c:\

set my TNS_ADMIN variable to c:\

I did that and i did type "set" and it was set correctly.

However, when i ran sql*plus it ran fine. I did not have a file in c:\. i was just testing if the oracle clients will start looking on c:\ which is the value set

Does it have to be in regisrty or how do oracle applications look for the TNS file to interpret SERVICE names

Tom:

Do you know what may be causing this:

We have a 10gR2 running on a box. Box went down last week.
Box has OEM. Now it does not run. Running sql*plus on server works fine. the tnsnames.ora file is there.

from client machine, TNSPING for that service works fine. SQL*PLUS will not work.
It prints ORA-12154 TNS could not resolve the connection identifier.

How do i troubleshoot this and get it running.

Tom:

But TNSPING will not work if the file "tnsnames.ora" is damaged or it could not find it but that works. if TNS_ADMIN is not set it should go to oracle home to read it too.

Would this log help you pinpoint where the problem is. Do yo urecommend uninstall OEM and reinstall. is it configuration of OEM.


-bash-3.00$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Mar 1, 2009 6:59:07 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: traxmeta
Database Control is already configured for the database traxmeta
You have chosen to configure Database Control for managing the database traxmeta
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /usr/oracle/app/trax/oracle/product/10.2.0/db_1

Database hostname ................ dev-trax-db.bsg.sa.tensy.com
Listener port number ................ 1521
Database SID ................ traxmeta
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Mar 1, 2009 6:59:44 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /usr/oracle/app/trax/oracle/product/10.2.0/db_1/cfgtoollogs/emca/traxmeta/emca_2009-03-06_06-59-07-PM.log.
Mar 1, 2009 6:59:45 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 1, 2009 6:59:46 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Mar 1, 2009 6:59:46 PM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing /usr/oracle/app/trax/oracle/product/10.2.0/db_1/sysman/admin/emdrep/bin/RepManager -connect (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dev-trax-db.bsg.sa.tensy.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=traxmeta.bsg.sa.tensy.com))) -repos_user SYSMAN -action drop -verbose -output_file /usr/oracle/app/trax/oracle/product/10.2.0/db_1/cfgtoollogs/emca/traxmeta/emca_repos_drop_2009-03-06_06-59-46-PM.log
Mar 1, 2009 6:59:46 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error dropping the repository
Mar 1, 2009 6:59:46 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Refer to the log file at /usr/oracle/app/trax/oracle/product/10.2.0/db_1/cfgtoollogs/emca/traxmeta/emca_repos_drop_<date>.log for more details.
Mar 1, 2009 6:59:46 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error dropping the repository
Refer to the log file at /usr/oracle/app/trax/oracle/product/10.2.0/db_1/cfgtoollogs/emca/traxmeta/emca_2009-03-06_06-59-07-PM.log for more details.
Could not complete the configuration. Refer to the log file at /usr/oracle/app/trax/oracle/product/10.2.0/db_1/cfgtoollogs/emca/traxmeta/emca_2009-03-06_06-59-07-PM.log for more details.
-bash-3.00$
***********************************************************************************************************************
THEN LOOKING AT the LOG

-bash-3.00$ more emca_repos_drop_2009-03-06_06-59-46-PM.log
[06-03-2009 18:59:46] Enter SYS user's password :
[06-03-2009 18:59:46]
[06-03-2009 18:59:46] Enter repository user password :
[06-03-2009 18:59:46]
[06-03-2009 18:59:46] Getting temporary tablespace from database...
[06-03-2009 18:59:46] Found temporary tablespace: TEMP
[06-03-2009 18:59:46] Environment :
[06-03-2009 18:59:46] ORACLE HOME = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1
[06-03-2009 18:59:46] REPOSITORY HOME = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1
[06-03-2009 18:59:46] SQLPLUS = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1/bin/sqlplus
[06-03-2009 18:59:46] SQL SCRIPT ROOT = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1/sysman/admin/emdrep/sql
[06-03-2009 18:59:46] EXPORT = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1/bin/exp
[06-03-2009 18:59:46] IMPORT = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1/bin/imp
[06-03-2009 18:59:46] LOADJAVA = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1/bin/loadjava
[06-03-2009 18:59:46] JAR FILE ROOT = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1/sysman/admin/emdrep/lib
[06-03-2009 18:59:46] JOB TYPES ROOT = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1/sysman/admin/emdrep/bin
[06-03-2009 18:59:46] Arguments :
[06-03-2009 18:59:46] Connect String = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS
=(PROTOCOL=TCP)(HOST=dev-trax-db.bsg.sa.tensy.com)(PORT=1521)))(CONNECT_DAT
A=(SERVICE_NAME=traxmeta.bsg.sa.tensy.com)))
[06-03-2009 18:59:46] Action = drop
[06-03-2009 18:59:46] Repos User = SYSMAN
[06-03-2009 18:59:46] Default tablespace = MGMT_TABLESPACE
[06-03-2009 18:59:46] Default Data file = mgmt.dbf
[06-03-2009 18:59:46] Dflt Dfile Init size = 20m
[06-03-2009 18:59:46] Dflt Dfile Ext size = 20m
[06-03-2009 18:59:46] ECM tablespace = MGMT_ECM_DEPOT_TS
[06-03-2009 18:59:46] ECM Data file = mgmt_ecm_depot1.dbf
[06-03-2009 18:59:46] ECM Dfile Init size = 100m
[06-03-2009 18:59:46] ECM Dfile Ext size = 100m
[06-03-2009 18:59:46] TEMP tablespace = TEMP
[06-03-2009 18:59:46] Create options = 3
[06-03-2009 18:59:46] Verbose output = 1
[06-03-2009 18:59:46] Output File = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1/cfgtoollogs/emca/traxmeta/emca_repos_drop_2009-03-06_06-59-46-PM.lo
g
[06-03-2009 18:59:46] Repos creation mode = CENTRAL
[06-03-2009 18:59:46] MetaLink user name = _NOT_AVAILABLE_
[06-03-2009 18:59:46] MetaLink URL = http://updates.oracle.com
[06-03-2009 18:59:46] Export Directory = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1/sysman/log
[06-03-2009 18:59:46] Import Directory = /usr/oracle/app/trax/oracle/product
/10.2.0/db_1/sysman/log
[06-03-2009 18:59:46] Checking SYS Credentials ...
[06-03-2009 18:59:46] File - /tmp/AJg9ljsznf/AV6rPap5wK.sql.

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 1 18:59:46 2009

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

Enter user-name: ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_identifier>] | /
Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[06-03-2009 18:59:46] Return code = 1.
[06-03-2009 18:59:46] Failed
[06-03-2009 18:59:46] SYS credentials or connect string is invalid.
-bash-3.00$

Tom:

I do not understand what you mean. The above was run on server where OEM and db are.

The client is Oracle Enterprise Manager. It is sitting on the server where the database is.

Does not OEM lookup the service name under oracle home on the server.

Tom:
I do not have direct access to this server nor i installed the product. I was trying to assist a group who just started with oracle and do not much about it.

Is there any directories/files/configurations you want me to verify on Monday.

Tom:

here is the listener and tns names config files. Does that tell you something is missing for OEM. The OEM web page would nto even come up. I found that server has also instant client in addition to regular client ( i assume).

# listener.ora Network Configuration File: /usr/oracle/app/dqxi/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 = /usr/oracle/app/trax/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = dev-trax-db.bsg.sa.tensy.com)(PORT = 1521))
)
)




# tnsnames.ora Network Configuration File: /usr/oracle/app/dqxi/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TRAXMETA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev-trax-db.bsg.sa.tensy.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev-trax-db.bsg.sa.tensy.com)
)
)

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

Tom:

here are the logs you want to see. The issue might me related to OEM using SSL to connect.
It seems it tries to use https instead of http. the configuration on test and prod does not use SSL.
the installer also did not mean to install SSL. is there a way to change this to http too.

# The startup on dev looks like this:

-bash-3.00$ emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
https://dev-trax-db.bsg.sa.tensy.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control .........................
.................................................................... failed.
------------------------------------------------------------------
Logs are generated in directory /usr/oracle/app/trax/oracle/product/10.2.0/db_1/dev-trax-db.bsg.sa.tensy.com_traxmeta/sysman/log
-bash-3.00$

# The log emdctl.trc contains this information:

-bash-3.00$ pwd
more emdctl.trc
2005-03-05 21:55:56 Thread-3086899424 WARN http: snmehl_connect: connect failed
to (dev-trax-db.bsg.sa.tensy.com:3938): Connection refused (error = 111)
2005-03-05 21:56:03 Thread-3086899424 ERROR ssl: nzos_Handshake failed, ret=2902
4
2005-03-05 21:56:03 Thread-3086899424 ERROR http: 6: Unable to initialize ssl co
nnection with server, aborting connection attempt
2005-03-05 21:56:07 Thread-3086899424 ERROR ssl: nzos_Handshake failed, ret=2902
4
2005-03-05 21:56:07 Thread-3086899424 ERROR http: 6: Unable to initialize ssl co
nnection with server, aborting connection attempt
2005-03-05 21:56:10 Thread-3086899424 ERROR ssl: nzos_Handshake failed, ret=2902
4
2005-03-05 21:56:10 Thread-3086899424 ERROR http: 6: Unable to initialize ssl co
nnection with server, aborting connection attempt
2005-03-05 21:56:13 Thread-3086899424 ERROR ssl: nzos_Handshake failed, ret=2902
4
2005-03-05 21:56:13 Thread-3086899424 ERROR http: 6: Unable to initialize ssl co
nnection with server, aborting connection attempt
2005-03-05 21:56:16 Thread-3086899424 ERROR ssl: nzos_Handshake failed, ret=2902
4
2005-03-05 21:56:16 Thread-3086899424 ERROR http: 6: Unable to initialize ssl co
nnection with server, aborting connection attempt
-bash-3.00$

# The WARN error occurs on test also but on test the OEM tool start succeeds.
# I can get to the OEM website using https instead of http.
# https://dev-trax-db.bsg.sa.tensy.com:1158/em/console/logon/logon
# But I do get certificate errors that I have to ignore.

# This is the output of some lsnrctl commands:

-bash-3.00$ lsnrctl services

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-MAR-2005 22:38:05

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

Connecting to (DESCRIPTION=(CONNECT_TIMEOUT=0)(CONNECT_LISTENER_TIMEOUT=0)(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "trax.bsg.sa.tensy.com" has 1 instance(s).
Instance "traxmeta", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:73 refused:0 state:ready
LOCAL SERVER
Service "traxmetaXDB.bsg.sa.tensy.com" has 1 instance(s).
Instance "traxmeta", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: dev-trax-db.bsg.sa.tensy.com, pid: 13156>
(ADDRESS=(PROTOCOL=tcp)(HOST=dev-trax-db.bsg.sa.tensy.com)(PORT=12922))
Service "traxmeta_XPT.bsg.sa.tensy.com" has 1 instance(s).
Instance "traxmeta", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:73 refused:0 state:ready
LOCAL SERVER
The command completed successfully
-bash-3.00$

-bash-3.00$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-MAR-2005 22:38:32

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

Connecting to (DESCRIPTION=(CONNECT_TIMEOUT=0)(CONNECT_LISTENER_TIMEOUT=0)(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 05-MAR-2005 20:21:06
Uptime 0 days 2 hr. 17 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /usr/oracle/app/trax/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /usr/oracle/app/trax/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))(CONNECT_TIMEOUT=0)(CONNECT_LISTENER_TIMEOUT=0))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev-trax-db.bsg.sa.tensy.com)(PORT=1521))(CONNECT_TIMEOUT=0)(CONNECT_LISTENER_TIMEOUT=0))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "traxmeta.bsg.sa.tensy.com" has 1 instance(s).
Instance "traxmeta", status READY, has 1 handler(s) for this service...
Service "traxmetaXDB.bsg.sa.tensy.com" has 1 instance(s).
Instance "traxmeta", status READY, has 1 handler(s) for this service...
Service "traxmeta_XPT.bsg.na.tensy.com" has 1 instance(s).
Instance "traxmeta", status READY, has 1 handler(s) for this service...
The command completed successfully
-bash-3.00$

Tom:

that should be 2009. i must have replaced the 9 with 5 by mistake.

Tom:

i replaced the 9 with 5.

The issue is somehow SSL related. Do you think there can be a bug. Can you reconfigure OEM not to use SSL.

Hi Tom,

I have a query about tnsnames.ora, suppose i have a connection string which connects me to a Database on some Host

Now on client machine i will insert that tns entry into the tnsnames.ora on client machine i.e


the thing that confuses me is
Do i need to add this tns entry on DB server Host too ?

Hi,

Just want to know, where tnsnames.ora can be defined in two locations ? for e.g. first search in location a and does not found in location a then search in location b ?

thanks & regards
PJP

access("/home/tkyte/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/tnsnames.ora", F_OK)       = -1 ENOENT (No such file or directory)
access("/home/ora10gr2/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora", F_OK) = 0
stat64("/home/ora10gr2/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora", {st_mode=S_IFREG|0640, st_size=552, ...}) = 0
open("/home/ora10gr2/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora", O_RDONLY|O_LARGEFILE) = 7
read(7, "# tnsnames.ora Network Configura"..., 4096) = 552
stat64("/home/ora10gr2/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora", {st_mode=S_IFREG|0640, st_size=552, ...}) = 0

Tom,

you are referencing a database guide as support for your "ifile" parameter. I cannot find "ifile" in the 10G Network reference manual (Part Number B14213-01), nor in the Net Services Administrators guide (Part Number B14212-02).

A quick test shows it actually does work, though. Poorly documented feature? I could only find it in the Heterogeneous Services Initialization Parameters section, using the Master Index. And after all, your initial response was that "ifile" was not supported.

Tom:

We connect to our database using service tstdv04.svc.com.

1) When i queried gv$session where service_name ='SYS$BACKGROUND', I see those connections are from Oracle background process (gv$session.program)

2) What is this 'SYS$USERS' is about? How it got mapped to my account ('rjeyaba'). where i am connecting to DB using service 'tstdv04.svc.com'.

rajesh@ORA10GR2> SELECT distinct service_name
  2  FROM gv$session;

SERVICE_NAME
--------------------
tstdv04.svc.com
SYS$BACKGROUND
SYS$USERS

Elapsed: 00:00:00.10
rajesh@ORA10GR2>
rajesh@ORA10GR2> SELECT distinct service_name,osuser,username
  2  FROM gv$session
  3  where service_name='SYS$USERS'
  4  /

SERVICE_NAME         OSUSER     USERNAME
-------------------- ---------- ----------
SYS$USERS            oracle     SYS
SYS$USERS            rjeyaba    TST_ODS
SYS$USERS            rjeyaba    TSTESB

Elapsed: 00:00:00.15
rajesh@ORA10GR2>

ops$tkyte%ORA11GR2> connect ops$tkyte/foobar@ora11gr2
Connected.
ops$tkyte%ORA11GR2> select service_name from v$session where username = user;

SERVICE_NAME
----------------------------------------------------------------
ora11gr2.localdomain

ops$tkyte%ORA11GR2> connect ops$tkyte/foobar
Connected.
ops$tkyte%ORA11GR2> select service_name from v$session where username = user;

SERVICE_NAME
----------------------------------------------------------------
SYS$USERS

ops$tkyte%ORA11GR2>