I was getting following error’s while starting a database using spfile on ASM. Actually this was a cloned RAC environment.
SQL> create SPFILE='+ASM_GROUP/PORTALDB/spfileportaldb.ora' from pfile; File created. SQL> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> exit SQL> startup mount ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+ASM_GROUP/PORTALDB/spfileportaldb.ora' ORA-17503: ksfdopn:2 Failed to open file +ASM_GROUP/PORTALDB/spfileportaldb.ora ORA-01000: maximum open cursors exceeded
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 ASM diskgroup which I confirmed by listing DG contets on asmcmd prompt.
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
In this case spfile too was created with same contents. Correct method is to specify the non-default pfile location in ‘create spfile’ syntax.
SQL> CREATE SPFILE='+ASM_GROUP/PORTALDB/spfileportaldb.ora' from pfile=\'/home/oracle/portaldb.ora\'; File created. SQL> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> exit SQL> sqlplus \"/ as sysdba\" Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 3674210304 bytes Fixed Size 2088384 bytes Variable Size 2197815872 bytes Database Buffers 1459617792 bytes Redo Buffers 14688256 bytes Database mounted. SQL> alter database open;