LLI Wiki — My Digital Knowledge Base

ORA-03113 at startup after changing SGA_MAX_SIZE

How to recover from ORA-03113 at startup after lowering sga_max_size in an spfile, by editing a pfile and recreating the spfile.

Oracle refuses to start with ORA-03113: end-of-file on communication channel when the SGA parameters in the spfile do not fit the memory that the instance needs. This article shows how to fix the parameter from a text parameter file (pfile) and then recreate the spfile. The example is from Oracle 9i, but the same steps apply to later releases that use an spfile.

Symptom

The database was running with an SGA of about 140 MB (show sga). Lowering sga_max_size to 20 MB in the spfile and restarting the instance gives:

SQL> startup
ORA-03113: end-of-file on communication channel

Cause

sga_max_size sets the maximum SGA size that the instance can use. A value smaller than the SGA the instance needs at startup prevents the instance from starting.

Procedure

  1. Connect as SYSDBA and create a text parameter file from the spfile:

    CREATE PFILE FROM SPFILE;
    
  2. Edit the pfile (for example $ORACLE_HOME/dbs/init<SID>.ora) and set sga_max_size to a value at least as large as the total SGA. Use a value that matches the sizes shown by show sga:

    sga_max_size = <value>
    
  3. Start the instance with the pfile:

    STARTUP PFILE='/path/to/init.ora';
    
  4. Recreate the spfile from the pfile:

    CREATE SPFILE='/path/to/spfile.ora' FROM PFILE='/path/to/init.ora';
    

If the instance uses the default spfile location, make sure the new spfile is the one read at the next startup.

Mastodon LLIMastodon SocialFosstodon