首页 » ORACLE 9i-23ai » Troubleshooting Oracle RAC Second instance start fail with ORA-01105 ORA-01677

Troubleshooting Oracle RAC Second instance start fail with ORA-01105 ORA-01677

  • 2023/12/10
  • ORACLE 9i-23ai
  • 236 views
  • Troubleshooting Oracle RAC Second instance start fail with ORA-01105 ORA-01677已关闭评论

一套测试环境因同事做过一些操作今天发现其中一个节点是crash，启动数据库第二个实例失败，提示ORA-01105: mount is incompatible with mounts by other instances， 我们都知道可能是两个节点间参数不一致. 简单记录一下处理，如果你遇到给希望可以参考减少诊断时间。

SQL> startup
ORACLE instance started.

Total System Global Area 8589932688 bytes
Fixed Size                 12695696 bytes
Variable Size            7482638336 bytes
Database Buffers         1073741824 bytes
Redo Buffers               20856832 bytes
ORA-01105: mount is incompatible with mounts by other instances
ORA-01677: standby file name conversion parameters differ from other instance

db alert log

SUCCESS: mounted group 2 (OCRDATA)
NOTE: grp 2 disk 0: OCRDATA_0000 path:/dev/sdb
2023-12-10T23:02:48.772476+08:00
NOTE: Under CF enqueue, no dependency request for disk group OCRDATA
2023-12-10T23:02:49.312511+08:00
Ping without log force is disabled:
  not an Exadata system.
2023-12-10T23:02:52.090839+08:00
Picked broadcast on commit scheme to generate SCNs
Endian type of dictionary set to little
2023-12-10T23:03:16.211511+08:00
LGWR (PID:13069): WARN: The 'LOG_ARCHIVE_CONFIG' init.ora parameter settings are
LGWR (PID:13069): inconsistent with another open instance.  This occurs if the DG_CONFIG
LGWR (PID:13069): settings on this instance are different than the DG_CONFIG settings
LGWR (PID:13069): on other open instances or if the DB_UNIQUE_NAME parameter for this
LGWR (PID:13069): instance is set differently than on other open instances.
2023-12-10T23:03:16.585158+08:00
Errors in file /u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_lgwr_13069.trc:
ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
2023-12-10T23:03:16.587134+08:00
Errors in file /u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_25308.trc:
ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
2023-12-10T23:03:16.592293+08:00
ORA-1092 : opitsk aborting process
2023-12-10T23:03:16.636964+08:00


# node1
SQL> show parameter config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.3.0
                                                 .0/dbhome_1/dbs/dr1anbob.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.3.0
                                                 .0/dbhome_1/dbs/dr2anbob.dat
log_archive_config                   string      DG_CONFIG=(anbob,anbobdg)
tde_configuration                    string

# node2
SQL> show parameter config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.3.0
                                                 .0/dbhome_1/dbs/dr1anbob.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.3.0
                                                 .0/dbhome_1/dbs/dr2anbob.dat
log_archive_config                   string
tde_configuration                    string

在之前<移除DataGuard Standby配置导致Primary启动失败>记录过LOG_ARCHIVE_CONFIG 参数不一致导致的问题，如果内存与spfile不一致也可能重启后参数生效导致问题。 解决方法log_archive_config配置为一致。

对比内存与参数文件不一致
在我的odbhc巡检脚本中有对于该项的检查.

prompt The parameters in memory and spfile difference Check
select * from (
select mem.inst_id, mem.name, mem.value, 'from mem' VALUE_FROM
  from (select inst_id, name, upper(display_value) value
          from gv$system_parameter2
         where isdefault = 'FALSE'
        minus
        select inst_id, name, upper(display_value) value
          from gv$spparameter
         where isspecified = 'TRUE') mem
union
select spf.inst_id, spf.name, spf.value, 'from spf'
  from (select inst_id, name, upper(display_value) value
          from gv$spparameter
         where isspecified = 'TRUE'
        minus
        select inst_id, name, upper(display_value) value
          from gv$system_parameter2
         where isdefault = 'FALSE') spf
		 )
where name not in('instance_number','thread','undo_tablespace','sessions')
order by 1, 2;

我这里把幸存节点重启正常，在启动第二个项目时依旧失败.

SQL> startup
ORACLE instance started.

Total System Global Area 8589932688 bytes
Fixed Size                 12695696 bytes
Variable Size            7482638336 bytes
Database Buffers         1073741824 bytes
Redo Buffers               20856832 bytes
ORA-01105: mount is incompatible with mounts by other instances
ORA-01677: standby file name conversion parameters differ from other instance

db alert log

