This discussion is archived
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 Newbie
Currently Being Moderated

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. Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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. Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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. Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

     

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

     

    Hope it helps

    Regards

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points