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:
Post a Comment