Friday, November 13, 2009

MAA for Active Dataguard

If you have some spare time you may want to read the MAA doc for Active Dataguard.

http://www.oracle.com/technology/deploy/availability/pdf/maa_wp_11gr1_activedataguard.pdf

Interesting things to note :-

1. a select statement to verify if a database is in Active Dataguard mode

SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE='READ ONLY';

2. a realtime select statement to verify Apply lag (the values in v$dataguard_stats are not realtime)

select scn_to_timestamp((select current_scn from v$database))-scn_to_timestamp((select current_scn from v$database@rtq_stby)) from dual;

3. A new statspack product for checking standby databases (it gets installed on the primary DB)

454848.1 Installing and Using Standby Statspack in 11gR1

Statspack cannot be executed on a standby due to its read-only nature therefore tuning the performance of the apply process involves manually collecting statistics.

In 11gR1, using the new Active Data Guard option, users can now use statspack from the primary database to collect data from a standby database that is opened read-only and performing recovery. The standby statspack is installed in a separate schema on the Primary database, STDBYPERF which is then propagated to the standby. This new user does not have DBA privileges and has no access to local V$ tables.