标题: STARTUP ERRORS ora-00824 cannot set sga_target with statistics_level=BASIC [打印本页] 作者: 刘泽宇 时间: 2025-10-12 18:19 标题: STARTUP ERRORS ora-00824 cannot set sga_target with statistics_level=BASIC 现象：
You shutdown the database and on restart you see the following message
SQL> startup restrict;
ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information
SQL> startup
ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information
SQL> startup mount
ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information
ALERT.LOG
ARCH: Archival disabled due to shutdown: ....
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Tue Oct 02 16:13:45 2007
Cannot set sga_target with statistics_level=BASIC
改动：
A new or recently altered PFILE or SPFILE was used for the first time with the database STATISTICS_LEVEL level set to BASIC and now after the instance is being restarted
原因：
If the STATISTICS_LEVEL=BASIC during startup while SGA_TARGET is set <> 0 , you will see this error.
This can happen due to changes in the PFILE explicitly or the SPFILE using the ALTER SYSTEM command:
SQL> alter system set statistics_level = basic scope=spfile;
System altered.
The problem is that "...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. It is therefore an error to try to set SGA_TARGET as well as STATISTICS_LEVEL=BASIC at the same time..."
处理方法：
Alter the SPFILE to either dereference the SGA_TARGET or set STATISTICS_LEVEL=TYPICAL
Make a backup copy of your SPFILE and rename the spfile<sid>.old
Make a copy of the SPFILE to edit it using a simple Text editor such as vi or Notepad
Cleanup the binary * and extraneous non-text data in the SPFILE to create a new usable PFILE
Dereference SGA_TARGET parameter in the new pfile -- e.g. # sga_target=188743680
Save the newly created PFILE in a simple text format renamed init<SID_clone>.ora
Startup the database using the new PFILE created from the original SPFILE -- e.g. startup pfile='C:\inita10gR2.ora'
Create a new SPFILE from the PFILE -- e.g. create spfile='c:\spfilea10gr2.ora' from pfile='C:\inita10gR2.ora';
Pseudocode
SPFILE='/somepath/spfile<sid>..ora' FROM PFILE='/somepath/cloned_n_cleanedspfile<sid>.ora';
Optionally: Add the STATISTICS_LEVEL=TYPICAL to the PFILE prior to startup
Make a backup copy of your SPFILE and rename the spfile<sid>.old
Make a copy of the SPFILE to edit it using a simple Text editor such as vi or Notepad
Cleanup the binary * and extraneous non-text data in the SPFILE to create a new usable PFILE
add STATISTICS_LEVEL=TYPICAL in the new pfile and leave SGA_TARGET as is:
e.g. SGA_TARGET = 188743680
STATISTICS_LEVEL = TYPICAL
Save the newly created PFILE in a simple text format renamed init<SID_clone>.ora
Startup the database using the new PFILE created from the original SPFILE -- e.g. startup pfile='C:\inita10gR2.ora'
Create a new SPFILE from the PFILE -- e.g. create spfile='c:\spfilea10gr2.ora' from pfile='C:\inita10gR2.ora';