RMAN Copy Adds Hidden Parameters

Some testers had a problem with an application running against an Oracle 11.2.0.1.0 database. It kept failing with ORA-03114 and/or ORA-24909 messages. The only clues were that production was copied into test around 6 weeks ago, production was OK but test was not. I looked the errors up on the Internet, saw a suggestion that I should compare initialization parameters in both databases and decided to give this a try. I noticed that the test database had some hidden parameters and decided to make a list of them. I found that the following SQL (which I ran again retrospectively after the problem had been fixed) did not work as the underscore was treated as a wild card and all the parameters were listed:

 

SQL> select name, value

  2  from v$parameter

  3  where name like '_%'

  4  order by 1

  5  /

 

NAME                                VALUE

----------------------------------- ---------------

O7_DICTIONARY_ACCESSIBILITY         FALSE

active_instance_count

aq_tm_processes                     0

archive_lag_target                  0

asm_diskgroups

asm_diskstring

asm_power_limit                     1

Etc

Etc

 

So I did it like this instead and found that the database had 124 hidden parameters:

 

SQL> select name, value

  2  from v$parameter

  3  where substr(name,1,1) = '_'

  4  order by 1

  5  /

 

NAME                                VALUE

----------------------------------- ---------------

_aggregation_optimization_settings  0

_always_anti_join                   CHOOSE

_always_semi_join                   CHOOSE

Etc

Etc

_union_rewrite_for_gs               YES_GSET_MVS

_unnest_subquery                    TRUE

_use_column_stats_for_function      TRUE

 

124 rows selected.

 

SQL>

 

There were no hidden parameters in the production database at all:

 

SQL> select name, value

  2  from v$parameter

  3  where substr(name,1,1) = '_'

  4  order by 1

  5  /

 

no rows selected

 

SQL>

 

Looking through the alert log, I found that there were no hidden parameters when the database was opened here:

 

Mon Mar 17 15:54:00 2014

Starting ORACLE instance (normal)

Etc

Etc

System parameters with non-default values:

  processes                = 150

  nls_language             = "ENGLISH"

  nls_territory            = "UNITED KINGDOM"

  memory_target            = 500M

  control_files            = "/database/NLGENUT1/gen_redo1/control01.ctl"

  control_files            = "/database/NLGENUT1/gen_redo2/control02.ctl"

  control_files            = "/database/NLGENUT1/gen_system/control03.ctl"

  db_file_name_convert     = "/agasprd/nlgenprd"

  db_file_name_convert     = "/database/NLGENUT1"

  db_recovery_file_dest    = "/agasprd/flash_recovery_area"

  db_recovery_file_dest_size= 32G

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  db_name                  = "NLGENUT1"

  open_cursors             = 300

  os_authent_prefix        = ""

Mon Mar 17 15:54:02 2014

 

… but they all appeared when the database was opened less than an hour later:

 

Mon Mar 17 16:47:29 2014

Starting ORACLE instance (normal)

Etc

Etc

System parameters with non-default values:

  processes                = 150

  _ksb_restart_policy_times= "0"

  _ksb_restart_policy_times= "60"

  _ksb_restart_policy_times= "120"

  _ksb_restart_policy_times= "240"

Etc

Etc

  _optimizer_mode_force    = TRUE

  _always_anti_join        = "CHOOSE"

  _optimizer_null_aware_antijoin= TRUE

  _partition_view_enabled  = TRUE

  _b_tree_bitmap_plans     = TRUE

  _cpu_to_io               = 0

  _optimizer_extended_cursor_sharing= "UDO"

  _optimizer_extended_cursor_sharing_rel= "SIMPLE"

  _optimizer_adaptive_cursor_sharing= TRUE

  _optimizer_cost_model    = "CHOOSE"

  _optimizer_undo_cost_change= "11.2.0.1"

  _optimizer_system_stats_usage= TRUE

  _new_sort_cost_estimate  = TRUE

  _complex_view_merging    = TRUE

  _unnest_subquery         = TRUE

Etc

Etc

 

I looked back to see what I was doing at that time and saw that I was using RMAN to copy production into test. Then I looked at Metalink and decided that bug 9752597 / base bug 10061364 had probably caused my problem (as usual, click on the image to enlarge it and bring it into focus):

I removed the hidden parameters from the test database and passed it back to the testers.

Hidden Parameters

Oracle has several hidden initialisation parameters. These have names which begin with an underscore. You can see them using the following query, which I ran on an Oracle 9 database:

 

SQL> l

  1  select ksppinm

  2  from   SYS.X$KSPPI

  3* where  substr(KSPPINM,1,1) = '_'

SQL> /

 

 

KSPPINM

-------------------------------------------------------

_trace_files_public

_latch_recovery_alignment

_spin_count

_latch_miss_stat_sid

_max_sleep_holding_latch

_max_exponential_sleep

_use_vector_post

_latch_class_0

_latch_class_1

_latch_class_2

_latch_class_3

 

Etc.

 

KSPPINM

-------------------------------------------------------

_xsolapi_sql_use_bind_variables

_xsolapi_sql_prepare_stmt_cache_size

_xsolapi_sql_result_set_cache_size

_xsolapi_debug_output

_xsolapi_cursor_use_row_cache

_xsolapi_cursor_max_rows_to_cache_per_req

_xsolapi_cursor_max_time_for_partial_cache

_xsolapi_source_trace

 

613 rows selected.

 

SQL>

 

What does it mean when we say they are hidden?  We can see the answer by comparison with an ordinary parameter e.g. sql_trace:

 

SQL> l

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = 'sql_trace'

SQL> /

 

NAME       VALUE      ISDEFAULT

---------- ---------- ---------

sql_trace  FALSE      TRUE

 

SQL>

 

In this case, sql_trace is set to its default value of FALSE but it still has an entry in V$PARAMETER. A hidden parameter, on the other hand, does not have an entry in V$PARAMETER:

 

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = '_trace_files_public'

SQL> /

 

no rows selected

 

SQL>

 

... unless it has been set on purpose. The query below was run on a different Oracle 9 database, which had _trace_files_public set in its parameter file:

 

SQL> l

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = '_trace_files_public'

SQL> /

 

NAME                 VALUE      ISDEFAULT

-------------------- ---------- ---------

_trace_files_public  TRUE       FALSE

 

SQL>

 

Documentation for hidden parameters is not freely available. Oracle will tell you when to implement them, usually when they are replying to a service request. Advice from Oracle to implement a hidden parameter relates to a given database running on a specific Oracle version. I went to an 11g release 2 seminar run by Oracle recently. They discussed upgrade policy there and said that hidden parameters should be removed before an upgrade.