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. So we should focus on the misspelled instance name.
On the other side, 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 some cases that generate ORA-29760.
Single-instance Database
After modifying some initialization parameters, we tried to start up the database, we got the error.
SQL> startup
ORA-29760: instance_number parameter not specified
RAC Database
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:
<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.
Misspelled Instance Name
Sometimes, we misspelled $ORACLE_SID or INSTANCE_NAME and made them unmatched.
Check $ORACLE_SID
[oracle@primary01 ~]$ echo $ORACLE_SID
ORCLCDB1
Check INSTANCE_NAME
A plain-text parameter file would be very helpful, so we should create one for us.
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
ORALCDB1.instance_number=1
As we can see, they don't match. We misspelled the instance name. So the solution is obvious, just correct the instance name in the parameter file to solve it.
Letter Case Issue
Check First Pair of Instance Names in 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 in the cluster configuration of the RAC database except that the instance names are lowercase.
Check Second Pair of Instance Names in Parameter File
A plain-text parameter file would be very helpful, so we should create one for us.
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, two pairs of instance names are the same. Of course, not exactly the same, the only difference is the letter case, the first pair is lowercase, the latter pair is uppercase.
So the solution is obvious, we should make instance names exactly match with each other including the letter case, it's really nothing to do with instance numbers in this case.
Solutions to ORA-29760
Misspelled instance name is easier to solve, just correct the instance name in the parameter file to align with ORACLE_SID.
Therefore, we focus on the letter case problem, it's a little tricky.
There're two ways to solve the problems.
- Uppercase the instance name in the RAC configuration, or
- Lowercase the instance name in the parameter file.
Uppercase the instance name in the RAC configuration
If you don't want to touch SPFILE, you can uppercase all instance names in the RAC configuration to align with SPFILE.
srvctl remove instance
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
srvctl add instance
Then add them 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
srvctl config database
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
...
srvctl start database
Good! All instance names are upper-cased. 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
Please note that, the instance names are all uppercase now.
Lowercase the instance name in the parameter file
We lowercase all instance names in the parameter file to align with the RAC configuration:
--------------------
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 lowercase.
Conclusions
Both ways are workable to solve ORA-29760. But I prefer the first 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!
Nice post, Yes, it helped me to resolve the isuse
Oh, I’m so glad.
Didn’t quite get this one though, both are uppercase from the example isn’t it?
[oracle@primary01 ~]$ cat /home/oracle/pfile
…
ORCLCDB2.instance_number=2
ORCLCDB1.instance_number=1
Basically, two pairs of instance names are the same. Of course, not exactly the same, the only difference is the letter case, the first pair is lowercase, the latter pair is uppercase.
I rephrased some text and make it more understandable.
I want to create a standby database and thus copied pfile, control file and database files from primary database. I do not understand what will be standby ORACLE_SID and instances name? Will they would be same as of primary or different?
In practice, we use a different instance name (i.e. ORACLE_SID) for the standby database. The key point is that the database unique name (DB_UNIQUE_NAME) must be different in data guard. For more differentiations, you may refer to the post: DB_NAME vs DB_UNIQUE_NAME.