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
