This discussion is archived
14 Replies Latest reply: Jan 24, 2013 11:12 PM by 947152 RSS

ora-12505

947152 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thank you

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

Legend

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