1 2 3 4 Previous Next 48 Replies Latest reply: Jun 16, 2014 7:46 AM by Paul M. RSS

    ORA-12514 and ORA-12560 .. how to fix this

    user3213880

      hi everybody

      my database service are running normally but i got these error and i can not fix them when i try to fix ora-12514 i got ora-12560, please help me even if i have to create the database again in other server just show me how to recovery my datafiles and run it again

      my database ORACLE_SID is smarthr  , please anyone can help it is very urgent for me

       

      thank you all

      ##########################################################################################

      ## Listener.ora

      ##########################################################################################

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (SID_NAME = CLRExtProc)

            (ORACLE_HOME = E:\app\administrateur\product\11.2.0\dbhome_1)

            (PROGRAM = extproc)

            (ENVS = "EXTPROC_DLLS=ONLY:E:\app\administrateur\product\11.2.0\dbhome_1\bin\oraclr11.dll")

          )

        )

       

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = AGADEZ.bsicne.lan)(PORT = 1521))

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

        )

       

      ADR_BASE_LISTENER = E:\app\administrateur

       

      ##########################################################################################

      ## tnsnames.ora

      ##########################################################################################

      SMARTHR =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.7.100.15)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = SMARTHR)

          )

        )

       

      ORACLR_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

          (CONNECT_DATA =

            (SID = CLRExtProc)

            (PRESENTATION = RO)

          )

        )

       

      SMARTBK =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.7.100.15)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = SMARTBK)

          )

        )

       

      ORACLNT =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.7.100.15)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = SMARTBK)

          )

        )

       

      SMARTRH =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.7.100.15)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = smartrh)

          )

        )

       

      ##########################################################################################

      ## lsnrctl status

      ##########################################################################################

       

      E:\app\administrateur\product\11.2.0\dbhome_1\NETWORK\ADMIN>lsnrctl status

       

      LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 12-JUN-2014 19:32:07

       

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

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AGADEZ.bsicne.lan)(PORT=1521)))

      STATUS of the LISTENER

      ------------------------

      Alias                     LISTENER

      Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

      Start Date                12-JUN-2014 19:15:37

      Uptime                    0 days 0 hr. 16 min. 33 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Log File         e:\app\administrateur\diag\tnslsnr\AGADEZ\listener\alert\log.xml

      Listening Endpoints Summary...

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AGADEZ.bsicne.lan)(PORT=1521)))

      Services Summary...

      Service "SMARTBKXDB" has 1 instance(s).

        Instance "smartbk", status READY, has 1 handler(s) for this service...

      Service "smartbk" has 1 instance(s).

        Instance "smartbk", status READY, has 1 handler(s) for this service...

      Service "smartrh" has 1 instance(s).

        Instance "smartrh", status READY, has 1 handler(s) for this service...

      Service "smartrhXDB" has 1 instance(s).

        Instance "smartrh", status READY, has 1 handler(s) for this service...

      The command completed successfully

       

      E:\app\administrateur\product\11.2.0\dbhome_1\NETWORK\ADMIN>

       

      ##########################################################################################

      ## lsnrctl stop & Start

      ##########################################################################################

      E:\app\administrateur\product\11.2.0\dbhome_1\NETWORK\ADMIN>lsnrctl stop

       

      LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 12-JUN-2014 19:33:03

       

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

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AGADEZ.bsicne.lan)(PORT=1521)))

      The command completed successfully

       

      E:\app\administrateur\product\11.2.0\dbhome_1\NETWORK\ADMIN>lsnrctl start

       

      LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 12-JUN-2014 19:33:10

       

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

       

      Starting tnslsnr: please wait...

       

      TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

      System parameter file is E:\app\administrateur\product\11.2.0\dbhome_1\network\admin\listener.ora

      Log messages written to e:\app\administrateur\diag\tnslsnr\AGADEZ\listener\alert\log.xml

      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AGADEZ.bsicne.lan)(PORT=1521)))

      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AGADEZ.bsicne.lan)(PORT=1521)))

      STATUS of the LISTENER

      ------------------------

      Alias                     LISTENER

      Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

      Start Date                12-JUN-2014 19:33:15

      Uptime                    0 days 0 hr. 0 min. 7 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Parameter File   E:\app\administrateur\product\11.2.0\dbhome_1\network\admin\listener.ora

      Listener Log File         e:\app\administrateur\diag\tnslsnr\AGADEZ\listener\alert\log.xml

      Listening Endpoints Summary...

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AGADEZ.bsicne.lan)(PORT=1521)))

        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

      Services Summary...

      Service "CLRExtProc" has 1 instance(s).

        Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

      The command completed successfully

       

      ##########################################################################################

      ## lsnrctl service

      ##########################################################################################

       

      E:\app\administrateur\product\11.2.0\dbhome_1\NETWORK\ADMIN>lsnrctl service

       

      LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 12-JUN-2014 19:34:27

       

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

       

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AGADEZ.bsicne.lan)(PORT=1521)))

      Services Summary...

      Service "CLRExtProc" has 1 instance(s).

        Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

          Handler(s):

            "DEDICATED" established:0 refused:0

               LOCAL SERVER

      Service "SMARTBKXDB" has 1 instance(s).

        Instance "smartbk", status READY, has 1 handler(s) for this service...

          Handler(s):

            "D000" established:0 refused:0 current:0 max:1022 state:ready

               DISPATCHER <machine: AGADEZ, pid: 6504>

               (ADDRESS=(PROTOCOL=tcp)(HOST=AGADEZ.bsicne.lan)(PORT=53058))

      Service "smartbk" has 1 instance(s).

        Instance "smartbk", status READY, has 1 handler(s) for this service...

          Handler(s):

            "DEDICATED" established:0 refused:0 state:ready

               LOCAL SERVER

      Service "smartrh" has 1 instance(s).

        Instance "smartrh", status READY, has 1 handler(s) for this service...

          Handler(s):

            "DEDICATED" established:0 refused:0 state:ready

               LOCAL SERVER

      Service "smartrhXDB" has 1 instance(s).

        Instance "smartrh", status READY, has 1 handler(s) for this service...

          Handler(s):

            "D000" established:0 refused:0 current:0 max:1022 state:ready

               DISPATCHER <machine: AGADEZ, pid: 4628>

               (ADDRESS=(PROTOCOL=tcp)(HOST=AGADEZ.bsicne.lan)(PORT=52716))

      The command completed successfully

       

      ##########################################################################################

      ## sqlplus /nolog

      ## conn / as sysdba

      ##########################################################################################

       

      E:\app\administrateur\product\11.2.0\dbhome_1\NETWORK\ADMIN>set ORACLE_SID=smarthr

       

      E:\app\administrateur\product\11.2.0\dbhome_1\NETWORK\ADMIN>sqlplus /nolog

       

      SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 12 19:36:01 2014

       

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

       

      SQL> conn sys / as sysdba

      Enter password:

      ERROR:

      ORA-12560: TNS:protocol adapter error

      SQL>

       

       

       

      E:\app\administrateur\product\11.2.0\dbhome_1\NETWORK\ADMIN>exp xxxxx/0000@smarthr

       

      Export: Release 10.2.0.1.0 - Production on Thu Jun 12 19:37:53 2014

       

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

       

      EXP-00056: ORACLE error 12514 encountered

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

        • 1. Re: ORA-12514 and ORA-12560 .. how to fix this
          Paul M.

          SMARTHR =

            (DESCRIPTION =

              (ADDRESS_LIST =

                (ADDRESS = (PROTOCOL = TCP)(HOST = 10.7.100.15)(PORT = 1521))

              )

              (CONNECT_DATA =

                (SERVICE_NAME = SMARTHR)

              )

            )

           


          Service "smartrh" has 1 instance(s).

            Instance "smartrh", status READY, has 1 handler(s) for this service...



          E:\app\administrateur\product\11.2.0\dbhome_1\NETWORK\ADMIN>set ORACLE_SID=smarthr

           

           

           

          E:\app\administrateur\product\11.2.0\dbhome_1\NETWORK\ADMIN>exp xxxxx/0000@smarthr

           

          Don't you see anything wrong ?

           

          You're trying to connect to SMARTHR service name, but smartrh is registered with the listener.

          • 2. Re: ORA-12514 and ORA-12560 .. how to fix this
            sb92075

            open Command window & issue following OS commands

             

             

            nslookup AGADEZ.bsicne.lan

            ping 10.7.100.15

             

            COPY the results from above then PASTE all back here

             

            >Log messages written to e:\app\administrateur\diag\tnslsnr\AGADEZ\listener\alert\log.xml

             

            excerpt lines, 20 lines above & 20 lines below,  from file above which contain status code = 12514

            • 3. Re: ORA-12514 and ORA-12560 .. how to fix this
              sb92075

              >Don't you see anything wrong ?

               

              No I don't

               

              SMARTRH =

                (DESCRIPTION =

                  (ADDRESS_LIST =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.7.100.15)(PORT = 1521))

                  )

                  (CONNECT_DATA =

                    (SERVICE_NAME = smartrh)

                  )

                )

              SMARTHR =

                (DESCRIPTION =

                  (ADDRESS_LIST =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.7.100.15)(PORT = 1521))

                  )

                  (CONNECT_DATA =

                    (SERVICE_NAME = SMARTHR)

                  )

                )

              • 4. Re: ORA-12514 and ORA-12560 .. how to fix this
                Paul M.

                sb92075 ha scritto:

                 

                >Don't you see anything wrong ?

                 

                No I don't

                 

                SMARTRH =

                  (DESCRIPTION =

                    (ADDRESS_LIST =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.7.100.15)(PORT = 1521))

                    )

                    (CONNECT_DATA =

                      (SERVICE_NAME = smartrh)

                    )

                  )

                SMARTHR =

                  (DESCRIPTION =

                    (ADDRESS_LIST =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.7.100.15)(PORT = 1521))

                    )

                    (CONNECT_DATA =

                      (SERVICE_NAME = SMARTHR)

                    )

                  )

                Yes, but he/she is using the wrong one.

                • 5. Re: ORA-12514 and ORA-12560 .. how to fix this
                  sb92075

                  >my database ORACLE_SID is smarthr

                   

                  no instance named as smarthr is now known by the listener.

                  listener knows about instance named "smartrh"

                  • 6. Re: ORA-12514 and ORA-12560 .. how to fix this
                    user3213880

                    hi .. first thank you everybody , no nothing wrong , i create new database i call it (SMARTRH), to check if the listener can connect to this db , i using lsnrctl service , but my problem in SMARTHR , this is unknown problem unknown cause !!? i google it but when i tried to fix ORA-12514 i get ORA-12560, and every body saying to fix ORA-12560 i should restart the service OracleEXsomething i don't have such service ??

                    the other services are working fine like SMARTBK ??

                     

                    please help me

                    incase this is not possibale ([ CAN I CREATE OTHER DATABASE WITH THE SAME NAME IN OTHER SERVER AND JUST COPY OR WHATEVER THE OLD SMARTHR TO THE NEW DATABASE AS RECOVERY ])

                     

                    thank you

                    • 7. Re: ORA-12514 and ORA-12560 .. how to fix this
                      Paul M.

                      i create new database i call it (SMARTRH), to check if the listener can connect to this db , i using lsnrctl service , but my problem in SMARTHR , this is unknown problem unknown cause !!?

                      Not sure what you mean... your database is registered with the listener as smartrh : did you try using this one as connection string and ORACLE_SID ?

                      • 8. Re: ORA-12514 and ORA-12560 .. how to fix this
                        user3213880

                        my database and my problem is SMARTHR the service is running (under windows) but i can not login or connect to this database , when i used lsnrctl i did not see this service/oracle_SID.

                         

                        forget about SMARTRH i create this db just to check is the listener will see new db or not , i have other db working well with no problem but SMARTHR has ORA-12514

                        • 9. Re: ORA-12514 and ORA-12560 .. how to fix this
                          Sunny kichloo

                          Modify SID_LIST_LISTENER of listener with the correct name of Service in your case it is SMARTHR and then reload the listener and try to connect and share the output.

                          • 10. Re: ORA-12514 and ORA-12560 .. how to fix this
                            Paul M.

                            my database and my problem is SMARTHR the service is running (under windows) but i can not login or connect to this database , when i used lsnrctl i did not see this service/oracle_SID.

                            Are you sure the windows service is correctly running ? If so, and the database is actually (physically) there, then this means your database is not running (for any reason) : check alert log for any errors.

                            • 11. Re: ORA-12514 and ORA-12560 .. how to fix this
                              Paul M.

                              Modify SID_LIST_LISTENER of listener with the correct name of Service in your case it is SMARTHR and then reload the listener and try to connect and share the output.

                              Why should that solve the problem ? the listener is running with default parameters, and if the database was running, it should automatically register with the listener.

                              • 12. Re: ORA-12514 and ORA-12560 .. how to fix this
                                Sunny kichloo

                                You are right better automatic register the services.But i thought of SID_LIST_LISTENER also the things that matter is that service should be running of name SMARTHR

                                • 13. Re: ORA-12514 and ORA-12560 .. how to fix this
                                  Paul M.
                                  You are right better automatic register the services.

                                  The problem is not what is better : if the database is not running, the static registration won't be very useful...

                                  • 14. Re: ORA-12514 and ORA-12560 .. how to fix this
                                    user3213880

                                    the database is running but the instanc is not , the problem that listener can not know the database ?? so how to register the database

                                    1 2 3 4 Previous Next