Step by step - How to upgrade from 11.2.0.3 to 11.2.0.4 Grid and RDBMS in RAC & Data Guard Environment

This document is for upgrading databases in RAC and dataguard environment. In this upgrade, Grid and RDBMS home is upgraded. This upgrade is done by out-of-place approach(new home for both GI and DB). There are basically 6 steps to complete this requirement:

Step 1: Prerequisite checks and backups

Step 2: New Grid Binary Installation:

            - Fill in the response file
            - Run the prerequisite checks
            - Install the new GI binary

Step 3: Manual steps performed after Grid binary installation to upgrade the Grid.

Step 4: New RDBMS installation

           -Fill in the response file
           -Run the prerequisite checks
           -Install the new RDBMS binary

Step 5: Manual steps performed after new binary installation to upgrade the RDBMS

Step 6: Upgrade the database on standby side.

Now we will move step by step in detail:

Step 1: Prerequisite checks and backups

1) Check storage space for the new binary to be installed. We must have around 20 GB of space         available for each homes(Grid and RDBMS) on each node.
Also issue df -kh and make sure we space available in /tmp and /(root).

2) Remove the audit files and any large log files on each node(not deleting will slowdown the upgrade).

$cd $GRID_HOME/rdbms/audit
$rm *.aud

3) Take full database backup using RMAN and also take full database export.

4) Backup of Grid and RDBMS binaries using tar.

5) Backup of Oracle Inventory

6) Backup of OCR on each nodes.

7) Backup of ASM metadata.

8) Secure dataguard.

Step 2: New Grid home installation:

1) Fill in the response file for the new binary home. Edit the response file according to the requirement.

2) Run the installer to install the GRID using this response file.

Step 3: Grid Upgrade:

Manual Grid upgrade step after new binary installation:

1) On each node, copy listener.ora and sqlnet.ora to new Grid home(11.2.0.4).

2) If necessary, adjust existing ASM instance memory parameter:

     Log in as sysasm:
     SQL>show parameter memory_target

     if the value is smaller than 1536 m, then issue the following command:

     SQL>alter system set memory_target=1536m scope=spfile;

    The number 1536m has proven to be efficient in most environment, the change will not be     effective util next restart.

    SQL>show parameter memory_max_target

   If the value is smaller than 4096, then issue the following command:

   SQL>alter system set memory_max_target=4096 scope=spfile;

   The number 4096m has proven to be sufficient in most environment, the change will not be effective until next restart.

3) Stop clusterware and database resources gracefully on each node.

    #crsctl stop crs

    This command will bring down the database so no need to shutdown the database exclusively.

    Make sure all CRS deamons are sto resopped on each node:

  #crsctl stat resource -t

4) On each node, execute rootupgrade.sh script from new 11.2.0.4 home to upgrade clusterware.

    The rootupgrade.sh script can only be run sequentially node by node, and not in parallel between nodes.

  Note: This script takes 15-20 minutes to complete.

While switching to root user to execute rootupgrade.sh, "su -" or "su - root" provides the full root environment. While sudo, pbrun,"su root" or "su" or similar facilities don't. It is recommended to execute rootupgrade.sh with full root acces to avoid any issue.

5) Once the Grid upgrade script finishes, all the clusterware and database services automatically comes up. If not then manually start it.

6) Verify and cluster and database services on each node.

     #crsctl check crs
     #crsctl stat resource -t
     #ocrcheck -local
     $srvctl status database -d dbname

7) After all the nodes are upgraded, update environment variables to point to new home 11.2.0.4

    Below files needs to be updated:

    1) .bash_profile
    2) /etc/oratab

Note: Once the grid is installed and upgraded in the primary servers, do the grid installation and upgrade on the standby side too. Otherwise it will not allow to install new 11.2.0.4 RDBMS home on standby side.

Step 4: New RDBMS Home Installation

Like grid installation, first thing we need to do is to prepare the response file. Fill all the necessary parameter which are required to install the new home. And then run the installer using this response file. Install new RDBMS(11.2.0.4) on standby also.

Step 5: RDBMS Upgrade

