Standby Database Monitoring & Protection Modes (9iR2)

[Vinod Sadanandan]

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

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

Thanks& Regards,
Vinod Sadanandan
Oracle DBA