Home » RDBMS Server » Server Administration » Cannot connect to database
Cannot connect to database [message #634808] Mon, 16 March 2015 08:43 Go to next message
ramy_send2010@yahoo.com
Messages: 8
Registered: March 2015
Junior Member
Hello All,



I'm new to Oracle Database.

I changed the password for sys user using

alter user SYS identified by "P@ssw0rd"

and tried to connect using the following command

sqlplus /nolog

SQL> connect sys as sysdba

Enter password:

ERROR:

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



SQL>



how can I solve this error ?



Thanks & Regards
Re: Cannot connect to database [message #634810 is a reply to message #634808] Mon, 16 March 2015 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> CONNECT / AS SYSDBA
Re: Cannot connect to database [message #634812 is a reply to message #634808] Mon, 16 March 2015 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post the result of "set" or "env | sort" depending you are on Windows or Unix/Linux.

Re: Cannot connect to database [message #634813 is a reply to message #634808] Mon, 16 March 2015 09:17 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
ramy_send2010@yahoo.com wrote on Mon, 16 March 2015 08:43
Hello All,



I'm new to Oracle Database.

I changed the password for sys user using

alter user SYS identified by "P@ssw0rd"

and tried to connect using the following command

sqlplus /nolog

SQL> connect sys as sysdba

Enter password:

ERROR:

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



SQL>



how can I solve this error ?



Thanks & Regards

This can be a tricky one, but yet simple once you understand it.

When you call sqlplus, the '@' chracter is taken as a special character to say that what immeidately follows is a net service name. So when you include '@' in your password, sqlplus parses it out as



username = sys
password = P
net serice name = ssword.

See (ora-12154 TNS:could not resolve the connect identifier
Re: Cannot connect to database [message #634814 is a reply to message #634813] Mon, 16 March 2015 09:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Gee! I didn't see that!

Re: Cannot connect to database [message #634815 is a reply to message #634814] Mon, 16 March 2015 09:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
But OP isn't specifying the password in the sqlplus call, so how is that relevant?
Re: Cannot connect to database [message #634816 is a reply to message #634815] Mon, 16 March 2015 09:24 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
This I have to test...
Re: Cannot connect to database [message #634817 is a reply to message #634813] Mon, 16 March 2015 09:25 Go to previous messageGo to next message
ramy_send2010@yahoo.com
Messages: 8
Registered: March 2015
Junior Member

Thanks for response.

SQL> CONNECT / AS SYSDBA ---> I can connect without any problem



the result of "set" command for windows

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Administrator.WIN-4RNKTD0ENN2>set
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\Administrator.WIN-4RNKTD0ENN2\AppData\Roaming
CLASSPATH=.
CommonProgramFiles=C:\Program Files\Common Files
CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
CommonProgramW6432=C:\Program Files\Common Files
COMPUTERNAME=WIN-4RNKTD0ENN2
ComSpec=C:\Windows\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\Administrator.WIN-4RNKTD0ENN2
LOCALAPPDATA=C:\Users\Administrator.WIN-4RNKTD0ENN2\AppData\Local
LOGONSERVER=\\WIN-4RNKTD0ENN2
NUMBER_OF_PROCESSORS=8
ORACLE_BASE=C:\app\Administrator
ORACLE_HOME=C:\app\Administrator\product\11.2.0\dbhome_1
ORACLE_SID=mysid
OS=Windows_NT
Path=C:\app\Administrator\product\11.2.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\Syst
em32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\ibm\gsk8\lib64;C:\Program Fil
es (x86)\ibm\gsk8\lib
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=AMD64
PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 42 Stepping 7, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=2a07
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files
ProgramFiles(x86)=C:\Program Files (x86)
ProgramW6432=C:\Program Files
PROMPT=$P$G
PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PUBLIC=C:\Users\Public
SESSIONNAME=Console
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\ADMINI~1.WIN\AppData\Local\Temp
TMP=C:\Users\ADMINI~1.WIN\AppData\Local\Temp
USERDOMAIN=WIN-4RNKTD0ENN2
USERNAME=Administrator
USERPROFILE=C:\Users\Administrator.WIN-4RNKTD0ENN2
windir=C:\Windows
windows_tracing_flags=3
windows_tracing_logfile=C:\BVTBin\Tests\installpackage\csilogfile.log
Re: Cannot connect to database [message #634818 is a reply to message #634816] Mon, 16 March 2015 09:26 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
@OP,

What user were you connected to the database as to change the SYS password?
Re: Cannot connect to database [message #634819 is a reply to message #634818] Mon, 16 March 2015 09:36 Go to previous messageGo to next message
ramy_send2010@yahoo.com
Messages: 8
Registered: March 2015
Junior Member
I changed the SYS password using the following steps.

C:\Users\Administrator.WIN-4RNKTD0ENN2>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 16 07:38:45 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user sys identified by "P@ssw0rd"
2 ;

User altered.

SQL> show user
USER is "SYS"
SQL>

so I think that I'm connected to the database with "Administrator" user. the user I used to install software and member of "ora_dba" group.
Re: Cannot connect to database [message #634820 is a reply to message #634819] Mon, 16 March 2015 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Within a single SQL*Plus session, execute:
conn / as sysdba
alter user sys identified by "P@ssw0rd";
conn sys/"P@ssw0rd" as sysdba

and post the session.
Re: Cannot connect to database [message #634821 is a reply to message #634820] Mon, 16 March 2015 09:46 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
I get:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> conn / as sysdba
Connected.
SQL> alter user sys identified by "P@ssw0rd";

User altered.

SQL> conn sys/"P@ssw0rd" as sysdba
Connected.
SQL>
Re: Cannot connect to database [message #634822 is a reply to message #634819] Mon, 16 March 2015 09:47 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
No, "sqlplus / as sysdba" means connect as SYSDBA. You're logged into your O/S as "Administrator".
Re: Cannot connect to database [message #634823 is a reply to message #634820] Mon, 16 March 2015 09:50 Go to previous messageGo to next message
ramy_send2010@yahoo.com
Messages: 8
Registered: March 2015
Junior Member
Thanks for response

the result of the command is :-

SQL> conn / as sysdba
Connected.
SQL> alter user sys identified by "P@ssw0rd";

User altered.

SQL> conn sys/"P@ssw0rd" as sysdba
Connected.
SQL>




but if I made the following commands, I'll fail to login

SQL> conn sys as sysdba
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

Re: Cannot connect to database [message #634824 is a reply to message #634821] Mon, 16 March 2015 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My answer, if you followed it but maybe we just had the same idea, was not for you but for OP. Wink

Re: Cannot connect to database [message #634825 is a reply to message #634823] Mon, 16 March 2015 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you post a copy of the content of registry key: HKLM\Software\Oracle\KEY_OraDb11g_home1 (this latter is your Oracle home name may not be this one).

Re: Cannot connect to database [message #634826 is a reply to message #634825] Mon, 16 March 2015 10:00 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
It appears that Ed is correct. The below is parsed as if it was just one command-line:
SQL> connect sys as sysdba
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

SQL>

The above assumes that @OP didn't type the quotation marks when prompted for the password.
Re: Cannot connect to database [message #634827 is a reply to message #634824] Mon, 16 March 2015 10:02 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
My attempt was for control purposes, Michel. I know what I did; I don't know for sure what OP did Wink
Re: Cannot connect to database [message #634828 is a reply to message #634825] Mon, 16 March 2015 10:04 Go to previous messageGo to next message
ramy_send2010@yahoo.com
Messages: 8
Registered: March 2015
Junior Member
my registry file is

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1]
"ORACLE_HOME"="C:\\app\\Administrator\\product\\11.2.0\\dbhome_1"
"ORACLE_HOME_NAME"="OraDb11g_home1"
"ORACLE_GROUP_NAME"="Oracle - OraDb11g_home1"
"NLS_LANG"="AMERICAN_AMERICA.WE8MSWIN1252"
"ORACLE_BUNDLE_NAME"="Enterprise"
"OLEDB"="C:\\app\\Administrator\\product\\11.2.0\\dbhome_1\\oledb\\mesg"
"ORAMTS_CP_TRACE_LEVEL"="0"
"ORAMTS_CP_TRACE_DIR"="C:\\app\\Administrator\\product\\11.2.0\\dbhome_1\\oramts\\Trace"
"ORAMTS_CONN_POOL_TIMEOUT"="120"
"ORAMTS_SESS_TXNTIMETOLIVE"="120"
"ORAMTS_NET_CACHE_MAXFREE"="5"
"ORAMTS_NET_CACHE_TIMEOUT"="120000"
"ORAMTS_OSCREDS_MATCH_LEVEL"="OS_AUTH_LOGIN"
"MSHELP_TOOLS"="C:\\app\\Administrator\\product\\11.2.0\\dbhome_1\\MSHELP"
"SQLPATH"="C:\\app\\Administrator\\product\\11.2.0\\dbhome_1\\dbs"
"ORACLE_HOME_KEY"="SOFTWARE\\ORACLE\\KEY_OraDb11g_home1"
"ORACLE_BASE"="C:\\app\\Administrator"
"RDBMS_CONTROL"="C:\\app\\Administrator\\product\\11.2.0\\dbhome_1\\DATABASE"
"RDBMS_ARCHIVE"="C:\\app\\Administrator\\product\\11.2.0\\dbhome_1\\DATABASE\\ARCHIVE"
"ORA_MYSID_AUTOSTART"=hex(2):54,00,52,00,55,00,45,00,00,00
"ORA_MYSID_SHUTDOWN"=hex(2):54,00,52,00,55,00,45,00,00,00
"ORA_MYSID_SHUTDOWNTYPE"=hex(2):69,00,6d,00,6d,00,65,00,64,00,69,00,61,00,74,\
00,65,00,00,00
"ORA_MYSID_SHUTDOWN_TIMEOUT"=hex(2):39,00,30,00,00,00
"ORACLE_SID"="mysid"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1\ODE]
"TraceFileName"="C:\\ODE.trc"
"TraceLevel"="0"
"TraceOption"="0"
".NETFramework"=""

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1\OLEDB]
"CacheType"="Memory"
"EnableCmdTimeout"="0"
"ChunkSize"="100"
"DistribTX"="1"
"FetchSize"="100"
"OSAuthent"="0"
"PLSQLRset"="0"
"PwdChgDlg"="1"
"SchRstLng"="10000"
"UserDefFn"="0"
"DisableRetClause"="1"
"VCharNull"="1"
"TraceCategory"="0"
"TraceFileName"="c:\\OraOLEDB.trc"
"TraceLevel"="0"
"TraceOption"="0"
"SPPrmDefVal"="0"
"StmtCacheSize"="0"
"MetaDataCacheSize"="0"
"DBNotifications"="0"
"DeferUpdChk"="0"

Re: Cannot connect to database [message #634829 is a reply to message #634828] Mon, 16 March 2015 10:15 Go to previous messageGo to next message
ramy_send2010@yahoo.com
Messages: 8
Registered: March 2015
Junior Member
Thanks for support.

when I put the password between quotation, the connection succeeded.
but this is not logic to put password between quotation.
Am I Right ?

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
Re: Cannot connect to database [message #634831 is a reply to message #634829] Mon, 16 March 2015 10:20 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Anything in quotation marks is used as that character by Oracle. Outside quotation marks, Oracle treats the "@" character as signifying what follows is a connection string.
Re: Cannot connect to database [message #634838 is a reply to message #634831] Mon, 16 March 2015 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I agree with OP, I tend to think this is a bug in SQL*Plus.
SQL*Plus itself should add the quotation marks when it sends the connection string to Oracle db, this is not the user that should give them in this case.

And when you use other non-special characters, you can connect without quotation mark:
SQL> alter user michel identified by "P@ssw0rd";

User altered.

SQL> conn michel
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Warning: You are no longer connected to ORACLE.
SQL> conn michel/"P@ssw0rd";
Connected.

SQL> alter user michel identified by "&'()";

User altered.

SQL> conn michel
Enter password:
Connected.
SQL>


Re: Cannot connect to database [message #634840 is a reply to message #634838] Mon, 16 March 2015 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

More, you can set password to P@ssw0rd without using quotation marks using SQL*Plus "password" command!
SQL> password
Changing password for MICHEL
Old password:
New password:
Retype new password:
Password changed
SQL> conn michel/"P@ssw0rd";
Connected.
SQL>
Re: Cannot connect to database [message #634843 is a reply to message #634838] Mon, 16 March 2015 12:46 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Michel Cadot wrote on Mon, 16 March 2015 11:21

I agree with OP, I tend to think this is a bug in SQL*Plus.
SQL*Plus itself should add the quotation marks when it sends the connection string to Oracle db, this is not the user that should give them in this case.



But how would sqlplus know where to put the quotation marks? How would it know if the first occurrence of the @ is supposed to be part of the password, or the delimiter/indicator that what follows is the net service name?

Suppose this tnsnames entry

mydb@mysrv =
  (description =
    (address = (protocol = tcp)(host = itmyserver)(port = 1521))
    (connect_data =
      (SERVICE_NAME = rmcatdev)
    )
  )


so ...

C:\Users\estevens\Documents\sql>sqlplus estevens/*****@mydb@mysrv

SQL*Plus: Release 12.1.0.1.0 Production on Mon Mar 16 12:42:02 2015

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL>
Re: Cannot connect to database [message #634844 is a reply to message #634843] Mon, 16 March 2015 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But how would sqlplus know where to put the quotation marks? How would it know if the first occurrence of the @ is supposed to be part of the password, or the delimiter/indicator that what follows is the net service name?


The service name is not part of the password.
I do not talk about "estevens/*****@mydb@mysrv" but about "estevens@mydb@mysrv" followed by "Enter password: " displayed by SQL*Plus.
There is no doubt for SQL*PLus when it asks for "Enter password:" that what is entered is the password not the service name.

Re: Cannot connect to database [message #634846 is a reply to message #634844] Mon, 16 March 2015 14:09 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Michel Cadot wrote on Mon, 16 March 2015 12:53


The service name is not part of the password.
I do not talk about "estevens/*****@mydb@mysrv" but about "estevens@mydb@mysrv" followed by "Enter password: " displayed by SQL*Plus.
There is no doubt for SQL*PLus when it asks for "Enter password:" that what is entered is the password not the service name.



Ah, point taken.

Lot's of permutations possible at that point, but a limited test seems to indicate that even when prompting for a username OR password, it will still try to parse out a net service name. Perhaps this could be considered acceptable at the username (think of the implications if not - it would have to add another prompt to get a net service name or confirm you want a local connection) but yeah, I'd agree that when specifically prompting for password, it should not try to parse anything else out of it.
Previous Topic: 10g to 11g migration
Next Topic: regarding deleting 80 million records
Goto Forum:
  


Current Time: Thu Mar 28 13:18:30 CDT 2024