Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘ORA-00119: invalid specification for system parameter LOCAL_LISTENER’

DBUA failing with LOCAL_LISTENER parameter error

Posted by Pavan DBA on December 5, 2013


Today, lets discuss about an article related to Database upgrade failure…

Sometimes, when we are upgrading the database from 9i/10g to 11gR2 version (11.2.0.1 or 11.2.0.3) using DBUA, we might land up with an error saying “LOCAL_LISTENER parameter is empty”.

One of the steps before we start DBUA for database upgrade is to copy the pfile or spfile from current ORACLE_HOME/dbs to the upgrading version ORACLE_HOME/dbs location. For example, when we upgrade from 10g to 11g, we will copy pfile or spfile of 10g database from 10g ORACLE_HOME/dbs to 11g ORACLE_HOME/dbs.

In that pfile or spfile, if we don’t set any value for LOCAL_LISTENER parameter, then we will face the error mentioned above.

Also, we can observe following message in the upgrade log file.

createAddressListAlias TNS_ADMIN=null
createAddressListAlias ORACLE_HOME=/app/oracle/product/11.2.0/db_1

If it is a RAC environment, we may see more errors as below

ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name ‘LISTENER_PRODDB1’

As DBUA failed, we need to restore the backup of the database.

Once done, we need to set TNS_ADMIN parameter to point to new ORACLE_HOME/network/admin path and also copy tnsnames.ora and listener.ora file from old ORACLE_HOME to new ORACLE_HOME.

export TNS_ADMIN=$11g_ORACLE_HOME/network/admin
cp $10g_ORACLE_HOME/network/admin/tnsnames.ora $11g_ORACLE_HOME/network/admin
cp $10g_ORACLE_HOME/network/admin/listener.ora $11g_ORACLE_HOME/network/admin

After the above steps, start DBUA once again and this time it would be successful.

 

Posted in upgradation | Tagged: , , , , , | Leave a Comment »

 
%d bloggers like this: