ORA-12514: TNS:listener does not currently know of service requested in connect descriptor !!!!!!!!!!!!!
Oracle 12c pluggable database connection problem
Oracle 12c Not able to connect pluggable database ...
SQL> startup ;
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2291424 bytes
Variable Size 473958688 bytes
Database Buffers 142606336 bytes
Redo Buffers 3293184 bytes
Database mounted.
Database opened.
SQL> alter pluggable database anujv open;
Pluggable database altered.
SQL> !tnsping anujv
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:33:10
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.18)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = anujv)))
OK (0 msec)
SQL> !lsnrctl start
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:33:19
Copyright (c) 1991, 2013, Oracle. All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been started
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:33:28
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 31-MAY-2014 12:30:27
Uptime 0 days 0 hr. 3 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/unknown/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "plsextproc" has 1 instance(s).
Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
Service "vihaan" has 1 instance(s).
Instance "vihaan", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
SQL> alter system register ;
System altered.
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:33:53
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 31-MAY-2014 12:30:27
Uptime 0 days 0 hr. 3 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/unknown/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "plsextproc" has 1 instance(s).
Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
Service "vihaan" has 1 instance(s).
Instance "vihaan", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
SQL> !tnsping anujv
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:34:22
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.18)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = anujv)))
OK (0 msec)
set linesize 200 pagesize 200
select SERVICE_ID,NAME,CON_NAME,CON_ID from v$active_services
SERVICE_ID NAME CON_NAME CON_ID
---------- ---------------------------------------------------------------- ------------------------------ ----------
7 anujv ANUJV 4
0 vihaanpdb VIHAANPDB 3
5 vihaanXDB CDB$ROOT 1
6 vihaan CDB$ROOT 1
1 SYS$BACKGROUND CDB$ROOT 1
2 SYS$USERS CDB$ROOT 1
6 rows selected.
[oracle@unknown ~]$ sqlplus anuj/vihaa1@192.168.0.18:1521/anujv
SQL*Plus: Release 12.1.0.1.0 Production on Sat May 31 12:38:23 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string ****************---- value is blank
SQL> def
DEFINE _DATE = "31-MAY-14" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "vihaan" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000100" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1201000100" (CHAR)
set local listener -----------------
SQL> ALTER SYSTEM SET local_listener="(address=(protocol=tcp)(host=192.168.0.18)(port=1521))" scope=both sid='vihaan' ;
System altered.
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 31-MAY-2014 12:42:27
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 31-MAY-2014 12:30:27
Uptime 0 days 0 hr. 11 min. 59 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/unknown/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.18)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "anujv" has 1 instance(s).
Instance "vihaan", status READY, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
Service "vihaan" has 2 instance(s).
Instance "vihaan", status UNKNOWN, has 1 handler(s) for this service...
Instance "vihaan", status READY, has 1 handler(s) for this service...
Service "vihaanXDB" has 1 instance(s).
Instance "vihaan", status READY, has 1 handler(s) for this service...
Service "vihaanpdb" has 1 instance(s).
Instance "vihaan", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@unknown ~]$ sqlplus anuj/vihaa1@192.168.0.18:1521/anujv
SQL*Plus: Release 12.1.0.1.0 Production on Sat May 31 12:44:15 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
====
VIHAANPDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.18)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = vihaanpdb)
)
)
anujv =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.18)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = anujv)
)
)
[oracle@unknown admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/n etwork/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.18)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = vihaan)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = vihaan)
)
(SID_DESC =
(SID_NAME = plsextproc)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.18)(PORT = 1521))
(ADDRESS = (PROTOCOL = ipc)(KEY = extproc))
)
)
