ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

While startup of the instance, the deprecated message always pop up due to unsupported parameters in newer oracle version

SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
Obsolete system parameters with specified values:
  _optimizer_adaptive_plans
End of obsolete system parameter listing
Deprecated system parameters with specified values:
  sql_trace
End of deprecated system parameter listing

Check the obsolete parameters

SQL> desc v$obsolete_parameter;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(64)
 ISSPECIFIED                                        VARCHAR2(5)
 CON_ID                                             NUMBER

SQL> select count(*) from v$obsolete_parameter;

  COUNT(*)
----------
       172

Filter out only the obsolete parameter with ISSPECIFIED as true

SQL> select * from v$obsolete_parameter where ISSPECIFIED='TRUE';

NAME                 ISSPE     CON_ID
-------------------- ----- ----------
_optimizer_adaptive_ TRUE           0
plans

You can also check the parameters from v$parameter and v$spparameter views

SQL> select name,value from v$parameter where isdeprecated='TRUE'; 
--v$spparameter

NAME                 VALUE
-------------------- --------------------
lock_name_space
instance_groups
resource_manager_cpu 1
_allocation

active_instance_coun
t

db_block_buffers     0
buffer_pool_keep
buffer_pool_recycle
log_archive_start    FALSE
cluster_database_ins 1
tances

fast_start_io_target 0
serial_reuse         disable
rdbms_server_dn
remote_os_authent    FALSE
sec_case_sensitive_l TRUE
ogon

unified_audit_sga_qu 1048576
eue_size

cursor_space_for_tim FALSE
e

plsql_v2_compatibili FALSE
ty

plsql_debug          FALSE
background_dump_dest /data01/product/19.0
                     .0/rdbms/log

user_dump_dest       /data01/product/19.0
                     .0/rdbms/log

commit_write
sql_trace            TRUE
parallel_adaptive_mu FALSE
lti_user

asm_preferred_read_f
ailure_groups


24 rows selected.

If you try to set a value to the deprecated parameter, then you get error message

SQL> alter system set "_optimizer_adaptive_plans"=false;
alter system set "_optimizer_adaptive_plans"=false
*
ERROR at line 1:
ORA-25138: _OPTIMIZER_ADAPTIVE_PLANS initialization parameter has been made obsolete

Remove the deprecated parameter from pfile and Create spfile from pfile

SQL> create spfile from pfile;

Bounce the instance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s