Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Doing a sqlplus with a user different than oracle gives ORA-12523

Miguel Silva Rentes
Regular Advisor

Doing a sqlplus with a user different than oracle gives ORA-12523

Hi everyone!

I'm trying to connect to "sqlplus user/pass" using a different user than oracle. I can do this as user oracle but as user scatex it gives me the following error:

[RTO1CCOP-SRV03]/usr/users/scatex> sqlplus scatex/scatex

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 7 12:15:53 2008

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

ERROR:
ORA-12523: TNS:listener could not find instance appropriate for the client
connection


Enter user-name:

Can you help me with this problem? The ORACLE_SID for user scatex is the same as for user oracle and I also have the TWO_TASK environment variable set. I got an answer from oracle stating I should run changePerms.sh script but I can't seem to find it in $ORACLE_HOME/install folder. Did anyone have this problem and knows how to solve it? I'm desperate for solving this problem.

Best regards,

Miguel Rentes
11 REPLIES
Paul Sperry
Honored Contributor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

I dont have a changePerms.sh anywhere in my ORACLE_HOME either.

Have you tried running lsnrctl services
Miguel Silva Rentes
Regular Advisor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

Hi!

In order to have the script changePerm.sh under $ORACLE_HOME/install it's necessary to install patch 4516865. I did that and everything worked fine. But I still can't connect to sqlplus as user scatex.

Doing a "lsnrctl services" I have the output:

[RT01CCO]/usr/users/scatex> lsnrctl services

LSNRCTL for HPUX: Version 10.2.0.1.0 - Production on 07-MAR-2008 17:03:59

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.18.200.139)(PORT=1521))(SERVER=shared))
Services Summary...
Service "prt" has 1 instance(s).
Instance "prt", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully

When I try to do a "sqlplus scatex/scatex" as user scatex I have another error now:

[RT01CCO]/usr/users/scatex> sqlplus scatex/scatex

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 7 17:04:15 2008

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

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


Enter user-name:

Can anyone help? I'm feeling hopeless...
Jeeshan
Honored Contributor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

Did you try with
sqlplus user/pass@database name.
a warrior never quits
Miguel Silva Rentes
Regular Advisor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

Yes, it also gives me the same error:

[RT01CCO]/usr/users/scatex> printenv ORACLE_SID
prt
[RT01CCO]/usr/users/scatex> printenv TWO_TASK
prt
[RT01CCO]/usr/users/scatex> sqlplus scatex/scatex@prt

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 7 17:33:59 2008

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

ERROR:
ORA-12519: TNS:no appropriate service handler found


Enter user-name:

Any ideas? =(
Paul Sperry
Honored Contributor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

Your service name might have an alias, so check for global (world) entries and local entries. Check $ORACLE_HOME/network/admin/tnsnames.ora.
Miguel Silva Rentes
Regular Advisor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

My $ORACLE_HOME/network/admin/tnsnames.ora only has these lines:

prt =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.200.139)(PORT = 1521))
)
(CONNECT_DATA =
(SID = prt)
(SERVER = SHARED)
)
)

=(
Paul Sperry
Honored Contributor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

restart the listener.
then as sysdba
ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE

Then try connecting as scatex
Yogeeraj_1
Honored Contributor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

hi Miguel,

can you post the output of the following command:

$ lsnrctl services


Also, can you please confirm that you are getting the same error when connecting from another client.

revert

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jeeshan
Honored Contributor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

please copy your user .profile environment here.
a warrior never quits
AINMS
Respected Contributor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

Hi,
Try to but user in dba group.

HTH
AINMS
If it were all easy, we would not have anything to do.
Miguel Silva Rentes
Regular Advisor

Re: Doing a sqlplus with a user different than oracle gives ORA-12523

Hi everyone!

Problem solved. It turns out that oracle needs the hostname (in /etc/hosts) and node name (in /etc/rc.config.d/netconf) to be at most 8 characters long and that otherwise my shared oracle server listener always started with an unknow state. Our company's dba found out this problem by mere change because all our oracle servers have small host names. The one that gave this problem had a name 14 characters long... I don't know why oracle has this strange limitation but in fact replacing the host name by a smaller name did solve this issue. Does anybody have other information regarding this name limitation?

Best regards,

Miguel Rentes