Database not nomounting: ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET

I have Oracle database with ASM. Database is started by spfile, that is located on ASM. I have pfile that indicates to spfile.

Let’s start from the beginning. I run the following command:

alter system set STATISTICS_LEVEL=basic scope=spfile;

Restarted the database and tried to startup. Got the following error:

ORA-00848 STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET

And because of this database is not nomounting.

My parameter file content is:

–/u01/app/oracle/product/11.2.0/db_1/dbs/initdevdb1.ora

So let’s solve it. My aim is to set STATISTICS_LEVEL parameter to TYPICAL and start the database normally.

1. Create pfile from spfile.

2. Change parameter file initdevdb1.ora(which was created form spfile)  and set statistics_level parameter to TYPICAL.

vi  /u01/app/oracle/initdevdb1.ora

3. Nomount database with newly created pfile.

4. Delete old spfile using ASMCMD.

5.  create spfile from pfile.

Note: Default location for pfile is $ORACLE_HOME/dbs. STARTUP command reads pfile from default location, that in our case refers to spfile located on ASM.

That’s it!