1) Before we start database upgrade, we need to create the restore point.

    SQL>CREATE RESTORE POINT before_upgrade GURANTEE FLASHBACK DATABASE;

2)Copy the parameter file, password file, tnsnames.ora and sqlnet.ora to the new RDBMS home. For      RAC copy on all the nodes.

3) Count invalid object and make a note of it.

4) Check the space in system tablespace.

5) For RAC only, change the instance to exclusive mode(i.e. cluster_database=false)

6) Gather dictionary stats from existing binary home.

      SQL>execute dbms_stats.gather_dictionary_stats;

7) Execute the following pre-check script from the existing home to check instance fulfill pre-upgrade requirement.

    SQL>@/u01/sql/ora_2db/11.2.0.4/rdbms/admin/utlu112i.sql

Fix all the errors which is reported by this script before proceeding to the actual upgrade.
For RAC this step only needs to be executed only once, not from all the nodes.

8) Startup instance with startup upgrade option in SQLPLUS with new oracle home(11.2.0.4).
     For RAC this step needs to be executed from one node only(not from all nodes). And then run the       upgrade script.

     SQL>startup upgrade
   
     SQL>@/u01/sq/ora_2/db/11.2.0.4/rdbms/admin/catupgrd.sql

9) Then restart the instance and run the following sql script:

     SQL>shut immediate

     SQL>startup

     SQL>@/u01/sq/ora_2/db/11.2.0.4/rdbms/admin/catuppst.sql

    (For RAC, this script needs to be executed only from one node)

10) Run the post-upgrade check script from new home.

      SQL>@/u01/sq/ora_2/db/11.2.0.4/rdbms/admin/utlu112s.sql

      (For RAC, this script needs to be executed from only one node)

11) Recompile objects and compile the object list to ensure no new invalid objects

      SQL>@?/rdbms/admin/utluiobj.sql

      SQL>@?/rdbms/admin/utlrp.sql

      SQL>@?/rdbms/admin/utluiobj.sql

     (for RAC, this script needs to be executed only from one node)

12) For RAC, fallback instance from exclusive mode(cluster_database=true)

13) Update database resources in CRS registry:
   
       $srvctl upgrade database -d dbname -o /u01/sq/ora_2/db/11.2.0.4

14) Startup the remaining instances of the database.

      $srvctl start instance -d dbname -i instance_name

15) After database is upgraded, update the environment variable so that it points to 11.2.0.4 in the              below files:

       /etc/oratab for oracle user
       .bash_profile for oracle user
       listener.ora file

16) If there is oracle valet then add VNCR parameter in the listener.ora file.
      (for RAC, it applies to all nodes).


17) Shutdown and restart the database.

18) Ask application team to verify everything is running fine for them.

19) Drop restore point befor_upgrade.


STEP 6 - UPGRADE THE DATABASE ON STANDBY SIDE

Perform these steps only if there is a physical standby database associated with the database being upgraded.

1) Copy below file from 11.2.0.3 home to new home 11.2.0.4:

      tnsnames.ora
      sqlnet.ora
      parameter file
      password file

2) Shutdown the primary database as follows:

    $srvctl stop database -d DBNAME -o immediate

3) Shutdown the standby database as follows:

   $srvctl stop database -d DBNAME

4) Edit the /etc/oratab and listener.ora file to point to the new 11.2.0.4 home

5) Update the OCR configuration

   $srvctl upgrade database -d DBNAME -o /u01/sq/ora_2/db/11.2.0.4

6) Start the standby database in mount mode from new home 11.2.0.4

    $srvctl start database -d DBNAME -o mount

7) Start the primary database in open mode

    $srvctl start database -d DBNAME

8) Start the redo apply on standby database

    SQL>alter database recover managed standby database disconnect from session

9)  Verify the standby alert log file and make sure logs are shipping properly, also check the archive
      gap.


Suggestions are most welcome :)

Script to find archivelog generation per hour

set pagesize 120;
set linesize 200;
col day for a8;
spool archivelog.lst

PROMPT Archive log distribution per hours on each day …
  

select
  to_char(first_time,’YY-MM-DD’) day,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’00’,1,0)),’999′) “00”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’01’,1,0)),’999′) “01”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’02’,1,0)),’999′) “02”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’03’,1,0)),’999′) “03”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’04’,1,0)),’999′) “04”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’05’,1,0)),’999′) “05”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’06’,1,0)),’999′) “06”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’07’,1,0)),’999′) “07”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’08’,1,0)),’999′) “08”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’09’,1,0)),’999′) “09”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’10’,1,0)),’999′) “10”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’11’,1,0)),’999′) “11”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’12’,1,0)),’999′) “12”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’13’,1,0)),’999′) “13”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’14’,1,0)),’999′) “14”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’15’,1,0)),’999′) “15”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’16’,1,0)),’999′) “16”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’17’,1,0)),’999′) “17”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’18’,1,0)),’999′) “18”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’19’,1,0)),’999′) “19”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’20’,1,0)),’999′) “20”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’21’,1,0)),’999′) “21”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’22’,1,0)),’999′) “22”,
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’23’,1,0)),’999′) “23”,
  COUNT(*) TOT
from v$log_history
group by to_char(first_time,’YY-MM-DD’)
order by day ;

Script to identify segments generating redologs

SELECT to_char(begin_interval_time,’YY-MM-DD HH24′) snap_time, 
        dhso.object_name, 
        sum(db_block_changes_delta) BLOCK_CHANGED 
  FROM dba_hist_seg_stat dhss, 
       dba_hist_seg_stat_obj dhso, 
       dba_hist_snapshot dhs 
  WHERE dhs.snap_id = dhss.snap_id 
    AND dhs.instance_number = dhss.instance_number 
    AND dhss.obj# = dhso.obj# 
    AND dhss.dataobj# = dhso.dataobj# 
    AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)  
                                AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’) 
  GROUP BY to_char(begin_interval_time,’YY-MM-DD HH24′), 
           dhso.object_name 
  HAVING sum(db_block_changes_delta) > 0 
ORDER BY sum(db_block_changes_delta) desc ;

Script to know which SQL’s are generating redo

SELECT when, sql, SUM(sx) executions, sum (sd) rows_processed 
FROM ( 
      SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24′) when, 
             dbms_lob.substr(sql_text,4000,1) sql, 
             dhss.instance_number inst_id, 
             dhss.sql_id, 
             sum(executions_delta) exec_delta, 
             sum(rows_processed_delta) rows_proc_delta 
        FROM dba_hist_sqlstat dhss, 
             dba_hist_snapshot dhs, 
             dba_hist_sqltext dhst 
        WHERE upper(dhst.sql_text) LIKE ‘%Z_PLACENO%’ 
          AND ltrim(upper(dhst.sql_text)) NOT LIKE ‘SELECT%’
          AND dhss.snap_id=dhs.snap_id 
          AND dhss.instance_Number=dhs.instance_number 
          AND dhss.sql_id = dhst.sql_id  
          AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)  
                                      AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’) 
        GROUP BY to_char(begin_interval_time,’YYYY_MM_DD HH24′), 
            dbms_lob.substr(sql_text,4000,1), 
              dhss.instance_number, 
             dhss.sql_id 
) 
group by when, sql;

Script to find PGA memory allocation to BG processes

*********************************************

PGA Memory allocation to background process

*********************************************

SELECT spid, program,

pga_max_mem max,

pga_alloc_mem alloc,

pga_used_mem used,

pga_freeable_mem free

FROM V$PROCESS

WHERE spid = 2587;

Script to find memory usage by BG processes

**************************************

Memory usage for backgroung processes

**************************************

SELECT p.program,

p.spid,

pm.category,

pm.allocated,

pm.used,

pm.max_allocated

FROM V$PROCESS p, V$PROCESS_MEMORY pm

WHERE p.pid = pm.pid

AND p.spid = 2587;

Script to show active distributed tx’s in database

***************************************

script to show active distributed tx’s

***************************************

REM distri.sql

column origin format a13

column GTXID format a35

column LSESSION format a10

column s format a1

column waiting format a15

Select /*+ ORDERED */

substr(s.ksusemnm,1,10)||’-‘|| substr(s.ksusepid,1,10) “ORIGIN”,

substr(g.K2GTITID_ORA,1,35) “GTXID”,

substr(s.indx,1,4)||’.’|| substr(s.ksuseser,1,5) “LSESSION” ,

substr(decode(bitand(ksuseidl,11),

1,’ACTIVE’,

0, decode(bitand(ksuseflg,4096),0,’INACTIVE’,’CACHED’),

2,’SNIPED’,

3,’SNIPED’, ‘KILLED’),1,1) “S”,

substr(event,1,10) “WAITING”

from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w

— where g.K2GTeXCB =t.ktcxbxba <= use this if running in Oracle7

where g.K2GTDXCB =t.ktcxbxba — comment out if running in Oracle8 or later

and g.K2GTDSES=t.ktcxbses

and s.addr=g.K2GTDSES

and w.sid=s.indx;

REM distri_details.sql

set headin off

select /*+ ORDERED */

‘—————————————-‘||’

Curent Time : ‘|| substr(to_char(sysdate,’dd-Mon-YYYY HH24.MI.SS’),1,22) ||’

‘||’GTXID=’||substr(g.K2GTITID_EXT,1,10) ||’

‘||’Ascii GTXID=’||g.K2GTITID_ORA ||’

‘||’Branch= ‘||g.K2GTIBID ||’

Client Process ID is ‘|| substr(s.ksusepid,1,10)||’

running in machine : ‘||substr(s.ksusemnm,1,80)||’

Local TX Id =’||substr(t.KXIDUSN||’.’||t.kXIDSLT||’.’||t.kXIDSQN,1,10) ||’

Local Session SID.SERIAL =’||substr(s.indx,1,4)||’.’|| s.ksuseser ||’

is : ‘||decode(bitand(ksuseidl,11),1,’ACTIVE’,0,

decode(bitand(ksuseflg,4096),0,’INACTIVE’,’CACHED’),

2,’SNIPED’,3,’SNIPED’, ‘KILLED’) ||

‘ and ‘|| substr(STATE,1,9)||

‘ since ‘|| to_char(SECONDS_IN_WAIT,’9999′)||’ seconds’ ||’

Wait Event is :’||’

‘|| substr(event,1,30)||’ ‘||p1text||’=’||p1

||’,’||p2text||’=’||p2

||’,’||p3text||’=’||p3 ||’

