How to Resolve ORA-29760: instance_number parameter not specified

  • by

ORA-29760

ORA-29760: instance_number parameter not specified means that Oracle cannot find any INSTANCE_NUMBER for given instance names in the parameter file to startup the instance.

For a single-instance database, INSTANCE_NUMBER is 0 by default, so we don't need to care about the parameter at all. On the contrary, INSTANCE_NUMBER is usually used by a RAC database. That why we got ORA-29760 mostly when starting up a RAC database.

Let's see a case. We got ORA-29760 when we tried to startup a newly created empty RAC database.

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

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

CRS-2674: Start of 'ora.orclcdb.db' on 'primary02' failed

This error may also appear in RMAN when RMAN tries to startup the database.

Rationale to ORA-29760

For a 2-node RAC database, the first instance usually takes the value 1 of INSTANCE_NUMBER and the second instance takes the value 2. Therefore, the entries in the parameter file should be:

<First_Instance_Name>.instance_number=1
<Second_Instance_Name>.instance_number=2

In other words, Oracle find the instance name to allocate instance number when starting up. If the instance name listed in the parameter file cannot match with the instance name configured in the database each other, Oracle cannot find the right instance number to startup.

Check RAC Database Configuration

Let's see both instance names configured in the database.

[oracle@primary01 ~]$ srvctl config database -d orclcdb
...
Database instances: orclcdb1,orclcdb2
Configured nodes: primary01,primary02
...

It seems no problem on the side of the RAC database.

Check Parameter File

Let's check another side, the parameter file.

SQL> create pfile='/home/oracle/pfile' from spfile='+DATA/ORCLCDB/spfile';

File created.

SQL> exit;

We list the parameter file for further investigation.

[oracle@primary01 ~]$ cat /home/oracle/pfile
...
ORCLCDB2.instance_number=2
ORCLCDB1.instance_number=1

Basically, they are the same. Of course, not exactly the same, the only difference is the letter case, the one is lowercase, the other is uppercase.

So the solution is obvious, we should make instance names match with each other, it's nothing to do with instance numbers in this case.

Solutions to ORA-29760

There're two ways to solve the problems.

  1. Lowercase the instance name in the parameter file, or
  2. Uppercase the instance name in the RAC configuration.

1. Lowercase the instance name in the parameter file

We lowercase all instance names in the parameter file like this:

   Old   ->   New
--------------------
ORCLCDB1 -> orclcdb1
ORCLCDB2 -> orclcdb2

The result look like this:

[oracle@primary01 ~]$ vi /home/oracle/pfile
orclcdb1.__data_transfer_cache_size=0
orclcdb2.__data_transfer_cache_size=0
orclcdb2.__db_cache_size=1644167168
orclcdb1.__db_cache_size=1577058304
orclcdb1.__inmemory_ext_roarea=0
orclcdb2.__inmemory_ext_roarea=0
orclcdb1.__inmemory_ext_rwarea=0
orclcdb2.__inmemory_ext_rwarea=0
orclcdb1.__java_pool_size=0
orclcdb2.__java_pool_size=0
orclcdb1.__large_pool_size=16777216
orclcdb2.__large_pool_size=16777216
orclcdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orclcdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orclcdb1.__pga_aggregate_target=822083584
orclcdb2.__pga_aggregate_target=822083584
orclcdb1.__sga_target=2449473536
orclcdb2.__sga_target=2449473536
orclcdb1.__shared_io_pool_size=117440512
orclcdb2.__shared_io_pool_size=117440512
orclcdb2.__shared_pool_size=654311424
orclcdb1.__shared_pool_size=721420288
orclcdb1.__streams_pool_size=0
orclcdb2.__streams_pool_size=0
orclcdb1.__unified_pga_pool_size=0
orclcdb2.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORCLCDB/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA/ORCLCDB/control01.ctl','+DATA/ORCLCDB/control02.ctl'
*.db_block_size=8192
*.db_name='ORCLCDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=50g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLCDBXDB)'
*.enable_pluggable_database=true
family:dw_helper.instance_mode='read-only'
orclcdb2.instance_number=2
orclcdb1.instance_number=1
*.local_listener='-oraagent-dummy-'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=779m
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sga_target=2336m
orclcdb2.thread=2
orclcdb1.thread=1
orclcdb1.undo_tablespace='UNDOTBS1'
orclcdb2.undo_tablespace='UNDOTBS2'

We create a new SPFILE for the database.

[oracle@primary01 ~]$ export ORACLE_SID=orclcdb1
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> startup nomount pfile='/home/oracle/pfile';
...
SQL> create spfile='+DATA/ORCLCDB/spfile' from pfile='/home/oracle/pfile';

File created.

SQL> shutdown immediate;
...

We startup the RAC database normally.

[oracle@primary01 ~]$ srvctl start database -d orclcdb -o nomount
[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance orclcdb1 is running on node primary01
Instance orclcdb2 is running on node primary02

As you can see the instance names are all lower case.

2. Uppercase the instance name in the RAC configuration

If you don't want to touch SPFILE, you can uppercase all instance names.

First, remove instances from the database

[oracle@primary01 ~]$ srvctl remove instance -d orclcdb -i orclcdb1
Remove instance from the database orclcdb? (y/[n]) y
[oracle@primary01 ~]$ srvctl remove instance -d orclcdb -i orclcdb2
Remove instance from the database orclcdb? (y/[n]) y

Then add then back with uppercase instance names.

[oracle@primary01 ~]$ srvctl add instance -d orclcdb -i ORCLCDB1 -n primary01
[oracle@primary01 ~]$ srvctl add instance -d orclcdb -i ORCLCDB2 -n primary02

Let's check the RAC configuration before starting it up.

[oracle@primary01 ~]$ srvctl config database -d orclcdb
Database unique name: ORCLCDB
...
Database instances: ORCLCDB1,ORCLCDB2
Configured nodes: primary01,primary02
...

Let's startup the database normally.

[oracle@primary01 ~]$ srvctl start database -d orclcdb -o nomount
[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance ORCLCDB1 is running on node primary01
Instance ORCLCDB2 is running on node primary02

Conclusions

Both ways are workable to solve ORA-29760. But I prefer the latter one, this is because we used to use uppercase instance names to build a RAC database conventionally.

Secondly, the error is really irrelevant to INSTANCE_NUMBER, all about INSTANCE_NAME.

Leave a Reply

Your email address will not be published. Required fields are marked *