Oracle 11G RAC One Node's Instance Arise 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

_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

_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

_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

_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

_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相关的参数

_gc_read_mostly_locking if TRUE, enable read-mostly locking TRUE

s
           

通过对比发现_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.

Database mounted.

Database opened.

原因是因为之前有人修改过_gc_read_mostly_locking隐藏参数，只是只修改了1号实例。