Shell Script for checking disk space

 ADMIN="tyagi.ankur07@gmail.com"
    # set alert level 90% is default
    ALERT=80
    df -HP | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $6 }' | while read output;
    do
    #echo $output
    usep=$(echo $output | awk '{ print $1 }' | cut -d'%' -f1 )
    partition=$(echo $output | awk '{ print $2 }' )

#echo "usep=$usep"

    if [ $usep -ge $ALERT ]; then
    echo "Running out of space \"$partition ($usep%)\" on $(hostname) as on $(date)" |
    mailx -s "Alert: Almost out of disk space on `hostname`  $usep" $ADMIN
    fi
    done

Shell Script to Gather Stats

ADMIN="tyagi.ankur07@gmail.com"

sqlplus /nolog <<EOF
connect / as sysdba

set head off
set underline off
set pages 0
set veri off
set lines 130
spool last_analyzed.lst

--select distinct 'EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('''||owner||''''||',DBMS_STATS.AUTO_SAMPLE_SIZE);' from dba_tables where last_analyzed is null;
select distinct 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''||table_name||''',cascade=>true);' from dba_tab_statistics where last_analyzed is null and STATTYPE_LOCKED <> 'ALL'; --added to avoid locked objects

spool off

--@last_analyzed.lst
exit;
EOF

echo -e "Gather stats completed" | mailx -s "stat compeleted" $ADMIN

Shell Script for Listener Status

#######################################################################
## cklsnr.sh                                                         ##
#######################################################################
#!/bin/ksh
DBALIST="tyagi.ankur07@gmail.com";export DBALIST
cd $TNS_ADMIN
rm -f lsnr.exist
ps -ef | grep LISTENER_NAME | grep -v grep  > lsnr.exist
if [ -s lsnr.exist ]
then
    echo
else
echo "Alert" | mailx -s "Listener 'LISTENER_NAME' on `hostname` is down" $DBALIST
    TNS_ADMIN=$ORACLE_HOME/network/admin/; export TNS_ADMIN
    ORACLE_SID=$SID; export ORACLE_SID
    ORAENV_ASK=NO; export ORAENV_ASK
    PATH=$PATH:/bin:/usr/local/bin; export PATH
    . oraenv
    LD_LIBRARY_PATH=${ORACLE_HOME}/lib;export LD_LIBRARY_PATH
    lsnrctl start LISTENER_NAME
fi

Shell script for checking instance availability

db_sid=""
ora_home=''"
envr="Prod"
mailto="tyagi.ankur07@gmail.com"
db_st=`ps -ef | grep smon | grep $db_sid | awk '{print $8}'`
db_sid_pro="ora_smon_$db_sid"

case "$db_st" in
"$db_sid_pro")
tag="true"
msg="$db_sid diatabase instance is running"
echo "$db_sid_pro database process is running"
;;
*)
if [ -e $ora_home/dbs/spfile$db_sid.ora ];then
msg="database is down"
echo "database is down"
tag="false"
else
tag="false"
msg="Incorrect database SID - $db_sid. Please provide the correct database SID"
echo "Incorrect database SID - $db_sid. Please provide the correct database SID"
fi
;;
esac


if [ $tag = "false" ]; then
echo "$msg " | mail -s "$envr database status - $db_sid " $mailto
fi

Shell Script for Viewing Alert Log contents from adrci

 # Name script         : show_alertlog_adrci.sh
# Version             : 1.0
# Date                : 20130520
# Author              : Ankur Tyagi    
# Description         : script to display contents of alert log of rdbms
# Parameters(optional):  n : To display number of Last n Line
# Suitable for        : >= Oracle11g
# Execute             : call this
#                       for example: $ ./show_alertlog_adrci.sh 100
#                       => show last 100 lines from rdbms alert log


#!/bin/bash
Daily_Log=/tmp/alert_log_check_daily.txt
MAIL_SUBJ="PROD:WARNING HOST: "
MAIL_RECIPIENT="tyagi.ankur07@gmail.com"
HOST_NAME=`hostname -a`

num_last_line=$1
#shows alert log tail from adrci
$ORACLE_HOME/bin/adrci exec="show homes"|grep rdbms | while read file_line
do
  echo "################################################################################"
  echo " adrci alert log contents " $file_line >> $Daily_Log
  echo "#################################################################################"
  #adrci exec="set homepath $file_line;show alert -tail -f"
  adrci exec="set homepath $file_line; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1\\\" $num_last_line"
done

num_errors=`grep -c -e 'TNS' -e 'ORA' $Daily_Log`
                if [ $num_errors != 0 ]
                then
                MAIL_SUBJ=$MAIL_SUBJ$HOST_NAME" Errors Found in Daily Alert Summary"
                mailx -s "$MAIL_SUBJ" $MAIL_RECIPIENT < $Daily_Log
                fi

Finding a locking session

How to identify lockers?

This article will explain about locks on rows and on objects in ORACLE.
Locks on rows can cause performance problems or even impede a transaction from finishing, when there are processes running for long time we need to validate that they are not waiting on a row(s).

