RAC spfile 坑 +data INSTANCE_NUMBER thread x is mounted by another instance

RAC相关三个参数

thread reset 就可以默认

instance_number 需要单独设置 sid=‘SIDX’

cluster_database boolean TRUE

SQL> alter system reset instance_number sid='*' scope=spfile;
alter system reset instance_number sid='*' scope=spfile
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

alter system set instance_number=3 sid='*' scope=spfile; 只要设错了才能改。

alter system reset thread scope=spfile;

----------ORA-29760---重现----

SQL> alter system reset instance_number sid='sid1' scope=spfile;
ORA-32010: cannot find entry to delete in SPFILE

SQL> alter system reset instance_number sid='cdb1' scope=spfile;

SQL> alter system reset instance_number sid='cdb2' scope=spfile;

SQL> alter system reset instance_number sid='cdb3' scope=spfile;

SQL> show spparameters instance_n

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* instance_name string
* instance_number integer
SQL> show parameters instance_n

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string cdb1
instance_number integer 1
SQL> exit

[oracle@rac1 ~]$ srvctl start database -d cdb
PRCR-1079 : Failed to start resource ora.cdb.db
CRS-5017: The resource action "ora.cdb.db start" encountered the following error:
ORA-29760: instance_number parameter not specified
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.cdb.db' on 'rac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.cdb.db' on that would satisfy its placement policy
CRS-5017: The resource action "ora.cdb.db start" encountered the following error:
ORA-29760: instance_number parameter not specified
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.cdb.db' on 'rac2' failed

SQL> create pfile='/tmp/pfile' from spfile;----未启动

ERROR at line 1:
ORA-01565: error in identifying file '?=/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

[oracle@rac1 ~]$ srvctl config database -d cdb |grep spfile
Spfile: +DATA/CDB/PARAMETERFILE/spfile.294.1173595505

SQL> create pfile='/tmp/pfile' from spfile='+DATA/CDB/PARAMETERFILE/spfile.294.1173595505';

[oracle@rac1 ~]$ vi /tmp/pfile---添加最后三行
[oracle@rac1 ~]$ tail -3 /tmp/pfile
cdb1.instance_number=1
cdb2.instance_number=2
cdb3.instance_number=3
[oracle@rac1 ~]$ s

SQL> create spfile='data' from pfile='/tmp/pfile';

File created.

SQL> exit
Disconnected
[oracle@rac1 ~]$ srvctl start database -d cdb

CRS-2674: Start of 'ora.cdb.db' on 'rac1' failed

[oracle@rac1 ~]$ s

SQL> startup
ORA-29760: instance_number parameter not specified
SQL> show parameters spfile;
ORA-01034: ORACLE not available

SQL> show parameter
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SQL> startup pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 608170680 bytes
Fixed Size 9167544 bytes
Variable Size 557842432 bytes
Database Buffers 33554432 bytes
Redo Buffers 7606272 bytes
Database mounted.
Database opened.
SQL> show parameters instance_number

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_number integer 1
SQL> create spfile='data' from pfile='/tmp/pfile'; RAC所有节点会更新，都要找这个~data

File created.

SQL> startup force;
ORACLE instance started.

Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
[oracle@rac1 ~]$ srvctl stop database -d cdb
[oracle@rac1 ~]$ srvctl start database -d cdb
PRCR-1079 : Failed to start resource ora.cdb.db
CRS-5017: The resource action "ora.cdb.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0/db_1/dbs/initcdb2.ora'
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.cdb.db' on 'rac2' failed
CRS-2632: There are no more servers to try to place resource 'ora.cdb.db' on that would satisfy its placement policy

[oracle@rac2 ~]$ s-- -'data' 错误找不到只能用pfile！！！

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0/db_1/dbs/initcdb2.ora'

[oracle@rac1 ~]$ s

SQL> show parameters spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/db_1/dbs/data
SQL> create spfile='+data' from pfile='/tmp/pfile';

File created.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
[oracle@rac1 ~]$ srvctl stop database -d cdb
[oracle@rac1 ~]$ srvctl start database -d cdb
[oracle@rac1 ~]$

-------------------

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

SYMPTOMS

After OS patching, the DB instance failed to come up with the below error on one node.

PRCR-1079 : Failed to start resource ora.<database>.db
CRS-5017: The resource action "ora.<database>.db start" encountered the following error:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory
Additional information: 2582
Additional information: 890000679
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/grid/app/diag/crs/<hostname>/crs/trace/crsd_oraagent_<instance>.trc".

From crsd_oraagent_oracle.trc:

: USRTHRD:11: CLSN.AQPROC.EMRMAXD.MASTER CssSemaphore::tryAcquire, acquired semaphore CLSN.AQPROC.<database>.MASTER
: USRTHRD:11: AQPROC.EMRMAXD CssSemMM::tryMaster I am the master
:CLSDYNAM:7: [ora.<database>.db]{1:45586:5510} [start] ORA-00304: requested INSTANCE_NUMBER is busy<<<<<<<<<<<<<<<<<<<<<<<<<

