Visit Counter

Sunday, January 22, 2017

Oracle 12c database cannot log in as a local user




I created database in Oracle 12c ...and I created local user but when I connect to database getting below error.

SQL> connect / as sysdba

SQL> alter session set container=ptest

SQL> alter user bsp
  2  identified by test123;

User altered.

SQL> conn bsp/test123@ptest
ERROR:

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

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
--------------------------------------------------------------------------------------------------------------

-bash-4.1$ cat listener.ora
# listener.ora Network Configuration File: /ora/app/oracle/product/12.1.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = inforln)
      (ORACLE_HOME = /ora/app/oracle/product/12.1.0)
      (SID_NAME = inforln)
    )
)
#    (SID_DESC =
#      (GLOBAL_DBNAME = inforln)
#      (ORACLE_HOME = /ora/app/oracle/product/12.1.0)

#      (SID_NAME = pinforln)
#    )
#  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.106)(PORT = 1522))
  )

ADR_BASE_LISTENER = /ora/app/oracle12c

-bash-4.1$



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

-bash-4.1$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /ora/app/oracle/product/12.1.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.




TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.144)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

LISTENER_TEST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.144)(PORT = 1522))


PTEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.144)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ptest)
    )
  )

-bash-4.1$

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

Solution:



We have to change the local_listener parameter in spfile.



SQL> connect / as sysdba



SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string




SQL> alter system set local_listener=inforln;

System altered.


SQL> show parameter local

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      INFORLN
parallel_force_local                 boolean     FALSE



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit


-bash-4.1$ ./sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 22 15:07:51 2017

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

SQL> conn / as sysdba
Connected.
SQL> alter session set container=ptest;

Session altered.

SQL> connect bsp/test123@pitest;
Connected.

SQL> alter session set container=ptest;

Session altered.

SQL> connect bsp/test123@ptest;
Connected.