DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, September 29, 2016

Log File Location in EBS R12

Log File Location in EBS R12

$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

Trace file location:
$ORACLE_HOME/admin/SID_Hostname/udump

Application Tier Logs


Start/Stop script log files location:
$COMMON_TOP/admin/log/CONTEXT_NAME/ 

OPMN log file location
$ORACLE_HOME/opmn/logs/ipm.log

$IAS_ORACLE_HOME/Apache/Apache/logs/ssl_engine_log
$IAS_ORACLE_HOME/Apache/Apache/logs/ssl_request_log
$IAS_ORACLE_HOME/Apache/Apache/logs/access_log
$IAS_ORACLE_HOME/Apache/Apache/logs/error_log
$IAS_ORACLE_HOME/Apache/JServ/logs

Concurrent log file location:
$APPL_TOP/admin/PROD/log or $APPLLOG/$APPLCSF

Patch log file location:
$APPL_TOP/admin/PROD/log

Worker Log file location:
$APPL_TOP/admin/PROD/log

AutoConfig log files location:
Application Tier:
$APPL_TOP/admin/SID_Hostname/log//DDMMTime/adconfig.log

Database Tier:
$ORACLE_HOME/appsutil/log/SID_Hostname/DDMMTime/adconfig.log

Error log file location:
Application Tier:
$APPL_TOP/admin/PROD/log

Database Tier :
$ORACLE_HOME/appsutil/log/SID_Hostname


Concurrent Reqeust related logs
$LOG_HOME/appl/conc - > location for concurrent requests log and out files
$LOG_HOME/appl/admin - > location for mid tier startup scripts log files

Apache Logs (10.1.3 Oracle Home which is equivalent to iAS Oracle Home - Apache, OC4J and OPMN)
$LOG_HOME/ora/10.1.3/Apache - > Location for Apache Error and Access log files
$LOG_HOME/ora/10.1.3/j2ee - > location for j2ee related log files
$LOG_HOME/ora/10.1.3/opmn - > location for opmn related log files

Forms & Reports related logs (10.1.2 Oracle home which is equivalent to 806 Oracle Home)
$LOG_HOME/ora/10.1.2/forms
$LOG_HOME/ora/10.1.2/reports

Startup/Shutdown Log files location:
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/admin/log

Patch log files location:
$APPL_TOP/admin/$SID/log/ 

Clone and AutoConfig log files location in Oracle E-Business Suite Release 12 

Logs for the adpreclone.pl are located: 
On the database tier: 
RDBMS $ORACLE_HOME/appsutil/log/< context >/StageDBTier_< timestamp >.log 

On the application tier: 
$INST_TOP/admin/log/StageAppsTier_< timestamp >.log 

Where the logs for the admkappsutil.pl are located? 
On the application tier: 
$INST_TOP/admin/log/MakeAppsUtil_< timestamp >.log 

Script to unlock locked users



# ###########
# Description:
# ###########
echo
echo "================================="
echo "This script LOCKS database users."
echo "================================="
echo
sleep 1

# ###########################
# Listing Available Databases:
# ###########################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|grep -v ASM|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
 then
   echo No Database Running !
   exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
 then
   export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
 then
    echo
    echo "Select the ORACLE_SID:[Enter the number]"
    echo ---------------------
    select DB_ID in $( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
     do
if [ -z "${REPLY##[0-9]*}" ]
then
          export ORACLE_SID=$DB_ID
          echo Selected Instance:
          echo
          echo "********"
          echo $DB_ID
          echo "********"
          echo
 break
else
 export ORACLE_SID=${REPLY}
 break
fi
     done

fi
# Exit if the user selected a Non Listed Number:
if [ -z "${ORACLE_SID}" ]
then
 echo "You've Entered An INVALID ORACLE_SID"
 exit
fi

# #########################
# Getting ORACLE_HOME
# #########################
  ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|grep -v ASM|awk '{print $1}'|tail -1`
  USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`

## If OS is Linux:
if [ -f /etc/oratab ]
  then
  ORATAB=/etc/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
  then
  ORATAB=/var/opt/oracle/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME
fi

## If oratab is not exist, or ORACLE_SID not added to oratab, find ORACLE_HOME in user's profile:
if [ -z "${ORACLE_HOME}" ]
 then
  ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
  export ORACLE_HOME
fi

# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
CURR_USER=`whoami`
if [ ${ORA_USER} != ${CURR_USER} ]; then
 echo ""
 echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
 echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
 echo "Script Terminated!"
 exit
fi

# #############################
# SQLPLUS: Lock An Oracle User:
# #############################
# Variables
echo
echo "Please enter the USERID:"
echo "======================="
while read USERNAME2
 do
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME=upper('$USERNAME2');
EOF
)
VAL22=`echo $VAL11| awk '{print $NF}'`
                        case ${VAL22} in
