Oracle Database Commands and Practical Examples

Crontab - Physical standby Archive sync details on mail

Crontab - Physical standby Archive sync details on mail

Crontab Details :

Check in every 6 hrs and send mail

0 */6 * * * /u04/MasterDB/scripts/DR_check/dr_check1.sh

Script Output:

oracle@test01:~> cat /u04/MasterDB/scripts/DR_Check/dr_check1.sh

#*******DR HOURLY STATUS REPORT*******
#
# Prepared by VARUN YADAV.
#
#*************************************
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Setting Oracle Environment Variables
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/DR_check/DR_Status.log
DR_log=/u04/MasterDB/scripts/DR_Check/dr_1.log
printf "to:abc@tech.com\n" > $DR_log
#printf "cc:abc@tech.com\n" >> $DR_log
printf "subject: Server DR HOURLY STATUS REPORT " >> $DR_log

printf "\nDR Status report for `uname -n` at `date` \n" >> $DR_log

sqlplus -s "/as sysdba" <<EOF >> $DR_log

set lines 200
select name, instance_name,db_unique_name, open_mode, database_role, flashback_on current_scn,to_char(standby_became_primary_scn) failover_scn from v\$database,v\$instance;
PROMPT
PROMPT
select process,status,sequence# from v\$managed_standby;
PROMPT
PROMPT

PROMPT
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V\$LOG_HISTORY GROUP BY THREAD#;
PROMPT

PROMPT
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V\$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V\$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#;

EOF
/usr/sbin/sendmail -t < $DR_log

oracle@test01:~>

OUTPUT :

DR Status report for test01 at Tue Nov 12 00:00:01 CET 2019

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------

PRODB PRODB_DR PRODB_DR READ WRITE PRIMARY YES 469372332

PROCESS STATUS SEQUENCE#

--------- ------------ ----------

ARCH OPENING 28118

ARCH CONNECTED 0

LNS OPENING 28119

ARCH CONNECTED 0

PROCESS STATUS SEQUENCE#

--------- ------------ ----------

ARCH CONNECTED 0

ARCH CLOSING 28122

ARCH CONNECTED 0

PROCESS STATUS SEQUENCE#

--------- ------------ ----------

ARCH CONNECTED 0

ARCH CLOSING 28118

ARCH CONNECTED 0

31 rows selected.

THREAD# LAST_APPLIED_LOG

---------- ----------------

1 28125

Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

1 28125 28125 0

Crontab -Daily Sar output Example in mail alert system

#### Daily Sar Output ######################

Crontab Details:

26 09 * * * /u04/MasterDB/scripts/daily_health_check/sar_output/sar.sh

Script Output:

oracle@test02:~> cat /u04/MasterDB/scripts/daily_health_check/sar_output/sar.sh

DD=`date +%d%m%y`

cd /u04/MasterDB/scripts/daily_health_check/sar_output

touch /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log

sar 3600 24 > /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log

cat /u04/MasterDB/scripts/daily_health_check/sar_output/sar_${DD}.log >> /u04/MasterDB/scripts/daily_health_check/sar_output/sar.tmp

mailx -s " Hourly OS CPU Utilization SAR Details " abc@abc.com< sar.tmp

rm -rf sar.tmp

exit

oracle@test02:~>

SAR OUTPUT :

Linux 4.12.14-122.20-default (test022) 05/25/20 _x86_64_ (32 CPU)

09:26:01 CPU %user %nice %system %iowait %steal %idle
10:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
11:26:01 all 0.53 0.00 0.19 0.01 0.00 99.28
12:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
13:26:01 all 0.52 0.00 0.19 0.01 0.00 99.28
14:26:01 all 0.57 0.00 0.21 0.01 0.00 99.22
15:26:01 all 0.54 0.00 0.19 0.01 0.00 99.26
16:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
17:26:01 all 0.53 0.00 0.19 0.01 0.00 99.28
18:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
19:26:01 all 0.53 0.00 0.19 0.01 0.00 99.28
20:26:01 all 0.56 0.00 0.20 0.01 0.00 99.22
21:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
22:26:01 all 0.60 0.00 0.20 0.01 0.00 99.20
23:26:01 all 0.59 0.00 0.24 0.01 0.00 99.16
00:26:01 all 0.54 0.00 0.19 0.02 0.00 99.26
01:26:01 all 0.52 0.00 0.18 0.02 0.00 99.28
02:26:01 all 0.56 0.00 0.20 0.02 0.00 99.22
03:26:01 all 0.54 0.00 0.18 0.02 0.00 99.26
04:26:01 all 0.52 0.00 0.18 0.01 0.00 99.28
05:26:01 all 1.64 0.00 0.21 0.02 0.00 98.13
06:26:01 all 0.53 0.00 0.19 0.01 0.00 99.27
07:26:01 all 0.53 0.00 0.18 0.02 0.00 99.27
08:26:01 all 0.56 0.00 0.20 0.01 0.00 99.23
09:26:01 all 0.54 0.00 0.19 0.01 0.00 99.26
Average: all 0.59 0.00 0.19 0.01 0.00 99.21

Crontab - Expdp Scripts with mail example

########################### EXPDP ###########################

Crontab Details :

25 06 * * * /u04/MasterDB/scripts/expdp_smrprodb_full.sh 2>&1 >>/u04/MasterDB/expdp/RODB_FULL.log | mail -s "EXPDP Production database backup logs" varun.yadav@mailid.com

Script Output :

oracle@test02:~> cat /u04/MasterDB/scripts/expdp_smrprodb_full.sh

export ORACLE_BASE=/u02/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1

export ORACLE_SID=PRODB

export PATH=$PATH:$ORACLE_HOME/bin

expdp abc/abc@PRODB dumpfile=expdp-$(date +%Y-%m-%d_%H-%M-%S).dmp directory=expdp logfile=expdp-$(date +%Y-%m-%d_%H-%M-%S).log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_PRODB parallel=2

#Moving log files

mv /u04/MasterDB/expdp/expdp*.log /u04/MasterDB/expdp/expdp_fulldb_backup_logs

#Granting permissions for Dump files

chmod -R 775 /u04/MasterDB/expdp/

#Compressing files

gzip /u04/MasterDB/expdp/*.*

#Removing 2 days old dump files

#find /u04/MasterDB/expdp/ -mtime +2 -exec rm {} \;

HOME

Tuesday 26 May 2020

Crontab - Physical standby Archive sync details on mail

Crontab -Daily Sar output Example in mail alert system

Crontab - Expdp Scripts with mail example