sga_max_size and sga_target

In this post, I am going to be demonstrating the basics of working of the sga_max_size and sga_target and also to cover some myths of playing around with these parameters.

To begin with, sga_max_size is set in the spfile to a value of 200M and then I try to set the sga_target and the story unfolds as below.

SQL> sho parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /opt/app/oracle/product/10.2.0

                                                 /dbs/spfileprod_db.ora

SQL> sho parameter sga_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 200M

sga_target                           big integer 0

SQL> sho sga

Total System Global Area  209715200 bytes

Fixed Size                  1977560 bytes

Variable Size             155194152 bytes

Database Buffers           50331648 bytes

Redo Buffers                2211840 bytes

SQL> sho parameter shared_pool_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

shared_pool_size                     big integer 84M

SQL> sho parameter db_cache_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 48M

SQL> sho parameter large

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

large_pool_size                      big integer 0

SQL> sho parameter java_pool_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

java_pool_size                       big integer 24M

SQL> sho parameter streams_pool

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

streams_pool_size                    big integer 0

Now, trying to set the sga_target to a value of 210M which is greater than that of sga_max_size gives out an error of ORA-02097 and ORA-00823, this shows that the sga_target can never be greater than the sga_max_size.

SQL> alter system set sga_target=210M scope=both;

alter system set sga_target=210M scope=both

*

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

However, sga_target can be set less than or equal to sga_max_size.

SQL> alter system set sga_target=200M scope=both;

System altered.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 200M

sga_target                           big integer 200M

But an interesting point to be noted in here is that as soon as the sga_target is set to a non-zero value, it starts affecting the memory areas of the SGA which is evident from the change in the value of Database Buffers (db_cache_size) from 50331648 (48M) from the previous execution of SHO SGA to 92274688 (88M), while every other memory area remains unchanged.

SQL> sho sga

Total System Global Area  209715200 bytes

Fixed Size                  1977560 bytes

Variable Size             113251112 bytes

Database Buffers           92274688 bytes

Redo Buffers                2211840 bytes

SQL>  sho parameter db_Cache_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 88M

SQL> sho parameter shared_pool_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

shared_pool_size                     big integer 84M

SQL> sho parameter java_pool

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

java_pool_size                       big integer 24M

SQL> sho parameter streams_pool

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

streams_pool_size                    big integer 0

SQL> sho parameter large_pool

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

large_pool_size                      big integer 0

Let's try to unset the sga_target and see if Database Buffers (db_cache_size) reverts back to what it was, but it does not.

SQL> alter system set sga_target=0 scope=both;

System altered.

SQL> sho sga

Total System Global Area  209715200 bytes

Fixed Size                  1977560 bytes

Variable Size             113251112 bytes

Database Buffers           92274688 bytes

Redo Buffers                2211840 bytes

SQL> sho parameter sga_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 200M

sga_target                           big integer 0

SQL>  sho parameter db_Cache_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 88M

It was hoped that bouncing the database would make a difference, on the contrary it did not. It is another discussion as to why the Database Buffers (db_Cache_size) does not change back to what it was even after unsetting the sga_target and bouncing the database, let's not get into the nitty-gritty of that.

SQL> sho sga

Total System Global Area  209715200 bytes

Fixed Size                  1977560 bytes

Variable Size             113251112 bytes

Database Buffers           92274688 bytes

Redo Buffers                2211840 bytes

SQL> sho parameter db_Cache_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 88M

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  209715200 bytes

Fixed Size                  1977560 bytes

Variable Size             113251112 bytes

Database Buffers           92274688 bytes

Redo Buffers                2211840 bytes

Database mounted.

Database opened.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 200M

sga_target                           big integer 0

SQL> sho parameter db_Cache_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 88M

The Oracle server does not let you to resize the sga_max_size on the fly since it is not a dynamically changeable parameter. Let's now change sga_target to the value of sga_max_size. After this I will comment the sga_max_size and add sga_target=200M in the pfile, convert it into spfile and bounce the database.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 200M

sga_target                           big integer 0

SQL> alter system set sga_max_size=0 scope=both;

alter system set sga_max_size=0 scope=both

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sga_target=200M scope=both;

System altered.

SQL> sho parameter sgA_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 200M

sga_target                           big integer 200M

### MAIN SGA PARAMS ###

###sga_max_size=200M

sga_target=200M

SQL> create spfile from pfile;

File created.

SQL> startup

ORACLE instance started.

Total System Global Area  209715200 bytes

Fixed Size                  1977528 bytes

Variable Size              75502408 bytes

Database Buffers          130023424 bytes

