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