How to Find Deprecated Parameters

While starting up a database, we sometimes see messages saying obsolete or depricated parameter(s).

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

This message says that such parameter(s) will not be available in the future releases of the database and it is time to remove them and use the alternative, if available.

Let us see how to identify the depricated parameter that is present in database.

The first place to know the deprecated parameter is alert.log. We should be able to see the message in alert.log as below :

  db_name                  = "dosa"
  db_unique_name           = "dosa1"
  open_cursors             = 300
  dg_broker_start          = TRUE
  diagnostic_dest          = "/u2/dosa1/diag"
Deprecated system parameters with specified values:
  standby_archive_dest
End of deprecated system parameter listing
Sat Aug 27 16:12:57 2016
PMON started with pid=2, OS id=11400
Sat Aug 27 16:12:57 2016
VKTM started with pid=3, OS id=11402 at elevated priority

This means that, in this database standby_archive_dest is explicitely set in spfile<Oracle SID>.ora.

SQL> show parameter standby_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      /u2/dosa1/arch

The column ISDEPRECATED of V$PARAMETER shows TRUE for all deprecated parameters

      
SQL> SELECT name, type, value, isdeprecated FROM v$parameter WHERE isdeprecated = 'TRUE';

NAME                                               TYPE VALUE                                                   ISDEP
-------------------------------------------- ---------- ------------------------------------------------------- -----
lock_name_space                                       2                                                         TRUE
instance_groups                                       2                                                         TRUE
resource_manager_cpu_allocation                       3 1                                                       TRUE
active_instance_count                                 3                                                         TRUE
buffer_pool_keep                                      2                                                         TRUE
buffer_pool_recycle                                   2                                                         TRUE
log_archive_start                                     1 FALSE                                                   TRUE
standby_archive_dest                                  2 /u2/dosa1/arch                                          TRUE
log_archive_local_first                               1 TRUE                                                    TRUE
parallel_server                                       1 FALSE                                                   TRUE
parallel_server_instances                             3 1                                                       TRUE
fast_start_io_target                                  3 0                                                       TRUE
serial_reuse                                          2 disable                                                 TRUE
max_enabled_roles                                     3 150                                                     TRUE
remote_os_authent                                     1 FALSE                                                   TRUE
global_context_pool_size                              2                                                         TRUE
cursor_space_for_time                                 1 FALSE                                                   TRUE
plsql_v2_compatibility                                1 FALSE                                                   TRUE
plsql_debug                                           1 FALSE                                                   TRUE
background_dump_dest                                  2 /u2/dosa1/diag/diag/rdbms/dosa1/dosa1/trace             TRUE
user_dump_dest                                        2 /u2/dosa1/diag/diag/rdbms/dosa1/dosa1/trace             TRUE
commit_write                                          2                                                         TRUE
sql_trace                                             1 FALSE                                                   TRUE
parallel_automatic_tuning                             1 FALSE                                                   TRUE
parallel_io_cap_enabled                               1 FALSE                                                   TRUE

25 rows selected.

This does not mean that all the parameters that are shown as TRUE in V$PARAMETER is present in your database, but to show us that these are marked as deprecated by Oracle.

Before removing the parameter, understand the actual procedure to remove them.

A parameter can be removed from database by setting it to NULL (an empty string).

ALTER SYSTEM SET <parameter> = '' SCOPE = <BOTH or SPFILE>;

For further understanding of deprecated parameters and which parameters are deprecated in version, please refer Oracle documentation here

Comments