Waited ‘||to_char(SEQ#,’99999′)||’ times ‘||’

Server for this session:’ ||decode(s.ksspatyp,1,’Dedicated Server’,

2,’Shared Server’,3,

‘PSE’,’None’) “Server”

from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w

— where g.K2GTeXCB =t.ktcxbxba <= use this if running Oracle7

where g.K2GTDXCB =t.ktcxbxba — comment out if running Oracle8 or later

and g.K2GTDSES=t.ktcxbses

and s.addr=g.K2GTDSES

and w.sid=s.indx;

set headin on

— end script

Query to find Active SQL’s in database

set feedback off

set serveroutput on size 9999

column username format a20

column sql_text format a55 word_wrapped

begin

for x in

(select username||'(‘||sid||’,’||serial#||’) ospid = ‘|| process ||

‘ program = ‘ || program username,

to_char(LOGON_TIME,’ Day HH24:MI’) logon_time,

to_char(sysdate,’ Day HH24:MI’) current_time,

sql_address,

sql_hash_value

from v$session

where status = ‘ACTIVE’

and rawtohex(sql_address) <> ’00’

and username is not null ) loop

for y in (select sql_text

from v$sqlarea

where address = x.sql_address ) loop

if ( y.sql_text not like ‘%listener.get_cmd%’ and

y.sql_text not like ‘%RAWTOHEX(SQL_ADDRESS)%’ ) then

dbms_output.put_line( ‘——————–‘ );

dbms_output.put_line( x.username );

dbms_output.put_line( x.logon_time || ‘ ‘ || x.current_time || ‘ SQL#=’ || x.sql_hash_value);

dbms_output.put_line( substr( y.sql_text, 1, 250 ) );

end if;

end loop;

end loop;

end;

/

Script to find redo generated by current sessions

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

redo generated by current sessions

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

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null

order by value

/

How to find in which datafile object resides

Lets say i created a new tablespace with 8 datafiles.

SQL> select file_name from dba_data_files where tablespace_name=’TESTNDEX’;

FILE_NAME
——————————————————————————–
/data1/oradata/TESTINDEX01.dbf
/data1/oradata/TESTINDEX02.dbf
/data1/oradata/TESTINDEX03.dbf
/data1/oradata/TESTINDEX04.dbf
/data1/oradata/TESTINDEX05.dbf
/data1/oradata/TESTINDEX06.dbf
/data1/oradata/TESTINDEX07.dbf
/data1/oradata/TESTINDEX08.dbf

8 rows selected.

i created a table called TEST in this tablespace.

now many people think that space will be allocated (or extents will be allocated) from 1st datafile of this tablespace and once it is full, it will use 2nd, 3rd etc….which is not the real picture

the fact is, if i create a table, when i start inserting data into that, extents will be allocated from all the datafiles in ROUND ROBIN fashion (if you don’t know about this, just do a google !)

So, in our example it will go to all the 8 datafiles

so based on the allocation of extents and size of data, object can reside in all the datafiles or some datafiles.

How can we check in which datafile my object resides?

select a.segment_name,a.file_id,b.file_name Datafile_name from dba_extents a,dba_data_files b where a.file_id=b.file_id and a.segment_name=’YOUR OBJECT NAME’;

How can we check what objects are there in a datafile?

select a.segment_name,a.file_id,b.file_name Datafile_name from dba_extents a,dba_data_files b where a.file_id=b.file_id and b.file_name=<your datafile name with path>;

Script for trimming alert log to 1 day and taking backup of prev day

#!/usr/bin/ksh

############################################################################
##  Program :   save_alert_log.sh                                          #
##                                                                         #
##  Purpose :   The alert logs on many Oracle databases can grow to a very #
##              large size over time.  This can often impede the maintenace#
##              of the system – because the DBA will need to sometimes scan#
##              through many days or months of data when researching an    #
##              issue.  This script tries to avoid that by ensuring that   #
##              the log file can be “refreshed” on a daily basis, meaning  #
##              that only the current day’s data will be kept in the log,  #
##              while the previous day’s data will be saved to another file#
##              in a backup area.                                          #
##                                                                         #
##              This script should be run from Oracle’s crontab at midnight#
##              every night, so that the database will always have a new   #
##              alert log file each day.  An example crontab entry could be#
##              0 00 * * * /oracle/product/local/scripts/save_alert.sh 2>&1#
##                                                                         #
##  Date    :   19 May 2006.                                               #
##  Author  :   Basil S. Mullings                                          #
############################################################################
##  Modified:                                                              #
##                                                                         #
##                                                                         #
#  Modification History:                                                   #
#  DATE       WHO      DESC                                                #
#  ——–   —–    —————————————————-#
#  05/29/06   Basil    Add an extra variable LOG_KEEP_DAYS to hold the     #
#                      number of days that the log files should be kept on #
##                     the server before being deleted.                    #
##                                                                         #
##                                                                         #
############################################################################

  ##Setup some needed variables.
BKUP=bkup   ##The backup directory to store the logs…
ORATAB=”/etc/oratab”
LOG_KEEP_DAYS=365   ##Keep this many days of log files on the server.
TMPFILE=/var/tmp/OracleAlertLog   ##Just a temp scratch work area.
SQLUSER=”/ as sysdba”
GEN_ORA_ERROR=”ORA\-[0-9][0-9]*”
PATH=”$HOME:$HOME/bin:/usr/contrib/bin:/usr/local/bin:/usr/bin:/bin:/etc:.”
export PATH

  ## Now, parse the oratab file for all databases on the system.
  ## Then use the ORACLE_SID that is found in the oratab file
  ## to log onto that database, and retrieve the directory where
  ## the alter log file is stored (.ie. retrieve the path to the
  ## bdump directory.
  ##
#for sidEntry in `cat $ORATAB | grep -v “^#”`
for sidEntry in `cat $ORATAB | awk -F: ‘{print $1}’ | grep -v “^#”`
do
       ## Get date and time
    CURR_DATE=`date ‘+%a_%m%d%H%M’`    ##Example Fri_05191256   for Friday May 19th @1256 PM.

    #ORACLE_SID=`echo  $sidEntry | cut -f 1 -d :`
    ORACLE_SID=$sidEntry
    echo “Oracle Sid is $ORACLE_SID”
                                                                                        
    export ORACLE_SID                                                                   
       ## Set the Oracle environment for this SID.                                      
    ORAENV_ASK=NO                                                                     
    . /usr/local/bin/oraenv                                                             
    rm -f $TMPFILE > /dev/null 2>&1

       ##Now, let’s log onto the DB, and try to
       ##retrieve the bdump directory path.
    sqlplus -s /nolog << EOF > $TMPFILE
    connect $SQLUSER
    set heading off;
    set echo off;
    set feedback off;

    select ‘BACKGROUND_DUMP_DEST=’ ||value
    from   v\$parameter
    where  name=’background_dump_dest’;
    exit;
EOF

       ##Ok, we had a problem talking to the database.
    if [ `grep -c $GEN_ORA_ERROR $TMPFILE` -ne 0 ]
    then
         echo “ERROR: Unable to find the path to the alert log for DB $ORACLE_SID”
         rm -f $TMPFILE > /dev/null 2>&1

    else  ##Ok, we can log into the DB, now let’s go find our bdump directory.
      
         bdump=`grep BACKGROUND_DUMP_DEST $TMPFILE | awk -F “=” ‘{print $2}’`
         #echo “BDUMP is $bdump”
         bkupDir=$bdump/$BKUP

            ##Make sure our backup directory exists.
         if [ ! -d $bkupDir ]
         then
               mkdir $bkupDir  > /dev/null 2>&1
         fi

           ##Now, move the alert log.                                                 
         #echo “now moving $bdump/alert_${ORACLE_SID}.log to $bkupDir/alert_${ORACLE_SID}.$CURR_DATE”
         mv $bdump/alert_${ORACLE_SID}.log  $bkupDir/alert_${ORACLE_SID}.$CURR_DATE
       
             #Procedure to shrink the log to 365 days
             ##Keep only the last 365 days worth of logs…delete all logs older than 365 days.
         #echo “Now shrinking the logs in dir $bkupDir …”
         find $bkupDir  -name “*.*” -mtime +${LOG_KEEP_DAYS} -exec rm -f {} \;
    fi

done

Script to check space for one mount point

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

SPACE CHECK FOR A MOUNT POINT IN UNIX BOX

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

#!/bin/ksh

lis=`df -k /d801/oracle/SDSS | grep % | awk ‘{print $5}’| sed ‘s/%//g’`

ci=80

SCRIPT_DIR=/d002/oracle/SDSS/utilitys/

CTIME=`date +%HH%MM%SS`

if test $lis -gt $ci

then

( echo “$lis% space is used in /d801/oracle/SDSS/” > /d002/oracle/SDSS/utilitys/spacecheck.txt

mailx -s “Archive Destination > 80% in /d801/oracle/SDSS/!!” dba_ora_offshore@bp.com 919949520316@airtelap.com < /d002/oracle/

SDSS/utilitys/spacecheck.txt

)

fi

Note : The above script is only for one mount point

Script to check mount point space and get email

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

SCRIPT FOR CHECKING DISK SPACE IN UNIX

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

 #!/bin/ksh

#rm /tmp/dfk.txt

#echo “df -k output for `date` `uname -n`” > /tmp/dfk.txt

echo “File system usage exceeded the threshold on `uname -n` server- `date`” > /tmp/dfk.txt

echo “” >> /tmp/dfk.txt

i=1

while [ $i -le `df -k | grep -v proc | grep -v capacity | wc -l` ] ;do

if [ `df -k | grep -v proc | grep -v capacity | head -n $i | tail -1 | awk ‘{print $5}’ | \

sed -e ‘s/%//’` -gt 97 ] ; then

#echo “File system usage exceeded the threshold on `uname -n` server- `date`” > /tmp/dfk.txt

#echo “” >> /tmp/dfk.txt

df -k | grep -v proc | grep -v capacity | head -n $i | tail -1 >> /tmp/dfk.txt

fi

((i=i+1))

done

if [ `cat /tmp/dfk.txt | wc -l` -gt 2 ] ; then

cat /tmp/dfk.txt | mailx -s “File system full alert” bp_hostsupport@satyam.com,dba_ora_offshore@satyam.com

#cat /tmp/dfk.txt

else

exit

fi

Note: the above script will check for mount point space and will send an alert if the used space is > 97%

Script for tablespace free space email alert

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

MONITOR SCRIPT – it will monitor objects close to max extents, tablespace free space

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

#! /bin/sh

TS=`date “+%y%m%d”`

ORACLE_HOME=/d001/oracle/9.2.0.8-64

LD_LIBRARY_PATH=$ORACLE_HOME/lib

ORACLE_TERM=vt100

ORACLE_SID=ORVIT8QA

LOG=/d002/oracle/$ORACLE_SID/utilitys

PATH=/$ORACLE_HOME/bin:$PATH

export ORACLE_HOME ORACLE_SID ORACLE_TERM PATH LOG LD_LIBRARY_PATH

for n in 1

do

if [ $n -eq 1 ]; then

TWO_TASK=brcvita8_ORVIT8QA

DB_NAME=ORVIT8QA

fi

export TWO_TASK

monitor_file=$LOG/$DB_NAME.log

#n=`expr $n + 1`

 rm $monitor_file

echo “Objects close to max extents:” >> $monitor_file

sqlplus -s system/systemorvit8QA << eof >> $monitor_file

set pagesize 100;

set linesize 80;

column segment_name format a35;

column type format a10;

column owner format a10;

column extents format 9999;

column max_extents format 9999;

select owner, segment_name, segment_type “TYPE”, extents, max_extents

from sys.dba_segments

where extents/greatest(max_extents,1) >= 0.90

and segment_type not in(‘CACHE’,’ROLLBACK’);

exit;

eof

 echo “Objects that cannot get next extent:” >> $monitor_file

sqlplus -s system/systemorvit8QA << eof >> $monitor_file

set pagesize 100;

set linesize 80;

column segment_name format a33;

column type format a8;

column owner format a10;

column tablespace format a13;

column next_extent format 999999999;

select owner, segment_name, segment_type “TYPE”,

tablespace_name “TABLESPACE”, next_extent

from sys.dba_segments b

where segment_type !=’ROLLBACK’ and

next_extent > (select max(bytes) from sys.dba_free_space a

where a.tablespace_name = b.tablespace_name);

exit;

eof

 echo “Tablespaces with low freespace (< 20% & < 250 MB):” >> $monitor_file

sqlplus -s system/systemorvit8QA << eof >> $monitor_file

set echo off

set pagesize 30

set feedback off;

drop table system.mon_smt_ts;

create table system.mon_smt_ts as

(select x.tablespace_name, round(sum(x.bytes)/1048576,1) freespace

from sys.dba_free_space x

group by x.tablespace_name);

 drop table system.mon_smt_ts1;

create table system.mon_smt_ts1 as

(select tablespace_name, round(sum(bytes)/1048576,1) availspace

from sys.dba_data_files

group by tablespace_name);

set feedback on;

column “Table Space” format a20

column “Allocated” format a12

column “Free Space” format a12

column “Space Used” format a12

select x.tablespace_name “Table Space”,

to_char(availspace,’99999.9′)||’ Meg’ “Allocated”,

to_char(freespace,’99999.9′)||’ Meg’ “Free Space”,

round(freespace/availspace*100) “Pct Free”,

to_char((availspace-freespace),’99999.9′)||’ Meg’ “Space Used”

from system.mon_smt_ts x, system.mon_smt_ts1 y

where y.tablespace_name = x.tablespace_name

and round(freespace/availspace*100) < 20 and freespace<250

order by round(freespace/availspace*100);

exit;

eof

 null_findings=`egrep “no rows selected” $monitor_file | wc -l`

null_findings=`echo $null_findings`

 if [ “$null_findings” != 3 ]; then

/usr/bin/mailx -s “$DB_NAME Exception Report” dba_ora_offshore@bp.com < $monitor_file

fi

 done