Redo Buffers                2211840 bytes

Database mounted.

Database opened.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 200M

sga_target                           big integer 200M

SQL> sho parameter db_cache_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 0

SQL> sho parameter shared_pool_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

shared_pool_size                     big integer 0

SQL> sho parameter large_pool

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

large_pool_size                      big integer 0

SQL> sho parameter java_pool_s

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

java_pool_size                       big integer 0

SQL> sho parameter streams_pool

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

streams_pool_size                    big integer 0

SQL>

It now shows that as soon as the sga_target is set and set_max_size is unset, all the other memory area related parameters get reset to a value of zero(0) - making it clear that the Automatic Shared Memory Management is fully functional and that Memory Manager (MMAN) background process is actively doing its job. Also has the Database Buffers (db_cache_size) taken a higher value of 130023424 (124M) against the previous 88M.

There is yet another point to be noted here, besides sga_max_size being commented in spfile - it takes a value equal to the sga_target.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 200M

sga_target                           big integer 200M

UNIX$> grep -i "sga" initprod_db.ora

###sga_max_size=200M

sga_target=200M

UNIX$> grep -i "sga" $ORACLE_HOME/dbs/spfileprod_db.ora

*.sga_target=200M

The conclusions that we can draw from these little experiments are listed below.

1) sga_max_size cannot be changed without bouncing the database.

2) sga_target can be changed on the fly without bouncing the database but it can never be set to a value greater than the sga_max_size.

3) If the sga_max_size is not set in either of the pfile or spfile and the sga_target is set then the sga_max_size takes the value of sga_target when you do a sho parameter sga_

HTML space report

Have you ever received a request from the Business people about the space usage of a database to be delivered to their inboxes on a daily basis, well yup, I did and since it had come from someone in real high authority - I decided to generate a pleasant report of the usage of database.

I wrote a shell script that has an in-built sql script which retrieves information from the dba_* views. This result is spooled as an html file and sent to the recipient(s) as an attachment. The shell script takes the ORACLE_SID (database name) as the command line argument. Please review the script once after pasting it into your editor of choice for editting/spacing errors.

####################################################

# Name : space_report.ksh

#

# Usage : ksh space_report.ksh ORACLE_SID

#

####################################################

#!/usr/bin/ksh

if [ "$#" -lt 1 ]

then

echo " \nUsage : ksh space_report.ksh ORACLE_SID "

echo "\n"

exit 1

fi

export ORATAB=/var/opt/oracle/oratab   ## change this per your env.

export date_string=`date '+%d-%b-%Y'`

export ORACLE_SID=$1

export ORACLE_HOME=`grep $ORACLE_SID $ORATAB|head -1|awk -F":" '{print $2}'`

export To_id=to_id@oracleandunix.blogspot.com

export Cc_id=cc_id@oracleandunix.blogspot.com

export LOG_DIR=$HOME   

export LOG_FILE=$LOG_DIR/${ORACLE_SID}_space_report_${date_string}.html

export CAP_ORACLE_SID=`echo $ORACLE_SID|tr [:lower:] [:upper:]`

$ORACLE_HOME/bin/sqlplus -S "/as sysdba" << E_O_F

set markup html on

spool $LOG_FILE

set pages 50

set lines 100

set echo off

set serveroutput on

set feed off

col "TABLESPACE" format a25

col "FREE" format 9999999.99

col "MAX_FREE" format 9999999.99

col "NEXT_EXT" format a8

col "% Full" format 9999

col TOTAL format 999999.99

col USED format 999999.99

col SIZE format 9999.99

select name "DB NAME", to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "REPORT RUN TIME"

from v$database;

prompt

prompt

select substr(t.tablespace_name,1,25) "TABLESPACE",

decode(b.EXTENT_MANAGEMENT,'LOCAL','L','DICTIONARY','D',b.EXTENT_MANAGEMENT)

"EXT MGMT",

t.totspace/1024/1024  "TOTAL (MBs)",

round((t.totspace-sum(f.bytes))/1024/1024,2) "USED (MBs)",

round(sum(f.bytes)/1024/1024,2) "FREE (MBs)",

round(max(f.bytes)/1024/1024,2) "MAX FREE (MBs)",

round(((t.totspace-sum(f.bytes)) / t.totspace)*100) "% Full"

from dba_free_space f, dba_tablespaces b,

(select tablespace_name,sum(bytes) totspace

from dba_data_files

group by tablespace_name) t

where t.tablespace_name=f.tablespace_name

and t.tablespace_name=b.tablespace_name

group by t.tablespace_name,t.totspace,

