This discussion is archived
12 Replies Latest reply: Apr 6, 2013 2:46 PM by user490795 RSS

Connecting to oracle 11g idle database on centOS

user490795 Newbie
Currently Being Moderated
Hello

I have installed oracle 11g expression edition on centOS machine. It was working fine. I recently changed my internet service provider. From that time, it is not working.
Whenever I startup oracle DB, I am getting following error.
ORA-00119: invalid specification for system parameter LOCAL LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP) (HOST=Client -3) (PORT=1521))'

can you please help me to resolve this issue.

Thanks

Mohamed Rafi
  • 1. Re: Connecting to oracle 11g idle database on centOS
    rukbat Guru Moderator
    Currently Being Moderated
    Moderator Action and Comment:
    This thread has been moved out of the Oracle Linux forum to the XE forum,
    for closer topic alignment.

    Your earlier hijack of an old (and unrelated) 2012 thread has been deleted.
    Your initial choice of the Oracle Linux forum for this new post was also utterly incorrect...

    That forum isn't a "Oracle application on some any old Linux OS" forum, and you're not using Oracle Linux.

    If you expect proper and considerate responses you may need to pay attention to exactly how and where you post your questions.
  • 2. Re: Connecting to oracle 11g idle database on centOS
    clcarter Expert
    Currently Being Moderated
    Will answer the question with another question ...
    HOST=Client -3
    Is that your machine's valid hostname? If not, figure out the correct value for hostname, and adjust your local_listener parameter. Or since its the default port, try clearing the parameter. If the database won't startup because of invalid parameters, that is another, different problem to solve. Trivial with experience, on the first try fixing a bad parameter could be tricky.

    Anyways, if the the database is running:
    sqlplus /nolog
    conn system
    ... password ...
    alter system reset local_listener scope=both;
    -- bounce it to make sure the error is gone
    conn /as sysdba
    shutdown immediate;
    startup;
  • 3. Re: Connecting to oracle 11g idle database on centOS
    Bas de Klerk Pro
    Currently Being Moderated
    Hi,

    check /u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora and /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora

    check if there are any references to your old hostname and/or ip address and if you find them change them to the new address/hostname

    If all this is ok and it still does not work check your hostfile ( /etc/hosts ) to see if the entries there are correct for your new setup.

    Usually the local listener error has to do with incorrect settings in the tnsnames.ora and/or hostfile but be sure to check all files.

    After changing you might need to restart some components like the listener and/or database (probably not but just to make sure if it still does not work ).

    Regards
    Bas
  • 4. Re: Connecting to oracle 11g idle database on centOS
    user490795 Newbie
    Currently Being Moderated
    Thanks for your reply. Sorry for late reply. I still have issue. I will send the details tomorrow.
  • 5. Re: Connecting to oracle 11g idle database on centOS
    user490795 Newbie
    Currently Being Moderated
    Thanks for your help and reply. I still have issue after changing hostname and I will send you the details tomorrow.
  • 6. Re: Connecting to oracle 11g idle database on centOS
    clcarter Expert
    Currently Being Moderated
    after changing hostname
    Which hostname? The host hostname? Or the listener.ora ...HOST= <hostname> setting? Using the "any IP" value of ... HOST = 0.0.0.0 or moving the listener.ora file out of the way will usually work around most host network configuration glitches.

    But it does appear that an invalid local_listener parameter value can prevent a startup. Getting a "bad" value out of an spfile is tricky, especially if the instance will not even startup.

    So do a startup mount using a static initialization file, create a new spfile, shut it down, and startup. That should fix it, if you can get a "good" init.ora file to use for the startup.

    Begin with your alertXE.log. Go all the way to the end of the alertlog file and search backwards, locate your last good startup, and find the lines after "System parameters with non-default values:".

    That section of lines are all the non-default parameters used for that startup. Copy just those lines to a pfile, i.e. in c:\temp\initXE.ora, use notepad. Not MSword. Remove the "spfile= ..." line. If there is a 'local_listener' entry, remove that line as well. The local_listener setting is not needed when using a listener on the 1521 default port.

    Now for the fun part. Make sure your OracleServiceXE service is running in the services applet (Start/Run/services.msc). Open up a command box (Start/Run/cmd.exe). In the command box get a sqlplus ... sysdba connection and:
    sqlplus /nolog
    conn /as sysdba;
     ... Connected to an idle instance.
    startup nomount pfile='c:\temp\initXE.ora';
    ORACLE instance started.
    ... SGA, redo, etc. details ...
    create spfile from  pfile='c:\temp\initXE.ora';
      ... File created.
    shutdown immediate;
      ... ORA-01507: database not mounted ... yes that is normal because the database was not open nor mounted
      ... ORACLE instance shut down.
    startup;
      ... ORACLE instance started.
      ... SGA, etc info ... 
      ... Database mounted.
      ... Database opened.
    If you don't get the 'ORACLE instance started.' from the startup nomount pfile=' bit, fix what ever is incorrect with the initXE.ora file, shutdown immediate; and try the startup nomount ... again. If that last startup doesn't show the ... mounted ... opened bits, something else still isn't quite right with the init file.
  • 7. Re: Connecting to oracle 11g idle database on centOS
    user490795 Newbie
    Currently Being Moderated
    Hello

    Thanks for your reply. After changing my host name with my ipaddress, I am getting this message (while try to start listener).

    I changed it in both tnsnames and listener files.

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) is replaced by my machine ip address

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.xxx.xx.xx)(PORT = 1521))


    [root@Client-3 bin]# ./lsnrctl start

    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-APR-2013 19:01:04

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

    Message 1070 not found; No message file for product=network, facility=TNSTNS-12546: Message 12546 not found; No message file for product=network, facility=TNS
    TNS-12560: Message 12560 not found; No message file for product=network, facility=TNS
    TNS-00516: Message 516 not found; No message file for product=network, facility=TNS
    Linux Error: 13: Permission denied
    [root@Client-3 bin]#

    Please let me know if I am doing anything wrong in changing host name.

    Thanks and appreciated your help

    Mohamed Rafi
  • 8. Re: Connecting to oracle 11g idle database on centOS
    Bas de Klerk Pro
    Currently Being Moderated
    You're executing the lsnrctl start comnand as user root which is not correct
    Execute this as user oracle, after logging in as user oracle ( su - oracle ) your environment should be set so the lsnrctl start command will work.

    Another way would be to do this as user root but by executing the services script, this starts both listener and database/instance :
    service oracle-xe start
    Edited by: Bas de Klerk on 4-apr-2013 22:50
  • 9. Re: Connecting to oracle 11g idle database on centOS
    user490795 Newbie
    Currently Being Moderated
    Hello

    Thanks for your reply.

    I tried to restart as root with oracle-xe start but I am still getting 'connected to idle instance'. I already changed host by replacing with ipaddress.

    Regards

    Mohamed Rafi
  • 10. Re: Connecting to oracle 11g idle database on centOS
    Bas de Klerk Pro
    Currently Being Moderated
    Are you still getting the same error about the local_listener when your try to start the database from SQL*Plus ( should be done as user oracle! ) ?

    If you connect to an idle instance it means that your environment is not correctly set or the instance you are refering to is not started ( is this is the case the command startup should start it up from SQL*plus ).
    As user root your environment will probably always be wrong to use sql*plus so make sure you're logged in as user Oracle and ORACLE_SID is set to XE.
  • 11. Re: Connecting to oracle 11g idle database on centOS
    user490795 Newbie
    Currently Being Moderated
    Hello

    Thanks for your reply. Appreciated.

    I correctly set ORACLE_HOME and ORACLE_SID and also changed tnsnames.ora and listener.ora with new ip address in the place of hostnaee.hostdomain as ( HOST = 192.XXX.XX.XX).
    and restart listner as oracle user ( and also tried as root user). Still I am getting the same message - connected to idle database.

    am I doing anything wrong? Is something missing in the tnsname.ora and listener.ora setting>

    Please advice.

    Thanks

    Mohamed Rafi
  • 12. Re: Connecting to oracle 11g idle database on centOS
    user490795 Newbie
    Currently Being Moderated
    Hello Friends

    Finally I could able to get through this things.

    1. First I changed tnsnames.ora and listener.ora files with my host name.
    2. Changed etc/hosts file accordingly
    3. service oracle-xe start.

    It works.

    Thanks for all your help. Highly appreciated.

    Mohamed Rafi

Legend

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