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
- #!/bin/ksh
- export ORACLE_HOME=/oracle/oracle/product/9.2
- export ORACLE_SID=ORCL
- export LD_LIBRARY_PATH=/oracle/oracle/product/9.2/lib
- export TNS_ADMIN=/oracle/oracle/product/9.2/network/admin
- export PATH=/oracle/oracle/product/9.2/bin:/usr/bin
- while true
- do
- sqlplus '/ as sysdba' << END
- spool $1/monsat.`date +%b%d_%T`
- set pagesize 10000 echo off feedback off TERMOUT OFF
- # Verify the state of the DG processes for more detailed
- # analysis if required
- select process, status , thread#, sequence#, blocks from v\$managed_standby;
- select max(sequence#), thread# from v\$log_history group by thread#;
- column event format a35
- column p1text format a20
- column p2text format a20
- # Obtain session wait information for more detailed
- # analysis if required
- select sid, event, p1, p1text, p2, p2text
- from v\$session_wait
- where wait_time !=0 and
- event not in ('rdbms ipc message','smon timer')
- order by wait_time desc;
- # Obtain file READ I/O and WRITE I/O times to ensure
- # there’s no IO bottlenecks on the standby. Should
- # be similar to production I/O times.
- column datafile format A45
- column tspace format A30
- select fs.*, df.name datafile, ts.name tspace
- from v\$filestat fs, v\$datafile df, v\$tablespace ts
- where fs.file#=df.file#
- and df.ts#=ts.ts#
- and PHYWRTS >0
- order by writetim desc;
- # Obtain top system wait events. Leveraged to get
- # average log file parallel write times on the standby.
- select * from v\$system_event where time_waited > 100
- order by time_waited desc;
- # Obtain sysstat detailed statistics for detailed
- # analysis if required
- Data Guard Primary Site and Network Configuration Best Practices Page 36
- select name, value from v\$sysstat where name like 'recovery%';
- spool off
- exit
- END
- sleep 60
- done
- exit 0
Vinod Sadanandan
Oracle DBA