b.next_extent,b.EXTENT_MANAGEMENT,b.tablespace_name

order by b.tablespace_name

prompt

prompt

prompt

select max('TOTAL SIZE OF THE DATABASE IS ') "DESCRIPTION",

rpad(round(sum(bytes)/1024/1024/1024,2),5,'0')||' GBs' "SIZE"

from dba_Data_Files

union all

select max('USED SIZE OF THE DATABASE IS '),

rpad(round(sum(bytes)/1024/1024/1024,2),5,'0')||' GBs'

from dba_segments

union all

select max('FREE SPACE AVAILABLE IN THE DATABASE IS '),

rpad(round(sum(bytes)/1024/1024/1024,2),5,'0')||' GBs'

from dba_free_space;

prompt

prompt

E_O_F

(

cat << E_O_F

Space report for $ORACLE_SID Database

E_O_F

echo "\n"

/usr/bin/uuencode $LOG_FILE $LOG_FILE

)|mailx -s "Space Report - $CAP_ORACLE_SID" -c "$Cc_id" $To_id

#####################

### END OF SCRIPT ###

#####################

ORA-00600 during controlfile recreation

The destructive errors like ORA-00600, ORA-07445 etc show up at unusual times, one such perfect example of getting an ORA-00600 is explained below - it appeared during the execution of CREATE CONTROLFILE script. Before we go any further, here is a quick background of what's being done. A 10.2.0.1 database is to be migrated from one server to another.

As a proactive DBA, which I am not most of the time ;-) - I dumped the control file to the trace and went about creating the scripts to copy the files from the source to destination. All went well until I confronted ORA-600 during the control file recreation.

I had to quickly check the Oracle version which i am currently using, just to make sure there is not a mis-match between the source and destination Oracle binaries. The error pointed me out to a trace file that Oracle usually generates when it needs to provide more information on a particular error.

SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-00600: internal error code, arguments: [kccscf_1], [9], [106012], [65535],

[], [], [], []

Here is the error entry in alert log

Errors in file /prd/u01/PROD/udump/prod_ora_12326.trc:

ORA-00600: internal error code, arguments: [kccscf_1], [9], [106012], [65535], [], [], [], []

ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG

UNIX$> more /prd/u01/PROD/udump/prod_ora_12326.trc

ksedmp: internal or fatal error

ORA-00600: internal error code, arguments: [kccscf_1], [9], [106012], [65535], [], [], [], []

Current SQL statement for this session:

CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG

    MAXLOGFILES 255

    MAXLOGMEMBERS 5

    MAXDATAFILES 12144

    MAXINSTANCES 12

    MAXLOGHISTORY 106012

Everything looked fine but there is no clue as to what's causing the mighty ORA-600 in this occasion, I gave it a couple of tries myself to troubleshoot before turning over to Mr. Ellison's boys for help!!!. A severity - 1 was raised for this issue and I was pointed out at the value of MAXLOGHISTORY to be equal to 65535 as the maximum limit against the value that I had as 106012.

This looks silly, how come a trace controlfile that was dumped from an SqlPlus session have a value not recommended by Oracle itself. It was later reported by the Oracle technician that its a Bug in 10.2.0.1 which has been fixed in 10.2.0.4. Corrected script is shown below.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/prd/u01/PROD/udump/prod_ora_12326.TXT' REUSE;

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

    MAXLOGFILES 255

    MAXLOGMEMBERS 5

    MAXDATAFILES 12144

    MAXINSTANCES 12

    MAXLOGHISTORY 65535

LOGFILE

  GROUP 1 'D:\APP\CYBADMIN\ORADATA\ORCL\REDO01.LOG' SIZE 50M BLOCKSIZE 512,

  GROUP 2 'D:\APP\CYBADMIN\ORADATA\ORCL\REDO02.LOG' SIZE 50M BLOCKSIZE 512,

  GROUP 3 'D:\APP\CYBADMIN\ORADATA\ORCL\REDO03.LOG' SIZE 50M BLOCKSIZE 512

STANDBY LOGFILE

DATAFILE

  'D:\APP\CYBADMIN\ORADATA\ORCL\SYSTEM01.DBF',

  'D:\APP\CYBADMIN\ORADATA\ORCL\SYSAUX01.DBF',

  'D:\APP\CYBADMIN\ORADATA\ORCL\UNDOTBS01.DBF',

  'D:\APP\CYBADMIN\ORADATA\ORCL\USERS01.DBF',

  'D:\APP\CYBADMIN\ORADATA\ORCL\EXAMPLE01.DBF'

CHARACTER SET WE8MSWIN1252

;