Q

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.

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

This was first published in May 2007

Dig deeper on Oracle error messages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close