Results 1 to 10 of 10
  1. #1
    Join Date
    May 2008
    Posts
    7

    Unhappy Unanswered: connecting to sqlplus problem

    Hi,
    i created database named AMOLTEST with instance name TEST1.
    when i try to login to sqlplus by giving following credential
    username: SYS
    password: *****
    Host String: test1

    then it gives error
    ORA-12541: no listener.

    Please help me out. and when i try connecting using SCOTT and tiger.it get connected to same instance without any issues.

  2. #2
    Join Date
    May 2006
    Posts
    42
    wats ur OS?
    r u using Windows??

    if yes, then while logging into SCOTT r u sure you are not logging into the default db. The one which gets created whn you install Oracle on Windows?

    how did you create your database? using manual method or using DBCA??
    after logging into SCOTT check which db you are in.

    SQL>select * from global_name;

    OR

    SQL> define

    Further try this
    at windows command prompt type the following
    >oradim -delete -sid test1
    >oradim -new -sid test1
    >set oracle_sid=test1
    >sqlplus "sys as sysdba"
    Enter password:

    now check.

  3. #3
    Join Date
    May 2008
    Posts
    7
    Quote Originally Posted by iinfi
    wats ur OS?
    r u using Windows??

    if yes, then while logging into SCOTT r u sure you are not logging into the default db. The one which gets created whn you install Oracle on Windows?

    how did you create your database? using manual method or using DBCA??
    after logging into SCOTT check which db you are in.

    SQL>select * from global_name;

    OR

    SQL> define

    Further try this
    at windows command prompt type the following
    >oradim -delete -sid test1
    >oradim -new -sid test1
    >set oracle_sid=test1
    >sqlplus "sys as sysdba"
    Enter password:

    now check.
    I am using Windows-XP
    See, i have created one new database with following details

    Gloabal Database: AMOLDATA
    SID: DATA1
    Service(windows service): OracleServicDATA1

    See below, i have copied the details in the LISTENER.ORA and TNSNAMES.ORA files:

    TNSNAMES.ORA

    # TNSNAMES.ORA Network Configuration File: C:\OraHome91\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    data1 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = amoverma-wxp)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID_NAME = DATA1)/(SERVICE_NAME = AMOLDATA)
    )
    )

    ORCL =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = amoverma-wxp)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl.global)
    )
    )

    INST1_HTTP =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = amoverma-wxp)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = MODOSE)
    (PRESENTATION = http://HRService)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    LISTENER.ORA

    # LISTENER.ORA Network Configuration File: C:\OraHome91\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = amoverma-wxp)(PORT = 1521))
    )
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\OraHome91)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = orcl.global)
    (ORACLE_HOME = C:\OraHome91)
    (SID_NAME = orcl)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = amoldata)
    (ORACLE_HOME = C:\OraHome91)
    (SID_NAME = data1)
    )
    )

    Now when i start sqlplus and try login with following credentials

    USERNAME: SYS
    PASSWORD:****
    HOST STRING: DATA1

    then it give the error
    ORA-12504: TNS:listener was not given the SID in CONNECT_DATA

    now please help me what can i do to solve this problem.

    one more thing i would like to mention is that in the TNSNAMES.ORA if i replace the SID_NAME = DATA1 with SERVICE_NAME = AMOLDATA(which i written in red color) then the sqlplus connects perfectly.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Service_name

    Quote Originally Posted by the_amol
    Gloabal Database: AMOLDATA
    SID: DATA1
    ...one more thing i would like to mention is that in the TNSNAMES.ORA if i replace the SID_NAME = DATA1 with SERVICE_NAME = AMOLDATA(which i written in red color) then the sqlplus connects perfectly.
    The value for the SERVICE_NAME parameter comes from the SERVICE_NAMES (with an S) parameter in the initalization parameter file. The SERVICE_NAMES parameter in the initialization parameter file is typically the global database name.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    May 2008
    Posts
    7
    Quote Originally Posted by LKBrwn_DBA
    The value for the SERVICE_NAME parameter comes from the SERVICE_NAMES (with an S) parameter in the initalization parameter file. The SERVICE_NAMES parameter in the initialization parameter file is typically the global database name.
    Thats absolutely fine sir. but my concern is with the SID_NAME part.why it is not working.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation You still may have a miss-match:

    The value for the INSTANCE_NAME parameter in the tnsnames.ora defaults to INSTANCE_NAME parameter in the initalization parameter file, and the INSTANCE_NAME parameter in the initialization parameter file defaults to the SID entered during installation or database creation.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    May 2008
    Posts
    7
    but my question is different that y with SID_NAME i am getting the error although everything is right in tnsnames.ora and listener files.

  8. #8
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    The correct parameter is SID, not SID_NAME. Replace all occurances of SID_NAME with SID, in tnsnames.ora and listener.ora, and try again.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Quote Originally Posted by buckeye234
    The correct parameter is SID, not SID_NAME. Replace all occurances of SID_NAME with SID, in tnsnames.ora and listener.ora, and try again.
    Good catch!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    May 2008
    Posts
    7

    Thumbs up

    Quote Originally Posted by buckeye234
    The correct parameter is SID, not SID_NAME. Replace all occurances of SID_NAME with SID, in tnsnames.ora and listener.ora, and try again.
    Thanks Dear.
    Replacing the SID_NAME with only SID in TNSNAMES.ORA done the trick for me. but in LISTENER.ORA this change result in error so i kept is SID_NAME overthere.
    Any way thanks. now connection is working fine.

    Cheers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •