I need to thank Emre Baransel's Oracle Blog for pulling me through a tight spot for something I should have already known.
OEM 12c reports the following :-
There are approximately 3,753 seconds of redo not yet available on this standby database.
I know this means that there is a delay or a gap in the transport of REDO logs to a Data Guard Standby Database, but having the commands to hand so you can make a quick diagnosis of the problem, was briefly out of my grasp. So this is what you do.
Locate your Primary and Standby databases and get a sqlplus session running as sysdba on each.
On the primary
SELECT a.thread#,
b. last_seq,
a.applied_seq,
a. last_app_timestamp,
b.last_seq - a.applied_seq arc_diff
FROM (SELECT thread#,
MAX(sequence#) applied_seq,
MAX(next_time) last_app_timestamp
FROM gv$archived_log
WHERE applied = 'YES'
GROUP BY thread#) a,
(SELECT thread#,
MAX (sequence#) last_seq
FROM gv$archived_log
GROUP BY thread#) b
WHERE a.thread# = b.thread#;
Sadly at this point I find that the 'arc_diff' is now zero and the problem has gone from OEM too.
However ,the principle still applies, and when this happens again, I'll put the figures in.
THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
---------- ---------- ----------- -------------------- ----------
1 568343 568343 03-NOV-2014 12:02:23 0
Now looking on the Standby.
SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 568343
And
select process, status, thread#, sequence#, block#, blocks from v$managed_standby ;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 568343 12289 1833
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 568342 8193 2034
ARCH CONNECTED 0 0 0 0
MRP0 APPLYING_LOG 1 568344 7008 204800
RFS IDLE 1 568344 7010 116
RFS IDLE 0 0 0 0
RFS IDLE ; 0 0 0 0
8 rows selected.
•Standby database process status: You can run following query on standby database to see what MRP and RFS processes are doing, which block of which archivelog sequences are being shipped or being applied.
And lastly..... Apply/transport lags: v$dataguard_stats view will show the general synchronization status of standby database.
set lines 200
col name format a40
col value format a20
select * from v$dataguard_stats;
NAME VALUE UNIT TIME_COMPUTED
---------------------------------------- -------------------- ------------------------------ ------------------------------
apply finish time +00 00:00:00.0 day(2) to second(1) interval 03-NOV-2014 12:58:01
apply lag +00 00:00:06 day(2) to second(0) interval 03-NOV-2014 12:58:01
estimated startup time 15 second 03-NOV-2014 12:58:01
standby has been open N 03-NOV-2014 12:58:01
transport lag +00 00:00:00 day(2) to second(0) interval 03-NOV-2014 12:58:01
SQL>
And ..... Apply rate: To find out the speed of media recovery in a standby database, you can use this query
set lines 200
col type format a30
col ITEM format a20
col comments format a20
select * from v$recovery_progress;
START_TIME TYPE ITEM UNITS SOFAR TOTAL TIMESTAMP
-------------------- ------------------------------ -------------------- -------------------------------- ---------- ---------- --------------------
03-NOV-2014 08:41:01 Media Recovery Log Files Files 4 0
03-NOV-2014 08:41:01 Media Recovery Active Apply Rate KB/sec 241 0
03-NOV-2014 08:41:01 Media Recovery Average Apply Rate KB/sec 3 0
03-NOV-2014 08:41:01 Media Recovery Redo Applied Megabytes 48 0
03-NOV-2014 08:41:01 Media Recovery Last Applied Redo SCN+Time 2147483647 0 03-NOV-2014 12:50:07
03-NOV-2014 08:41:01 Media Recovery Active Time Seconds 13 0
03-NOV-2014 08:41:01 Media Recovery Apply Time per Log Seconds 2 0
03-NOV-2014 08:41:01 Media Recovery Checkpoint Time per Seconds 0 0
Log
03-NOV-2014 08:41:01 Media Recovery Elapsed Time Seconds 15598 0
9 rows selected.
Happyjohn
No comments:
Post a Comment