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';