BASH SCRIPT
Monitoring the apply rate of the dataguard is pretty important. If you have a slow disk system or you had a batch update on the primary system then the dataguard might not catch up with the primary database. On the dataguard database you can use the v$recovery_progress view for this purpose. I wrote a bash script so that I can monitor the apply rate, last apply rate on the system. On the other hand I wanted to monitor the system resources so that I use the “top” command.
#!/bin/bash
date;
hostname;
export ORACLE_HOME=/oracle/product/11.2.0
export ORACLE_SID=TESTDB
echo "*******************************************************************************************************************"
sqlplus -S / as sysdba <<EOF
set lines 200
set feedback off
set pages 0
col comments for a20
col start_time for a30
col item for a30
col units for a15
col last_apply_time for a30
select to_char(R.START_TIME,'DD.MON.YYYY HH24:MI')start_time ,
R.ITEM, R.UNITS, R.SOFAR ,
to_char(R.TIMESTAMP,'DD.MON.YYYY HH24:MI:SS') last_apply_time
from v\$recovery_progress R
where start_time = (select max(start_time) from v\$recovery_progress) order by start_time, item;
select process, status, thread#, sequence#, block#, blocks from v\$managed_standby order by 1;
EOF
echo ""
echo "*******************************************************************************************************************"
top -cbn 1 | head -n 40
USAGE
It is pretty nice to use watch command on linux for continuous update and monitoring of the screen. It refreshes the screen every n minutes.
watch -n 5 ./guard_apply_takip.sh
OUTPUT:
Every 5.0s: ./guard_apply_takip.sh Sat Apr 5 13:22:27 2014
Sat Apr 5 13:22:27 EEST 2014
*******************************************************************************************************************
START_TIME ITEM UNITS SOFAR LAST_APPLY_TIME
----------------- -------------------------------- --------------- ---------- --------------------
05.APR.2014 11:56 Active Apply Rate KB/sec 58062
05.APR.2014 11:56 Active Time Seconds 4998
05.APR.2014 11:56 Apply Time per Log Seconds 9
05.APR.2014 11:56 Average Apply Rate KB/sec 43143
05.APR.2014 11:56 Checkpoint Time per Log Seconds 2
05.APR.2014 11:56 Elapsed Time Seconds 5137
05.APR.2014 11:56 Last Applied Redo SCN+Time 0 04.APR.2014 22:57:54
05.APR.2014 11:56 Log Files Files 436
05.APR.2014 11:56 Maximum Apply Rate KB/sec 84430
05.APR.2014 11:56 Redo Applied Megabytes 216436
*******************************************************************************************************************
top - 13:22:28 up 3 days, 1:20, 3 users, load average: 4.47, 3.69, 3.55
Tasks: 502 total, 1 running, 501 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.8%us, 0.3%sy, 0.0%ni, 84.1%id, 14.6%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 148443976k total, 101675228k used, 46768748k free, 379152k buffers
Swap: 141590520k total, 0k used, 141590520k free, 1974632k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
18689 root 16 0 761m 130m 79m S 55.7 0.1 101:34.00 /opt/simpana/Base/cvd
23924 oracle 15 0 90.3g 67m 27m S 50.0 0.0 12:14.53 ora_pr00_TESTDB
23971 oracle 15 0 90.2g 24m 18m S 42.3 0.0 3:13.71 ora_pr08_TESTDB
23975 oracle 15 0 90.2g 22m 16m S 30.7 0.0 3:17.01 ora_pr09_TESTDB
23983 oracle 15 0 90.2g 24m 17m S 21.1 0.0 3:18.59 ora_pr0b_TESTDB
23943 oracle 15 0 90.2g 24m 18m S 13.4 0.0 3:08.81 ora_pr01_TESTDB
23967 oracle 15 0 90.2g 22m 16m S 13.4 0.0 3:09.19 ora_pr07_TESTDB
23987 oracle 15 0 90.2g 24m 18m S 11.5 0.0 3:20.01 ora_pr0c_TESTDB
23947 oracle 15 0 90.2g 24m 18m S 9.6 0.0 3:19.58 ora_pr02_TESTDB
23951 oracle 15 0 90.2g 24m 17m S 7.7 0.0 3:25.46 ora_pr03_TESTDB
23959 oracle 15 0 90.2g 24m 18m S 7.7 0.0 3:22.38 ora_pr05_TESTDB
23963 oracle 15 0 90.2g 24m 18m S 7.7 0.0 3:11.85 ora_pr06_TESTDB
23955 oracle 15 0 90.2g 24m 18m S 5.8 0.0 3:09.00 ora_pr04_TESTDB
23979 oracle 15 0 90.2g 24m 18m S 5.8 0.0 3:10.95 ora_pr0a_TESTDB
4537 oracle 15 0 13020 1296 720 R 1.9 0.0 0:00.01 top -cbn 1
18860 oracle 18 0 90.2g 19m 14m S 1.9 0.0 0:12.26 ora_dia0_TESTDB
1 root 15 0 10368 684 572 S 0.0 0.0 0:06.21 init [5]
2 root RT -5 0 0 0 S 0.0 0.0 0:02.20 [migration/0]
3 root 34 19 0 0 0 S 0.0 0.0 0:00.89 [ksoftirqd/0]
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 [watchdog/0]
5 root RT -5 0 0 0 S 0.0 0.0 0:00.29 [migration/1]
6 root 34 19 0 0 0 S 0.0 0.0 0:01.68 [ksoftirqd/1]
7 root RT -5 0 0 0 S 0.0 0.0 0:00.00 [watchdog/1]
8 root RT -5 0 0 0 S 0.0 0.0 0:00.51 [migration/2]
9 root 34 19 0 0 0 S 0.0 0.0 0:02.17 [ksoftirqd/2]
10 root RT -5 0 0 0 S 0.0 0.0 0:00.00 [watchdog/2]
11 root RT -5 0 0 0 S 0.0 0.0 0:00.15 [migration/3]
12 root 34 19 0 0 0 S 0.0 0.0 0:02.23 [ksoftirqd/3]
13 root RT -5 0 0 0 S 0.0 0.0 0:00.00 [watchdog/3]
14 root RT -5 0 0 0 S 0.0 0.0 0:00.31 [migration/4]
15 root 34 19 0 0 0 S 0.0 0.0 0:01.17 [ksoftirqd/4]
16 root RT -5 0 0 0 S 0.0 0.0 0:00.00 [watchdog/4]
17 root RT -5 0 0 0 S 0.0 0.0 0:00.24 [migration/5]
Notes On Oracle 