Search This Blog

Total Pageviews

Saturday 31 May 2014

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

Oracle DBA

anuj blog Archive