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
  • 45. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    mv listener.ora listsner.ora.sav
    $ ls -l
    total 16
    -rw-r--r-- 1 oracle oinstall 632 Mar 10 20:18 listener.ora.sav
    -rw-r--r-- 1 oracle oinstall 782 Mar 10 20:18 tnsnames.ora


    *$ lsnrctl start # wait 60+ seconds*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2013 21:22:28

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

    Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

    TNSLSNR for Linux: Version 10.2.0.1.0 - Production
    Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=1521)))

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
    Start Date 10-MAR-2013 21:22:31
    Uptime 0 days 0 hr. 0 min. 1 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    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)))
    The listener supports no services
    The command completed successfully

    *[oracle@linuxserver admin]$ lsnrctl status*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2013 21:23:39

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

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
    Start Date 10-MAR-2013 21:22:31
    Uptime 0 days 0 hr. 1 min. 8 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    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)))
    Services Summary...
    Service "orcltest" has 1 instance(s).
    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

    *[oracle@linuxserver admin]$ lsnrctl service*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2013 21:23:49

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

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    Services Summary...
    Service "orcltest" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "orcltestXDB" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Handler(s):
    "D000" established:0 refused:0 current:0 max:1022 state:ready
    DISPATCHER <machine: linuxserver, pid: 5485>
    (ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=32777))
    Service "orcltest_XPT" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    The command completed successfully
    the listener & wait 60+ second
    - MAY I i know what is the purpose of this comamnd ?

    Thanks sb;
  • 46. Re: Cant connect my database using net service_name
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    mv listener.ora listsner.ora.sav
    $ ls -l
    total 16
    -rw-r--r-- 1 oracle oinstall 632 Mar 10 20:18 listener.ora.sav
    -rw-r--r-- 1 oracle oinstall 782 Mar 10 20:18 tnsnames.ora


    *$ lsnrctl start # wait 60+ seconds*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2013 21:22:28

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

    Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

    TNSLSNR for Linux: Version 10.2.0.1.0 - Production
    Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=1521)))

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
    Start Date 10-MAR-2013 21:22:31
    Uptime 0 days 0 hr. 0 min. 1 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    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)))
    The listener supports no services
    The command completed successfully

    *[oracle@linuxserver admin]$ lsnrctl status*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2013 21:23:39

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

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
    Start Date 10-MAR-2013 21:22:31
    Uptime 0 days 0 hr. 1 min. 8 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    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)))
    Services Summary...
    Service "orcltest" has 1 instance(s).
    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

    *[oracle@linuxserver admin]$ lsnrctl service*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2013 21:23:49

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

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    Services Summary...
    Service "orcltest" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "orcltestXDB" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Handler(s):
    "D000" established:0 refused:0 current:0 max:1022 state:ready
    DISPATCHER <machine: linuxserver, pid: 5485>
    (ADDRESS=(PROTOCOL=tcp)(HOST=linuxserver)(PORT=32777))
    Service "orcltest_XPT" has 1 instance(s).
    Instance "orcltest", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    The command completed successfully
    the listener & wait 60+ second
    - MAY I i know what is the purpose of this comamnd ?

    Thanks sb;
    every V10+ database registers itself with LISTENER on port #1521 every 60 seconds.
    by waiting more than 1 minute, this delay guarantees that the listener STATUS & SERVICE reports active & online DBs.

    Look above to see the actual SERVICE_NAME that the listener knows about; specifically "orcltest".

    SERVICE_NAME=orcltest

    above what should be in tnsnames.ora file.
  • 47. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    TNSNAMES.ORA file Entry

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

    ORCLMYTEST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LINUXSERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = MYDB.TEST.ORCLTEST.COM)
    )
    )

    ASDF =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = asdf)
    )
    )

    LISTENER.ORA

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

    LISTENER_ORCL =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL =TCP)(HOST = linuxserver)(PORT = 1521))
    )
    )
    SID_LIST_LISTENER_ORCL =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = orcltest)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (GLOBAL_DBNAME = MYDB.TEST.ORCLTEST.COM)
    )
    (SID_DESC =
    (SID_NAME = orclprod)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (GLOBAL_DBNAME = MYDB.PROD.ORCLPROD.COM)
    )
    )
  • 48. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Look above to see the actual SERVICE_NAME that the listener knows about; specifically "orcltest".
    SERVICE_NAME=orcltest
    above what should be in tnsnames.ora file.
    Should i change anything [ service_name ] in tnsnames.ora file ?
  • 49. Re: Cant connect my database using net service_name
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    Look above to see the actual SERVICE_NAME that the listener knows about; specifically "orcltest".
    SERVICE_NAME=orcltest
    above what should be in tnsnames.ora file.
    Should i change anything [ service_name ] in tnsnames.ora file ?
    make entry look like below

    ORCLMYTEST,ORCLTEST,orcltest =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LINUXSERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcltest)
    )
    )
  • 50. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    I made entry look like below
    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.

    ORCLMYTEST,ORCLTEST,orcltest =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LINUXSERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcltest)
    )
    )

    ASDF =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linuxserver)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = asdf)
    )
    )

    ORCLMYPROD =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LINUXSERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = MYDB.PROD.ORCLPROD.COM)
    )
    )

    Thanks sb ..
  • 51. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    *$ lsnrctl reload*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2013 22:23:00

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

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    The command completed successfully

    SQL> conn u1/u1@ORCLMYTEST
    Connected.
    SQL> startup
    ORA-01031: insufficient privileges
    SQL> conn u1/asdf@ORCLTEST
    ERROR:
    ORA-01017: invalid username/password; logon denied


    Warning: You are no longer connected to ORACLE.
  • 52. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    - Still i am getting good support. Thanks for good support sb ;

    Should i make any changes ?
    Please provide good solution and what will i do ?
  • 53. Re: Cant connect my database using net service_name
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    *$ lsnrctl reload*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2013 22:23:00

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

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    The command completed successfully

    SQL> conn u1/u1@ORCLMYTEST
    Connected.
    SQL> startup
    ORA-01031: insufficient privileges
    SQL> conn u1/asdf@ORCLTEST
    ERROR:
    ORA-01017: invalid username/password; logon denied


    Warning: You are no longer connected to ORACLE.
    >
    *$ lsnrctl reload*

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2013 22:23:00

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

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    The command completed successfully

    SQL> conn u1/u1@ORCLMYTEST
    Connected.
    SQL> startup
    ORA-01031: insufficient privileges
    SQL> conn u1/asdf@ORCLTEST
    ERROR:
    ORA-01017: invalid username/password; logon denied


    Warning: You are no longer connected to ORACLE.
    http://docs.oracle.com/cd/E11882_01/server.112/e10897/instance.htm#ADMQS05141
  • 54. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    Ok sb. I tried very much but i can't  ....
    I need only one answer for my big doubt.
    I have password file=orapworcltest passwod=asdf

    in my database i ceated user  u1  and  granted sysdba  privilege.   u1  password  is :  u1

    when u1 connecting as sysdba privilege which password is need to use ?

    $ conn u1/u1@ORCLMYTEST
    $ conn u1/asdf @ORCLMYTEST
    Even i tried , not able to connect my database. so i don't know.
    Thanks ..
  • 55. Re: Cant connect my database using net service_name
    TSharma-Oracle Guru
    Currently Being Moderated
    The answer to this is u1.

    But you should go and get some classes on Oracle OR understand Oracle documentation otherwise you won't learn anything ever.
  • 56. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    But you should go and get some classes on Oracle OR understand Oracle documentation otherwise you won't learn anything ever.
    - Yes. I am learning and i am working very hard. Few things are not documented ex ( same this thread ).

    Anyhow thanks ..
  • 57. Re: Cant connect my database using net service_name
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    But you should go and get some classes on Oracle OR understand Oracle documentation otherwise you won't learn anything ever.
    - Yes. I am learning and i am working very hard. Few things are not documented ex ( same this thread ).

    Anyhow thanks ..
    IMO, it is a Security Risk to allow login AS SYSDBA across the network & I never enable it on any DB.
  • 58. Re: Cant connect my database using net service_name
    972355 Newbie
    Currently Being Moderated
    IMO, it is a Security Risk to allow login AS SYSDBA across the network & I never enable it on any DB.
    Yes. Almost I got same answer from my lead before my oracle session.

    Thanks sb;
  • 59. Re: Cant connect my database using net service_name
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    IMO, it is a Security Risk to allow login AS SYSDBA across the network & I never enable it on any DB.
    Yes. Almost I got same answer from my lead before my oracle session.

    Thanks sb;
    http://www.orafaq.com/forum/m/579268/136107/#msg_579268

Legend

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