14 Replies Latest reply: Jul 26, 2013 10:51 PM by user12288492 RSS

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

    56722c7e-3a21-436d-bd0b-35ed1c36efd1

      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.

        • 1. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
          Sven W.

          does it have the CONNECT priviledge?

           

          grant connect to USER;

          • 2. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
            56722c7e-3a21-436d-bd0b-35ed1c36efd1

            Hi SvenW.

             

            Thanks for answer.

            Yes, it has this grant. At any way this new user can connect as SYSDBA mode, but it can't connect as Normal mode.

            I don't why

             

            Please, help me.

            • 3. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
              Sven W.

              Well you don't give much information. Like, the error message that you get when trying, or a sql*plus log that shows which connect command you use, etc.

              My next guess would be, that you don't have a oracle client configured.

               

              On the server (=localhost) you can always connect as sysdba. But as soon as you start using client connect logic, then it might fail for several reasons (missing listener, wrongly configured tnsnames, etc.).

              • 4. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                Peter

                Can you try to log on using SQL*Plus?

                If that succeeds in a normal way, it might be a bug in Toad 12

                • 5. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                  56722c7e-3a21-436d-bd0b-35ed1c36efd1

                  Hi SvenW.

                   

                  First of all, thanks for answer so quickly.

                  The first image is the error message that Toad show.

                  I can't attach images . The message that Toad showed was ORA-01017: Invalid username/password; logon denied.

                  I can logon as System user as SYSDBA and Normal mode, and I can logon as my new user as SYSDBA but not as Normal mode.

                   

                  >My next guess would be, that you don't have a oracle client configured.

                  That's not my case, I have a oracle cliente, listener, tnsnames, etc... well configured. I show you:

                  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)

                     )

                  )

                   

                  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.

                   

                  Thanks you.

                  • 6. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                    56722c7e-3a21-436d-bd0b-35ed1c36efd1

                    I tried it and I hadn't succes . I got the same error message like in Toad 12.

                    ORA-01017: Invalid username/password; logon denied.

                     

                    tHANKS Peter.

                    • 7. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                      Sven W.

                      How about believing the error message: your password is wrong.

                      Try to reset the password with a different DBA account.

                       

                      If you connect on the server as sysdba then no password is needed, at least for the SYS account.

                      In fact if you enter any password there, it is silently ignored.

                      connect / as sysdba  works too.

                       

                      ALso starting from 11g onwards the passwords might be case sensitive

                      http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams211.htm#REFRN10299

                       

                      One more idea: check if the account is locked. This can happen after several failed logon attempts using the wrong password.

                       

                      select username, account_status, lock_date, expiry_date
                      from dba_users;
                      
                      

                       

                      The command to unlock it would be

                       

                      alter user MYUSERNAME account unlock;
                      
                      

                       

                      Message was edited by: SvenW. -- added select

                      • 8. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                        rp0428

                        56722c7e-3a21-436d-bd0b-35ed1c36efd1 wrote:

                         

                        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.

                        For 12c you need to provide a LOT more info if you want help.

                         

                        1. Type of database - CDB, PDB on non-CDB?

                        2. What user are you using? How did you create that user? What privileges does the user have?

                        3. Are you trying to connect to the CDB or a PDB? Local users can't connect to the CDB.

                        4. Is the PDB open that you are trying to connect to? Only the CDB opens when you do a startup unless you open the PDBs or write a trigger to open them automatically

                         

                        Provide the EXACT commands that you used to create the user and that you are using to connect. Your TNSNAMES and LISTENER files only show ONE service-name. You need a service-name for any PDB that you want to connect to.

                        >

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

                        >

                        That's a GOOD thing. Do NOT create objects with names that are reserved words or that might be confused with reserved words. Pick a different name.

                        • 9. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                          56722c7e-3a21-436d-bd0b-35ed1c36efd1

                          Hello rp0428!!

                           

                          I'll show you all step that you wrote.

                          In sql plus:

                           

                          sho con_name

                          CON_NAME

                          ----------------

                          CDB$ROOT

                           

                          SELECT name, created, open_mode FROM v$database;

                          NAME CREATED                OPEN_MODE

                          --------------------------------------------------------

                          ORCL 23/07/2013 15:59:44 READ WRITE

                           

                           

                          SELECT username, account_status, lock_date, expiry_date FROM dba_users WHERE USERNAME like '%IMEI%' ORDER BY 1;

                          username       account_status  lock_date     expiry_date

                          -----------------------------------------------------------------------------------

                          IMEILOCAL   OPEN                                    22/01/2014 12:20:25

                           

                           

                          SELECT USERNAME,CON_ID,USER_ID FROM CDB_USERS WHERE USERNAME like '%IMEI%';

                          USERNAME     CON_ID   USER_ID

                          -----------------------------------------------

                          IMEILOCAL      3               117

                           

                           

                          select GRANTEE,con_id from cdb_ROLE_PRIVS where GRANTED_ROLE='CONNECT' AND GRANTEE LIKE '%IMEI%';

                          GRANTEE                  con_id

                          --------------------------------------

                          IMEILOCAL                3

                           

                           

                          SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

                          NAME       CON_ID    DBID                CON_UID         GUID

                          -----------------------------------------------------------------------------------------------------------------------------

                          ORCLC    3               2835062256     2835062256     14236144864B451C8E04D5C6453034FA

                           

                           

                          To create my user I did:

                          select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

                          2    4064112103    PDB$SEED    READ ONLY

                          3    2835062256    ORCLC      MOUNTED

                           

                          alter PLUGGABLE database ORCLC open;

                          select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

                          --2    4064112103    PDB$SEED    READ ONLY

                          --3    2835062256    ORCLC      READ WRITE

                           

                          alter session set container=ORCLC;

                           

                          CREATE TABLESPACE DATA3 DATAFILE 'C:\app\X05699SA\oradata\orcl\DATA3.dbf' SIZE 50M;

                          CREATE USER IMEILOCAL IDENTIFIED BY IMEILOCAL DEFAULT TABLESPACE DATA3 TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;

                           

                          select username, password, created, password_versions, default_tablespace from dba_users where username like '%IMEI%';

                          username      password     created                         password_versions    default_tablespace

                          -----------------------------------------------------------------------------------------------------------------------

                          IMEILOCAL                       24/07/2013 16:41:35   11G                             DATA3

                           

                          GRANT CREATE SESSION TO IMEILOCAL;

                          GRANT CREATE TABLE TO IMEILOCAL;

                          GRANT CREATE VIEW TO IMEILOCAL;

                          GRANT CREATE procedure TO IMEILOCAL;

                          GRANT CREATE trigger TO IMEILOCAL;

                          GRANT CONNECT TO IMEILOCAL;

                          GRANT CREATE SEQUENCE to IMEILOCAL;

                          GRANT create any context TO IMEILOCAL;

                          GRANT create public synonym TO IMEILOCAL;

                          GRANT execute on dbms_rls TO IMEILOCAL;

                          GRANT administer database trigger TO IMEILOCAL;

                           

                           

                          SELECT user, osuser, terminal, program FROM gv$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

                          user     osuser                                 terminal                      program

                          ------------------------------------------------------------------------------------------------------

                          SYS    SECTORIALES\X05699SA MX3500906DC1549 Toad.exe

                           

                           

                          SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS", u.profile, r.granted_role,

                          r.admin_option, r.default_role

                          FROM sys.dba_users u, sys.dba_role_privs r

                          WHERE u.username = r.grantee and u.username like '%IMEI%'

                          GROUP BY u.username, u.default_tablespace, u.temporary_tablespace, u.profile, r.granted_role,

                          r.admin_option, r.default_role;

                          IMEILOCAL USERS TEMP DEFAULT CONNECT NO YES

                           

                           

                          SELECT tablespace_name FROM dba_tablespaces;

                          SYSTEM

                          SYSAUX

                          TEMP

                          USERS

                          EXAMPLE

                          DATA3

                           

                           

                          SELECT name, password FROM user$ where name like '%IMEI%';

                          IMEILOCAL passwordx

                           

                           

                          select USERNAME, USER_ID, CREATED,                      COMMON, ORACLE_MAINTAINED from all_users WHERE username like '%IMEI%' order by 1;

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

                           

                           

                          select * from user$ where name like '%IME%';

                          117 IMEILOCAL 1 passwordx 3 2 24/07/2013 16:41:35 26/07/2013 12:20:25   0  1   0 0 DEFAULT_CONSUMER_GROUP  0   S:566C0A818AC42C203D49706D3586926A7656F5B16AA6C37E8FE10A1F779B;H:6FB057BA9F5B0690B93FD9A20695654D      

                           

                           

                          Here you're my tnsnames.ora

                          # 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)
                              )
                            )

                           

                          # Here you're my 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 think my problem source from about CDBs and PDBs, that is new in Oracle 12c and I'm not configure correcly my new DB and neither my new User

                           

                          Thanks for all!!!

                          • 10. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                            Solomon Yakobson

                            By default you connect to container database. To connect to a pluggable database you need to specify @pluggable_db_tns_entry. Modify listener.ora and add SID_DECS (just change XXX with your pluggable database name):

                             

                                (SID_DESC =

                                  (GLOBAL_DBNAME = XXX)

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

                                  (SID_NAME = XXX)

                                )

                             

                            Then add to tnsnames.ora (just change XXX with your pluggable database name and enter your_domain_name):

                             

                            XXX =
                              (DESCRIPTION =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
                                (CONNECT_DATA =
                                  (SERVER = DEDICATED)
                                  (SERVICE_NAME = XXX.your_domain_name)
                                )
                              )

                             

                             

                            SY.

                            • 11. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                              56722c7e-3a21-436d-bd0b-35ed1c36efd1

                              Hello SalomonYakobson.

                               

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

                               

                              thanks!!

                              • 12. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                                56722c7e-3a21-436d-bd0b-35ed1c36efd1

                                Hello again SolomonYakobson!!

                                 

                                This is my listener.ora after your advices.

                                # 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 =
                                   (GLOBAL_DBNAME = ORCL)
                                      (SID_NAME = ORCL)
                                      (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))
                                    )
                                  )

                                 

                                And this is my new tnsnames.ora

                                # 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)
                                    )
                                  )

                                 

                                But when I tried to logon with my new user and connect as Normal Mode, it fail again.

                                What happen?? Have I create manualy a PDB or what more??

                                 

                                Thanks for your patience

                                • 13. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                                  rp0428

                                  Thanks for posting the detailed info before about the commands you executed. We can now see what your problem is.

                                   

                                  One set of statements shows this:

                                  select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

                                  --2    4064112103    PDB$SEED    READ ONLY

                                  --3    2835062256    ORCLC      READ WRITE

                                  Your PDB name is ORCLC but your tnsnames entry is for ORCL. ORCL is your CDB. You need an entry for your PDB.

                                   

                                  Just a note. You CAN add entries to your LISTENER file if you want to but your CDB and PDBs will self-register with the listener when you open them.

                                  • 14. Re: Connect as: normal with Oracle 12c and Toad 12 FAILS.
                                    user12288492

                                    Followng link gives detailed demonstration of connecting to PDB's :

                                     

                                    http://oracleinaction.com/12c-connect-pdbs-with-same-name/

                                     

                                    Hope it helps

                                    Regards