Standby database process status:
Following queries on standby database to see what MRP and RFS processes are doing, which block of which archivelog sequences are being shipped or being applied.
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 78 1 1680
ARCH CLOSING 1 77 1 181
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 79 1 93
MRP0 APPLYING_LOG 1 80 905 102400
RFS IDLE 0 0 0 0
RFS IDLE 1 80 906 1
Last applied log:
Following queries on the standby database to see the last applied archivelog sequence number for each thread.
SQL> SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 78
Archivelog difference:
Following queries archivelog difference: Run this on primary database
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (S
ELECT 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 WHER
E a.thread# = b.thread#;
THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
---------- ---------- ----------- -------------------- ----------
1 79 78 18-SEP-2014 04:14:01 1
Apply/transport lags:
Following queries the general synchronization status of standby database.
SQL> set linesize 120
SQL> set pagesize 500
SQL>
SQL> col name format a30
SQL> col value format a20
SQL> col TIME_COMPUTED format a15
SQL> col DATUM_TIME format a15
SQL>
SQL> select * from v$dataguard_stats;
Apply rate:
Following queries to find out the speed of media recovery in a standby database
SQL> set linesize 150
SQL> set pagesize 500
SQL> col type format a20
SQL> col ITEM format a20
SQL> col UNITS format a15
SQL> col SOFAR format 9999999
SQL> col comments format a20
SQL> col TOTAL format 999999
SQL> select * from v$recovery_progress;
START_TIME TYPE ITEM UNITS SOFAR TOTAL TIMESTAMP COMMENTS
-------------------- -------------------- -------------------- --------------- -------- ------- -------------------- --------------------
18-EYL-2014 01:02:19 Media Recovery Log Files Files 18 0
18-EYL-2014 01:02:19 Media Recovery Active Apply Rate KB/sec 573 0
18-EYL-2014 01:02:19 Media Recovery Average Apply Rate KB/sec 14 0
18-EYL-2014 01:02:19 Media Recovery Maximum Apply Rate KB/sec 574 0
18-EYL-2014 01:02:19 Media Recovery Redo Applied Megabytes 182 0
18-EYL-2014 01:02:19 Media Recovery Last Applied Redo SCN+Time 0 0 18-EYL-2014 04:45:08 SCN: 03045961
18-EYL-2014 01:02:19 Media Recovery Active Time Seconds 145 0
18-EYL-2014 01:02:19 Media Recovery Apply Time per Log Seconds 7 0
18-EYL-2014 01:02:19 Media Recovery Checkpoint Time per Seconds 0 0
Log
18-EYL-2014 01:02:19 Media Recovery Elapsed Time Seconds 13369 0
18-EYL-2014 01:01:54 Media Recovery Log Files Files 1 1
Following queries Redo apply mode on physical standby database:
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS where dest_id=1;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
Following queries what MRP process is waiting:
SQL> select a.event, a.wait_time, a.seconds_in_wait from gv$session_wait a, gv$session b where a.sid=b.sid and b.sid=(se
lect SID from v$session where PADDR=(select PADDR from v$bgprocess where NAME='MRP0'));
EVENT WAIT_TIME SECONDS_IN_WAIT
---------------------------------------------------------------- ---------- ---------------
class slave wait 0 1
Following reports redo switch of primary database
SQL> SET PAGESIZE 9999
SQL> col day format a15
SQL> SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM(SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1
) Count#, Min(RECID) Min#, Max(RECID) Max# FROM gv$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC
) A,(SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM gv$log ) B;
DAY COUNT# MIN# MAX# DAILY_AVG_MB
--------------- ---------- ---------- ---------- ------------
2014-09-18 19 61 79 950
2014-09-17 1 60 60 50
2014-09-16 1 59 59 50
2014-09-15 2 57 58 100
2014-09-14 1 56 56 50
2014-09-13 1 55 55 50
2014-09-12 1 54 54 50
2014-09-11 2 52 53 100
2014-09-10 1 51 51 50
2014-09-09 1 50 50 50
2014-09-08 2 48 49 100
2014-09-07 1 47 47 50
2014-09-06 1 46 46 50
2014-09-05 1 45 45 50
2014-09-04 2 43 44 100
2014-09-03 1 42 42 50
2014-09-02 1 41 41 50
2014-09-01 2 39 40 100
2014-08-31 1 38 38 50
2014-08-30 1 37 37 50
2014-08-29 1 36 36 50
2014-08-28 35 1 35 1750
-- This script should be executed on standby database
-- Time difference between primary and standby database
set serveroutput on;
DECLARE
v_diff NUMBER := 0;
v_hrs NUMBER := 0;
v_min NUMBER := 0;
v_sec NUMBER := 0;
p_dte1 DATE;
p_dte2 DATE;
date1 long;
date2 long;
BEGIN
date1 := 'select sysdate from dual';
date2 := 'select max(TIMESTAMP) from v$recovery_progress';
execute immediate date1 into p_dte1;
execute immediate date2 into p_dte2;
v_diff := ABS(p_dte2 - p_dte1);
v_hrs := TRUNC(v_diff, 0)*24;
v_diff := (v_diff - TRUNC(v_diff, 0))*24;
v_hrs := v_hrs + TRUNC(v_diff, 0);
v_diff := (v_diff - TRUNC(v_diff, 0))*60;
v_min := TRUNC(v_diff, 0);
v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);
DBMS_OUTPUT.put_line(
TO_CHAR(v_hrs) ||' '||
TO_CHAR(v_min) ||' '||
TO_CHAR(v_sec) );
END;
/
No comments:
Post a Comment