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

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

Error

SQL> startup
ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings
ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET
ORA-01078: failure in processing system parameters

Cause
SGA_TARGET cannot be set if STATISTICS_LEVEL=BASIC since the self-tuning mechanism requires information from component advisories that are not enabled at this level.

Solution
Following are the two ways for solution:
1. Comment SGA_TARGET parameter in SPFILE defined value(# SGA_TARGET=1550M) or make it Zero.
2. Change the value of STATISTICS_LEVEL to default (set STATISTICS_LEVEL=TYPICAL)

For starting the database you have to choose one of the either method.
For basic Database default setting, you have to choose second method.

Method:
1. Create PFILE from SPFILE
2. Open the PFILE in Notepad or VI editor.
3. Modified the parameter either of them mentioned above
Either comment SGA_TARGET or Change STATISTICS_LEVEL=TYPICAL
For default setting, Change STATISTICS_LEVEL=TYPICAL
4. Create spfile from pfile
5. Start the Database.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.