How to remove all hidden parameters from Oracle Database

Question

How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended?

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\'

Here are parameters:

_fast_full_scan_enabled
_gby_hash_aggregation_enabled
_like_with_bind_as_equality
_optimizer_push_pred_cost_based
_sort_elimination_cost_ratio
_sqlexec_progression_cost
_trace_files_public
_b_tree_bitmap_plans

Regards,

Answer 1

Create a pfile from the spfile:

CREATE PFILE FROM SPFILE;

Edit the generated pfile and remove the parameters, then recreate the spfile from the edited pfile. Bounce the database & all should be well.

The database might need to be down when you recreate the spfile from the pfile.

Answer 2

You'd need to add IS_SPECIFIED (or ISSPECIFIED) to the where clause, as some hidden parameters may be set by... other things.

A parameter can be removed from an spfile by issuing:

ALTER SYSTEM RESET "_some_hidden_parameter" scope = spfile;

You will likely have to stop and start the instance(s) to have the changes take effect.

Answer 3

Please note when RAC you need to define scope=both

IE:

Check if there are any hidden "_" parameters.

SELECT name,description
FROM SYS.V$PARAMETER
WHERE name LIKE '\_%' ESCAPE '\';

Remove the hidden parameter, In my case it is "_rac_dbtype_reset"

alter system reset "_rac_dbtype_reset" scope=both;

Check again

SELECT name,description
FROM SYS.V$PARAMETER
WHERE name LIKE '\_%' ESCAPE '\';

Then STOP and START the DB on ALL NODE in the RAC

shutdown immediate;

startup;