Oracle memory leak info

How to check if oracle instance is leaking memory

Oracle memory leak info


col name format a20
select sid, substr(name,1,30) name , value , value/(1024*1024) "MB"
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and name like '%a memory'
order by sid, name;

set linesize 200
col name format a20
col MACHINE format a15
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10
col SCHEMANAME format a10
col OSUSER format a10

select b.sid, substr(name,1,30) name, value , value/(1024*1024) "MB", c.serial#,c.status,c.username,c.schemaname,c.osuser,c.machine,
-- c.terminal,
c.program,c.module,state,logon_time
from v$statname a, v$sesstat b,v$session c
where a.statistic# = b.statistic#
and name like '%a memory'
and b.sid=c.sid
and osuser!='oracle'
order by status,MB desc,sid, name;




set linesize 200
col name format a20
col MACHINE format a15
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10
col SCHEMANAME format a10
col OSUSER format a10

select b.sid, substr(name,1,30), value , value/(1024*1024) "MB",s.sid ssid, s.serial#,
-- ,s.sid ssid,
s.status,s.username,s.schemaname,s.osuser,s.machine,
-- s.terminal,
--s.program,
s.module,state,logon_time,substr(p.spid,1,8) spid,
--substr(sa.sql_text,1,2000) txt
sa.sql_id
from v$statname a, v$sesstat b,v$session s, v$process p ,v$sqlarea sa
where a.statistic# = b.statistic#
and name like '%a memory'
and b.sid=s.sid
and osuser!='oracle'
and p.addr = s.paddr
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
order by status,MB desc,sid, name;


set long 4000
select sql_text from v$sqlarea where sql_id='&1'

oracle start up shutdown script on linux / unix

Oracle start up automatic script

Oracle start up shutdown script

Oracle start up automatically at the time of boot

Oracle Database start automatic on Linux

Automating Database Start up and Shutdown on Linux


1. Change in the oratab file from N to Y


oracle@apt-lnxtst-01:/opt/oracle/product/10.2/bin> cat /etc/oratab





# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/opt/oracle/product/10.2:N <<----- change N to Y





apt-lnxtst-01:/etc/init.d # ls -ltr /etc/oratab
-rw-rw-r-- 1 oracle root 710 2011-04-20 08:38 /etc/oratab




2.

as a root

cd /etc/init.d/

apt-lnxtst-01:/etc/init.d # cat dbora ----- create this file as a root

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.


# change the value as per your home -------------<<<<<<<<<<<
ORA_HOME=/opt/oracle/product/10.2

ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi

case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
touch /var/lock/subsys/dbora
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
rm -f /var/lock/subsys/dbora
;;
esac



apt-lnxtst-01:/etc/init.d # ls -ltr dbora
-rw-r--r-- 1 root root 1045 2011-04-20 08:49 dbora


3 .

Change the prmission of this file

apt-lnxtst-01:/etc/init.d # chmod 750 /etc/init.d/dbora



4.

apt-lnxtst-01:/etc/init.d # chkconfig --add dbora
dbora 0:off 1:off 2:off 3:on 4:off 5:on 6:off

or


Linux

# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

rpm info

Software Management with RPM
Quick Tips

rpm
-qa <-- list all packages (a)
-qi < --list package information (i)
-ql <-- list package files (l)
-qf <-- list package that owns the file (ex. rpm -qf /etc/inittab)
-V <-- file Verification
-ivh <-- new install
-Uvh <-- Update -F <--Freshing. Package willl be updated ONLY if already installed else ignored.
-E < --Erase

Unix Cron format

Cron format - crontab

crontab -l


1. minute (from 0 to 59)
2. hour (from 0 to 23)
3. day of month (from 1 to 31)
4. month (from 1 to 12)
5. day of week (from 0 to 6) (0=Sunday)


1  2  3  4  5  6
*  *  *  *  *  *
|  |  |  |  |  | 
|  |  |  |  |  +-- Year              (range: 1900-3000)
|  |  |  |  |
|  |  |  |  +---- Day of the Week    (range: 1-7, 1 standing for Monday)
|  |  |  |
|  |  |  +------ Month of the Year   (range: 1-12)
|  |  |
|  |  +-------- Day of the Month     (range: 1-31)
|  |
|  +---------- Hour                  (range: 0-23)
|
+------------ Minute                 (range: 0-59)



* * * * * /xxx/xxx/xx/anuj.sh


if 5 stars then



1. every minute
2. of every hour
3. of every day of the month
4. of every month
5. and every day in the week.



Execute every Friday 1AM

So if we want to schedule script to run at 1AM every Friday,


0 1 * * 5 /xxx/xxx/xx/anuj.sh

The script is now being executed when the system clock hits:

1. minute: 0
2. of hour: 1
3. of day of month: * (every day of month)
4. of month: * (every month)
5. and weekday: 5 (=Friday)
=================================================================

##┌───────────── minute (0 - 59)
# │ ┌───────────── hour (0 - 23)
# │ │ ┌───────────── day of month (1 - 31)
# │ │ │ ┌───────────── month (1 - 12)
##┌───────────── minute (0 - 59)
# │ ┌───────────── hour (0 - 23)
# │ │ ┌───────────── day of month (1 - 31)
# │ │ │ ┌───────────── month (1 - 12)
# │ │ │ │ ┌───────────── day of week (0 - 6) (Sunday to Saturday;
# │ │ │ │ │                                       7 is also Sunday)
# │ │ │ │ │
# │ │ │ │ │
# * * * * *  command to execute

======================================================================

Execute on Mon to friday


So if we want to schedule the script to Monday till Friday at 1 AM


0 1 * * 1-5 /bin/execute/this/script.sh

The script is now being executed when the system clock hits:

1. minute: 0
2. of hour: 1
3. of day of month: * (every day of month)
4. of month: * (every month)
5. and weekday: 1-5 (=Monday til Friday)



# Minute   Hour   Day of Month       Month          Day of Week        Command    
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)                
    0        2          12             *                *            /usr/bin/find




Examples:

*/30 * * * * /aptus/oracle/admin/aptdb/script/check_oracle_tablespace.sh -c aptdb -w 90 -c95 > /tmp/tablespace.nagios

5 5 * * * /aptus/oracle/admin/aptdb/script/scheduler.sh > /dev/null 2>&1

# 5 6 * * * /aptus/oracle/admin/aptdb/script/exp_solomon.sh > /dev/null 2>&1



Standard output (STDOUT) and standard errors (STDERR).

STDOUT is marked 1, STDERR is marked 2.

So the following statement tells Linux to store STDERR in STDOUT as well, creating one datastream for messages & errors:

2>&1


every hr ....


***-amd-02:/home/anujs/Downloads # crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.XXXXwcdEfa installed on Tue Sep 7 10:10:07 2010)
# (Cron version V5.0 -- $Id: crontab.c,v 1.12 2004/01/23 18:56:42 vixie Exp $)

59 * * * * echo " ">/var/log/warn > /dev/null 2>&1

59 * * * * echo " ">/var/log/messages > /dev/null 2>&1



cron and email confirmation .....

15 22 * * * /xxxx/oracle/admin/xxdb/scripts/Rmanbackup.sh 2>&1 | mail -s "RMAN BACKUP On (xxx-xxx-01) completed... " anuj.singh@xxxx.co.uk

RMAN Backup Report for SCN for Recovery

oracle@novagenesis$ rman target / log=rmanbackupinfo.log
RMAN> list backup ;

RMAN> list backup;

RMAN>exit;


cat rmanbackupinfo.log this will give all the SCN no of and valid backup info


This will provide last SCN number

SQL> set numwidth 18
SQL>select max(CHECKPOINT_CHANGE#) from v$backup_datafile;

MAX(CHECKPOINT_CHANGE#)
-----------------------
121937781

unix kill all process

unix kill process
awk kill


hosea:~ # ps -ef | grep cron | awk '{print $2}'|wc -l
3342

kill -9 `ps -ef | grep cron |grep -v grep | awk '{print $2}'`

Oracle ldap clear entry Flush LDAP Cache

Flushing LDAP Cache information for group
Flushing LDAP Cache information for passwd


uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall),6002(dba)
jtime76@apt-lnxtst-01:~> logout
apt-lnxtst-01:~ # nscd -i passwd
apt-lnxtst-01:~ # su - jtime76
jtime76@apt-lnxtst-01:~> id
uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall),6002(dba)
jtime76@apt-lnxtst-01:~> logout
apt-lnxtst-01:~ # nscd -i passwd
apt-lnxtst-01:~ # vi /etc/nscd.conf
apt-lnxtst-01:~ # /etc/init.d/nscd stop
Shutting down Name Service Cache Daemon done
apt-lnxtst-01:~ # /etc/init.d/nscd start
Starting Name Service Cache Daemon done

changed the value in ldap browser ( removed the group from



/ldapbrowser> ./lbe.sh








apt-lnxtst-01:~ # su - jtime76
jtime76@apt-lnxtst-01:~> id
uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall),6002(dba) ---- dba group is still there
jtime76@apt-lnxtst-01:~> logout
apt-lnxtst-01:~ # nscd -i group -------- then flush the group information using this command
apt-lnxtst-01:~ # nscd -i passwd -------- if you want to flush user information
apt-lnxtst-01:~ # su - jtime76
jtime76@apt-lnxtst-01:~> id
uid=7011(jtime76) gid=100(users) groups=100(users),6001(oinstall)