This discussion is archived
9 Replies Latest reply: Oct 13, 2012 11:04 PM by Osama_Mustafa RSS

Error Oracle connect

R. Royal Newbie
Currently Being Moderated
Hi,
Oracle version: 10g
O.S. Linux Red Hat

Where I try to connect my DB I get: ORA-12154

Below tnsnames and listener
--tnsnames.ora

alias_oqlt =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP) (HOST = ora102) (PORT = 1522))
        (CONNECT_DATA =
                    (SERVICE_NAME = oqlt)
        )
    )
     
--listener.ora

alias_oqlt =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora102)(PORT = 1522))
    )
  )

SID_LIST_alias_oqlt =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = oqlt)
      (ORACLE_HOME = /u01/oracle/product/10.2.0)
    )
  )

tnsping alias_oqlt

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = ora102) (PORT = 1522)) (CONNECT_DATA = (SERVICE_NAME = oqlt)))
OK (0 msec)
--


If I change port from 1522 to 1521 I can connect correctly

Have you any idea?
  • 1. Re: Error Oracle connect
    Nimai_Karmakar Newbie
    Currently Being Moderated
    http://library.blackboard.com/ref/df5b20ed-ce8d-4428-a595-a0091b23dda3/Content/_admin_server_database/database_oracle_changing_database_ports.htm
  • 2. Re: Error Oracle connect
    Nimai_Karmakar Newbie
    Currently Being Moderated
    alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))";
    alter system register;
  • 3. Re: Error Oracle connect
    EdStevens Guru
    Currently Being Moderated
    Raf Royal wrote:
    Hi,
    Oracle version: 10g
    O.S. Linux Red Hat

    Where I try to connect my DB I get: ORA-12154

    Below tnsnames and listener
    --tnsnames.ora
    
    alias_oqlt =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP) (HOST = ora102) (PORT = 1522))
    (CONNECT_DATA =
    (SERVICE_NAME = oqlt)
    )
    )
         
    --listener.ora
    
    alias_oqlt =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora102)(PORT = 1522))
    )
    )
    
    SID_LIST_alias_oqlt =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = oqlt)
    (ORACLE_HOME = /u01/oracle/product/10.2.0)
    )
    )
    
    tnsping alias_oqlt
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = ora102) (PORT = 1522)) (CONNECT_DATA = (SERVICE_NAME = oqlt)))
    OK (0 msec)
    --


    If I change port from 1522 to 1521 I can connect correctly

    Have you any idea?
    What you post is not consistent with the error you report. for the reported error, See http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/

    From what you post, I'd say your listener is not configured using the listener.ora you posted. Go to the db server and issue
    lsnrctl status
    lsnrctl status alias_oqlt
    And why do you try to give your listener a non-default name? What does that achive, other than making management and troubleshooting more difficult?
  • 4. Re: Error Oracle connect
    EdStevens Guru
    Currently Being Moderated
    Nimai_Karmakar wrote:
    alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))";
    alter system register;
    There is nothing in the OP to indicate the problem is with the database not being registered with the listener. This suggestion is very premature.
  • 5. Re: Error Oracle connect
    ShankarViji Pro
    Currently Being Moderated
    Hi Raf,

    The default port is 1521.

    Add the Connection String to the remote database in the TNSNAMES.ora usually located in $ORACLE_HOME\ora81\network\ADMIN

    And, Once added you can check the Service for the database can be reached using <a href = "http://www.oracleutilities.com/OSUtil/ping.html">TNSPING</a>

    Something like this, From Command Prompt,
    C:\Documents and Settings\s0891811>tnsping orcl
     
    TNS Ping Utility for 32-bit Windows: Version 9.0.1.3.1 - Production on 13-OCT-20
    12 10:59:11
     
    Copyright (c) 1997 Oracle Corporation.  All rights reserved.
     
    Used parameter files:
    C:\oracle\ora81\network\admin\sqlnet.ora
    C:\oracle\ora81\network\admin\tnsnames.ora
     
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
    (HOST = 17.12.1.9)(PORT = 1521))) (CONNECT_DATA = (SID = orcl)))
    OK (70 msec)
     
    C:\Documents and Settings\s0891811>
    Here orcl is the database you are trying to connect.

    Thanks,
    Shankar
  • 6. Re: Error Oracle connect
    EdStevens Guru
    Currently Being Moderated
    Shankar Viji wrote:
    Hi Raf,

    The default port is 1521.

    Add the Connection String to the remote database in the TNSNAMES.ora usually located in $ORACLE_HOME\ora81\network\ADMIN
    BZZZTTT!

    The OP has stated he is running 10g. I'd be very surprised if his tns config files were located in $ORACLE_HOME/ora81/anything.

    More likely at $ORACLE_HOME/network/admin. Even if he were running 8.1, the default would still be $ORACLE_HOME/network/admin.

    No 'ora81' and since he's on a *nix system the syntax is 'forward-slash ('/'), not the Windows standard 'back-slash' ('\').



    >
    And, Once added you can check the Service for the database can be reached using <a href = "http://www.oracleutilities.com/OSUtil/ping.html">TNSPING</a>

    Something like this, From Command Prompt,
    C:\Documents and Settings\s0891811>tnsping orcl
    
    TNS Ping Utility for 32-bit Windows: Version 9.0.1.3.1 - Production on 13-OCT-20
    12 10:59:11
    
    Copyright (c) 1997 Oracle Corporation.  All rights reserved.
    
    Used parameter files:
    C:\oracle\ora81\network\admin\sqlnet.ora
    C:\oracle\ora81\network\admin\tnsnames.ora
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
    (HOST = 17.12.1.9)(PORT = 1521))) (CONNECT_DATA = (SID = orcl)))
    OK (70 msec)
    
    C:\Documents and Settings\s0891811>
    Here orcl is the database you are trying to connect.

    Thanks,
    Shankar
  • 7. Re: Error Oracle connect
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    To Change Ports from 1522 to 1521 follow the below steps :
    -lsnrctl stop
    -use netca to reconfigure listener and modify to new ports 
    -go to $ORACLE_HOME/network/admin and change port in tnsnames.ora and listener.ora
    -lsnrctl start
    -lsnrctl status
    -check connection 
  • 8. Re: Error Oracle connect
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    To Change Ports from 1522 to 1521 follow the below steps :
    -lsnrctl stop
    -use netca to reconfigure listener and modify to new ports 
    -go to $ORACLE_HOME/network/admin and change port in tnsnames.ora and listener.ora
    -lsnrctl start
    -lsnrctl status
    -check connection 
  • 9. Re: Error Oracle connect
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    To Change Ports from 1522 to 1521 follow the below steps :
    -lsnrctl stop
    -use netca to reconfigure listener and modify to new ports 
    -go to $ORACLE_HOME/network/admin and change port in tnsnames.ora and listener.ora
    -lsnrctl start
    -lsnrctl status
    -check connection 

Legend

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