When there is a lock on a row there is also a lock on the dependent objects, if we want to perform a DDL on a locked object we will get an ORA-00054 error.

#1 - find blocking sessions with v$session

SELECT
   s.blocking_session, 
   s.sid, 
   s.serial#, 
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL

#2 - find blocking sessions using v$lock

SELECT 
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM 
   v$lock l1, v$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2

#3 - Views to find blocking session

•         DBA_WAITERS

•         DBA_BLOCKERS

Scripts for detecting locking

 

Note 1020012.6 TFTS SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO Note 1020008.6TFTS FULLY DECODED LOCKING SCRIPT
Note 1020007.6 SCRIPT: DISPLAY LOCKS AND GIVE SID AND SERIAL # TO KILL ()
Note 1020010.6 SCRIPT: DISPLAY SQL TEXT FROM LOCKS
Note 1020047.6 SCRIPT: SCRIPT TO DISPLAY USER LOCK INFORMATION Note 1020088.6 SCRIPT: REPORT SESSIONS WAITING FOR LOCKS ()

 

 

How to resolve locking situations?

Most locking issues are application specifics. To resolve locking contention, one needs to free the resource by:

1. Asking the HOLDER to commit or rollback
2. Killing the session which holds the lock,
   For example:
   
   ALTER SESSION KILL SESSION 'sid, serial#';
3.  Killing the  unix/vms shadow process directly.
    This is not recommended as it may prevent proper cleanup of a session
   When killing the shadow process, please be careful of shared servers in a  multi-threaded environment.
4. ROLLBACK FORCE or COMMIT FORCE if 2pc pending transaction.

 

 

ORA-20005: object statistics are locked (stattype = ALL)

Today I analyze a table and get the error messages below

ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 23829
ORA-06512: at “SYS.DBMS_STATS”, line 23880
ORA-06512: at line 2


Solution:
You can see list of all locked tables in a schema by running following query:

select table_name, stattype_locked from dba_tab_statistics where owner = ‘<schema>’ and stattype_locked is not null;

You can unlock the schema stats:
exec dbms_stats.unlock_schema_stats('<shema_name>');

Or unlock the table stats:
SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('SYSTEM', 'DEF$_AQERROR');

PL/SQL procedure successfully completed.

And the stats can be collected:
SQL> exec dbms_stats.unlock_schema_stats('<shema_name>');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>'cisadm',tabname=>'c0_installation',ESTIMATE_PERCENT => 30);

PL/SQL procedure successfully completed. 


To generate unlock statement for all tables in the schema you can use following,
select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name ||”’);’ from dba_tab_statistics where owner = ‘<schema>” and stattype_locked is not null;

Script to start Oracle stats gathering in background from shell in Linux

Following is the script to gather stats for test.foo table. You can create a file at OS level and paste the following script:

touch gatherstats.sh
chmod a+x gatherstats.sh

then paste:

#!/bin/sh
sqlplus <<-EOF
/ as sysdba
set echo on timing on
exec dbms_stats.gather_table_stats(ownname=>'test',tabname=>'foo', ESTIMATE_PERCENT=>.01, METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',CASCADE=>TRUE,DEGREE=>6);
exit
EOF
echo -e "Stat completed for table FOO." | mail -s "Stat completed"


Now you can run the script in background:

nohup ./gatherstats.sh &

The script will email you upon completion.



-------------------------------------------------x--------------------------------
 

Another script which will analyze all the tables and indexes.

cat Gather_stat.sh

ADMIN="tyagi.ankur07@gmail.com"

sqlplus /nolog <<EOF
connect / as sysdba

set head off
set underline off
set pages 0
set veri off

spool last_analyzed.lst

--select distinct 'EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('''||owner||''''||',DBMS_STATS.AUTO_SAMPLE_SIZE);' from dba_tables where last_analyzed is null;
select distinct 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''||table_name||''',cascade=>true);' from dba_tables where last_analyzed is null;
spool off

@last_analyzed.lst
exit;
EOF

echo -e "Gather stats completed" | mailx -s "stat compeleted" $ADMIN

Internal Concurrent Manager status could not be determined

There can a scenario where while running

adcmctl status apps/apps_password

The output is:

$ adcmctl.sh status apps/apps_password

You are running adcmctl.sh version 115.28

Internal Concurrent Manager status could not be determined.

adcmctl.sh: exiting with status 0

Solution:

1. We can run cmclean.sql script.

    As suggested by (Oracle Note: 134007.1 - CMCLEAN.SQL - Non Destructive Script to 

    Clean Concurrent Manager Tables). Shutdown the CM, run the script (make sure you issue 

    commit once the script is  done), start the CM and check for the issue.

2. If after running the the cmclean.sql the issue still persists then take down the application tier

    services and run autoconfig. This will relink the binaries of the adcmctl.sh. After autoconfig is 

    successful then take up  the application services and and check the issue.

 You may also review this doc

Adcmctl.Sh Script Is Unable To Determine The ICM Status [ID 1320217.1]