This discussion is archived
4 Replies Latest reply: Mar 28, 2013 3:11 PM by 998440 RSS

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

998440 Newbie
Currently Being Moderated
While connecting "sqlplus SYSADM/SYSADM@HCM" I am getting the below error:

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


Installation background:

Windows 7 l(64-bit) laptop.
Created linux virtualbox to installl peoplesoft VM templates.
so far installed: Oracle11gR2.vdi and HCMDB.vdi on the linux virtualbox.


Followed the steps exactly what it is in the link below: Got the above error in page 4 of the link at connecting to database via sqlplus.

http://bloggingaboutoracleapplications.org/running-oracle-vm-templates-oracle-enterprise-linux-guide-part-1-7/


Below provided initHCM.ora, tnsnames.ora, listerner.ora and some responses I tried. I could not figure out what causing the issue even after researching in web.

Your help would be highly appreciated.


tnsnames.ora:
=============
# tnsnames.ora Network Configuration File:

HCM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hcmdb91)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = HCM)
)
)


Listener.ora:
=============

# listener.ora Network Configuration File:
/u01/app/oracle/product/11.2.0.x/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hcmdb91)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)



initHCM.ora:
============

####
DB_FILES =256
OPEN_CURSORS = 1000
db_block_size = 8192
remote_login_passwordfile = NONE
undo_tablespace=PSUNDOTS
NLS_LENGTH_SEMANTICS=CHAR

###########################################
# Cache and I/O
###########################################
#db_block_size=
db_file_multiblock_read_count=16


###########################################
# Database Identification
###########################################
# db_domain=us.oracle.com
# db_name=

###########################################
# Diagnostics and Statistics
###########################################
#background_dump_dest=
#core_dump_dest=
#user_dump_dest=


###########################################
# Job Queues
###########################################
job_queue_processes=10

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# SGA Memory
###########################################
sga_target=287309824

###########################################
# Security and Auditing
###########################################
#audit_file_dest=
#remote_login_passwordfile=EXCLUSIVE

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=XDB)"

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=95420416

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
#undo_tablespace=
unnestsubquery=false

###########################################
# Miscellaneous
###########################################
compatible=11.2.0.2.0
control_files=(/u01/app/oracle/oradata/HCM/control01.ctl)
DB_NAME=HCM





[oracle@hcmdb91 ~]$ tnsping hcm

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 28-MAR-2013
07:40:37

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
hcmdb91)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
HCM)))
OK (20 msec)
[oracle@hcmdb91 ~]$




[oracle@hcmdb91 ~]$ tnsping hcm

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 28-MAR-2013
07:40:37

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
hcmdb91)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
HCM)))
OK (20 msec)
[oracle@hcmdb91 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-MAR-2013 07:41:32

Copyright (c) 1991, 2010, Oracle. All rights reserved.

TNS-01106: Listener using listener name LISTENER has already been started
[oracle@hcmdb91 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-MAR-2013 07:42:20

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hcmdb91)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 28-MAR-2013 07:29:21
Uptime 0 days 0 hr. 13 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
/u01/app/oracle/product/11.2.0.x/db_1/network/admin/listener.ora
Listener Log File
/u01/app/oracle/product/11.2.0.x/db_1/log/diag/tnslsnr/hcmdb91/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hcmdb91.example.com
)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@hcmdb91 ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-MAR-2013 07:42:44

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hcmdb91)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@hcmdb91 ~]$



[oracle@hcmdb91 ~]$ sqlplus SYSADM/SYSADM@HCM

SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 28 07:44:25 2013

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

ERROR:

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


My guess is that the database service "HCM" is not up and running but not sure how to make up and running. Even I could not find it listed in the linux services list.


Note: Able to login sqlplus using "sqlplus sys/sys as sysdba" but failing to access database table as "select * from PSRECDEFN;"


thanks
Espii.

Edited by: 995437 on Mar 28, 2013 6:52 AM

Edited by: 995437 on Mar 28, 2013 6:59 AM
  • 1. Re: ORA-12514: TNS:listener does not currently know of service requested
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    If you are trying to connect from within the database server itself, don't use the alias, but set ORACLE_SID before invoking sqlplus:
    export ORACLE_SID=HCM
    sqlplus SYSADM/SYSADM
    The use of alis (@HCM) is for the client connection (out of the server).
    Note: Able to login sqlplus using "sqlplus sys/sys as sysdba" but failing to access database table as "select * from PSRECDEFN;"
    The Peoplesoft table (such as PSRECDEFN) are owned by SYSADM user, not by sys. Did you already loaded the database ?

    Nicolas.
  • 2. Re: ORA-12514: TNS:listener does not currently know of service requested
    kumar15 Newbie
    Currently Being Moderated
    Please check the below two cases
    1 )If you have multiple listeners running on the server, make sure your database is registered with both the listeners.
    2) Please check the sqlnet.ora file, if you are using some domain name like HCM.yourdomain, you have to define it in the SQLNET.ora file as
    names.default_domain = yourdomain
    names.directory_path = (tnsnames)
  • 3. Re: ORA-12514: TNS:listener does not currently know of service requested
    998440 Newbie
    Currently Being Moderated
    Hi Nicolas,

    I have no luck and the error still persists.


    I have set the "export ORACLE_SID=HCM" from root user and oracle user too.


    I guess, I have already loaded database when I ran the "cd /opt/oracle/psft/vm
    ./oraclevm-template.sh"


    The outcome of the above script as similar as below:


    Creating
    ovm_configure_pre
    CreateVirtualEnvironment
    /u01/scripts/db-reconfig.sh: line 238: ovm_set_password: command not found
    /u01/scripts/db-reconfig.sh: line 239: ovm_configure_network: command not found
    INFO:
    Starting Oracle database reconfiguration.

    Oracle Database Configuration
    -------------------------------------------------
    This will configure on-boot properties of Oracle Database.
    The following questions will determine whether the database should be
    starting upon system boot, the ports it will use.
    Press <Enter> to accept the defaults.

    Do you want to relink binaries? (y/n) [n] y

    Specify a port that will be used for the database listener [1521]:

    Do you want Oracle Database to be started on boot (y/n) [y]:y

    Wrote to /etc/sysconfig/oracle11g

    Relinking Oracle Binaries...
    Oracle Relinking Completed Successfully
    Logs: /home/oracle/relink.log
    Running orainstRoot.sh and root.sh...
    set 2GB tmpfs
    Enabling Oracle Database service
    Configuration Completed Successfully.
    Enter the name of the database SID [TESTDB]:HCM
    ORACLE instance started.

    Total System Global Area 288104448 bytes
    Fixed Size 2225832 bytes
    Variable Size 104859992 bytes
    Database Buffers 176160768 bytes
    Redo Buffers 4857856 bytes

    Control file created.

    Database altered.

    Tablespace altered.

    Tablespace altered.

    Tablespace altered.

    1 row updated.

    160 rows updated.

    Commit complete.


    Kumar - I have found no sqlnet.ora file exist in the $ORACLE_HOME/NETWORK/ADMIN

    Edited by: 995437 on Mar 28, 2013 9:15 AM
  • 4. Re: ORA-12514: TNS:listener does not currently know of service requested
    998440 Newbie
    Currently Being Moderated
    The issue is resolved now.

    Solution:

    1. logon as "sqlplus / as sysdba"
    2. Startup - that started my oracle database.
    3. exit from sqlplus and then it enabled me to logon to my HCM (ps database) as "sqlplus SYSADM/SYSADM@HCM"


    Thanks for all your help.


    espii.

Legend

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