This discussion is archived
1 2 3 4 5 6 Previous Next 79 Replies Latest reply: Mar 13, 2013 8:36 AM by EdStevens Go to original post RSS
  • 60. Re: Cant connect my database using net service_name
    Mark D Powell Guru
    Currently Being Moderated
    You always have to specifiy when you want a sysdba connection. The following is a valid UNIX command entry for a server where I configured remote sysdba connection for the purpose of this demo. I have changed the displayed information to be generic. Normally I think allowing remote sysdba connections are a security issue. If you cannot log onto the database server as the Oracle software owner you do should not be allowed a sysdba connection. But if you want to remote administrator your database via EM or GRID then you allow this feature.

    sqlplus "username@tns_alias as sysdba" and then provide the password on the prompt otherwise the password would be provided as "username/pswd@tns"


    HTH -- Mark D Powell --
  • 61. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Good Morning to all ;

    I can connect my database.

    *[oracle@linuxserver dbs]$ orapwd file=orapworcltest password=asdf force=y*

    SQL> conn sys/asdf@ORCLTEST as sysdba
    Connected.

    SQL> conn sys@ORCLTEST as sysdba
    Enter password:
    Connected.
    I am giving wrong password here ..
    SQL> conn sys/1234@ORCLTEST as sysdba
    ERROR:
    ORA-01017: invalid username/password; logon denied
    Warning: You are no longer connected to ORACLE.

    SQL> conn sys/asdf@ORCLTEST as sysdba
    Connected.

    SQL> startup
    ORA-01081: cannot start already-running ORACLE - shut it down first

    SQL> select name from v$database;
    NAME
    ORCLTEST


    Thanks to all ; who has helped me to solve my problem.
  • 62. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Good Morning sb;
    It bothers me that the tnsnames.ora file specifies SERVICE_NAME while listener.ora specifies SID.
    IMO, both should use the same; either both use SID or both use SERVICE_NAME.
    I made some changes in my tnsnames.ora and listener.ora

    i.e. ( i deleted both files and i created new files with new listener name (listener)

    My new tnsnames.ora file  entry i

    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.

    ORCLPROD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orclprod)
    )
    )
    ORCLTEST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcltest)
    )
    )

    My LISTENER.ORA  Entry

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME =orcltest)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (GLOBAL_DBNAME=orcltest)
    )
    (SID_DESC =
    (SID_NAME = orclprod)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (GLOBAL_DBNAME=orclprod)
    )
    )
    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
    )
    )

    Once again please clear my doubt sb
    My problem is solved.  but i can't understand clearly why  first time  i can't connect ?

    I made some changes ( listener name modified , service_name modified , even both database also newly created just now)

    Thanks  sb  and  Mark .Powell and sharma
  • 63. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Some additional information  about my new listener
    *$ lsnrctl status*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-MAR-2013 13:03:24

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxserver)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
    Start Date 12-MAR-2013 11:39:09
    Uptime 0 days 1 hr. 24 min. 15 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
    Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
    Services Summary...
    Service "orclprod" has 2 instance(s).
    Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...
    Instance "orclprod", status READY, has 1 handler(s) for this service...
    Service "orclprodXDB" has 1 instance(s).
    Instance "orclprod", status READY, has 1 handler(s) for this service...
    Service "orclprod_XPT" has 1 instance(s).
    Instance "orclprod", status READY, has 1 handler(s) for this service...
    Service "orcltest" has 2 instance(s).
    Instance "orcltest", status UNKNOWN, has 1 handler(s) for this service...
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Service "orcltestXDB" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Service "orcltest_XPT" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    The command completed successfully
  • 64. Re: Cant connect my database using net service_name
    EdStevens Guru
    Currently Being Moderated
    969352 wrote:
    Good Morning sb;
    <snip>
    My problem is solved.  but i can't understand clearly why  first time  i can't connect ?
    see: [url http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/] Help! I can’t connect to my database
    see: [url http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/] Help! I can’t connect to my database (part duex)
    I made some changes ( listener name modified , service_name modified , even both database also newly created just now)

    Thanks  sb  and  Mark .Powell and sharma
  • 65. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Hello EdStevens ;
    Yesterday i saw following thread; What can go wrong? => Really i wonder when reading ..
    Re: TNS:listener does not currently know of service requested in connect

    Is this reason  why first time i can't connect ?

    1st time : In tns file , I mentioned service name like *(database global name)*
    2nd time : In tns file , I mentioned service name as oracle SID_NAME
    My doubt is ( in your blogs also . service name is not like database global_name)
    http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/
    http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/
    - service name mentioned as curely

    Always I am keeping touch with OTN. Good Supporters *(sb, edstevens , rp, justin and some others )*

    Really i feel good. Thanks EdStevens ..
  • 66. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    what i believe
    If we make service name is database globalname we can't connect database"

    This is not workable ( if service_name is database global_name)
    SQL> conn sys/password@net_service_name as sysdba
    Thanks ..
  • 67. Re: Cant connect my database using net service_name
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    what i believe
    If we make service name is database globalname we can't connect database"

    This is not workable ( if service_name is database global_name)
    "not workable" is 100% & totally devoid of any actionable detail.
    We don't know what you did.
    We don't know how Oracle responded.

    I don't know what you have.
    I don't know what you do.
    I don't know what you see.
    It is really, Really, REALLY difficult to fix a problem that can not be seen.
    use COPY & PASTE so we can see what you do & how Oracle responds.

    >
    SQL> conn sys/password@net_service_name as sysdba
    You should *NOT* be connecting "as sysdba" across the network.

    my car won't go
    tell me how to make my car go.
  • 68. Re: Cant connect my database using net service_name
    TSharma-Oracle Guru
    Currently Being Moderated
    I think you guys are going to make a record with maximum number of posts in a single thread ;)
  • 69. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Now i entirely changed my database , tnsnames.ora ,listener.ora including listener name. (old files were deleted)
    It is really, Really, REALLY difficult to fix a problem that can not be seen.
    But my problem was  solved. Thanks sb for your all replies ..
  • 70. Re: Cant connect my database using net service_name
    jgarry Guru
    Currently Being Moderated
    TSharma wrote:
    I think you guys are going to make a record with maximum number of posts in a single thread ;)
    Not even close ;)

    I'm still wondering if this a typo from not using cut and paste:
    oracle 11696 1 0 16:31 ? 00:00:00 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTener_ORcl -inherit
  • 71. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    I am so sorry .. again disturbing forum members ..
    i red following thread ..
    Listener status shows 2 instances when only 1 exists
    Uwe Hesse said
    READY means listener knows this because PMON of that instance told him so (dynamic registration)
    UNKNOWN means listener knows this because of SID_LIST entry in the listener.ora (static registration)

    when i am checking my listener
    $ lsnrctl status

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-MAR-2013 00:20:41

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linuxserver)(PORT=1521)))
    STATUS of the LISTENER
    Alias LISTENER
    Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
    Start Date 12-MAR-2013 11:39:09
    Uptime 0 days 12 hr. 41 min. 31 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
    Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
    Services Summary...
    Service "orclprod" has 2 instance(s).
    Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...
    Instance "orclprod", status READY, has 1 handler(s) for this service...
    Service "orclprodXDB" has 1 instance(s).
    Instance "orclprod", status READY, has 1 handler(s) for this service...
    Service "orclprod_XPT" has 1 instance(s).
    Instance "orclprod", status READY, has 1 handler(s) for this service...
    Service "orcltest" has 2 instance(s).
    Instance "orcltest", status UNKNOWN, has 1 handler(s) for this service...
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Service "orcltestXDB" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Service "orcltest_XPT" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    The command completed successfully

    Please Note : My db (orcltest , orclprod) having single instance only.
    but listener showing 2 instances.
    when static registration required and dynamic registration required (listener) ?
    I feel panic  continuously  asking  here ..

    Thanks ..
  • 72. Re: Cant connect my database using net service_name
    sb92075 Guru
    Currently Being Moderated
    stop looking for problems where none exist.

    stop acting like Chicken Little!
    http://en.wikipedia.org/wiki/Chicken_little

    post here only when you have error code & message; like ORA-01555 Snapshot Too Old.
  • 73. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    I think and i am sure , my question was not related to Snapshot Too Old.
    stop acting like Chicken Little!
    - I am sorry sb. Am i getting angry reply ?

    I am confused. I am extremely sorry to disturbed you.

    Thanks ..
  • 74. Re: Cant connect my database using net service_name
    507581 Newbie
    Currently Being Moderated
    do not confused with rac .

    it says

    Services Summary...




    Service "orclprod" has 2 instance(s).
    its coming because of listener you have entry with this name GLOBAL_DBNAME
    (SID_DESC =
          (GLOBAL_DBNAME=orclprod)
          (ORACLE_HOME = / / / / /db_1)
          (SID_NAME = orclprod)
        )
    Instance "orclprod", status UNKNOWN, has 1 handler(s) for this service...
    its coming because of listener you have entry with this name SID_NAME
    Instance "orclprod", status READY, has 1 handler(s) for this service...
    due to pmon register based on db_name,service_names plus db_domain parameter in spfile and pmon registers in every 60 seconds
    or manually you may have tried alter system register;
    UNKNOWN

    Further, notice that its status is UNKNOWN.
    This status of UNKNOWN is the indication that this registration came from the SID_LIST section of listener.ora.
    It is unknown because the listener doesn’t make a check to see if there is an instance named “orclprod” broadcasting a service name of “orclprod”.


    the READY instance comes from the database having registered with the listener (dynamic registration).



    thanks to stevens for nice post
    http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

Legend

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