By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,353 Members | 1,555 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Standby Database Monitoring & Protection Modes (9iR2)

P: 16
STANDBY DATABASE MONITORING & PROTECION MODES (9iR2)

This document is written for understanding and monitoring standby database configured with diffrent protection modes .

MAXIMUM PROTECTION

-Guarantees that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to a standby redo log on at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down if a fault prevents it from writing its redo stream to at least one synchronized standby database.

MAXIMUM AVAILABILITY

-Provides the highest level of data protection that is possible without affecting the availability of the primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronized standby database. The primary database will not shut down if a fault prevents it from writing its redo stream to a synchronized standby database. Instead, the primary database will operate in RESYNCHRONIZATION until the fault is corrected and all log gaps have been resolved. When all log gaps have been resolved, the primary database automatically resumes operating in maximum availability

MAXIMUM PERFORMANCE

-Provides the highest level of data protection that is possible without affecting the performance of the primary database.A transaction will commit as soon as the redo data needed to recover that transaction is written to the local redo log. The primary database's redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data.

STANDBY DATABASE MONITORING
Expand|Select|Wrap|Line Numbers
  1. #!/bin/ksh
  2.  
  3. export ORACLE_HOME=/oracle/oracle/product/9.2
  4.  
  5. export ORACLE_SID=ORCL
  6.  
  7. export LD_LIBRARY_PATH=/oracle/oracle/product/9.2/lib
  8.  
  9. export TNS_ADMIN=/oracle/oracle/product/9.2/network/admin
  10.  
  11. export PATH=/oracle/oracle/product/9.2/bin:/usr/bin
  12.  
  13. while true
  14.  
  15. do
  16.  
  17. sqlplus '/ as sysdba' << END
  18.  
  19. spool $1/monsat.`date +%b%d_%T`
  20.  
  21. set pagesize 10000 echo off feedback off TERMOUT OFF
  22.  
  23. # Verify the state of the DG processes for more detailed
  24.  
  25. # analysis if required
  26.  
  27. select process, status , thread#, sequence#, blocks from v\$managed_standby;
  28.  
  29. select max(sequence#), thread# from v\$log_history group by thread#;
  30.  
  31. column event format a35
  32.  
  33. column p1text format a20
  34.  
  35. column p2text format a20
  36.  
  37. # Obtain session wait information for more detailed
  38.  
  39. # analysis if required
  40.  
  41. select sid, event, p1, p1text, p2, p2text
  42.  
  43. from v\$session_wait
  44.  
  45. where wait_time !=0 and
  46.  
  47. event not in ('rdbms ipc message','smon timer')
  48.  
  49. order by wait_time desc;
  50.  
  51. # Obtain file READ I/O and WRITE I/O times to ensure
  52.  
  53. # there’s no IO bottlenecks on the standby. Should
  54.  
  55. # be similar to production I/O times.
  56.  
  57. column datafile format A45
  58.  
  59. column tspace format A30
  60.  
  61. select fs.*, df.name datafile, ts.name tspace
  62.  
  63. from v\$filestat fs, v\$datafile df, v\$tablespace ts
  64.  
  65. where fs.file#=df.file#
  66.  
  67. and df.ts#=ts.ts#
  68.  
  69. and PHYWRTS >0
  70.  
  71. order by writetim desc;
  72.  
  73. # Obtain top system wait events. Leveraged to get
  74.  
  75. # average log file parallel write times on the standby.
  76.  
  77. select * from v\$system_event where time_waited > 100
  78.  
  79. order by time_waited desc;
  80.  
  81. # Obtain sysstat detailed statistics for detailed
  82.  
  83. # analysis if required
  84.  
  85. Data Guard Primary Site and Network Configuration Best Practices Page 36
  86.  
  87. select name, value from v\$sysstat where name like 'recovery%';
  88.  
  89. spool off
  90.  
  91. exit
  92.  
  93. END
  94.  
  95. sleep 60 
  96.  
  97. done
  98.  
  99. exit 0
  100.  
Thanks& Regards,
Vinod Sadanandan
Oracle DBA
Nov 21 '07 #1
Share this Article
Share on Google+