This content has been marked as final. Show 12 replies
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.
Will answer the question with another question ...
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;
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 ).
Thanks for your reply. Sorry for late reply. I still have issue. I will send the details tomorrow.
Thanks for your help and reply. I still have issue after changing hostname and I will send you the details tomorrow.
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.
after changing hostname
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:
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.
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.
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 18.104.22.168.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
Please let me know if I am doing anything wrong in changing host name.
Thanks and appreciated your help
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 :
Edited by: Bas de Klerk on 4-apr-2013 22:50
service oracle-xe start
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.
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.
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>
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.
Thanks for all your help. Highly appreciated.