STANDBY DATABASE MONITORING & PROTECTION MODES (9iR2)

This document is written for understanding and monitoring standby database configured with different 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

#!/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

select name, value from v\$sysstat where name like 'recovery%';
spool off
exit
END
sleep 60
done
exit 0

Ref: Data Guard Primary Site and Network Configuration Best Practices Page 36