oracle rac ora-01105,Oracle undo_management参数不一致错误

骑猪看日落 2022-10-21 12:54 221阅读 0赞

环境Linux 5.8 10.2.0.5 RAC,两个节点只能一个节点mount,如果尝试mount另外节点就报ORA-01105和ORA-01606错误

数据库版本

SQL> select \* from v$version;

BANNER

\----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE    10.2.0.5.0      Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

crs资源情况

\[oracle@node1 dbs\]$ $ORA\_CRS\_HOME/bin/crs\_stat -t

Name           Type           Target    State     Host

\------------------------------------------------------------

ora....D1.inst application    OFFLINE   OFFLINE

ora....D2.inst application    ONLINE    ONLINE    node2

ora.PROD.db    application    ONLINE    ONLINE    node2

ora....SM1.asm application    ONLINE    ONLINE    node1

ora....E1.lsnr application    ONLINE    ONLINE    node1

ora.node1.gsd  application    ONLINE    ONLINE    node1

ora.node1.ons  application    ONLINE    ONLINE    node1

ora.node1.vip  application    ONLINE    ONLINE    node1

ora....SM2.asm application    ONLINE    ONLINE    node2

ora....E2.lsnr application    ONLINE    ONLINE    node2

ora.node2.gsd  application    ONLINE    ONLINE    node2

ora.node2.ons  application    ONLINE    ONLINE    node2

ora.node2.vip  application    ONLINE    ONLINE    node2

节点1 mount报错

SQL> startup

ORACLE instance started.

Total System Global Area  171966464 bytes

Fixed Size                  2094832 bytes

Variable Size             113248528 bytes

Database Buffers           50331648 bytes

Redo Buffers                6291456 bytes

ORA-01105: mount is incompatible with mounts by other instances

ORA-01606: gc\_files\_to\_locks not identical to that of another mounted instance

Error:    ORA 1105

Text:     mount is incompatible with mounts by other instances

\-------------------------------------------------------------------------------

Cause:  An attempt was made to mount the database, but another instance has already mounted

a database by the same name, and the mounts are not compatible.

dditional messages will accompany this message to report why the mounts are incompatible.

Action:  See the accompanying messages for the appropriate action to take.

Error:  ORA 1606

Text:   GC\_FILES\_TO\_LOCKS not identical to that of another mounted instance

\-------------------------------------------------------------------------------

Cause:  The initialization parameter GC\_FILES\_TO\_LOCKS is not the same as

another instance mounted in parallel mode.

This parameter must be the same as that for all shared instances.

Action: Modify the parameter to be compatible with the other instances, then

shut down and restart the instance.

根据这个错误提示，查询两个节点的gc\_files\_to\_locks参数，均为空值(默认值),也就是值相同

SQL> show parameter gc\_files\_to\_locks;

NAME                                 TYPE        VALUE

\------------------------------------ ----------- ------------------------------

gc\_files\_to\_locks                    string

检查两个节点的gc相关隐含参数,发现所有值也均一致

NAME                           DESCRIPTION                                                           VALUE

\------------------------------ --------------------------------------------------------------------- -------

\_gc\_affinity\_limit             dynamic affinity limit                                                50

\_gc\_affinity\_minimum           dynamic affinity minimum activity per minute                          6000

\_gc\_affinity\_time              if non zero, enable dynamic object affinity                           10

\_gc\_async\_memcpy               if TRUE, use async memcpy                                             FALSE

\_gc\_check\_bscn                 if TRUE, check for stale blocks                                       TRUE

\_gc\_coalesce\_recovery\_reads    if TRUE, coalesce recovery reads                                      TRUE

\_gc\_defer\_time                 how long to defer down converts for hot buffers                       3

\_gc\_dissolve\_undo\_affinity     if TRUE, dissolve undo affinity after an offline                      FALSE

\_gc\_dynamic\_affinity\_locks     if TRUE, get dynamic affinity locks                                   TRUE

\_gc\_element\_percent            global cache element percent                                          103

\_gc\_global\_lru                 turn global lru off, make it automatic, or turn it on                 AUTO

\_gc\_initiate\_undo\_affinity     if TRUE, initiate undo affinity after an online                       TRUE

\_gc\_integrity\_checks           set the integrity check level                                         1

\_gc\_keep\_recovery\_buffers      if TRUE, make recovery buffers current                                TRUE

\_gc\_latches                    number of latches per LMS process                                     8

\_gc\_maximum\_bids               maximum number of bids which can be prepared                          0

\_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 process   64

\_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\_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\_tsn\_undo\_affinity          if TRUE, use TSN undo affinity                                        TRUE

\_gc\_undo\_affinity              if TRUE, enable dynamic undo affinity                                 TRUE

\_gc\_undo\_affinity\_locks        if TRUE, get affinity locks for undo                                  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

仔细对比数据库参数，发现undo异常

\--节点1

SQL>  show parameter undo

NAME                                 TYPE        VALUE

\------------------------------------ ----------- ------------------------------

undo\_management                      string      MANUAL

undo\_retention                       integer     900

undo\_tablespace                      string      SYSTEM

\--节点2

SQL>  show parameter undo

NAME                                 TYPE        VALUE

\------------------------------------ ----------- ------------------------------

undo\_management                      string      AUTO

undo\_retention                       integer     900

undo\_tablespace                      string      UNDOTBS1

这里已经明确，因为两个节点的undo\_\*相关参数配置不正确，导致数据库只能一个节点mount。进一步定位问题发现，原来是因为dba粗心在编辑节点1的参数文件的时候把undo\_\*相关的参数给弄丢了,从而数据库使用了默认值undo\_management=manual,undo\_tablespace=system