Wikipedia

Search results

Monday, 23 June 2014

ICAR Project - Environment

Cron entry in AIX Unix
*********************

bash-3.2# cat /var/spool/cron/crontabs/oracle
30 20 * * * /FMSBKP/RMAN_BACKUP/scripts/rman_dailyDB_PRODAPPS > /FMSBKP/RMAN_KUP/scripts/rman_dailyDB_PRODAPPS.log
00 08,13,17 * * * /FMSBKP/Scripts/Health_Check/HealthCheck.sh
bash-3.2#

RMAN Configuration
*******************

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 4;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/FMSBKP/RMAN_BACKUP/PROD/b_%u_%p_%c';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/FMSBKP/RMAN_BACKUP/PROD/snapcf_FMS.f';

RMAN Script
*************

oracle@DBPRD1 $ cat rman_dailyDB_PRODAPPS

export ORACLE_SID=PROD
export ORACLE_HOME=/FMS1/11G/DB/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:$PATH

N_DATE=`date +"%u"`

$ORACLE_HOME/bin/rman target / nocatalog log=/FMSBKP/RMAN_BACKUP/PROD/dailyDB_$N_DATE.log cmdfile=/FMSBKP/RMAN_BACKUP/scripts/rman_dailyDB_PRODAPPS.rman

#dat=`date +"%b%d%Y"`
#export dat
#mkdir /nfs_bkp/FMSPROD_DBBKP/RMAN_BACKUP_$dat
#find /FMS2/RMAN_BACKUP -mtime -1 -type f -exec cp  {} /nfs_bkp/FMSPROD_DBBKP/RMAN_BACKUP_$dat \;
exit;
oracle@DBPRD1 $ cat rman_dailyDB_PRODAPPS.rman

run{
DELETE OBSOLETE;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/FMSBKP/RMAN_BACKUP/PROD/b_%u_%p_%c';
BACKUP as compressed backupset DATABASE PLUS ARCHIVELOG;
Backup current controlfile format '/FMSBKP/RMAN_BACKUP/PROD/%d_prod_control_%s_%p_%u';
REPORT OBSOLETE;
CROSSCHECK BACKUP;
CROSSCHECK COPY;
DELETE EXPIRED BACKUP;
DELETE EXPIRED COPY;
DELETE OBSOLETE;
RESTORE DATABASE VALIDATE;
}

Health Checkup Script 
********************

ORACLE_HOME=/FMS1/11G/DB/product/11.2.0/dbhome_1
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/lib32:$ORACLE_HOME/jdk/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
ORACLE_SID=PROD
export ORACLE_SID

dat=`date +"%b-%d-%Y"`
export dat

cd /FMSBKP/Scripts/Health_Check
SCR_PATH=/FMSBKP/Scripts/Health_Check
export SCR_PATH

 Checking Tablespaces usage > 85%

sqlplus -s  "/ as sysdba" <<EOF
set pages 1000;
spool $SCR_PATH/Tblspc_Check.log

