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.

6 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

chet justice 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.