"TNS-12541: TNS:no listener" error proves no match for Super-useful Oracle commands

I'm running Oracle on my XP development box. After making a few changes to my network I found that Oracle no longer started. The first step to checking things was to use super-useful Oracle command #1: "tnsping". If you have the Oracle bin in your path, you can run this from anywhere:

tnsping mybase

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 18-JUN-2
008 11:14:42

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

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.1
98)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mybase)))

TNS-12535: TNS:operation timed out


So, first problem is that the host name I'm using is hard-coded in there to be "10.0.1.1". A quick check of "ipconfig /all" reveals that, with my network tinkering, my IP address has changed. In fact, I'm running XP on a virtual machine and not using a static IP address so the IP address was going to change sometime anyway. So we'll replace my IP address with my new IP address instead. To modify this setting, find your "tnsnames.ora" file; for me it lives at C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora.

OK, so now a "tnsping" gives me the lovely "TNS-12541: TNS:no listener" message. Checking my Windows Services, I can see that the Oracle TNS Listener service (OracleOraDb10g_home1TNSListener) is stopped. If I try to start it I get a nice generic Windows Service message "Error 1067: The process terminated unexpectedly."

So, how do I find out what is happening with the listener? By using super-useful Oracle command #2: "lsnrctl". Again you can find this in your Oracle bin directory. You can now check the listener status via "lsnrctl status". Again, this revealed the same "10.0.1.198" IP address hard-coded in the listener config. So again we need to replace that with the new IP address also. The file that controls this is "listener.ora" and it lives in the same place as your "tnsnames.ora".

I found these needed to be done in a set order:

1. Shutdown Oracle.
   

   sqlplus user/pwd as sysdba
   shutdown immediate



2. Stop the listener.
   

   lsnrctl stop



3. Change the IP addresses in *.ora files.



4. Start the listener (notice that your database is not registered)
   

   lsnrctl start



5. Start Oracle.
   

   sqlplus user/pwd as sysdba
   startup



6. Check the db is registered to the listener.
   

   lsnrctl status



7. Try to login via the tnsname.
   

   sqlplus user/pwd@mybase

So, to summarise, replace the hard-coded IP address with your new IP address in your "tnsnames.ora" and "listener.ora" files.

Note that I did try to use both "localhost" and "127.0.0.1" but couldn't get the listener to pick up the database√.