# If the provided value match an exist username in the DB:
1)
${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME=upper('$USERNAME2');
PROMPT
PROMPT Locking user [${USERNAME2}] ...
PROMPT
EOF
VAL1=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
ALTER user "$USERNAME2" ACCOUNT LOCK;
EOF
)
VAL2=`echo $VAL1| grep "User altered"`
               if [ -z "${VAL2}" ]
                then
                 echo "Failed to lock User \"${USERNAME2}\" !"
   echo
                   exit
                else
                   echo
                   echo User ${USERNAME2} locked Successfully.
                   echo
                fi; break;;
# If no value provided or the value doesn't match any user in the DB try to search for matching:
                        *) echo; echo "INFO: USER [${USERNAME2}] IS NOT EXIST ON DATABASE [$ORACLE_SID] !"
                           echo; echo "Searching ..."; sleep 1
${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set linesize 143
set pagesize 1000
set feedback off
set trim on
set echo off
col USERNAME for a30
col account_status for a23
select username,account_status,profile,LOCK_DATE,EXPIRY_DATE from dba_users where username like upper ('%$USERNAME2%');
EOF
                           echo; echo "Please Enter the FULL USERID:"
echo "=============================" ;;
                        esac
 done

Shell script for Database COLD Backup in Linux and Solaris

# #################################################
# Database COLD Backup Script.
# #################################################

# ###########
# Description:
# ###########
echo
echo "==============================================="
echo "This script Takes a COLD BACKUP for a database."
echo "==============================================="
echo
sleep 1

# ###########################
# Listing Available Databases:
# ###########################

# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|grep -v ASM|wc -l )

# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
 then
   echo No Database Running !
   exit
fi

# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
 then
   export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )

# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
 then
    echo
    echo "Select the ORACLE_SID:[Enter the number]"
    echo ---------------------
    select DB_ID in $( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
     do
        if [ -z "${REPLY##[0-9]*}" ]
         then
          export ORACLE_SID=$DB_ID
          echo Selected Instance:
 echo
 echo "********"
          echo $DB_ID
          echo "********"
          break
         else
          export ORACLE_SID=${REPLY}
          break
        fi
     done

fi
# Exit if the user selected a Non Listed Number:
        if [ -z "${ORACLE_SID}" ]
         then
          echo "You've Entered An INVALID ORACLE_SID"
          exit
        fi

# #########################
# Getting ORACLE_HOME
# #########################
  ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|grep -v ASM|awk '{print $1}'|tail -1`
  USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`

## If OS is Linux:
if [ -f /etc/oratab ]
  then
  ORATAB=/etc/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
  then
  ORATAB=/var/opt/oracle/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME
fi

## If oratab is not exist, or ORACLE_SID not added to oratab, find ORACLE_HOME in user's profile:
if [ -z "${ORACLE_HOME}" ]
 then
  ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
  export ORACLE_HOME
fi

# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
CURR_USER=`whoami`
        if [ ${ORA_USER} != ${CURR_USER} ]; then
          echo ""
          echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
          echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
          echo "Script Terminated!"
          exit
        fi

# ################################
# Creating Backup & Restore Script:
# ################################
echo
echo "Enter the Backup location: [Full Path]"
echo "-------------------------"
while read LOC1
        do
                EXTEN=${ORACLE_SID}_`date '+%F'`
                LOC2=${LOC1}/COLDBACKUP_${EXTEN}
                /bin/mkdir -p ${LOC2}

                if [ ! -d "${LOC2}" ]; then
                 echo "Provided Backup Location is NOT Exist/Writable !"
                 echo
                 echo "Please Provide a VALID Backup Location:"
echo "---------------------------------------"
                else
                 echo
                 sleep 1
                 echo "Backup Location Validated."
                 echo
                 break
                fi
        done
BKPSCRIPT=${LOC2}/Cold_Backup.sh
RSTSCRIPT=${LOC2}/Restore_Cold_Backup.sh
BKPSCRIPTLOG=${LOC2}/Cold_Backup.log
RSTSCRIPTLOG=${LOC2}/Restore_Cold_Backup.log

# Creating the Cold Backup script:
echo
echo "Creating Cold Backup and Cold Restore Scripts ..."
sleep 1
cd ${LOC2}
${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0
set termout off echo off feedback off linesize 400;
spool Cold_Backup.sh
PROMPT echo "Shutting Down Database $ORACLE_SID ... [Ctrl+c to CANCEL]"
PROMPT echo "[5]"
PROMPT sleep 1
PROMPT echo "[4]"
PROMPT sleep 1
PROMPT echo "[3]"
PROMPT sleep 1
PROMPT echo "[2]"
PROMPT sleep 1
PROMPT echo "[1]"
PROMPT sleep 1
PROMPT echo "SHUTTING DOWN NOW ..."
PROMPT sleep 3
PROMPT echo ""
PROMPT ${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
PROMPT shutdown immediate;
PROMPT EOF
PROMPT echo "Database SHUTDOWN SUCCESFULLY."
PROMPT sleep 1
PROMPT echo
PROMPT echo "Starting DB FILES copy ..."
PROMPT echo
PROMPT echo "************************"
PROMPT echo "DON'T CLOSE THIS SESSION, Once the BACKUP JOB is DONE, it will return you back to the PROMPT."
PROMPT echo "************************"
PROMPT echo
PROMPT sleep 1
PROMPT
PROMPT echo -ne '...'
select 'cp -vpf '||name||' $LOC2 ; echo ' ||'-ne '''||'...''' from v\$controlfile
union
select 'cp -vpf '||name||' $LOC2 ; echo ' ||'-ne '''||'...''' from v\$datafile
union
select 'cp -vpf '||member||' $LOC2 ; echo ' ||'-ne '''||'...''' from v\$logfile;
PROMPT touch $LOC2/verifier.log
PROMPT echo

spool off
EOF
chmod 700 ${BKPSCRIPT}
# Creating the Restore Script:
${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 termout off echo off feedback off linesize 400;
spool Restore_Cold_Backup.sh
PROMPT echo ""
PROMPT echo "Restoring Database $ORACLE_SID from Cold Backup [${EXTEN}] ..."
PROMPT sleep 1
PROMPT echo ""
PROMPT echo "ARE YOU SURE YOU WANT TO RESTORE DATABASE [${ORACLE_SID}] ? [Y|N] [N]"
PROMPT while read ANS
PROMPT  do
PROMPT          case \$ANS in
PROMPT                  y|Y|yes|YES|Yes) echo "RESTORATION JOB STARTED ...";break ;;;
PROMPT                  ""|n|N|no|NO|No) echo "Script Terminated.";exit;break ;;;
PROMPT                  *) echo "Please enter a VALID answer [Y|N]" ;;;
PROMPT          esac
PROMPT  done
PROMPT ORACLE_SID=${ORACLE_SID}
PROMPT export ORACLE_SID
PROMPT echo "Shutting Down Database ${ORACLE_SID} ... [Ctrl+c to CANCEL]"
PROMPT echo "[5]"
PROMPT sleep 1
PROMPT echo "[4]"
PROMPT sleep 1
PROMPT echo "[3]"
PROMPT sleep 1
PROMPT echo "[2]"
PROMPT sleep 1
PROMPT echo "[1]"
PROMPT sleep 1
PROMPT echo "SHUTTING DOWN NOW ..."
PROMPT sleep 3
PROMPT echo ""
PROMPT ${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
PROMPT shutdown immediate;
PROMPT EOF
PROMPT
PROMPT echo "Restoration Job Started ..."
PROMPT echo ""
PROMPT echo -ne '...'
select 'cp -vpf $LOC2/'||SUBSTR(name, INSTR(name,'/', -1,1)+1)||'  '||name||' ; echo ' ||'-ne '''||'...''' from v\$controlfile
union
select 'cp -vpf $LOC2/'||SUBSTR(name, INSTR(name,'/', -1,1)+1)||'  '||name||' ; echo ' ||'-ne '''||'...''' from v\$datafile
union
select 'cp -vpf $LOC2/'||SUBSTR(member, INSTR(member,'/', -1,1)+1)||'  '||member||' ; echo ' ||'-ne '''||'...''' from v\$logfile;
PROMPT echo
PROMPT ${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
PROMPT startup
PROMPT PROMPT
PROMPT PROMPT Adding TEMPFILES TO TEMPORARY TABLESPACES...
select 'ALTER DATABASE TEMPFILE '''||file_name||''' DROP;' from dba_temp_files;
select 'ALTER TABLESPACE '||tablespace_name||' ADD TEMPFILE '''||file_name||''' REUSE;' from dba_temp_files;
PROMPT EOF
PROMPT VAL1=\$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
PROMPT set heading off echo off feedback off termout off
PROMPT select status from v\\\$instance;;
PROMPT EOF
PROMPT )
PROMPT VAL2=\`echo \$VAL1 | perl -lpe'\$_ = reverse' |awk '{print \$1}'|perl -lpe'\$_ = reverse'\`
PROMPT case \${VAL2} in "OPEN")
PROMPT echo "******************************************************"
PROMPT echo "Database [$ORACLE_SID] has been Restored Successfully."
PROMPT echo "Database [$ORACLE_SID] is UP."
PROMPT echo "******************************************************"
PROMPT echo
PROMPT echo ;;;
PROMPT *)
PROMPT echo "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
PROMPT echo "Database [$ORACLE_SID] CANNOT OPEN !"
PROMPT echo "Please check the ALERTlOG and investigate."
PROMPT echo "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
PROMPT echo
PROMPT echo ;;;
PROMPT esac
spool off
EOF

chmod 700 ${RSTSCRIPT}

        if [ ! -f "${BKPSCRIPT}" ]; then
          echo ""
          echo "Backup & Restore Scripts CANNOT be Created."
          echo "Script Failed to Create the Cold Backup job !"
          echo "Please check the Backup Location permissions."
          exit
        fi

echo
echo "--------------------------------------------------------"
echo "Backup & Restore Scripts have been Created Successfully."
echo "--------------------------------------------------------"
echo
echo
sleep 1

# ############################
# Executing Cold Backup Script:
# ############################
# Checking if more than one instance is running: [RAC]
echo "Checking Other OPEN instances [RAC]."
sleep 1
VAL3=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select count(*) from gv\$instance;
EOF
)
VAL4=`echo $VAL3 | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
                if [ ${VAL4} -gt 1 ]
                 then
                  echo
                  echo "WARNING:"
                  echo "-------"
                  echo "Please SHUTDOWN ALL other RAC INSTANCES EXCEPT the one on the CURRENT Node."
                  echo "Then Re-run COLD_BACKUP.sh script Again."
                  echo ""
                  exit
                fi
echo
echo "VERIFIED: Only ONE INSTANCE is RUNNING for Database [${ORACLE_SID}]."
echo
sleep 1
echo "ARE YOU SURE TO SHUTDOWN DATABASE [${ORACLE_SID}] AND START THE COLD BACKUP JOB? [Y|N] [N]"
while read ANS
 do
         case $ANS in
                 y|Y|yes|YES|Yes) echo;echo "COLD BACKUP PROCEDURE STARTED ...";break ;;
                 ""|n|N|no|NO|No) echo;echo "Script Terminated.";exit;break ;;
                 *) echo "Please enter a VALID answer [Y|N]" ;;
         esac
 done
echo
echo "Database [${ORACLE_SID}] Will SHUTDOWN within [5 Seconds] ... [To CANCEL press [Ctrl+c]]"
echo "[5]"
sleep 1
echo "[4]"
sleep 1
echo "[3]"
sleep 1
echo "[2]"
sleep 1
echo "[1]"
sleep 1
echo ""
echo "Shutting Down Database [${ORACLE_SID}] ..."
echo "Backup Files will be Copied Under: [${LOC2}] ..."
echo
sleep 1
exec ${BKPSCRIPT} |tee  ${BKPSCRIPTLOG}

 VAL11=$LOC2/verifier.log
 if [ ! -f ${VAL11} ]
  then
   echo
   echo "xxxxxxxxxxxxxxxxxxx"
   echo "Backup Job Failed !"
   echo "xxxxxxxxxxxxxxxxxxx"
   echo
  else
   echo
   echo "Database Cold Backup is DONE."
   echo "Please Note that TEMP DATAFILES are NOT included in this Backup."
   echo
   echo "****************************************************************"
   echo "COLD BACKUP files located under: ${LOC2}"
   echo "****************************************************************"
   echo
   echo "****************************************************************"
   echo "Later, To restore database ${DB_ID} from this COLD BACKUP,"
   echo "use this script to do that job automatically:"
   echo "${RSTSCRIPT}"
   echo "****************************************************************"
 fi

rm -f ${VAL11}
echo
echo "Do You Want to STARTUP Database [${ORACLE_SID}]? [Y|N] [Y]"
echo "==========================================="
while read ANS
 do
         case $ANS in
                 ""|y|Y|yes|YES|Yes) echo "STARTING UP DATABASE [${ORACLE_SID}] ..."
${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
STARTUP
EOF
echo
break ;;
                 n|N|no|NO|No) echo;echo "Script FINISHED."
echo "To restore this database from the COLD BACKUP, Run Script: [${RSTSCRIPT}]"
exit
break ;;
                 *) echo "Please enter a VALID answer [Y|N]" ;;
         esac
 done

# #############
# END OF SCRIPT
# #############

Identify the datafiles in number of hosts in hostfile

for host in `cat ~oracle/.rhosts|\
cut -d"." -f1|awk '{print $1}'|sort -u`
do
    echo " "
    echo "************************"
    echo "$host"
    echo "************************"
    # loop from database to database
    for db in `cat /etc/oratab|egrep ':N|:Y'|\
      grep -v \*|grep ${db}|cut -f1 -d':'"`
    do
        home=`rsh $host "cat /etc/oratab|egrep ':N|:Y'|\
          grep -v \*|grep ${db}|cut -f2 -d':'"`
        echo "************************"
        echo "database is $db"
        echo "************************"
        rsh $host "
        ORACLE_SID=${db}; export ORACLE_SID;
        ORACLE_HOME=${home}; export ORACLE_HOME;
        ${home}/bin/sqlplus -s /<<!
        set pages 9999;
        set heading off;
        select name from v"\\""$"datafile;
        exit
        !"
    done
done

Script to check the node Eviction (RAC)

#!/bin/bash
. ~/.bash_profile

mailid=’nimai.karmakar@hotmail.com’
date=`date  +%Y-%m-%d” “%H:`

alertlog=/rac/app/oracle/product/11.2.0/dba-scripts/logs/ioerr.log
errlog=err_`date –date=”0 days ago” +_%d_%m_%y_%H_%M`.txt

err1=`grep -B 2 “$date” $alertlog | grep -A 2 “An I/O error” | wc -l`
err2=`grep -B 2 “$date” $alertlog | grep -A 2 “Network communication with node” | wc -l`

if [ $err1 -ge 1 -o $err2 -ge 1 ]
then
echo “Node eviction condition found in server `hostname`. Immediately check DB alert log for further action”  >> $errlog
echo “” >> $errlog
echo `grep -B 2 “$date” $alertlog | grep -A 2 “An I/O error”` >> $errlog
echo “” >> $errlog
echo =`grep -B 2 “$date” $alertlog | grep -A 2 “Network communication with node”` >> $errlog
mutt -s “Node evition type condition found in `hostname`” $mailto < $errLog
rm $errlog
fi

Script to find Oracle database performance

This single script  provides the overall picture of the database in terms of Waits events, Active/Inactive killed sessions, Top Processes (physical i/o, logical i/o, memory and CPU processes),Top CPU usage by users Etc


set serveroutput on
declare
cursor c1 is select version
from v$instance;
cursor c2 is
    select
          host_name
       ,  instance_name
       ,  to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime
       ,  to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime
     from v$instance;
cursor c4 is
select * from (SELECT count(*) cnt, substr(event,1,50) event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message',
'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC) where rownum <6;
cursor c5 is
select round(sum(value)/1048576) as sgasize from v$sga;
cursor c6 is select round(sum(bytes)/1048576) as dbsize
from v$datafile;
cursor c7 is select 'top physical i/o process' category, sid,
       username, total_user_io amt_used,
       round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('physical reads', 'physical writes',
                     'physical reads direct',
                     'physical reads direct (lob)',
                     'physical writes direct',
                     'physical writes direct (lob)')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_io
      from v$statname c, v$sesstat a
      where a.statistic# = c.statistic#
      and c.name in ('physical reads', 'physical writes',
                       'physical reads direct',
                       'physical reads direct (lob)',
                       'physical writes direct',
                       'physical writes direct (lob)'))
where rownum < 2
union all
select 'top logical i/o process', sid, username,
       total_user_io amt_used,
       round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('consistent gets', 'db block gets')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
 and b.sid = a.sid
      and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid,
       username, total_user_mem,
       round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_mem
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('session pga memory', 'session uga memory')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_mem
      from v$statname c, v$sesstat a
      where a.statistic# = c.statistic#
      and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username,
       total_user_cpu,
       round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session'
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session')
where rownum < 2;


cursor c8 is select username, sum(VALUE/100) cpu_usage_sec
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and name like '%CPU used by this session%'
and se.sid = ss.sid
and username is not null
and username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by username
order by 2 desc;
begin
dbms_output.put_line ('Database Version');
dbms_output.put_line ('-----------------');
for rec in c1
loop
dbms_output.put_line(rec.version);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Hostname');
dbms_output.put_line ('----------');
for rec in c2
loop
     dbms_output.put_line(rec.host_name);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('SGA Size (MB)');
dbms_output.put_line ('-------------');
for rec in c5
loop
     dbms_output.put_line(rec.sgasize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Database Size (MB)');
dbms_output.put_line ('-----------------');
for rec in c6
loop
     dbms_output.put_line(rec.dbsize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Instance start-up time');
dbms_output.put_line ('-----------------------');
for rec in c2 loop
 dbms_output.put_line( rec.starttime );
  end loop;
dbms_output.put_line( chr(13) );
  for b in
    (select total, active, inactive, system, killed
    from
       (select count(*) total from v$session)
     , (select count(*) system from v$session where username is null)
     , (select count(*) active from v$session where status = 'ACTIVE' and username is not null)


     , (select count(*) inactive from v$session where status = 'INACTIVE')
     , (select count(*) killed from v$session where status = 'KILLED')) loop
dbms_output.put_line('Active Sessions');
dbms_output.put_line ('---------------');
dbms_output.put_line(b.total || ' sessions: ' || b.inactive || ' inactive,' || b.active || ' active, ' || b.system || ' system, ' || b.killed || ' killed ');
  end loop;
  dbms_output.put_line( chr(13) );
 dbms_output.put_line( 'Sessions Waiting' );
  dbms_output.put_line( chr(13) );
dbms_output.put_line('Count      Event Name');
dbms_output.put_line('-----      -----------------------------------------------------');
for rec in c4
loop
dbms_output.put_line(rec.cnt||'          '||rec.event);
end loop;
dbms_output.put_line( chr(13) );


dbms_output.put_line('-----      -----------------------------------------------------');


dbms_output.put_line('TOP Physical i/o, logical i/o, memory and CPU processes');
dbms_output.put_line ('---------------');
for rec in c7
loop
dbms_output.put_line (rec.category||': SID '||rec.sid||' User : '||rec.username||': Amount used : '||rec.amt_used||': Percent used: '||rec.pct_used);
end loop;


dbms_output.put_line('------------------------------------------------------------------');


dbms_output.put_line('TOP CPU users by usage');
dbms_output.put_line ('---------------');
for rec in c8
loop


dbms_output.put_line (rec.username||'--'||rec.cpu_usage_sec);
dbms_output.put_line ('---------------');
end loop;


end;

The automatic installation script for Oracle Database on Linux


#########———— Installing Rpm files —–########



#Change directory to /tmp/install

cd /tmp/install

#Install all packages that are not installed during OS installation and that are required packages for Oracle Database 10gR2

echo “Installing rpm packages …”



rpm -Uvh “$(find /media/ -name compat-db*)”

rpm -Uvh “$(find /media/ -name sysstat*)”

rpm -Uvh “$(find /media/ -name libaio-devel*)”

rpm -Uvh “$(find /media/ -name libXp-1*)”



echo “Rpm packages installed





#Add lines to limits.conf file

echo “Changing limits.conf file”

cat >> /etc/security/limits.conf <<EOF

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

EOF

echo “limits.conf file changed successfully





#Add lines to profile to give maximum limit for Oracle user

echo “Changing /etc/profile file ….”

cat >> /etc/profile <<EOF

if [ \$USER = “oracle” ]; then

                                                  if [ \$SHELL = “bin/ksh” ]; then

                                                                ulimit -p 16384

                                                                ulimit -n 65536

                                                  else

                                                                ulimit -u 16384 -n 65536

                                                  fi

                                                  umask 022

fi

EOF

echo “/etc/profile file changed successfully





#Add line to /etc/pam.d/login file

echo “Changing /etc/pam.d/login file …”

cat >> /etc/pam.d/login <<EOF

session required /lib/security/pam_limits.so

EOF

echo “/etc/pam.d/login file changed successfuly





#Add some kernel parameters to /etc/sysctl.conf file

echo “Changing kernel parameters … “



cat >> /etc/sysctl.conf <<EOF

kernel.shmmax = 2147483648

kernel.shmall = 2097152

kernel.shmmni=4096

kernel.sem=250 32000 100 128

fs.file-max=65536

net.ipv4.ip_local_port_range=1024 65000

net.core.rmem_default=1048576

net.core.rmem_max=1048576

net.core.wmem_default=262144

net.core.wmem_max=262144

EOF



echo “Kernel parameters changed successfully



#Save all new kernel parameters



/sbin/sysctl -p



#Add “redhat-4” line to /etc/redhat-release file



echo “Changing /etc/redhat-release file …”

cp /etc/redhat-release /etc/redhat-release.original

echo “redhat-4” > /etc/redhat-release

echo “/etc/redhat-release file changed successfully





#Create new groups and “oracle” user and add this user to group

echo “Creating new groups and ‘oracle’ user …”

groupadd oinstall

groupadd dba

useradd -m -g oinstall -G dba -d /home/oracle -s /bin/bash -c “Oracle Software Owner” oracle

passwd oracle

echo “Groups and user created successfully



#Adding Environment Variables

#Adding Environment Variables

cat >> /home/oracle/.bashrc <<EOF

export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

alias mydb=’export ORACLE_SID=mydb;sqlplus “/ as sysdba”‘

export ORACLE_SID=mydb

EOF

EOF





#Unzip setup of Oracle

echo “Unzipping setup of Oracle 10g Release 2…. “

unzip 10201_database_linux32.zip

echo “Setup file successfully unzipped



#Enter to installation directory and run the installation …

echo “Installation begins …”

cd /tmp/install/database

chmod 755 runInstaller

chmod 755 install/.oui

chmod 755 install/unzip

xhost +

sudo -u oracle /tmp/install/database/runInstaller

User access to database within limits of time

SQL> create user gangai identified by gangai;

User created.

SQL> grant connect, resource to gangai;

Grant succeeded.

SQL> conn gangai/gangai
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
  2     AFTER LOGON ON DATABASE
  3  BEGIN
  4     IF USER = 'gangai' THEN
  5        IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
  6        THEN
  7           RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
  8        END IF;
  9     END IF;
10  END limit_connection;
11  /

Trigger created.

SQL> select to_char(sysdate,'hh24') from dual;

TO
--
23

SQL> conn gangai/gangai
Connected.
SQL> select to_char(sysdate,'hh24') from dual;

TO
--
18

SQL> conn gangai/gangai
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user gangai! You can't login between 08 and 22
ORA-06512: at line 5


Warning: You are no longer connected to ORACLE.
SQL>

Check the concurrent manager status from backend (database)

Check the concurrent manager status from backend (database) 



SELECT a.USER_CONCURRENT_QUEUE_NAME "  MANAGER_NAME" ,a.MAX_PROCESSES,a.RUNNING_PROCESSES,
sum(decode(b.PHASE_CODE,'R',decode(b.STATUS_CODE,'R',1,0),0)) Running_Normal,
sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby,
sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'I',1,0),0)) Pending_Normal
FROM apps.FND_CONCURRENT_Q

Block developers from using TOAD and other tools on production databases



CREATE OR REPLACE TRIGGER block_tools_from_prod
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  -- Don't Check SYS Connections
    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
 
  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
  END IF;
END;
/
SHOW ERRORS

ORA-01940: cannot drop a user that is currently connected

ORA-01940: cannot drop a user that is currently connected

Solution:

sql> drop user oracle;
drop user oracle
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

sql> select sid, serial# from v$session where username = 'oracle';
SID    SERIAL#
-----  --------
17         37

sql> alter system kill session '17,37;
System altered.

sql> drop user oracle;
User dropped.

ORA-609 : opiodr aborting process unknown ospid

ORA-609 : opiodr aborting process unknown ospid

Solution:

Increase the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure.
If the problem  is due to connection timeouts,an increase in the following parameters should eliminate or reduce the occurrence of the ORA-609s.

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

ORA-25153 temporary tablespace is empty

ORA-25153 temporary tablespace is empty

Solution: Use one of the below to fix the issue.

1. Create new datafile for defaul TEMP tablespace or solution 2

SQL> alter tablespace temp add tempfile ‘xxx/temp.dbf’ size 500M;

Tablespace altered.

2.Make existing TEMP1 tablesapcle to defaul temporary tablespace

SQL> alter database default temporary tablespace temp1;

Database altered.

3.

SQL> drop tablespace temp including contents and datafiles;

SQL> create temporary tablespace temp tempfile ‘xxx/temp01.dbf’ size 100m autoextend off extent management local uniform size 1m;

SQL> alter database default temporary tablespace temp;

4. Issue exists only for particular use then

SQL> alter user sys temporary tablespace temp1;