spfile

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

SPFILE='+ASM_GROUP/PORTALDB/spfileportaldb.ora'

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;

Cheers
Amit