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.

image

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.

image

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

vi  /u01/app/oracle/initdevdb1.ora

image

3. Nomount database with newly created pfile.

image

4. Delete old spfile using ASMCMD.

image

5.  create spfile from pfile.

image

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: