Friday, September 3, 2010

ORA-29760: instance_number parameter not specified

Problem Description
Using sql*plus whenever I do startup it fails with ORA-29760 like below.

SQL> startup nomount
ORA-29760: instance_number parameter not specified

Similarly, whenever I start my database using srvctl it returns same error like following.

$ srvctl start database -d didar
PRKP-1001 : Error starting instance didar1 on node node1
CRS-0215: Could not start resource ora.node1.didar1.inst.

If you look for imon_didar.log file inside $ORACLE_HOME/log/node1/racg there is showed that startup has failed with 'ORA-29760: instance_number parameter not specified'.

Cause of the Problem
Cause 01:
The database and instance names have been defined in lowercase in the cluster registry (OCR). That's why "srvctl start database" is being passed the argument 'test'.

However, if you look for spfile or pfile you will see that the instance names have been defined in uppercase.

DIDAR1.instance_number=1
DIDAR2.instance_number=2

Cause 02:
You are using sql*plus and within your pfile/spfile it is set proper name that is in lower case. But your environmental variable value of ORACLE_SID contains uppercase database SID "DIDAR". In such case, ORA-29760 will also be reported whenever you try to startup your database.

Solution of the Problem
srvctl is case sensitive. So you need to ensure that the instance and database definitions set in the spfile/pfile are the same case as those in the OCR and as are used in the srvctl commands.
Before going into solution be sure that your ORACLE_SID reflects the correct case so that the instance can be accessed using SQL*Plus. If your ORACLE_SID environmental variable is set to uppercase but inside pfile/spfile it is set to lowercase then you might face the problem.

If they are not then either:

Solution 01
If you use spfile, then

SQL> create pfile from spfile;

Edit the pfile to alter all definitions to the correct case

SQL> create spfile from pfile=[location of pfile here];

SQL> startup

Solution 02
modify the definitions in the OCR:

srvctl remove instance -d didar -i didar1

srvctl remove instance -d didar -i didar2

srvctl remove database -d didar

srvctl add database -d DIDAR -o $ORACLE_HOME

srvctl add instance -d DIDAR -i DIDAR1 -n [node1]

srvctl add instance -d DIDAR -i DIDAR2 -n [node2]

Solution 03
In the pfile add instance_number=1 value.
$ vi newpfile.ora
SPFILE='+DATA/DIDAR/spfileDIDAR.ora'
instance_number=1

SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup pfile='/opt/app/oracle/product/10.2.0/db_1/dbs/newpfile.ora'

SQL> alter system set instance_number=1 scope=spfile;

SQL> shutdown immediate;

SQL> startup

No comments:

Post a Comment