怎么解决Oracle中的ORA-01105、ORA-01606错误-创新互联
这篇文章主要讲解了“怎么解决Oracle中的ORA-01105、ORA-01606错误”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“怎么解决Oracle中的ORA-01105、ORA-01606错误”吧!
创新互联公司专业为企业提供洪雅网站建设、洪雅做网站、洪雅网站设计、洪雅网站制作等企业网站建设、网页设计与制作、洪雅企业网站模板建站服务,十年洪雅做网站经验,不只是建网站,更提供有价值的思路和整体网络服务。生产环境,Oracle Linux 6.4 RAC 11.2.4.0,今天出现了grid集群因为OCR磁盘组不能访问而不能通过命令来显示集群状态信息,在手动mount OCR磁盘组后,准备重启节点2时出现了ORA-01105,ORA-01606错误,具体信息如下:
SQL> startup ORACLE instance started. Total System Global Area 5.3447E+10 bytes Fixed Size 2265864 bytes Variable Size 1.3019E+10 bytes Database Buffers 4.0265E+10 bytes Redo Buffers 160698368 bytes ORA-01105: mount is incompatible with mounts by other instances ORA-01606: parameter not identical to that of another mounted instance
查看错误详细说明,根据错误描述可知是由于2号实例与1号实例由于某些参数设置一样所导致的
[oracle@db2 dbs]$ oerr ora 1105 01105, 00000, "mount is incompatible with mounts by other instances" // *Cause: An attempt to mount the database discovered that another instance // mounted a database by the same name, but the mount is not // compatible. Additional errors are reported explaining why. // *Action: See accompanying errors. [oracle@db2 dbs]$ oerr ora 1606 01606, 00000, "parameter not identical to that of another mounted instance" // *Cause: A parameter was different on two instances. // *Action: Modify the initialization parameter and restart.
使用spfile文件来创建文本格式的pfile文件
SQL> create pfile='rlcs.ora' from spfile; File created. [oracle@db2 dbs]$ cat rlcs.ora *._serial_direct_read='AUTO' *._swrf_mmon_flush=TRUE *._use_adaptive_log_file_sync='FALSE' *.audit_file_dest='/u01/app/oracle/admin/RL/adump' *.audit_trail='NONE' *.cluster_database=true *.compatible='11.2.0.4.0' *.control_files='+DATA/rl/controlfile/current.260.926786537','+ARCH/rl/controlfile/current.256.926786537' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='+data/rl/','+data/rldg/' *.db_name='RL' *.db_recovery_file_dest='+ARCH' *.db_recovery_file_dest_size=10737418240 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=RLZYXDB)' *.fal_server='yb_st' RL1.instance_number=1 RL2.instance_number=2 *.listener_networks='' *.log_archive_config='dg_config=(rl,rldg)' *.log_archive_dest_1='location=+ARCH valid_for=(all_logfiles,all_roles) db_unique_name=rl' *.log_archive_dest_2='service=yb_st valid_for=(online_logfiles,primary_role) db_unique_name=rldg' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.arch' *.log_file_name_convert='+data/rl/','+data/rldg/','+arch/rl/','+arch/rldg/' *.open_cursors=300 *.parallel_adaptive_multi_user=TRUE *.parallel_degree_policy='AUTO' *.parallel_force_local=FALSE *.pga_aggregate_target=21474836480 *.processes=2000 *.remote_listener='scan-ip:1521' *.remote_login_passwordfile='exclusive' *.service_names='rl' *.sessions=2205 *.sga_max_size=53687091200 *.sga_target=53687091200 *.standby_file_management='manual' RLZY2.thread=2 RLZY1.thread=1 *.undo_retention=7200 RLZY2.undo_tablespace='UNDOTBS2' RLZY1.undo_tablespace='UNDOTBS1' *.utl_file_dir='/rmanbak/utl'
从文本参数文件看不出来有什么参数是两个实例不一致的。 通过执行下面的语句来查看2号实例与gc相关的参数
SQL> set linesize 333 SQL> col name for a35 SQL> col description for a66 SQL> col value for a30 SQL> SELECT i.ksppinm name, 2 i.ksppdesc description, 3 CV.ksppstvl VALUE 4 FROM sys.x$ksppi i, sys.x$ksppcv CV 5 WHERE i.inst_id = USERENV ('Instance') 6 AND CV.inst_id = USERENV ('Instance') 7 AND i.indx = CV.indx 8 AND i.ksppinm LIKE '/_gc%' ESCAPE '/' 9 ORDER BY REPLACE (i.ksppinm, '_', ''); NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gc_affinity_locking if TRUE, enable object affinity TRUE _gc_affinity_locks if TRUE, get affinity locks TRUE _gc_affinity_ratio dynamic object affinity ratio 50 _gc_async_memcpy if TRUE, use async memcpy FALSE _gc_bypass_readers if TRUE, modifications bypass readers TRUE _gc_check_bscn if TRUE, check for stale blocks TRUE _gc_coalesce_recovery_reads if TRUE, coalesce recovery reads TRUE _gc_cpu_time if TRUE, record the gc cpu time FALSE _gc_cr_server_read_wait if TRUE, cr server waits for a read to complete TRUE _gc_defer_ping_index_only if TRUE, restrict deferred ping to index blocks only TRUE _gc_defer_time how long to defer pings for hot buffers in milliseconds 0 NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gc_delta_push_compression if delta >= K bytes, compress before push 3072 _gc_delta_push_max_level max delta level for delta push 100 _gc_delta_push_objects objects which use delta push 0 _gc_disable_s_lock_brr_ping_check if TRUE, disable S lock BRR ping check for lost write protect TRUE _gc_down_convert_after_keep if TRUE, down-convert lock after recovery TRUE _gc_element_percent global cache element percent 110 _gc_escalate_bid if TRUE, escalates create a bid TRUE _gc_fg_merge if TRUE, merge pi buffers in the foreground TRUE _gc_flush_during_affinity if TRUE, flush during affinity TRUE _gc_fusion_compression compress fusion blocks if there is free space 1024 _gc_global_checkpoint_scn if TRUE, enable global checkpoint scn TRUE NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gc_global_cpu global cpu checks TRUE _gc_global_lru turn global lru off, make it automatic, or turn it on AUTO _gc_global_lru_touch_count global lru touch count 5 _gc_global_lru_touch_time global lru touch time in seconds 60 _gc_integrity_checks set the integrity check level 1 _gc_keep_recovery_buffers if TRUE, make single instance crash recovery buffers current TRUE _gc_latches number of latches per LMS process 8 _gc_log_flush if TRUE, flush redo log before a current block transfer TRUE _gc_long_query_threshold threshold for long running query 0 _gc_max_downcvt maximum downconverts to process at one time 256 _gc_maximum_bids maximum number of bids which can be prepared 0 NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gc_no_fairness_for_clones if TRUE, no fairness if we serve a clone TRUE _gc_object_queue_max_length maximum length for an object queue 0 _gc_override_force_cr if TRUE, try to override force-cr requests TRUE _gc_persistent_read_mostly if TRUE, enable persistent read-mostly locking TRUE _gc_policy_minimum dynamic object policy minimum activity per minute 1500 _gc_policy_time how often to make object policy decisions in minutes 10 _gc_read_mostly_flush_check if TRUE, optimize flushes for read mostly objects FALSE _gc_read_mostly_locking if TRUE, enable read-mostly locking FALSE _gcr_enable_high_cpu_kill if TRUE, GCR may kill foregrounds under high load FALSE _gcr_enable_high_cpu_rm if TRUE, GCR may enable a RM plan under high load FALSE _gcr_enable_high_cpu_rt if TRUE, GCR may boost bg priority under high load FALSE NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gcr_high_cpu_threshold minimum amount of CPU process must consume to be kill target 10 _gcr_use_css if FALSE, GCR wont register with CSS nor use any CSS feature TRUE _gc_sanity_check_cr_buffers if TRUE, sanity check CR buffers FALSE _gcs_disable_remote_handles disable remote client/shadow handles FALSE _gcs_disable_skip_close_remastering if TRUE, disable skip close optimization in remastering FALSE _gc_serve_high_pi_as_current if TRUE, use a higher clone scn when serving a pi TRUE _gcs_fast_reconfig if TRUE, enable fast reconfiguration for gcs locks TRUE _gcs_latches number of gcs resource hash latches to be allocated per LMS proces 64 s _gcs_pkey_history number of pkey remastering history 4000 NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gcs_process_in_recovery if TRUE, process gcs requests during instance recovery TRUE _gcs_resources number of gcs resources to be allocated _gcs_res_per_bucket number of gcs resource per hash bucket 4 _gcs_shadow_locks number of pcm shadow locks to be allocated _gc_statistics if TRUE, kcl statistics are maintained TRUE _gcs_testing GCS testing parameter 0 _gc_transfer_ratio dynamic object read-mostly transfer ratio 2 _gc_undo_affinity if TRUE, enable dynamic undo affinity TRUE _gc_undo_block_disk_reads if TRUE, enable undo block disk reads TRUE _gc_use_cr if TRUE, allow CR pins on PI and WRITING buffers TRUE _gc_vector_read if TRUE, vector read current buffers TRUE 64 rows selected.
查看1号实例与gc相关的参数
SQL> set linesize 333 SQL> col name for a35 SQL> col description for a66 SQL> col value for a30 SQL> SELECT i.ksppinm name, 2 i.ksppdesc description, 3 CV.ksppstvl VALUE 4 FROM sys.x$ksppi i, sys.x$ksppcv CV 5 WHERE i.inst_id = USERENV ('Instance') 6 AND CV.inst_id = USERENV ('Instance') 7 AND i.indx = CV.indx 8 AND i.ksppinm LIKE '/_gc%' ESCAPE '/' 9 ORDER BY REPLACE (i.ksppinm, '_', ''); NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gc_affinity_locking if TRUE, enable object affinity TRUE _gc_affinity_locks if TRUE, get affinity locks TRUE _gc_affinity_ratio dynamic object affinity ratio 50 _gc_async_memcpy if TRUE, use async memcpy FALSE _gc_bypass_readers if TRUE, modifications bypass readers TRUE _gc_check_bscn if TRUE, check for stale blocks TRUE _gc_coalesce_recovery_reads if TRUE, coalesce recovery reads TRUE _gc_cpu_time if TRUE, record the gc cpu time FALSE _gc_cr_server_read_wait if TRUE, cr server waits for a read to complete TRUE _gc_defer_ping_index_only if TRUE, restrict deferred ping to index blocks only TRUE _gc_defer_time how long to defer pings for hot buffers in milliseconds 0 NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gc_delta_push_compression if delta >= K bytes, compress before push 3072 _gc_delta_push_max_level max delta level for delta push 100 _gc_delta_push_objects objects which use delta push 0 _gc_disable_s_lock_brr_ping_check if TRUE, disable S lock BRR ping check for lost write protect TRUE _gc_down_convert_after_keep if TRUE, down-convert lock after recovery TRUE _gc_element_percent global cache element percent 110 _gc_escalate_bid if TRUE, escalates create a bid TRUE _gc_fg_merge if TRUE, merge pi buffers in the foreground TRUE _gc_flush_during_affinity if TRUE, flush during affinity TRUE _gc_fusion_compression compress fusion blocks if there is free space 1024 _gc_global_checkpoint_scn if TRUE, enable global checkpoint scn TRUE NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gc_global_cpu global cpu checks TRUE _gc_global_lru turn global lru off, make it automatic, or turn it on AUTO _gc_global_lru_touch_count global lru touch count 5 _gc_global_lru_touch_time global lru touch time in seconds 60 _gc_integrity_checks set the integrity check level 1 _gc_keep_recovery_buffers if TRUE, make single instance crash recovery buffers current TRUE _gc_latches number of latches per LMS process 8 _gc_log_flush if TRUE, flush redo log before a current block transfer TRUE _gc_long_query_threshold threshold for long running query 0 _gc_max_downcvt maximum downconverts to process at one time 256 _gc_maximum_bids maximum number of bids which can be prepared 0 NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gc_no_fairness_for_clones if TRUE, no fairness if we serve a clone TRUE _gc_object_queue_max_length maximum length for an object queue 0 _gc_override_force_cr if TRUE, try to override force-cr requests TRUE _gc_persistent_read_mostly if TRUE, enable persistent read-mostly locking TRUE _gc_policy_minimum dynamic object policy minimum activity per minute 1500 _gc_policy_time how often to make object policy decisions in minutes 10 _gc_read_mostly_flush_check if TRUE, optimize flushes for read mostly objects FALSE _gc_read_mostly_locking if TRUE, enable read-mostly locking TRUE _gcr_enable_high_cpu_kill if TRUE, GCR may kill foregrounds under high load FALSE _gcr_enable_high_cpu_rm if TRUE, GCR may enable a RM plan under high load FALSE _gcr_enable_high_cpu_rt if TRUE, GCR may boost bg priority under high load FALSE NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gcr_high_cpu_threshold minimum amount of CPU process must consume to be kill target 10 _gcr_use_css if FALSE, GCR wont register with CSS nor use any CSS feature TRUE _gc_sanity_check_cr_buffers if TRUE, sanity check CR buffers FALSE _gcs_disable_remote_handles disable remote client/shadow handles FALSE _gcs_disable_skip_close_remastering if TRUE, disable skip close optimization in remastering FALSE _gc_serve_high_pi_as_current if TRUE, use a higher clone scn when serving a pi TRUE _gcs_fast_reconfig if TRUE, enable fast reconfiguration for gcs locks TRUE _gcs_latches number of gcs resource hash latches to be allocated per LMS proces 64 s _gcs_pkey_history number of pkey remastering history 4000 NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _gcs_process_in_recovery if TRUE, process gcs requests during instance recovery TRUE _gcs_resources number of gcs resources to be allocated _gcs_res_per_bucket number of gcs resource per hash bucket 4 _gcs_shadow_locks number of pcm shadow locks to be allocated _gc_statistics if TRUE, kcl statistics are maintained TRUE _gcs_testing GCS testing parameter 0 _gc_transfer_ratio dynamic object read-mostly transfer ratio 2 _gc_undo_affinity if TRUE, enable dynamic undo affinity TRUE _gc_undo_block_disk_reads if TRUE, enable undo block disk reads TRUE _gc_use_cr if TRUE, allow CR pins on PI and WRITING buffers TRUE _gc_vector_read if TRUE, vector read current buffers TRUE 64 rows selected.
通过对比发现_gc_read_mostly_locking参数在1号实例中为true,2号实例为false
将所有实例中的_gc_read_mostly_locking参数设置为true
SQL> alter system set "_gc_read_mostly_locking"=true scope=spfile sid='*'; System altered.
重启2号实例成功
SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 5.3447E+10 bytes Fixed Size 2265864 bytes Variable Size 1.3019E+10 bytes Database Buffers 4.0265E+10 bytes Redo Buffers 160698368 bytes Database mounted. Database opened.
原因是因为之前有人修改过_gc_read_mostly_locking隐藏参数,只是只修改了1号实例。
感谢各位的阅读,以上就是“怎么解决Oracle中的ORA-01105、ORA-01606错误”的内容了,经过本文的学习后,相信大家对怎么解决Oracle中的ORA-01105、ORA-01606错误这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是创新互联,小编将为大家推送更多相关知识点的文章,欢迎关注!
标题名称:怎么解决Oracle中的ORA-01105、ORA-01606错误-创新互联
文章链接:http://lswzjz.com/article/dshseg.html