Connect as: normal with Oracle 12c and Toad 12 FAILS

[neoyoyo.mail]

Connect as: normal with Oracle 12c and Toad 12 FAILS.

I’ve been looked at forums, blogs and wikies but I couldn’t found why my new user, that I created before, can’t connect as NORMAL but as SYSDBA it can.

I don’t know why this occurs.

My intention is create a user/schema in a local DB to have my own db.

I can create a new user but when I go to connect to this new schema I only connect as SYSDBA, this is a big trouble because I can “see” object’s DBA and I don’t want this, I want create my own new schema db and not share name’s object or name’s tables with DBA.

For example, object JOB exists as SYSDBA and I can’t create a new table with this name.

Please, help me.

Thanks very much for read me.

[NormTeamT]

Hi neoyoyo,
On 25/07/13 12:10, neoyoyo.mail wrote:

Connect as: normal with Oracle 12c and Toad 12 FAILS.
Do you get any error messages at all?
What user are you connecting to in the database?
Does it have "create session" privilege granted?
I've been looked at forums, blogs and wikies but I couldn't found why my
new user, that I created before, can't connect as NORMAL but as SYSDBA
it can.
When you connect to ANY user "as sysdba" you actually connect to SYS, not the user you thought you were connecting to as shown in this example from sqlplus:
$ sqlplus nod/nod as sysdba
SQLPlus: Release 11.2.0.2.0 Production on Thu Jul 25 12:36:08 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option
SQL> show user
USER is "SYS"
So that explains why you can connect as SYSDBA to your new user.
I don't know why this occurs.
Neither do I at the moment, but if you did get any error messages etc, they will help.
What happens when you try to connect as normal to your user using sql*plus instead of Toad? Do you connect? So yo get the same errors?
...

For example, object JOB exists as SYSDBA and I can't create a new table
with this name.
JOB is a reserved word and you cannot create objects with reserved words as names. See V$RESERVED_WORDS for details of what is not allowed.
select keyword from v$reserved_words order by keyword;
In my 11g database, that's 1846 reserved words to avoid!
-- Cheers,
Norm. [TeamT]

[neoyoyo.mail]

First of all, thanks for answer so quickly.

The first image is the error message that Toad show.

Second image is user SYSTEM loggon as SYSDBA.

Third image is user SYSTEM loggon as Normal.

Forth image is my new USER loggon as SYSDBA.

My new user has 'GRANT CREATE SESSION TO IMEILOCAL' that you said.

That's all I did.

[NormTeamT]

Hi neoyoyo,
On 25/07/13 15:04, neoyoyo.mail wrote:

The first image is the error message that Toad show.
That's better I can see your problem now.
Second image is user SYSTEM loggon as SYSDBA.
Third image is user SYSTEM loggon as Normal.
Forth image is my new USER loggon as SYSDBA.
These are ok, as they are not related to your error.
My new user has 'GRANT CREATESESSION TO IMEICOMCEL' that you said.
Yes, that gives your new user the ability to connect to a database. However, your user is not connecting as normal because you are supplying the wrong password.
You mentioned that this was 12c, well, from 11g onwards there's a setting in the initialisation parameters that tells the database whether or not the password is to be checked with case ignored or case exact. That setting is "sec_case_sensitive_logon" and defaults to true meaning that you must supply the password in exactly the same letter case as you created it.
The error message you are getting is saying that either the username or the password is wrong. I suspect the username is ok, but the password could be the cause.
Login as SYSDBA again and run the following:
alter user IMEICOMCEL identified by passwordx;
That will set a temporary password, all in lower case, of passwordx. If it works, you can move on. If not, then your username isn't as specified! Find the correct username as follows (still as SYSDBA):
select username from all_users order by 1;
Somewhere in the list , you should find your username - check the spelling. if you don;t see it, then your creation of the user failed and you need to recreate it.
Assuming it is present, and spelt correctly, try connecting as your user with this new password, as normal:
connect IMEICOMCEL/passwordx
You should be able to connect. If not, and you get the same error, then one of two things is happening:

