Dataguard · Oracle

Monitor dataguard apply rate

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]
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s