Determine Granule Size in Oracle

What is a memory granule

When a database instance starts up, the amount of memory allocated is determined by the allocations requested in the parameter file (init file or spfile).  This memory is allocated in units called granules. All memory pool sizes will be allocated in multiples of the granule size.

How is the granule size determined

The granule size is determined based on the amount of memory requested at the instance startup. It is based on the SGA_MAX_SIZE.  If MEMORY_MAX_TARGET is specified, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET for the purpose of sizing the granule.  Once set, the granule size does not change for the life of the instance.

The granule sizes at the time of writing, are:

RDBMS	SGA_MAX_SIZE (or memory_max_target)	GRANULE SIZE
9.2	<= 128MB	4MB
	> 128MB	16MB
10.2	<= 1GB	4MB
	> 1GB	16MB
11gR1	<= 1GB	4MB
	>1Gb   <= 4GB	16MB
	>4Gb   <= 16GB	64MB
	>16Gb <= 64GB	256MB
	> 64GB	512MB
11gR2	<= 1Gb	4Mb
	>1Gb   <= 8Gb	16Mb
	>8Gb   <= 16Gb	32Mb
	>16Gb <= 32Gb	64Mb
	>32Gb <= 64Gb	128Mb
	>64Gb <= 128Gb	256Mb
	> 128Gb	512Mb

How to find the Granule size

You can check the granule size that is currently set for your database instance by running the following SQL statement as SYSDBA

select bytes from v$sgainfo where name like 'Granule Size';

Global Enqueue Services Deadlock detected – TX deadlock in Exclusive(x) mode

In single instance environment, when a deadlock happens, it often reports ORA-60: Detecting and Resolving Locking Conflicts and Ora-00060 errors. In a Real Application Cluster (RAC) environment, instead of ORA-60, one would see the following messages in database alert log:

Global Enqueue Services Deadlock detected. More info in file
/u01/oracle/diag/rdbms/node/node1/trace/node1_ora_763770.trc.

OR

Global Enqueue Services Deadlock detected. More info in file
/u01/oracle/diag/rdbms/node/node1/trace/node1_lmd0_87933.trc.

You can see similar below lines in the trace file.

**************
---------resource 0x2efbe8510----------------------
resname       : [0xae0003][0x5149ef],[TX]
Local node    : 3
dir_node      : 2
master_node   : 2
hv idx        : 70
hv last r.inc : 138
current inc   : 140
hv status     : 0
hv master     : 3
.
.
.
.
.
.
.
----------enqueue 0x30d34bd30------------------------
lock version     : 232019
Owner node       : 3
grant_level      : KJUSEREX
req_level        : KJUSEREX
bast_level       : KJUSERNL
notify_func      : (nil)
resp             : 0x3170afe58
procp            : 0x30c334570
pid              : 13495
proc version     : 0

.

.
.
.
.
 

Global Wait-For-Graph(WFG) at ddTS[0.260b8] :

BLOCKED 0x30dcd0708 5 wq 2 cvtops x1001 TX 0xae0003.0x5149ef [8B000-0004-0036637E] 3

BLOCKER 0x30d29ce90 5 wq 1 cvtops x28   TX 0xae0003.0x5149ef [5D000-0003-001E8444] 2
BLOCKED 0x30e7999b0 5 wq 2 cvtops x1001 TX 0x270010.0x4c2def [5D000-0003-001E8444] 2
BLOCKER 0x30d34bd30 5 wq 1 cvtops x28   TX 0x270010.0x4c2def [8B000-0004-0036637E] 3
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0xae0003][0x5149ef],[TX]

  

**********

Here  mode 5 is exclusive lock and  instance# starts from 0

 

Above deadlock means two sessions involved in TX-0xae0003-0x5149ef and TX-0x270010-0x4c2def forms a deadlock, both sessions are from instance 4.

This is a typical application transaction TX enqueue lock, usually caused by SQL commit sequence and high concurrency. To avoid such deadlock, application code and logic need to be modified.
The application and SQL involved in the deadlock can be found in lmd0 or foreground trace (check all instances).

Displays LSNR SERVICE or STAT details

For example, if the sample script is saved to a file named test_lsnr, execute the following commands:
    chmod +x test_lsnr

If the sample script is saved to a file named test_lsnr, execute the following commands:
    ./test_lsnr -serv
 OR
    ./test_lsnr -stat


Script

 #!/bin/bash
 #
 #
 # Usage: test_lsnr [-stat||-serv]
 #
 if [ "$1" = "-serv" ]; then
 switch="service"
 elif [ "$1" = "-stat" ]; then
 switch="status"
 else
 echo "Usage: test_lsnr [-stat||-serv]"
 exit
 fi
 echo "Switch = $switch"
 # PS, GREP and AWK must be executable by the script user
 for lsnr in `ps -ef | grep tnslsnr | grep -v grep | awk '{ print $9}'`; do
 TEMP=`ps -ef | grep tnslsnr | grep " ${lsnr} " | grep -v grep | awk '{print $8}'`
 export ORACLE_HOME=`printf "%s" "${TEMP%\/bin\/tnslsnr}"`
 echo "Listener Name: ${lsnr}"
 echo "Home: ${ORACLE_HOME}"
 ${ORACLE_HOME}/bin/lsnrctl ${switch} ${lsnr}
 echo ""
 echo ""
 done

Sample output

>test_lsnr -stat


Switch = status
Listener Name: LISTENER_node1
Home: /home/oracle/oracle/product/11.1.0/asm
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 12-AUG-2011 01:54:15
Copyright (c) 1991, 2008, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.sftech.corp.biz)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_node1
Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date                12-JUN-2011 00:47:03
Uptime                    31 days 1 hr. 7 min. 11 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/oracle/product/11.1.0/asm/network/admin/listener.ora
Listener Log File         /home/oracle/oracle/diag/tnslsnr/node1/listener_node1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX.XXX.XX.XXX)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX.XXX.XX.XXX)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "instance" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
  Instance "instance2", status READY, has 1 handler(s) for this service...
Service "instanceXDB" has 2 instance(s).
  Instance "instance1", status READY, has 1 handler(s) for this service...
  Instance "instance2", status READY, has 1 handler(s) for this service...
Service "instance_XPT" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
  Instance "instance2", status READY, has 1 handler(s) for this service...
Service "conn1" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
  Instance "instance2", status READY, has 1 handler(s) for this service...
The command completed successfully

> test_lsnr - serv


Switch = service
Listener Name: LISTENER_node1
Home: /home/oracle/oracle/product/11.1.0/asm
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 12-AUG-2011 01:57:45
Copyright (c) 1991, 2008, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.test.com)(PORT=1521)(IP=FIRST)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "instance" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.test.com)(PORT=1521))
      "DEDICATED" established:1097331 refused:0 state:ready
         LOCAL SERVER
  Instance "instance2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2201353 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip.test.com)(PORT=1521))
Service "instanceXDB" has 2 instance(s).
  Instance "instance1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: node1.test.com, pid: 4904>
         (ADDRESS=(PROTOCOL=tcp)(HOST=node1.test.com)(PORT=3909))
  Instance "instance2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: node2.test.com, pid: 5389>
         (ADDRESS=(PROTOCOL=tcp)(HOST=node2.test.com)(PORT=54323))
Service "instance_XPT" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.test.com)(PORT=1521))
      "DEDICATED" established:1097331 refused:0 state:ready
         LOCAL SERVER
  Instance "instance2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2201353 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip.test.com)(PORT=1521))
Service "conn1" has 2 instance(s).
  Instance "instance1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.test.com)(PORT=1521))
      "DEDICATED" established:1097331 refused:0 state:ready
         LOCAL SERVER
  Instance "instance2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2201353 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip.test.com)(PORT=1521))
The command completed successfully

Required Network Bandwidth Transfer Of Redo In Data Guard

Based on primary database's redo generation rate we need calculate the required network bandwith.

How to calculate Redo rate.

Method 1:

We need run AWR report at periodic intervals. Please note down  "Per Second" value. Take the highest "Redo size" "Per Second" value from AWR report, and that is your peak redo generation rate.

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	0.7	0.1	0.00	0.00
DB CPU(s):	0.2	0.0	0.00	0.00
Redo size:	171,813.4	15,421.0
Logical reads:	6,451.7	579.1

 

Method 2:

Query v$sysmetric_history view to find redo generation per second.
select avg (value) from v$sysmetric_history where metric_name = 'Redo Generated Per Sec';

The formula used for calculation the network bandwidth is:

Required bandwith = ((Redo rate bytes per sec./0.7)*8) / 1,000,00


Example

Let us assume the redo rate is a 740 KB/sec.
Required bandwith = ((Redo rate bytes per sec./0.7)*8) / 1,000,000

Required bandwith = ((757760./0.7)*8) / 1,000,000
Required bandwith = 8.66 Mbps

Data Guard monitoring, primary and Standby databases management actions

When I start implement data guard, I find out some helpful hits from Oracle document. In Data Guard monitoring, primary database management actions and where to find information related to these actions.

Database action	Primary Database	Standby Database
Enable or disable a redo thread	Alert log V$THREAD	Alert log
Display database role, protection mode, protection level, switchover status, fast-start failover information, and so forth	V$DATABASE	V$DATABASE
Add or drop a redo log file group	Alert log V$LOG STATUS column of V$LOGFILE	Alert log
CREATE CONTROLFILE	Alert log	Alert log
Monitor Redo Apply	Alert log V$ARCHIVE_DEST_STATUS	Alert log V$ARCHIVED_LOG V$LOG_HISTORY V$MANAGED_STANDBY
Change tablespace status	V$RECOVER_FILE DBA_TABLESPACES Alert log	V$RECOVER_FILE DBA_TABLESPACES
Add or drop a datafile or tablespace	DBA_DATA_FILES Alert log	V$DATAFILE Alert log
Rename a datafile	V$DATAFILE Alert log	V$DATAFILE Alert log
Unlogged or unrecoverable operations	V$DATAFILE V$DATABASE	Alert log
Monitor redo transport	V$ARCHIVE_DEST_STATUS V$ARCHIVED_LOG V$ARCHIVE_DEST Alert log	V$ARCHIVED_LOG Alert log
Issue OPEN RESETLOGS or CLEAR UNARCHIVED LOGFILES statements	Alert log	Alert log
Change initialization parameter	Alert log	Alert log

Various memory management methods:

Memory Management Mode	For	You Set	Oracle Database Automatically Tunes
Automatic memory management (AMM)	SGA and PGA	Total memory target size for the Oracle instance (MEMORY_TARGET) (Optional) Maximum memory size for the Oracle instance (MEMORY_MAX_TARGET)	Total SGA size SGA component sizes Instance PGA size Individual PGA sizes
Automatic shared memory management (ASMM) (AMM disabled)	SGA	SGA target size (SGA_TARGET) (Optional) SGA maximum size (SGA_MAX_SIZE)	SGA component sizes
Manual shared memory management (AMM and ASMM disabled)	SGA	Shared pool size (SHARED_POOL_SIZE) Buffer cache size (DB_CACHE_SIZE or DB_BLOCK_BUFFERS) Java pool size (JAVA_POOL_SIZE) Large pool size (LARGE_POOL_SIZE) (Optional) SGA maximum size (SGA_MAX_SIZE)	None
Automatic PGA memory management	PGA	Instance PGA target size (PGA_AGGREGATE_TARGET)	Individual PGA sizes
Manual PGA memory management (Not recommended)	PGA	Maximum work area size for each type of SQL operator	None

Find out database size

Below query to use we can find  out database size.

For Non-RAC database:

select DATA.TOTAL/1024/1024 "DataFile Size Mb",
       LOG.TOTAL/1024/1024 "Redo Log Size Mb",
       CONTROL.TOTAL/1024/1024 "Control File Size Mb",
       (DATA.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1024/1024 "Total Size Mb" from dual,
        (select sum(a.bytes) TOTAL from dba_data_files a) DATA,
        (select sum(b.bytes) TOTAL from v$log b) LOG,
        (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

For RAC database:

select DATA.TOTAL/1024/1024 "DataFile Size Mb",
LOG.TOTAL/1024/1024 "Redo Log Size Mb",
CONTROL.TOTAL/1024/1024 "Control File Size Mb",
(DATA.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1024/1024 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DATA,
(select sum(b.bytes) TOTAL from gv$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

OS Specific Commands

To Find Swap, RAM, and OS Version

OS	SWAP	RAM	OS VERSION
AIX	/usr/sbin/lsps -a	/usr/sbin/lsattr -HE -l sys0 -a realmem	oslevel
HP PA-RISC	swapinfo -a	grep "Physical:" /var/adm/syslog/syslog.log	uname -a
HP Itanium	swapinfo -a	/usr/contrib/bin/machinfo | grep -i Memory	uname -a
Tru64	swapon -s	vmstat -P	/usr/sbin/sizer -v
Solaris	swap -l	/usr/sbin/prtconf | grep -i memory	uname -r
Linux	free	free	uname -a
Mac OS X	# df -h /	# /usr/sbin/system_profiler SPHardwareDataType | grep Memory	# sw_vers

To Determine the Unix Kernel Parameters

OS	LOCATION	COMMAND TO SEARCH
AIX	automatically configured	do 'env' for LINK_CNTRL, To determine if AIX is 64 bit enabled do 'genkex | grep 64' or 'genkex | grep call'
HP	/stand/system or use SAM -->Kernel Configuration	/etc/sysdef, /usr/sbin/kmtune (kmtune desupported in 11.31, use /usr/sbin/kctune -v), or /usr/sbin/kcweb -F
Tru64	/etc/sysconfigtab	/sbin/sysconfig -q ipc or /sbin/sysconfig -q vm or /sbin/sysconfig -q proc
Solaris	/etc/system	"/etc/sysdef | grep SHM" or "/etc/sysdef | grep SEM"
Linux	/usr/src/linux/include/asm/shmparam.h /usr/src/linux/include/linux/sem.h /proc/sys/kernel/sem /proc/sys/kernel/shmall /proc/sys/kernel/shmmax /proc/sys/kernel/shmmni	ipcs -lms
Mac OS X	/etc/sysctl.conf	"# /usr/sbin/sysctl -a | grep "

O/S Software Installed Packages and Patches

OS	COMMAND FOR PACKAGES	COMMAND FOR PATCHES
AIX	lslpp -w | grep -i "software title" (applies to APARs and PTFs)	/usr/sbin/instfix -ik patch number
HP	Prior to 11: /usr/sbin/swlist -lproduct PH\* hp-ux 11 and after: * /usr/sbin/swlist -l patch \*\.*,c=patch * /usr/contrib/bin/show_patches (from patch PHCO_19550)	/usr/sbin/swlist -l fileset | grep -i
Solaris	/bin/pkginfo -l | grep -i "software title"	/bin/showrev -p
Tru64	/usr/sbin/setld -i | grep -i "software title"	/usr/sbin/setld -i | more For patchkits: /usr/sbin/dupatch -track -type kit
Linux	To see if a particular RPM is installed (without the architectur): $ rpm -qa | grep "package name" To see if a particular RPM is installed (with the architectur): $ rpm -qa --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}\\n" | grep "package name" To see what RPM provided a particular object/library: $ rpm -q --whatprovides --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}\\n" "full path/library name" To see the contents listing of an RPM: $ rpm -ql "RPM name" For example: $ rpm -ql compat-libstdc++-33-3.2.3-47.3.ppc /usr/lib/libstdc++.so.5 /usr/lib/libstdc++.so.5.0.7
Mac OS X	to be supplied later	to be supplied later

To Check if an OS is 64 bit Capable or not

OS	COMMAND	RESULTS
Aix	lslpp -L | grep 64bit	It should return "bos.64bit"
HP	getconf KERNEL_BITS	It should return "64"
Solaris	/bin/isainfo -kv	If the isainfo command does not exist it is not 64-bit. It should return "64-bit sparcv9 kernel modules"