• the username isn't what you are using;
• you may be connecting to the wrong database.
  If you were able to reset the password as above, then you are in the correct database and you do have the correct username so you should be able to connect.
  To make sure you are in the correct database, try this:
  connect IMEICOMCEL/passwordx@db_name
  Obviously, replace db_name with the name of your database. Obviously this requires that the listener for the database is running - you do have a listener?
  This method of connecting also requires an entry in tnsnames.ora - have you set one of those up?
  If it still fails, we will probably need to see the output from trying to connect with sqlplus to this user, the output from a tnsping of the database, lsnrctl status output and a look at your listener.ora and tnsnames.ora files.
  Cheers,
  Norm.
  -- Cheers,
  Norm. [TeamT]

[neoyoyo.mail]

Hello Norm,

Thanks you very much for your reply again.

These are ok, as they are not related to your error.

I think these are related, I'll explain you why. Because with these pictures you can see that I can connect with my new user and System user, but in the last case I can logon as Normal Mode and Sysdba Mode.

My question is... why can't connect as Normal Model with my new User?? Only as SYSDBA why??

Neither do I at the moment, but if you did get any error messages etc, they will help.

The unique error message that Toad show is the "ErrorMessage" I attached before. It occurs when I try to loggin as Normal with my new user.

Login as SYSDBA again and run the following:

alter user IMEILOCAL identified by passwordx;

I did it and it worked ok, but when I went to loggin with my new user and try to connect as Normal, Toad showed me the same error message. In other hand, if I connected as SYSDBA with the new password and user, it works OK.

select username from all_users order by 1;

USERNAME USER_ID CREATED COMMON ORACLE_MAINTEINED

IMEILOCAL 117 24/07/2013 16:41:35 NO N

In SQL Plus, you writted me:

connect IMEICOMCEL/passwordx

And now see this:

If you were able to reset the password as above, then you are in the correct database and you do have the correct username so you should be able to connect.

I show you before that I could change the password for my new user, loggon as System in SYSDBA mode.

connect IMEICOMCEL/passwordx@db_name

And now see this:

Obviously this requires that the listener for the database is running - you do have a listener?

And now see this:

This method of connecting also requires an entry in tnsnames.ora - have

you set one of those up?

Generated by Oracle configuration tools.

LISTENER_ORCL =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

(CONNECT_DATA =

(SID = CLRExtProc)

(PRESENTATION = RO)

)

)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.iecisa.corp)

)

)

If it still fails, we will probably need to see the output from trying

to connect with sqlplus to this user, the output from a tnsping of the

database, lsnrctl status output and a look at your listener.ora and

tnsnames.ora files.

listener.ora Network Configuration File: C:\app\X05699SA\product\12.1.0\dbhome_1\network\admin\listener.ora

Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = C:\app\X05699SA\product\12.1.0\dbhome_1)

(PROGRAM = extproc)

(ENVS = "EXTPROC_DLLS=ONLY:C:\app\X05699SA\product\12.1.0\dbhome_1\bin\oraclr12.dll")

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

)

)

I try to do all that you said me and show you all steps you commeted.

If you have any idea for "fix" this problem, I'll very thankfull with you.

Cheers.

Neoyoyo.

[NormTeamT]

Morning neoyoyo,

ok, here’s the deal on SYSDBA versus NORMAL users.

If I connect as SYSTEM as SYSDBA, I will NOT be connecting as SYSTEM, I will in fact be connecting as SYS - as I showed you in my first reply. If I connect as your user AS SYSDBA I will not be connecting to your user, but to SYS.

This is why it appears that you can login to your user AS SYSDBA, but you are not, you are connecting as SYS instead.

So, that’s a red herring I’m afraid.

Oh, your images are not coming through to me by the way, I use my email system and I get your text, but not the images. I’m having to logon to the web site to see them

The fact that when you tried to login as your user, as normal, with the changed password, and still got the invalid username/password error is interesting. This means that the problem isn’t with Toad, but with Oracle. Hmmm.

Equally, attempting to connect with a database name attached (ORCL I believe) is failing too. However, you appear to be using “ezconnect” format strings. Try just using the database name after the ‘@’ - in your case, it looks to be orcl. So:

connect IMEICOMCEL/passwordx@orcl

If this doesn’t work - and it should because you have a listener.ora set up correctly, from what I can see, then I will need to see the following:

