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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: