9 Replies Latest reply on Mar 5, 2010 12:22 AM by 753598

    Can't start up after shutdown - ORA-12514

    753598
      Hi -

      I installed 11gR2 on RHEL 5.4, creating a database during installation.

      After shutting down via SQLPlus (remotely), I found I am unable to startup.

      Attempts to startup via SQLPlus fail with: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

      However, I am able to startup via Enterprise Manager.

      I have been viewing similar threads but cannot seem to get the issue resolved.



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

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = s2.srs-7.net)(PORT = 1521))
      )
      )

      ADR_BASE_LISTENER = /home/app/oracle





      # tnsnames.ora Network Configuration File: /home/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
      # Generated by Oracle configuration tools.

      ELF =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = s2.srs-7.net)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = elf.srs-7.net)
      )
      )




      tnsping:

      [oracle@s2 bin]$ tnsping elf

      TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-MAR-2010 22:21:56

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

      Used parameter files:
      /home/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = s2.srs-7.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = elf.srs-7.net)))
      OK (0 msec)



      Stop and Start Listener: (shows "listener supports no services")



      [oracle@s2 bin]$ lsnrctl stop

      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAR-2010 22:16:18

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
      The command completed successfully


      [oracle@s2 bin]$ lsnrctl start

      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAR-2010 22:16:24

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

      Starting /home/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

      TNSLSNR for Linux: Version 11.2.0.1.0 - Production
      System parameter file is /home/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
      Log messages written to /home/app/oracle/diag/tnslsnr/server12/listener/alert/log.xml
      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s2.srs-7.net)(PORT=1521)))

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
      Start Date 04-MAR-2010 22:16:24
      Uptime 0 days 0 hr. 0 min. 0 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File /home/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
      Listener Log File /home/app/oracle/diag/tnslsnr/server12/listener/alert/log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s2.srs-7.net)(PORT=1521)))
      The listener supports no services
      The command completed successfully




      My environment

      [oracle@s2 bin]$ env
      HOSTNAME=s2.srs-7.net
      SHELL=/bin/bash
      TERM=xterm
      HISTSIZE=1000
      TMPDIR=/tmp
      KDE_NO_IPV6=1
      USER=oracle
      TEMP=/tmp
      LD_LIBRARY_PATH=/home/app/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib:/usr/local/lib
      LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
      ORACLE_SID=elf
      ORACLE_BASE=/home/app/oracle
      KDEDIR=/usr
      MAIL=/var/spool/mail/oracle
      PATH=/home/app/oracle/product/11.2.0/dbhome_1/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
      INPUTRC=/etc/inputrc
      PWD=/home/app/oracle/product/11.2.0/dbhome_1/bin
      LANG=en_US.UTF-8
      KDE_IS_PRELINKED=1
      ORACLE_TERM=xterm
      SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
      SHLVL=1
      HOME=/home/oracle
      LOGNAME=oracle
      CLASSPATH=/home/app/oracle/product/11.2.0/dbhome_1/JRE:/home/app/oracle/product/11.2.0/dbhome_1/jlib:/home/app/oracle/product/11.2.0/dbhome_1/rdbms/jlib
      LESSOPEN=|/usr/bin/lesspipe.sh %s
      ORACLE_HOME=/home/app/oracle/product/11.2.0/dbhome_1
      G_BROKEN_FILENAMES=1
      _=/bin/env
      OLDPWD=/home/app/oracle/product/11.2.0/dbhome_1





      I have tried to add a static entry (below) to my listener,ora file, but this not does not solve the issue and prevents starting up even via EM until the entry is removed:


      ELF =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = s2.srs-7.net)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = elf.srs-7.net)
      )
      )





      All else (so far) seems to be functioning normally and I have encountered only this issue.

      Any input would be greatly appreciated.

      S Rosen.

      Edited by: ScottRn on Mar 4, 2010 2:02 PM
        • 1. Re: Can't start up after shutdown - ORA-12514
          damorgan
          Verify the values of the environment variables ORACLE_BASE, ORACLE_HOME, and ORACLE_SID

          can you ping the server?

          can you tnsping the Oracle service?

          After verifying the environment is correct?
          1 person found this helpful
          • 2. Re: Can't start up after shutdown - ORA-12514
            753598
            Everything seems to point in the right place for the environment:

            [oracle@s2 bin]$ echo $ORACLE_HOME
            /home/app/oracle/product/11.2.0/dbhome_1
            [oracle@s2 bin]$ echo $ORACLE_SID
            elf
            [oracle@s2 bin]$ echo $ORACLE_BASE
            /home/app/oracle
            [oracle@s2 bin]$ tnsping elf

            TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-MAR-2010 23:59:47

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

            Used parameter files:
            /home/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


            Used TNSNAMES adapter to resolve the alias
            Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = s2.srs-7.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = elf.srs-7.net)))
            OK (0 msec)


            But now, when I connect via SQLPlus, then go to shut down and then try to start up:


            [oracle@s2 bin]$ sqlplus sys/psswd@elf as sysdba

            SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 5 00:02:59 2010

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


            Connected to:
            Oracle Database 11g Release 11.2.0.1.0 - Production

            SQL> shutdown immediate
            Database closed.
            Database dismounted.
            ORACLE instance shut down.
            SQL> startup
            ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
            SQL>


            This first occurred when I went to place the db in archive mode, after shutting down and then issuing startup mount, is when I first saw the error.

            Thank you,
            Scott
            • 3. Re: Can't start up after shutdown - ORA-12514
              367117
              ScottRn wrote:
              Hi -

              I installed 11gR2 on RHEL 5.4, creating a database during installation.

              After shutting down via SQLPlus (remotely), I found I am unable to startup.



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

              LISTENER =
              (DESCRIPTION_LIST =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
              (ADDRESS = (PROTOCOL = TCP)(HOST = s2.srs-7.net)(PORT = 1521))
              )
              )

              ADR_BASE_LISTENER = /home/app/oracle



              Seems like you are using AUTO registration with the listener.

              lsnrctl status LISTENER would probably not be aware of the service once the DB is down.


              Add SID_LIST_LISTENER ......... i.e configure the listener and restart...

              see if that helps.

              edit..

              or start the database locally (from the server)

              Edited by: ordba on Mar 4, 2010 3:22 PM

              Edited by: ordba on Mar 4, 2010 3:22 PM
              1 person found this helpful
              • 4. Re: Can't start up after shutdown - ORA-12514
                sb92075
                CUT lines below then PASTE into a terminal window

                env | sort
                sqlplus
                / as sysdba
                startup
                exit

                CUT results then PASTE all back here
                • 5. Re: Can't start up after shutdown - ORA-12514
                  753598
                  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
                  SQL> exit
                  Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production
                  [oracle@s2 bin]$ env | sort
                  _=/bin/env
                  CLASSPATH=/home/app/oracle/product/11.2.0/dbhome_1/JRE:/home/app/oracle/product/11.2.0/dbhome_1/jlib:/home/app/oracle/product/11.2.0/dbhome_1/rdbms/jlib
                  G_BROKEN_FILENAMES=1
                  HISTSIZE=1000
                  HOME=/home/oracle
                  HOSTNAME=s2.srs-7.net
                  INPUTRC=/etc/inputrc
                  KDEDIR=/usr
                  KDE_IS_PRELINKED=1
                  KDE_NO_IPV6=1
                  LANG=en_US.UTF-8
                  LD_LIBRARY_PATH=/home/app/oracle/product/11.2.0/dbhome_1/lib:/lib:/usr/lib:/usr/local/lib
                  LESSOPEN=|/usr/bin/lesspipe.sh %s
                  LOGNAME=oracle
                  LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
                  MAIL=/var/spool/mail/oracle
                  OLDPWD=/home/app/oracle/product/11.2.0/dbhome_1
                  ORACLE_BASE=/home/app/oracle
                  ORACLE_HOME=/home/app/oracle/product/11.2.0/dbhome_1
                  ORACLE_SID=elf
                  ORACLE_TERM=xterm
                  PATH=/home/app/oracle/product/11.2.0/dbhome_1/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
                  PWD=/home/app/oracle/product/11.2.0/dbhome_1/bin
                  SHELL=/bin/bash
                  SHLVL=1
                  SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
                  TEMP=/tmp
                  TERM=xterm
                  TMPDIR=/tmp
                  USER=oracle
                  [oracle@s2 bin]$ sqlplus

                  SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 5 00:34:21 2010

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

                  Enter user-name: */ as sysdba*
                  Connected to an idle instance.

                  SQL> startup
                  ORACLE instance started.

                  Total System Global Area 2042241024 bytes
                  Fixed Size 1337548 bytes
                  Variable Size 503318324 bytes
                  Database Buffers 1526726656 bytes
                  Redo Buffers 10858496 bytes
                  Database mounted.
                  Database opened.
                  SQL> exit
                  Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production
                  [oracle@s2 bin]$


                  Thanks,
                  Scott

                  Edited by: ScottRn on Mar 4, 2010 3:41 PM
                  • 6. Re: Can't start up after shutdown - ORA-12514
                    367117
                    Since your DB is now started, Listener will know about the service.
                    • 7. Re: Can't start up after shutdown - ORA-12514
                      753598
                      Hi -

                      Thanks, I will mark the answers.

                      I'll need to adjust the password set up.....

                      Scott

                      Edited by: ScottRn on Mar 4, 2010 4:17 PM
                      • 8. Re: Can't start up after shutdown - ORA-12514
                        sb92075
                        sqlplus sys/psswd@elf as sysdba
                        When you use "@elf", this results in sqlnet to make the connection.
                        Oracle now utilizes "automatic registration" which means that after the database starts, the DB will register its existence with the listener.
                        When the database is down, the listener does not know the DB exists.
                        Therefore you need to connect locally as follows
                        sqlplus
                        / as sysdba

                        once connected, the the DB can be started using "STARTUP".

                        Please mark this thread as answered.
                        • 9. Re: Can't start up after shutdown - ORA-12514
                          753598
                          Already marked :)

                          Thanks all for taking the time.

                          Scott