select tablespace_name, ROUND(bytes_free,2) "Free Bytes" , ROUND(bytes_total,2) "Total Size", perused "% Used" from (select a.tablespace_name, a.bytes_free,b.bytes_total, ROUND((100-(100*a.bytes_free/b.bytes_total)),2)  perused from (Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a, (Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b where a.tablespace_name=b.tablespace_name) where tablespace_name not like '%UNDOTB%'  ORDER BY tablespace_name;

spool off
EOF

# Checking Blocking Sessions

sqlplus -s  "/ as sysdba" <<EOF
set linesize 500
set pagesize 1000

spool $SCR_PATH/Blocking_Sess.log

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
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
EOF

# Checking for indexes that need to be rebuilt:
sqlplus -s  "/ as sysdba" <<EOF
set linesize 500
set pagesize 1000

spool $SCR_PATH/Index_Rebuild.log

select index_name, owner from dba_indexes where blevel >4;

spool off
EOF

#Checking Space Usage on All the production servers

#df -k > $SCR_PATH/Space_usage.log


# Checking Workflow Status

sqlplus -s  "/ as sysdba" <<EOF
Set pages 1000;
set lines 180;
col COMPONENT_NAME format a60;
col COMPONENT_STATUS format a15;
col STARTUP_MODE format a18;
spool $SCR_PATH/Workflow_Check.log

select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, apps.fnd_svc_components fsc where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

spool off
EOF


# Checking the Pending Concurrent Requests

sqlplus -s  "/ as sysdba" <<EOF
Set Pages 58
Set Linesize 75

spool $SCR_PATH/Request_Check.log

Column Reqst     Format 9999999
Column Orcl      Format A8
Column Priority  Format 99999
Column Program   Format A10
Column Start_At  Format A15
Column Req_Date  Format A15

Column Reqst    HEADING 'Request|ID'
Column Orcl     HEADING 'Oracle|Name'
Column Priority HEADING 'Priority'
Column Program  HEADING 'Program'
Column Req_Date HEADING 'Requested at'
Column Start_At HEADING 'To Start at'
Break On Report
Comput Count OF Reqst ON Report
TTITLE 'Pending for Conflict Resolution Manager'

select request_id Reqst,  Oracle_Username Orcl, Priority,
       Concurrent_Program_Name Program,
       To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
       To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At,
       Run_Alone_Flag, Single_Thread_Flag, Fcp.Enabled_Flag
from apps.fnd_concurrent_requests Fcr, apps.Fnd_Concurrent_Programs Fcp,
     apps.fnd_oracle_userid O
where Status_Code = 'Q'
  And (
       Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
          Program_Application_ID = Application_ID )
  And  Hold_Flag = 'N'
  And  Fcr.Oracle_ID = O.Oracle_ID
  And  Requested_Start_Date <= Sysdate
Order By  Requested_Start_Date Asc,
          Decode(Priority, Null, 9999999, Priority) Asc,
          Request_ID Asc
/


TTITLE 'Pending for Conflict Resolution Manager|( requested to run at a later time )'

select request_id Reqst,  Oracle_Username Orcl, Priority,
       Concurrent_Program_Name Program,
       To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
       To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At,
       Run_Alone_Flag, Single_Thread_Flag, Fcp.Enabled_Flag
from apps.fnd_concurrent_requests Fcr, apps.Fnd_Concurrent_Programs Fcp,
     apps.fnd_oracle_userid O
where Status_Code = 'Q'
  And (
       Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
          Program_Application_ID = Application_ID )
  And  Hold_Flag = 'N'
  And  Fcr.Oracle_ID = O.Oracle_ID
  And  Requested_Start_Date > Sysdate
Order By  Requested_Start_Date Asc,
          Decode(Priority, Null, 9999999, Priority) Asc,
          Request_ID Asc
/


TTITLE 'Pending for Conflict Resolution Manager|( on Hold )'

select request_id Reqst,  Oracle_Username Orcl, Priority,
       Concurrent_Program_Name Program,
       To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
       To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At,
       Run_Alone_Flag, Single_Thread_Flag, Fcp.Enabled_Flag
from apps.fnd_concurrent_requests Fcr, apps.Fnd_Concurrent_Programs Fcp,
     apps.fnd_oracle_userid O
where Status_Code = 'Q'
  And (
       Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
          Program_Application_ID = Application_ID )
  And  Hold_Flag = 'Y'
  And  Fcr.Oracle_ID = O.Oracle_ID
Order By  Requested_Start_Date Asc,
          Decode(Priority, Null, 9999999, Priority) Asc,
          Request_ID Asc
/


TTITLE 'Waiting Concurrent Requests'

select request_id Reqst,  Oracle_Username Orcl, Priority,
       Concurrent_Program_Name Program,
       To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
       To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At,
       Run_Alone_Flag, Single_Thread_Flag, Fcp.Enabled_Flag
from apps.fnd_concurrent_requests Fcr, apps.Fnd_Concurrent_Programs Fcp,
     apps.fnd_oracle_userid O
where Status_Code = 'A'
  And (
       Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
          Program_Application_ID = Application_ID )
  And  Fcr.Oracle_ID = O.Oracle_ID
Order By  Requested_Start_Date Asc,
          Decode(Priority, Null, 9999999, Priority) Asc,
          Request_ID Asc
/


TTITLE OFF
Set Pages 60
Set Linesize 80
Clear Breaks
Clear Computes

spool off
EOF

#Sending Mail to onlineDBA

echo " ================ Health check details for ICAR Production =============== " > $SCR_PATH/Health_Check.log

echo "  " >> $SCR_PATH/Health_Check.log
echo "1.Workflow Mailer Notification(ICAR PROD) Status on $dat ">> $SCR_PATH/Health_Check.log
echo "---------------------------------------------" >> $SCR_PATH/Health_Check.log
cat $SCR_PATH/Workflow_Check.log >> $SCR_PATH/Health_Check.log
echo "  " >> $SCR_PATH/Health_Check.log

echo "2.Pending Concurrent Requests Status of ICAR PROD on $dat ">> $SCR_PATH/Health_Check.log
echo "---------------------------------------------" >> $SCR_PATH/Health_Check.log
cat $SCR_PATH/Request_Check.log >> $SCR_PATH/Health_Check.log
echo "  " >> $SCR_PATH/Health_Check.log

echo "3.Tablespace Usage on ICAR PROD on $dat ">> $SCR_PATH/Health_Check.log
echo "---------------------------------------------" >> $SCR_PATH/Health_Check.log
cat $SCR_PATH/Tblspc_Check.log >> $SCR_PATH/Health_Check.log
echo "  " >> $SCR_PATH/Health_Check.log

echo "4.Space Usage details on ICAR PROD on $dat ">>  $SCR_PATH/Health_Check.log
echo "---------------------------------------------" >> $SCR_PATH/Health_Check.log
cat $SCR_PATH/Space_usage.log >>  $SCR_PATH/Health_Check.log
echo "  " >> $SCR_PATH/Health_Check.log

echo "5.Blocking Session details on ICAR PROD on $dat ">> $SCR_PATH/Health_Check.log
echo "---------------------------------------------" >> $SCR_PATH/Health_Check.log
cat $SCR_PATH/Blocking_Sess.log >> $SCR_PATH/Health_Check.log
echo "  " >> $SCR_PATH/Health_Check.log

echo "6.Indexes that need to be rebuilt ICAR  PROD on $dat">> $SCR_PATH/Health_Check.log
echo "---------------------------------------------" >> $SCR_PATH/Health_Check.log
cat  $SCR_PATH/Index_Rebuild.log >> $SCR_PATH/Health_Check.log
echo "  " >> $SCR_PATH/Health_Check.log

echo " Thanks and Regards " >> $SCR_PATH/Health_Check.log
echo " IASRI Team " >> $SCR_PATH/Health_Check.log
echo " " >> $SCR_PATH/Health_Check.log

SUBJECT="Health Check details for OF Production on $dat"
EMAIL_ADDRESS="narender@iasri.res.in,icarerphelp@iasri.res.in,narender303@gmail.com,vijayksh@in.ibm.com,kumar.sourabh@in.ibm.com,anilsharma@in.ibm.com,varsohar@in.ibm.com"
ATTACHED_FILE="$SCR_PATH/Health_Check.log"

cat "$ATTACHED_FILE" | mailx -s "$SUBJECT" "$EMAIL_ADDRESS"
exit
 

No comments:

Post a Comment