tnsping orcl - send me the output from that command. It will show if you are reaching the listener.

sqlnet.ora if you have one. It’s in the same place as listener.ora and tnsnames.ora. It’s not a problem if you don’t have one. At least, not yet!

tnanames.ora if you don’t have one, this is your problem. You will need one. It tells Oracle how to get from “@orcl” to localhost:1521/orcl.

It will look something like the following:

orcl.world,orcl =

(description =

(address_list =

(address = (protocol = tcp)(host = localhost)(port = 1521))

)

(connect_data = (service_name = orcl))

)

This is off the top of my head, and is for an 11g database. I’m not up to 12c yet, so bear in mind that there may well be differences.

My own test (11g) database is called ant12. It accepts me connecting as follows:

connect nod/nod

connect nod/nod@ant12

connect nod/nod@localhost:1521/ant12

And I do not have a sqlnet.ora. I do have a listener.ora set up similar to your one, and a tnsnames.ora set up as shown above, or very similar to it.

However, if you use the “ezconnect” connection string format, as shown in one of your example, then you should not need the tnsnames.ora file, as you are already supplying the details on the connection string. Very strange.

One final thought, do you have a firewall on this computer? It could be that Windows 7’s firewall is preventing you from getting through the network. Even though the database and client (toad, sqlplus) are on the same computer, using the TCP protocol is going to hit the network.

You could try this in a Dos session:

set %ORACLE_SID%=orcl

sqlplus MEICOMCEL/passwordx

If that works, then your problem is almost certainly the firewall.

HTH

Cheers,

Norm. [TeamT]

[neoyoyo.mail]

Hello Norm,

tnsping orcl - send me the output from that command. It will show if you are reaching the >listener.

C:\Users\x05699sa>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 26-JUL-2

013 16:17:30

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

Archivos de parßmetros utilizados:

C:\app\X05699SA\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Adaptador TNSNAMES utilizado para resolver el alias

Intentando contactar con (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = local

host)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.ie

cisa.corp)))

Realizado correctamente (250 mseg)

sqlnet.ora if you have one. It's in the same place as listener.ora and tnsnames.ora. It's not a >problem if you don't have one. At least, not yet!

sqlnet.ora Network Configuration File: C:\app\X05699SA\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Generated by Oracle configuration tools.

This file is actually generated by netca. But if customers choose to

install "Software Only", this file wont exist and without the native

authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tnanames.ora if you don't have one, this is your problem. You will need one. It tells Oracle >how to get from "@orcl" to localhost:1521/orcl.

tnsnames.ora Network Configuration File: C:\app\X05699SA\product\12.1.0\dbhome_1\network\admin\tnsnames.ora

Generated by Oracle configuration tools.

LISTENER_ORCL =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

(CONNECT_DATA =

(SID = CLRExtProc)

(PRESENTATION = RO)

)

)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.iecisa.corp)

)

)

You could try this in a Dos session:

set %ORACLE_SID%=orcl

sqlplus MEICOMCEL/passwordx

If that works, then your problem is almost certainly the firewall.

Ouch! That doesn't work.

I think is something related with Oracle 12c.

Thanks very much Norm!

[NormTeamT]

Afternoon neoyoyo,
On 26/07/13 15:30, neoyoyo.mail wrote:

tnsping orcl - send me the output from that command. It will show
if you are reaching the >listener.

C:\Users\x05699sa>tnsping orcl
Archivos de parßmetros utilizados:
C:\app\X05699SA\product\12.1.0\dbhome_1\network\admin\sqlnet.ora
This is good ...
Adaptador TNSNAMES utilizado para resolver el alias
Intentando contactar con (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST
= local
host)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
orcl.ie
cisa.corp)))
This is good too ...
Realizado correctamente (250 mseg)
This is excellent. You are reaching the database listener for the "orcl" database. So far so good.

sqlnet.ora Network Configuration File:

C:\app\X05699SA\product\12.1.0\dbhome_1\network\admin\sqlnet.ora

Generated by Oracle configuration tools.

This file is actually generated by netca. But if customers choose to

install "Software Only", this file wont exist and without the native

authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
I cannot see anything wrong here. I have a feeling that the SQLNET.AUTHENTICATION_SERVICES has been known to cause problems in the past. I wonder what would happen if you commented it out (add a '#' to the start of the line) and then tried again.
I'm not 100% certain, but I'm sure I've had to do that in the past, I just can't remember why or what the problem was - it was some time ago.

tnsnames.ora Network Configuration File:

C:\app\X05699SA\product\12.1.0\dbhome_1\network\admin\tnsnames.ora

Generated by Oracle configuration tools.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.iecisa.corp)
)
)
Looks ok. The service_name part shows the full domain name of the database though. What does the command:
show parameter service_names
Return? I can't remember why, but sometimes this parameter appears in upper case and whetver case it is, needs to match that in tnanmes.ora.
Equally, what happens when you change the service_name, in tnsnames.ora, from "orcl.iecisa.corp" to just "orcl"?

set %ORACLE_SID%=orcl
sqlplus MEICOMCEL/passwordx

If that works, then your problem is almost certainly the firewall.
Ouch! That doesn't work.
Hmmm, what did you see as an error? The same messages about username/password being invalid? Or something else?
Actually, my mistake, it should be the following:
set ORACLE_SID=orcl
sqlplus IMEICOMCEL/passwordx
I think is something related with Oracle 12c.
I'm sure we'll crack it though. Problem is, I'm no expert of Oracle on Windows, my working life has been spent with Oracle on various flavours of Unix and Linux. There are differences!
-- Cheers,
Norm. [TeamT]

[NormTeamT]

On 26/07/13 16:00, Norm [TeamT] wrote:

Looks ok. The service_name part shows the full domain name of the
database though. What does the command:

show parameter service_names
Sorry, I meant when you are connected as SYSDBA to the database.
Return? I can't remember why, but sometimes this parameter appears in
upper case and whetver case it is, needs to match that in tnanmes.ora.

Equally, what happens when you change the service_name, in tnsnames.ora,
from "orcl.iecisa.corp" to just "orcl"?
I just tried this in my own 11g database. I set the service_name in tnsnames.ora to a full domain, like yours is above. It refused to connect! My service_names in the database shows:
ant12
Which is what I need to have in tnsnames.ora to allow connections.
I also tried changing the service name in tnsnames.ora to upper case, but that still worked, so whatever it was that caused me problems in the part is fixed at 11g!
-- Cheers,
Norm. [TeamT]

[neoyoyo.mail]

Good Aftenoon Norm.

You wrote me:

show parameter service_names

C:\Users\x05699sa>sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Vie Jul 26 18:31:03 2013

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

Introduzca el nombre de usuario: system

Introduzca la contrase±a:

Hora de ┌ltima Conexi¾n Correcta: Vie Jul 26 2013 18:19:13 +02:00

Conectado a:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt

ions

SQL> show parameter service_names

NAME TYPE VALUE

---

service_names string orcl.iecisa.corp

SQL>

In other way, you wrote me:

set ORACLE_SID=orcl

sqlplus IMEICOMCEL/passwordx

C:\Users\x05699sa>set ORACLE_SID=ORCL

C:\Users\x05699sa>SQLPLUS IMEICOMCEL/passwordx

SQL*Plus: Release 12.1.0.1.0 Production on Vie Jul 26 18:34:21 2013

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

ERROR:

ORA-01017: invalid username/password; logon denied

Introduzca el nombre de usuario:

Neither It was Not work

I just tried this in my own 11g database. I set the service_name in

tnsnames.ora to a full domain, like yours is above. It refused to

connect! My service_names in the database shows:

ant12

Which is what I need to have in tnsnames.ora to allow connections.

I'll change my tnsnames.ora and then I'll tell you.

Thanks for all.

Cheers

Neoyoyo.

[neoyoyo.mail]

Norm, bad news I’ve just tried to logon with my new user connect as Normal Mode but It hadn’t worked :((

What can we do?

[NormTeamT]

Sorry neoyoyo, I replied to this email but it doesn’t seem to have appeared here.

Have you tried, as I suggested a couple of times previously. to comment out the following line from sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

I haven’t seen any replies from you to say that you did and that it worked/didn’t work.

Cheers,

Norm.

[damir.vadas_250]

How does this thread has anything with Toad?