4 Replies Latest reply: Mar 28, 2013 5:11 PM by 998440 RSS

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

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