Friday, May 09, 2014

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.


No comments: