Sunday, 15 July 2012

ORA-1078 ORA-1565 Errors while starting database with SPFILE on ASM/RAC (shared location)

Fact:
Create pfile='/data01/pfile.ora' from spfile;
Did some changes in pfile
Startup the database using pfile
Now create spfile from pfile using coomand,
create spfile='/data01/oradata/ORCL/spfileORCL.ora' from pfile;
Then I startup the database Node1,but I got the below mentioned error

Node 1:
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '/ices/data01/oradata/ORCL/spfileORCL.ora'
ORA-27041: unable to open file
HPUX-ia64 Error: 24: Too many open files
Additional information: 2

Node 2:
SYS@ORCL1>startup
ORA-03113: end-of-file on communication channel


Findings:
Error first states that it has failed to process the parameter. Second states that it has failed to identify the spfile and is unable to open.
But I was able to open the database normally with pfile. Also the spfile was present in the Shared Location and accessible from both the Nodes.

Problem here was that I had started the database with pfile from non-default location and pfile located in $ORACLE_HOME/dbs had following entries


Solution:
Specify the Pfile path manually while creation of Spfile

SQL> CREATE SPFILE='/data01/oradata/ORCL/spfileORCL.ora' from pfile='/data01/pfile.ora';
File created.

Now,retest the issue.