Answer

How to increase SGA_MAX_SIZE in Oracle

How to increase SGA_MAX_SIZE in Oracle? I got the error "ORA-03113: end-of-file on communication channel" during startup.

Phillip Bracken
By
Published: 22 May 2007
How can I increase SGA_MAX_SIZE in Oracle ? I got the error "ORA-03113: end-of-file on communication channel" during startup. Please help me with the steps to increase the SGA. I'm connected to Oracle9i Enterprise Edition Release 9.0.1.1.1 (Production) with the partitioning option. 
SQL> connect sys as sysdba
Enter password: ******
Connected.
SQL> show sga;

Total System Global Area  147615836 bytes
Fixed Size                   282716 bytes
Variable Size             113246208 bytes
Database Buffers           33554432 bytes
Redo Buffers                 532480 bytes
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
spfile                               string
%ORACLE_HOME%DATABASESPFILE%
                                                 ORACLE_SID%.ORA
SQL> create pfile from spfile;

File created.

SQL> alter system set sga_max_size=20m scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-03113: end-of-file on communication channel
Increase the Oracle SGA_MAX_SIZE setting in your pfile that you created from your spfile. Then in SQL*Plus start up the database using that pfile as follows. 
Startup pfile=/u00/oracle/admin/ora/pfile/init.ora
Then re-create your spfile. 
Create spfile=/u00/oracle/admin/ora/pfile/spfile.ora 
  from pfile==/u00/oracle/admin/ora/pfile/init.ora

For more information on SGA_MAX_SIZE in Oracle :
Database fully utilized with hang

Dig Deeper on Oracle database administration