How to Disable Automatic SGA Management on 11g Release 2


Login database using

sqlplus / as sysdba

Dump the pfile using existing spfile

SQL> create pfile='/home/oracle/Desktop/init.ora' from spfile;

File created.

Oracle will generate implicit parameters defining current sizing of the memory regions in SGA and write them at the top of generated pfile

orcl.__db_cache_size=79691776
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=142606336
orcl.__sga_target=272629760
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=167772160
orcl.__streams_pool_size=4194304
.
.
.
*.memory_target=414187520
.
.
.

Edit the file to disable AMM and enable static SGA configuration by simply removing memory_target, oracle_base and shared_io_pool_size parameters and doing some make up

*.db_cache_size=79691776
*.java_pool_size=4194304
*.large_pool_size=4194304
*.pga_aggregate_target=142606336
*.sga_target=272629760
*.shared_pool_size=167772160
*.streams_pool_size=4194304
.
.
.

Rebounce database instance by using modified pfile.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile='/home/oracle/Desktop/init.ora';
ORACLE instance started.

Total System Global Area  271400960 bytes
Fixed Size                  2227544 bytes
Variable Size             180355752 bytes
Database Buffers           83886080 bytes
Redo Buffers                4931584 bytes
Database mounted.
Database opened.

Once you ensure that new pfile works, recreate new spfile using this pfile (refer to How to move ASM spfile to a different disk group [ID 1082943.1] if your target is ASM diskgroup)

SQL> create spfile using pfile='/home/oracle/Desktop/init.ora';

File created.

Finally rebounce the database by using new spfile

SQL> startup force;
ORACLE instance started.

Total System Global Area  271400960 bytes
Fixed Size                  2227544 bytes
Variable Size             180355752 bytes
Database Buffers           83886080 bytes
Redo Buffers                4931584 bytes
Database mounted.
Database opened.
About these ads

About kocakahin

Just a computer engineer

Posted on February 12, 2013, in Oracle and tagged , , , . Bookmark the permalink. 2 Comments.

  1. We had just your solution today, in turkish there is a saying for fresh things… “Smoke on it!”
    Thank you grandson! Simple solution needs, experience and willing to share…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 173 other followers

%d bloggers like this: