Saturday, September 21, 2013

ORA-00119 During a Database Upgrade

I consider myself a reasonably detail oriented person – reasonably… Well, every now and then, more often than not, I tend to overlook documentation. Either that, or my dyslexia kicks in.

Before I go into my story, let me explain the configuration, which led me to the setup on which I had a problem.
  •     11.1.0.7.0 CRS & ASM upgraded to 11.2.0.3.0 GI. 
  •     11.2.0.3.0 RDBMS Software Installed.

During an upgrade of a few 11.1.0.7.0 Databases to 11.2.0.3.0 on Solaris 10 deployed on a two node RAC, I encountered the error below.

CRS-2674: Start of 'ora.freedom.db' on 'jellybean1' failed
CRS-2632: There are no more servers to try to place resource 'ora.freedom.db' on that would satisfy its placement policy
CRS-5017: The resouce action "orac.freedom.db start" encountered the following error
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA000132: syntax error inresolved network name 'LISTENER_FREEDOM1'

CRS-2674: Start of 'ora.freedom.db' on 'jellybean2' failed
CRS-2632: There are no more servers to try to place resource 'ora.freedom.db' on that would satisfy its placement policy
CRS-5017: The resouce action "orac.freedom.db start" encountered the following error
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA000132: syntax error inresolved network name 'LISTENER_FREEDOM2'

The error to note is the ORA-00119. It’s basically telling me the issue, CRS is unable to start the resource!

I found a MOS note which talks about my situation. “DBUA failed to start instance (ORA-00119) when upgrading from pre 11.2 database to 11.2 (Doc ID 1552530.1)”.

It simply said, to set the LOCAL_LISTENER parameters to the actual address prior to running the DBUA.

For example (excuse my plagiarism from MOS, I’m too lazy to change the names).
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx-vip)(PORT = 1521))' scope=spfile sid='xxx1';
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx-vip)(PORT = 1521))' scope=spfile sid='xxx2';

Again, unfortunately I did not create flashback restore point, but fortunately the error only happens with the Post Upgrade Steps. My fix, for the currently database, was to startup the database and set the parameter correctly. That “seemed” to fix the issue.

For the next database, I set the parameter before the upgrade. But to my surprise, the LOCAL_LISTENER parameter was reset to LISTENER_<SID>!!

Okay, at this point, I had to find out what was going on. On my third instance, I actually set the parameter as per the recommendation above and bounced it. Voila, the parameter is set because the instance dynamically registers it. But, where is it even getting the value??

After a little creative digging around, it turns out that I missed a step after installing the RDBMS software. I will outline the steps for the upgrade process and follow up with what actually happened.

  1. Shutdown Database (from 11.1 RDBMS home)
  2.  Startup Upgrade one instance on (from 11.2 RDBMS home)
  3. Upgrade Database Components
  4. Post Upgrade

a.     Shutdown instance
b.     Startup Database from 11.2 GI
c.      Error with ORA-00119

It took me a few minutes to put the pieces together. I was able to start the database via server control (srvctl) on the 11.1 home, but not from srvctl on 11.2 home. What was missing? Wait, where is the LOCAL_LISTENER parameter getting its default value? Think man think! Oh shoot, the TNSNAMES.ORA file!



After I copied the TNSNAMES.ORA file to the new 11.2 RDBMS home, the next upgrade worked without a glitch.

So, if you’ve found your way to this post, I sincerely hope it helps you.

Cheers!





at
Labels: , , ,

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )