473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,326 developers and data experts.

Standby Database Monitoring & Protection Modes (9iR2)

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
0 4690

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Mahesh Hardikar | last post by:
Hi , Oracle 8.1.7.0.0 on HP-UX 11.0 EMC2 Disk Array We are in a process of implementing DR Site using Standby Database feature of Oracle. We have tested it on 2 NT machines here & things are...
0
by: Cherrish Vaidiyan | last post by:
hello, Thanx for the suggestions on my Listener query. Now i am performing a simple work.. STANDBY DATABASE creation. I have followed the instraction from Oracle 9i Release 1 documentation...
0
by: Cherrish Vaidiyan | last post by:
sir, The following are the steps that i followed in setting up standby database on Red hat Linux 9. i am using Oracle 9i. i have followed the steps in this site : ...
3
by: Cherrish Vaidiyan | last post by:
Sir, A Warm and Smart 2004 greetings to all. I have a problem with standby database.I have been posting queries but no response ..... I am using 2 system running on Red Hat Linux9 and...
0
by: Officer | last post by:
I want to setup standby database which reside on the same host with my primary database -dbSA is my primary and dbSB is my standby database. I select v$log as follows: SVRMGR> select...
346
by: rkusenet | last post by:
http://biz.yahoo.com/rc/040526/tech_database_marketshare_1.html Interesting to see that database sales for windows is more than Unix.
5
by: Phyxius | last post by:
I just had a new computer built. My previous computer was also a custom and I was using Windows XP home. I purchased a new XP Home edition from Microsoft and installed it into the new computer-not...
3
by: Scott S. | last post by:
Does anyone know how to disable standby and any other power saver settings when my app is running? My app runs on computers that use touch screens and there is a bug in the driver that doesn't...
6
by: shorti | last post by:
db2 8.1 fix pack 12 on AIX 5.3 This is a newly configured HADR machine. The HADR was up and running. I was 'playing' around some on the standby and did a db2 deactivate and things sort of went...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.