MEMORY_TARGET in Oracle 11g

It is amazing memory management in Oracle 11g. Upto Oracle 9i one of the hectic process was to define the memory parameters (SGA and PGA) and there are some thumb role needs to followed for the each parameters. Now DBA sit back and relax since Oracle automated the Memory parameter completely on 11g.

Some lights on old versions:

Oracle 8i: –
db_block_buffer
shared_pool_size
large_pool_size
java_pool_size
sort_area_size
pga size,etc........

Oracle 9i: - introduced PGA_AGGREGATE_TARGET.

Oracle 10g: - Continued this era by automating SGA management using the SGA_TARGET and PGA_AGGREGATE_TARGET parameter.

Oracle 11g: - Fully automated using MEMORY_TARGET

Oracle 10g onwards this parameter automated, but again there was some sort of calculation for 2 parameters called sga_target and pga_aggregate_target. Now, you can take long breath, leave it without any difficult, because 11g all these two parameters auto tuned.

In Oracle 10g setting the sga_target, I have seen many bugs. Some people use the workaround as normal sga setting.

Automatic Memory Management (AMM) is only supported on the major platforms like AIX,Linux, Solaris, Windows, and HP-UX.

There are 2 initialization parameters for AMM confugration:

MEMORY_MAX_TARGET: - The maximum size the memor_target can be increased to without an instance restart (like earlier version sga_max_size). If the memory_max_target is not specified, it defaults to MEMORY_TARGET setting.

Using AMM, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas.

MEMORY_TARGET: - Elaborating this parameter that Shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit.

Automatic Memory Management Setup: -
The DBCA is also allows you to configure automatic memory management during database creation.

We can manually set, the appropriate MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters before creating the database or after.

Enabling automatic memory management on a system that didn't previously use it is a simple task.

Kindly use the following calculations:

MEMORY_TARGET = SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, "maximum PGA allocated")

Assuming our required setting was 4G, we might issue the following statements.

$sqlplus “/as sysdba”

Set the static parameter. Leave some room for possible future growth without restart.

ALTER SYSTEM SET MEMORY_MAX_TARGET=5G SCOPE=SPFILE;

Set the dynamic parameters. Assuming Oracle has full control.

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;

Shutdown and restart the instance: -
SHUTDOWN IMMEDIATE;
STARTUP;

The database is restarted the MEMORY_TARGET parameter can be amended as required without an instance restart.

ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;

Dictionary View Informations: -

V$MEMORY_TARGET_ADVICE
V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_RESIZE_OPS
V$MEMORY_DYNAMIC_COMPONENTS

You can also, configure and get lot of information through ENERPRISE

MANAGER, using graphics,etc.

Oracle 11g Installation on Linux 5

This articles covers the Installation of Oracle Database 11g on Enterprise Linux 5 and CentOS 5. Server needs minimum 2gb RAM for the installation. Swap should have double of physical ram.
Set the Linux Kernal Parameters:
Linux Kernel Parameters
What is the Shared Memory:
Shared memory allows processes to access common structures and data by placing them in shared memory segments. It's the fastest form of IPC (Interprocess Communication) available since no kernel involvement occurs when data is passed between the processes. In fact, data does not need to be copied between the processes.
Oracle uses shared memory segments for the SGA (Shared Global Area) which is an area of memory that is shared by all Oracle background and foreground processes. The size of the SGA has a major impact to Oracle's performance since it holds database buffer cache and much more.
Setting SHMMAX Parameter
This parameter defines the maximum size in bytes for a shared memory segment. Since the SGA is comprised of shared memory, SHMMAX can potentially limit the size of the SGA.
Setting SHMMNI Parameter
This parameter sets the maximum number of shared memory segments system wide.
Setting SHMALL Parameter
This parameter sets the total amount of shared memory in bytes that can be used at one time on the system.
The SEMMSL Parameter
This parameter defines the maximum number of semaphores per semaphore set.
Oracle recommends to set SEMMSL to the largest PROCESSES init.ora parameter of any database on the Linux system.
The SEMMNI Parameter
This parameter defines the maximum number of semaphore sets in the entire Linux system.
The SEMMNS Parameter
This parameter defines the total number of semaphores (not semaphore set) in the entire Linux system.
The SEMOPM Parameter
This parameter defines the maximum number of semaphore operations that can be performed per semop(2) system call.
Setting File Handles
The maximum number of file handles denotes the maximum number of open files that you can have on the Linux system.
Setting System Wide Limit for File Handles
The value in /proc/sys/fs/file-max sets the maximum number of file handles or open files that the Linux kernel will allocate. When you get error messages about running out of file handles, then you might want to raise this limit.
Change the parameters:

#vi /etc/sysctl.conf
Add the following:
kernel.shmmax = 2147483648
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536 # 512 * PROCESSES
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=262144
net.ipv4.ip_local_port_range = 1024 65000

#vi /etc/hosts
IP-address Machinename.domainname Hostname

#vi /etc/security/limits.conf
Add or change /etc/security/limits.conf file:
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384

#vi /etc/pam.d/login
session required pam_limits.so
session required /lib/security/pam_limits.so
Very important Steps:
On secure linux has to be disabled:

#vi /etc/selinux/config
Change or add
SELINUX=disabled

The following packages(rpm) required for Enterprise Linux Disk1 :
Insert the 1st cd and mount the same: -
Go to cd /media/cdrom/Server (cd /media/cdrom/Server)
rpm -Uvh binutils-2.*
rpm -Uvh elfutils-libelf-0.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh libaio-0.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh make-3.*
cd /
eject

Insert Disk2 Cd. Mount the CD: -
Go to cd /media/cdrom/Server (cd /media/cdrom/Server)
rpm -Uvh compat-libstdc++-33*
rpm -Uvh elfutils-libelf-devel-0.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh unixODBC-2.*
rpm -Uvh unixODBC-devel-2.*
cd /
eject

Insert Disk3 Cd. Mount the CD: -
Go to cd /media/cdrom/Server (cd /media/cdrom/Server)
rpm -Uvh sysstat-7.*
cd /
eject

Create user and groups: -
Adding Groups: -
groupadd oinstall
groupadd dba
groupadd asmadmin

Adding user and assign the group: -
useradd -g oinstall -G dba,oper,asmadmin oracle

Add password for Oracle user
#passwd oracle

I would like to light on the "asmadmin" group is used for ASM.
Make directories in which the Oracle Binary to be installed:

#mkdir -p /data1/app/oracle/product/11/db_1
#chown -R oracle:oinstall /data1
#chmod -R 777 /data1

#vi /home/oracle.bash_profile
Add the following: -
ORACLE_HOSTNAME=machinename.domainname
ORACLE_BASE=/data1/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

Login as Oracle user: -
Run Install Steps:
Set the Display first
$export DISPLAY=hostname:0.0
Go setup Disk directory
$cd Disk1
$./runInstaller
Rest follow the GUI provided by Oracle

How to kill locking session: -

1) Creating .sh file to automate the job through crontab: -
kill.sh: -
# .bash_profile
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
PATH=${PATH}:$ORACLE_HOME/bin/ export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

cd $ORACLE_HOME/kill
sqlplus system/fopsdb2008@fopsfat1 @/u01/app/oracle/product/10.2.0/db_1/kill/kill.sql
exit

2) Creating .sql file to get the locked session ID in OS level: -
kill.sql: -

set heading off
set echo off
spool /u01/app/oracle/product/10.2.0/db_1/kill/kill_in.sh
select 'kill -9 '|| vp.spid from v$session vs, v$process vp
where vs.paddr = vp.addr
and vs.sid in (select s1.sid from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1 and l2.id2 = l2.id2);
spool off

select name from v$database;
!chmod 777 /u01/app/oracle/product/10.2.0/db_1/kill/kill_in.sh
!export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
!cd /u01/app/oracle/product/10.2.0/db_1/kill
!pwd
!./kill_in.sh
Exit

3) Making an entry to automate this job every 1 minute: -
Crontab -e: -
* * * * * /u01/app/oracle/product/10.2.0/db_1/kill/kill.sh > /dev/null 2>&1

Creating Synonyms and Granting permissions to another user: -