:CLSDYNAM:7: [ora.<database>.db]{1:45586:5510} [start] InstAgent::start: 1 errcode 304
:CLSDYNAM:7: [ora.<database>.db]{1:45586:5510} [start] InstAgent::start: ORA-304 instance busy m_instanceType 1 m_lastOCIError 304
:CLSDYNAM:7: [ora.<database>.db]{1:45586:5510} [start] InstAgent::start: ORA-304 instance busy dbType RAC
:CLSDYNAM:7: [ora.<database>.db]{1:45586:5510} [start] InstConnection::connectInt: server not attached
:CLSDYNAM:7: [ora.<database>.db]{1:45586:5510} [start] ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory
Additional information: 2582
Additional information: 890000679
Process ID: 0
Session ID: 0 Serial number: 0

CHANGES

OS patching and server reboot

CAUSE

Created pfile from spfile and found the incorrect below entry

*.instance_number=1

SOLUTION

1. Correct the instance numbers:
alter system set instance_number=2 scope=spfile sid='<instance_2>';
alter system set instance_number=1 scope=spfile sid='<instance_1>';

2. Stop and start the database using srvctl

--------------ORA-00304:

SQL> show parameter instance

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 3
instance_abort_delay_time integer 0
instance_groups string
instance_mode string READ-WRITE
instance_name string cdb1
instance_number integer 3
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string

SQL> alter system set instance_number=2 sid='cdb2' scope=spfile;

System altered.

SQL> alter system set instance_number=3 sid='cdb3' scope=spfile;

System altered.

SQL> show parameter instance

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_number integer 3

SQL> alter system set instance_number=1 sid='cdb1' scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 608170680 bytes
Fixed Size 9167544 bytes
Variable Size 557842432 bytes
Database Buffers 33554432 bytes
Redo Buffers 7606272 bytes
Database mounted.
Database opened.

----------ORA-01619

[oracle@rac1 ~]$ srvctl start database -d cdb
PRCR-1079 : Failed to start resource ora.cdb.db
CRS-5017: The resource action "ora.cdb.db start" encountered the following error:
ORA-01619: thread 3 is mounted by another instance
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac3/crs/trace/crsd_oraagent_oracle.trc".

CRS-5017: The resource action "ora.cdb.db start" encountered the following error:
ORA-01619: thread 3 is mounted by another instance
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.cdb.db' on 'rac2' failed
CRS-2674: Start of 'ora.cdb.db' on 'rac3' failed

SQL> show parameters instance_number

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_number integer 1
SQL> show spparameters instance_number

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
cdb1 instance_number integer 1
cdb2 instance_number integer 2
cdb3 instance_number integer 3

SQL> show spparameters sid
SQL> show spparameters cdb

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* cdb_cluster boolean
* cdb_cluster_name string
* noncdb_compatible boolean

[oracle@rac1 ~]$ srvctl stop database -d cdb
[oracle@rac1 ~]$ srvctl start database -d cdb
PRCR-1079 : Failed to start resource ora.cdb.db
CRS-5017: The resource action "ora.cdb.db start" encountered the following error:
ORA-01619: thread 3 is mounted by another instance
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.cdb.db' on 'rac2' failed
[oracle@rac1 ~]$ s

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 6 19:21:50 2024
Version 19.20.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> show parameters clust

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_cluster_by_rowid_control integer 129
cdb_cluster boolean FALSE
cdb_cluster_name string
cluster_database boolean TRUE
cluster_database_instances integer 3
cluster_interconnects string
SQL> show parameters instance_number

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_number integer 1
SQL> show spparameters instance_number

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
cdb1 instance_number integer 1
cdb2 instance_number integer 2
cdb3 instance_number integer 3
SQL> show parameters thread

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* ofs_threads integer
* parallel_threads_per_cpu integer
* thread integer 3
* threaded_execution boolean
SQL> alter system reset thread scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* ofs_threads integer
* parallel_threads_per_cpu integer
* thread integer
* threaded_execution boolean
SQL> show parameters thread

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ofs_threads integer 4
parallel_threads_per_cpu integer 1
thread integer 0
threaded_execution boolean FALSE
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
[oracle@rac1 ~]$ srvctl stop database -d cdb
[oracle@rac1 ~]$ srvctl start database -d cdb
[oracle@rac1 ~]$ show spparameters thread
-bash: show: command not found
[oracle@rac1 ~]$ s

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 6 19:26:19 2024
Version 19.20.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> show spparameters thread

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* ofs_threads integer
* parallel_threads_per_cpu integer
* thread integer
* threaded_execution boolean
SQL> show parameters thread