Database not nomounting: ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET
June 22, 2012 Leave a comment
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
SPFILE=’+DATA/devdb/spfiledevdb.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!