Can I increase SGA target size greater than SGA max size ?

Answer is NO. Can I increase SGA_MAX_SIZE greater than memory_target ?.....NO.Now we can see some examples. However you can clear more about from oracle document. Here I just mentioned some usefull url about this related.

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

• Buffer cache (DB_CACHE_SIZE)
• Shared pool (SHARED_POOL_SIZE)
• Large pool (LARGE_POOL_SIZE)
• Java pool (JAVA_POOL_SIZE)
• Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

C:\Users\mazar>set oracle_sid=own

C:\Users\mazar>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 30 12:33:38 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys/sys@own as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter sga_target;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 0

This below view to see the shared pool or buffer pool size.
SQL> select * from v$sgainfo;

NAME                                  BYTES RES
——————————– ———- —
Fixed SGA Size                      1375792 No
Redo Buffers                        5804032 No
Buffer Cache Size                 339738624 Yes
Shared Pool Size                  146800640 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  535662592 No
Startup overhead in Shared Pool    58720256 No

NAME                                  BYTES RES
——————————– ———- —
Free SGA Memory Available          33554432

12 rows selected.

SQL> show parameter sga_max_size;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_max_size                         big integer 512M

  

Here I want to increase my sga target size 500m. The system will be altered.Because SGA_MAX_SIZE is 512m. Suppose to be if you’re increase more than sga_max_size, it will throw error. See below,SQL> alter system set sga_target=500m;

System altered.

SQL> show parameter sga_target;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 500M
SQL> show parameter sga_max_size;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_max_size                         big integer 512M
SQL> show parameter sga_target;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 4M
SQL> alter system set sga_target=600m;
alter system set sga_target=600m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

C:\Users\mazar>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 30 12:43:47 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys/sys as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             167772624 bytes
Database Buffers          360710144 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
SQL> select * from v$sgainfo;

NAME                                  BYTES RES
——————————– ———- —
Fixed SGA Size                      1375792 No
Redo Buffers                        5804032 No
Buffer Cache Size                 360710144 Yes
Shared Pool Size                  146800640 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  535662592 No
Startup overhead in Shared Pool    58720256 No

NAME                                  BYTES RES
——————————– ———- —
Free SGA Memory Available          12582912

12 rows selected.

Here I tried to increase my sga_max_size , but it show error because memory target is low compared to increase your sga max size values.

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

System altered.

SQL> show parameter sga_max_size;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_max_size                         big integer 512M
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 1585446912 (1512mb) cannot be set to more than MEMORY_TARGET 8388
60800. (800mb)
SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 9 Serial number: 5

So I created pfile and modified in pfile.
SQL> create pfile=’c:\initown.ora’ from spfile;

File created.

SQL> startup pfile=’C:\azar\initown.ora’;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1373996 bytes
Variable Size             176163028 bytes
Database Buffers          352321536 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.

If you want to increase your SGA_MAX_SIZE, you should increase your  memory target size..

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/memory003.htm#BGBGJFHE

Very interestng quesition here about this post related.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30011178429375

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516