Monday, January 24, 2011

ORA-00130: invalid listener address...

The actual error was:
SQL>STARTUP;

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))
I had finally managed to create my 11gR2 instance when I got this. So what happened?

1. Started OEL 5.5 64 bit VM
2. Logged in as user oracle
3. Opened terminal and ran . oraenv
4. sqlplus / as sysdba
5. Issued the startup command.

ORA-00130 from the docs:
Cause: The listener address specification is not valid.
Action: Make sure that all fields in the listener address (protocol, port, host, key, ...) are correct.

So it has something to do with how I configured everything. What else did I change on the system?

After installing the database, I ran netca to set up the listener. I manually edited the file to change localhost.localdomain to just localhost. I also added an entry in the tnsnames.ora file to point to the listener.

From a system perspective, I decided I didn't like "new-host" showing up on my router configuration page. So I changed the hostname to oracle. I used the network GUI tool, not the CLI (still hazy on how exactly that is done).

I'm back to my sql prompt.
SQL>STARTUP;

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))
Come on!

I then asked The Google Machine for help. That led me to this thread on OTN. About 6 or 7 replies in, Hans Forbrich offers up some very helpful troubleshooting tips.

1. Check that your environment variables are set. Check.
2. Make sure the listener is started. Check.

There was a caveat for #2 though. If your listener blew up on start, check these other things. That's when I saw cat /etc/hosts and his output for that. I quickly checked mine and saw this:
[oracle@oracle bin]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
Ah...where's oracle? I tried pinging oracle, no response. I open up the file and add it in:
[oracle@oracle bin]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost oracle
::1 localhost6.localdomain6 localhost6
I ping oracle again, response!

Start and restart the listener. tnsping TESTING OK!
SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 616566616 bytes
Database Buffers 444596224 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.
SQL>
Awesome.

In my defense (or will this be my excuse?), I don't do this everyday, or ever week, or every month. It is a once a quarter, at best, exercise. I forget things. Next time, I won't forget. If I do, I'll have this helpful page to remind me what an ID10T I am.

8 comments:

Anonymous said...

you just save me and my hole group of oracle certification thanks

telugubomma said...

thanks a lot...this is very helpful

Luciano Alvarenga M. Pires said...

Muito util!!! Obrigado!!!

Jeremy said...

Chet, while wearing my oraclenerd hat today (seriously) I came across this post and it saved my ass what would have otherwise been way too many hours or beating my head against the wall.

I owe you a beer or three at Kscope. See you in Seattle.

Jeremy

oraclenerd said...

@Jeremy,

Glad to "hear" you're wearing the hat. Pictures would be nice though. :)

I shall take you up on your offer of thanks.

Jeremy said...

LOL, understood on the "hear" -- I'll see what I can do for ya.

Nagarajan said...

Chet,

I had the same problem with the lister, found that I had changed the hostname and ip address of the server due to a lab change, fixed the entries in the /etc/hosts file and that solved the problem.

Thank you,

Nagarajan.

Unknown said...

Thaaaaaank you very very very much hahah
you saved my life!
I'll be thankful for all my life.
Sucess
Lorena