NOTE: ASMB mounting group 1 (DATA01)
NOTE: Assigned CGID 0x1000e for group 1
NOTE: ASMB process initiating disk discovery for grp 1 (reqid:0)
2023-12-10T23:30:10.867875+08:00
NOTE: Assigning number (1,0) to disk (/dev/sdc)
SUCCESS: mounted group 1 (DATA01)
NOTE: grp 1 disk 0: DATA01_0000 path:/dev/sdc
2023-12-10T23:30:14.359861+08:00
ERROR: failed to establish dependency between database anbob and diskgroup resource ora.DATA01.dg
2023-12-10T23:30:16.512341+08:00
NOTE: delay file unidentification for gn=1 fn=262 cnt=1
2023-12-10T23:30:16.546398+08:00
ORA-1105 signalled during: ALTER DATABASE   MOUNT...
2023-12-10T23:30:16.570752+08:00
NOTE: completing the delayed file unidentification for gn=1 fn=262
2023-12-10T23:30:16.697227+08:00
Errors in file /u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_26641.trc:
ORA-00202: control file: ''
ORA-15079: ASM file is closed
2023-12-10T23:30:22.919103+08:00
Errors in file /u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_26553.trc:
2023-12-10T23:30:25.075346+08:00
Errors in file /u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_26553.trc:
2023-12-10T23:30:25.892758+08:00
Errors in file /u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_26553.trc:

trace 文件

oracle@19c1:/home/oracle $ vi /u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_26641.trc
Instance name: anbob1
Redo thread mounted by this instance: 0 
Oracle process number: 78
Unix process pid: 26641, image: oracle@19c1 (TNS V1-V3)


*** 2023-12-10T23:30:16.570508+08:00 (CDB$ROOT(1))
*** SESSION ID:(77.36188) 2023-12-10T23:30:16.570560+08:00
*** CLIENT ID:() 2023-12-10T23:30:16.570566+08:00
*** SERVICE NAME:() 2023-12-10T23:30:16.570571+08:00
*** MODULE NAME:(sqlplus@19c1 (TNS V1-V3)) 2023-12-10T23:30:16.570576+08:00
*** ACTION NAME:() 2023-12-10T23:30:16.570581+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2023-12-10T23:30:16.570585+08:00
*** CONTAINER ID:(1) 2023-12-10T23:30:16.570590+08:00

NOTE: completing the delayed file unidentification for gn=1 fn=262
DDE rules only execution for: ORA 202

*** 2023-12-10T23:30:16.690544+08:00 (CDB$ROOT(1))
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
ORA-00202: control file: ''
ORA-15079: ASM file is closed
----- Current SQL Statement for this session (sql_id=6jaghrm3vy74f) -----
select database_role from v$database

----- Call Stack Trace -----

Note：
错误日志中对参数提示并不明显， 第一步其实我们就该确认所有使用使用的是否是相同的pfile.

对比pfile

# node1
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.3.0
                                                 .0/dbhome_1/dbs/spfileanbob1.ora

# node2
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA01/spfile.ora

Note:
节点间使用了不同的pfile

修复pfile

oracle@19c1:/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs $ mv spfileanbob1.ora spfileanbob3.ora_bak
oracle@19c1:/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs $ vi initanbob1.ora
spfile='+DATA01/spfile.ora'

startup force

Note：

启动正常

对比参数不同

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

oracle@19c1:/u01/app/oracle/admin/anbob $ diff /home/oracle/pfile.ora /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/initanbob3.ora
67d66
< *.db_file_name_convert='+DATA/anbobdg','+DATA01/anbob'
73d71
< *.ddl_lock_timeout=100000
76c74
< *.dispatchers='' --- > *.dispatchers='(PROTOCOL=TCP) (SERVICE=anbobXDB)'
80,81d77
< *.fal_client='anbob'
< *.fal_server='anbobdg'
90d85
< *.log_archive_config=''
92,95d86
< *.log_archive_dest_2='SERVICE=anbob_adg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=anbobdg'
< *.log_archive_dest_state_1='enable'
< *.log_archive_dest_state_2='enable'
< *.log_file_name_convert='+DATA/anbobdg','+DATA01/anbob'

Note:
log_file_name_convert和db_file_name_convert参数不同

总结：
RAC重启后第二实例启动在OPEN阶段失败提示ORA-01105, 首先检查是否使用相同的spfile?其次检查幸存节点内存与pfile是否一致?如果使用独立的（ seperate）spfile,  对于参数log_archive_config、log_file_name_convert、db_file_name_convert必须保持一致。分别对应ORA-16188和

打赏

, , , ,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：