Sunday, April 14, 2013

Removing obsolete parameters from spfile

This is a common error message after upgrade of the database:
ORA-32004: obsolete or deprecated parameter(s) 
specified for RDBMS instance

In 11g (last tested with 11.2.0.3) even if your start up the database from an old type pfile without any obsolete parameters and then create an spfile from memory like this:
create spfile from memory;

Oracle will insert two obsolete parameters into the spfile. This query will list obsolete parameters in your spfile:
select p.name,p.value
from v$parameter p, v$spparameter s
where s.name=p.name
and p.isdeprecated='TRUE'
and s.isspecified='TRUE';

NAME                  VALUE
--------------------- ----------------------------------------------
background_dump_dest  /u01/app/oracle/diag/rdbms/hfmtst/HFMTST/trace
user_dump_dest        /u01/app/oracle/diag/rdbms/hfmtst/HFMTST/trace
They can be cleared with:
alter system reset background_dump_dest;
alter system reset user_dump_dest;