14 Replies Latest reply: Jan 25, 2013 1:12 AM by 947152 RSS

    ora-12505

    947152
      Hi ,
      i am using oracle 11g .i am not able to access or login to database .i am getting ora-12505 error . but when i tried to login by cmd i can login .pls help me

      Thanks:)
        • 1. Re: ora-12505
          Sunny kichloo
          Please Share the output of following command.

          lsnrctl status

          And If it works fine Check for Service name from Above output

          Compare that Service name with Service mentioned in connect descriptor present in tnsnames.ora file.
          • 2. Re: ora-12505
            947152
            C:\Users\ashish>lsnrctl status

            LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-JAN-2013 18:07
            :01

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

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
            STATUS of the LISTENER
            ------------------------
            Alias LISTENER
            Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
            ction
            Start Date 24-JAN-2013 15:51:21
            Uptime 0 days 2 hr. 15 min. 47 sec
            Trace Level off
            Security ON: Local OS Authentication
            SNMP OFF
            Listener Parameter File c:\app\ashish\product\11.2.0\dbhome_1\network\admin\li
            stener.ora
            Listener Log File c:\app\ashish\diag\tnslsnr\ashish-PC\listener\alert\lo
            g.xml
            Listening Endpoints Summary...
            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
            Services Summary...
            Service "CLRExtProc" has 1 instance(s).
            Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
            The command completed successfully
            • 3. Re: ora-12505
              947152
              here the tns.ora file
              ORACLR_CONNECTION_DATA =
              (DESCRIPTION =
              (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
              )
              (CONNECT_DATA =
              (SID = CLRExtProc)
              (PRESENTATION = RO)
              )
              )

              orcl =
              (DESCRIPTION=
              (ADDRESS=(PROTOCOL=TCP)(HOST= localhost)(PORT=1521))
              )
              (CONNECT_DATA=(SERVER=DEDICATED)
              (SERVICE_NAME= orcl)
              )


              suggest me plz ?
              • 4. Re: ora-12505
                EdStevens
                944149 wrote:
                C:\Users\ashish>lsnrctl status

                LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-JAN-2013 18:07
                :01

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

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
                STATUS of the LISTENER
                ------------------------
                Alias LISTENER
                Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
                ction
                Start Date 24-JAN-2013 15:51:21
                Uptime 0 days 2 hr. 15 min. 47 sec
                Trace Level off
                Security ON: Local OS Authentication
                SNMP OFF
                Listener Parameter File c:\app\ashish\product\11.2.0\dbhome_1\network\admin\li
                stener.ora
                Listener Log File c:\app\ashish\diag\tnslsnr\ashish-PC\listener\alert\lo
                g.xml
                Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
                with HOST=127.0.0.1 this listener will NEVER get a request from any other machine.
                Services Summary...
                Service "CLRExtProc" has 1 instance(s).
                Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                The command completed successfully
                This listener does not know of any databases. There was no SID_LIST section in the listener.ora file, AND no databases have (at the time of this status check) registered with this listener.
                • 5. Re: ora-12505
                  EdStevens
                  944149 wrote:
                  here the tns.ora file
                  No need for a separate post just for that.
                  ORACLR_CONNECTION_DATA =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                  )
                  (CONNECT_DATA =
                  (SID = CLRExtProc)
                  (PRESENTATION = RO)
                  )
                  )

                  orcl =
                  (DESCRIPTION=
                  (ADDRESS=(PROTOCOL=TCP)(HOST= localhost)(PORT=1521))
                  Due to HOST= localhost a request using this entry will NEVER leave this machine.
                  )
                  (CONNECT_DATA=(SERVER=DEDICATED)
                  (SERVICE_NAME= orcl)
                  )


                  suggest me plz ?
                  read the following:

                  [url http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/] Help! I can’t connect to my database
                  [url http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/] Help! I can’t connect to my database (part duex)
                  [url http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/] ora-12154tns-03505
                  [url http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/] tnsping-101
                  [url http://edstevensdba.wordpress.com/2011/03/19/ora-12514/] ora-12514
                  [url http://edstevensdba.wordpress.com/2011/03/05/ora-12545/] ora-12545
                  [url http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/] exploring-the-local_listener-parameter

                  Edited by: EdStevens on Jan 24, 2013 7:00 AM
                  • 6. Re: ora-12505
                    947152
                    hi i change the tns
                    orcl =
                    (DESCRIPTION=
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL=TCP)(HOST= localhost)(PORT=1521))
                    (CONNECT_DATA=(SERVER = DEDICATED)
                    (SERVICE_NAME= orcl)
                    )
                    )
                    • 7. Re: ora-12505
                      EdStevens
                      944149 wrote:
                      hi i change the tns
                      orcl =
                      (DESCRIPTION=
                      (ADDRESS_LIST =
                      (ADDRESS = (PROTOCOL=TCP)(HOST= localhost)(PORT=1521))
                      (CONNECT_DATA=(SERVER = DEDICATED)
                      (SERVICE_NAME= orcl)
                      )
                      )
                      And how is that different from what you had, other than the fact that you added the ADDRESS_LIST? How does that address what I mentioned about LOCALHOST and ip address 127.0.0.1? And what was the result of the change?
                      I KNOW you didn't read the links I gave, simply because you replied before you would have had time to do so.
                      • 8. Re: ora-12505
                        947152
                        hi i changed splnet ora file

                        SQLNET.AUTHENTICATION_SERVICES= (NTS)

                        NAMES.DIRECTORY_PATH= (TNSNAMES)

                        even tns also changed
                        orcl =

                        (DESCRIPTION=

                        (ADDRESS_LIST =

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

                        )
                        (CONNECT_DATA=

                        (SERVICE_NAME= orcl)

                        )
                        )

                        plz suggest wat to do with local host .since i am using oracle in my ownlaptop not share .when i installed it was working fine suddenly i got this error.i am new in oracle
                        • 9. Re: ora-12505
                          EdStevens
                          944149 wrote:
                          hi i changed splnet ora file

                          SQLNET.AUTHENTICATION_SERVICES= (NTS)

                          NAMES.DIRECTORY_PATH= (TNSNAMES)
                          What was it before the change? What lead you to make the changes?
                          even tns also changed
                          orcl =

                          (DESCRIPTION=

                          (ADDRESS_LIST =

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

                          )
                          (CONNECT_DATA=

                          (SERVICE_NAME= orcl)

                          )
                          )
                          After you made those changes, (and again, what lead you to make the changes?) what bevavior do you see?

                          Back to your opening statement ".i am not able to access or login to database .i am getting ora-12505 error . but when i tried to login by cmd i can login"
                          What you mean "when i tried to login by cmd i can login"?
                          plz suggest wat to do with local host .since i am using oracle in my ownlaptop not share .when i installed it was working fine suddenly i got this error.i am new in oracle
                          What exact oracle version and edition? 11g is not a version, it is a marketing term. A version would be like '11.2.0.1.0'. "Edition" means "Enterprise" or "Standard" or "XE"

                          and what OS? Oracle is NOT certified to run on ANY "home" editions of windows, nor is it certified for ANY editon of Win 8.

                          go to a command prompt and enter these commands exactly as you see them here:
                          sqlplus / as sysdba
                          copy the entire session .. commands and full response .. and paste back here.

                          And again .. did you read the links I provided?
                          • 10. Re: ora-12505
                            947152
                            C:\Users\ashish>sqlplus / as sysdba

                            SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 25 00:00:10 2013

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


                            Connected to:
                            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                            With the Partitioning, OLAP, Data Mining and Real Application Testing options


                            i am using windows7 basic



                            SQLNET.AUTHENTICATION_SERVICES= (NTS)
                            NAMES.DIRECTORY_PATH= (TNSNAMES)
                            before change it was like

                            SQLNET.AUTHENTICATION_SERVICES= (NTS)
                            NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

                            after change nothing happned


                            Back to your opening statement ".i am not able to access or login to database .i am getting ora-12505 error . but when i tried to login by cmd i can login"
                            What you mean "when i tried to login by cmd i can login"?

                            i am able to access the data base in sqlplus

                            here the result
                            C:\Users\ashish>sqlplus scott\tiger@orcl

                            SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 25 00:04:37 2013

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

                            Enter password:
                            ERROR:
                            ORA-12154: TNS:could not resolve the connect identifier specified


                            Enter user-name: scott
                            Enter password:

                            Connected to:
                            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                            With the Partitioning, OLAP, Data Mining and Real Application Testing options


                            yes i read all link but still i got stck here only
                            • 11. Re: ora-12505
                              947152
                              hi here one more issue
                              C:\Users\ashish>tnsping orcl

                              TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JAN-2
                              013 00:18:47

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

                              Used parameter files:
                              c:\app\ashish\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= localhost)(P
                              ORT=1521))) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME= orcl))
                              TNS-12533: TNS:illegal ADDRESS parameters
                              • 12. Re: ora-12505
                                EdStevens
                                944149 wrote:
                                C:\Users\ashish>sqlplus / as sysdba

                                SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 25 00:00:10 2013

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


                                Connected to:
                                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                                With the Partitioning, OLAP, Data Mining and Real Application Testing options


                                i am using windows7 basic
                                That would be Windows 7 Home basic. Not a supported OS for Oracle.

                                >
                                >
                                >
                                SQLNET.AUTHENTICATION_SERVICES= (NTS)
                                NAMES.DIRECTORY_PATH= (TNSNAMES)
                                before change it was like

                                SQLNET.AUTHENTICATION_SERVICES= (NTS)
                                NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

                                after change nothing happned
                                For your case the change was meaningless, but what lead you to make the change in the first place?

                                >
                                Back to your opening statement ".i am not able to access or login to database .i am getting ora-12505 error . but when i tried to login by cmd i can login"
                                What you mean "when i tried to login by cmd i can login"?

                                i am able to access the data base in sqlplus

                                here the result
                                C:\Users\ashish>sqlplus scott\tiger@orcl

                                SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 25 00:04:37 2013

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

                                Enter password:
                                ERROR:
                                ORA-12154: TNS:could not resolve the connect identifier specified
                                That error was the entire focus of this link --->>> [url http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/] ora-12154tns-03505
                                Perhaps in manually editing your tnsnames.ora file, you introduced some error. It can be sensitive to seemingly innocuous formatting issues ... especially blank lines in the middle of a definition .....

                                >
                                Enter user-name: scott
                                Enter password:

                                Connected to:
                                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                                With the Partitioning, OLAP, Data Mining and Real Application Testing options
                                You were able to connect here because you left off the "@orcl", so were not using tns to connect. Instead, you got a bequeath connection, that only works on the same system as where the database is running.

                                >
                                yes i read all link but still i got stck here only
                                • 13. Re: ora-12505
                                  EdStevens
                                  944149 wrote:
                                  hi here one more issue
                                  C:\Users\ashish>tnsping orcl

                                  TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JAN-2
                                  013 00:18:47

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

                                  Used parameter files:
                                  c:\app\ashish\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= localhost)(P
                                  ORT=1521))) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME= orcl))
                                  TNS-12533: TNS:illegal ADDRESS parameters
                                  and what did you discover when you googled "TNS-12533" ??

                                  I'd guess you messed up your tnsnames.ora file in some strange and bizarre way that is not apparent to the eye. Delete it and recreate it, this time using the 'netca' utility.
                                  • 14. Re: ora-12505
                                    947152
                                    thank you

                                    i did Delete it and recreate it, this time using the 'netca' utility.
                                    it works for me