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
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.