CREATE OR REPLACE PROCEDURE CREATE_SYNONYMS_GRANTS(P_Owners varchar2) IS
cursor obj_crs is
select owner, object_name, object_type
from all_objects
where owner = upper(P_Owners)
and upper(object_type) in ('TABLE','VIEW','PROCEDURE','FUNCTION','SEQUENCE','SYNONYM','TYPE','PACKAGE');
obj_REC obj_CRS%ROWTYPE;
l_create_synonyms varchar2(500);
l_grants_objects varchar2(500);
BEGIN
FOR obj_REC IN obj_CRS LOOP
begin
--dbms_output.put_line(obj_REC.object_type);
l_create_synonyms := 'create synonym '||obj_REC.owner||'_USER.'||obj_REC.object_name||' for '||obj_REC.owner||'.'||obj_REC.object_name;
dbms_output.put_line(l_create_synonyms||';');
EXECUTE IMMEDIATE l_create_synonyms;
Exception
when others then
dbms_output.put_line(SQLERRM);
end;

begin
CASE obj_REC.object_type
WHEN 'TABLE' THEN l_grants_objects := 'grant select, update, delete, insert on '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'VIEW' THEN l_grants_objects := 'grant select, update, delete, insert on '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'PROCEDURE' THEN l_grants_objects := 'GRANT EXECUTE ON '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'FUNCTION' THEN l_grants_objects := 'GRANT EXECUTE ON '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'PACKAGE' THEN l_grants_objects := 'GRANT EXECUTE ON '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'SEQUENCE' THEN l_grants_objects := 'GRANT SELECT, ALTER ON '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'TYPE' THEN l_grants_objects := 'GRANT EXECUTE ON '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';

END CASE;
dbms_output.put_line(l_grants_objects||';');
EXECUTE IMMEDIATE l_grants_objects;
Exception
when others then
dbms_output.put_line(SQLERRM);
end;
end loop;
End;
/

Ex: -
exec CREATE_SYNONYMS_GRANTS ('OBJECTS_OWNER_NAME');

Crontab - Quick reference :

Setting up cronjobs in Unix and Solaris

Cron is a unix, solaris utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix , solaris.

Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.

1. Crontab Restrictions

You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use

crontab if your name does not appear in the file /usr/lib/cron/cron.deny.

If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.

2. Crontab Commands

export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e Edit your crontab file, or create one if it doesn't already exist.

crontab -l Display your crontab file.

crontab -r Remove your crontab file.

crontab -v Display the last time you edited your crontab file. (This option is only available on a few systems.)

3. Crontab file

Crontab syntax :-

A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (Sunday=0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)

* in the value field above means all legal values as in braces for that column.

The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).

Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

4. Crontab Example

A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.

30 18 * * * rm /home/someuser/tmp/*

Changing the parameter values as below will cause this command to run at different time schedule below :

30 0 1 1,6,12 * -- 00:30 Hrs on 1st of Jan, June & Dec.

0 20 * 10 1-5 --8.00 PM every weekday (Mon-Fri) only in Oct.

0 0 1,10,15 * * -- midnight on 1st ,10th & 15th of month

5,10 0 10 * 1 -- At 12.05,12.10 every Monday & on 10th of every month

Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

5. Crontab Environment

Cron invokes the command from the user's HOME directory with the shell, (/usr/bin/sh).

cron supplies a default environment for every shell, defining:

HOME=user's-home-directory

LOGNAME=user's-login-id

PATH=/usr/bin:/usr/sbin:.

SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

6. Disable Email

By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .

>/dev/null 2>&1

7. Generate log file

To collect the cron execution execution log in a file :

30 18 * * * rm /home/someuser/tmp/* > /home/someuser/cronlogs/clean_tmp_dir.log

8. Next Steps

This article covered a significant aspect of system administration of setting up cronjobs . Unix administration involves lots of different tasks and some of these tasks are covered in this website but still there are many areas not covered here .

Following books available for online buying from Amazon.com . You should have following two books in your bookshelf for ready reference if you are involved in Unix system administration .

Essential System Administration, Third Edition by by Æleen Frisch

Solaris Operating Environment Boot Camp by David Rhodes, Dominic Butler

If you already own these books the amazon display panel below shows some of the best-selling books for System Administration and you can choose the book here or visit my other website for more selection of best selling unix system administration books by following this link - .Unix system administration books at besttechbooks.com ,from Amazon.com.

You can show your appreciation by buying the books for yourself and encouraging the friends to buy using amazon links below or anywhere at adminschoice.com or besttechbooks.com . Thanks for your appreciation in advance.