Pavan DBA's Blog

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.