Oracle DBA – A lifelong learning experience

Posts Tagged ‘v$archived_log’

Using DataGuard broker to show apply lag and throughput

Posted by John Hallas on June 20, 2017

To determine how much lag there is I normally run a script similar to this

select sequence#, applied, to_date(to_char(first_time,’DD-MON-YY:HH24:MI:SS’),
‘DD-MON-YY:HH24:MI:SS’) “First Time” ,
to_char(next_time,’DD-MON-YY:HH24:MI:SS’) “Next Time”
from v$archived_log
UNION
select NULL,database_role,NULL, db_unique_name from v$database
order by “First Time”;

However there is another way which I sometime use which actually gives a lot more information. This uses the dataguard broker command line. Use the show configuration parameter to determine database name if you are not sure

DGMGRL> show configuration

Configuration - DR

Protection Mode: MaxPerformance
 Databases:
 xxxxxx2a - Primary database
 xxxxxx2b - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Show specific database details.

DGMGRL> show database "xxxxxx2b"

Database - xxxxxx2b

Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 4 minutes 24 seconds (computed 6 seconds ago)
 Apply Lag: 5 minutes 16 seconds (computed 0 seconds ago)
 Apply Rate: 191.08 MByte/s
 Real Time Query: ON
 Instance(s):
 xxxxxx2b1 (apply instance)
 xxxxxx2b2
 xxxxxx2b3
 xxxxxx2b4
 xxxxxx2b5
 xxxxxx2b6
 xxxxxx2b7
 xxxxxx2b8

Database Status:
SUCCESS

Lots of good information there including which node is hosting the MRP process and also the apply rate. In our case that is normally between 150 and 400 MB per second.

Posted in Oracle | Tagged: , , , , , , | Leave a Comment »

Archived logs stuck ‘in-memory’ when building a standby

Posted by John Hallas on December 3, 2010

We had to build a new copy of a physical standby database (11.1.0.7) and the RMAN duplicate command had completed  successfully so all we needed to do was to let the archived logs catchup. After an hour or so this was the status as seen from v$archived_log


column "First Time" format A40
column applied format A10
column "Next Time" format A40
set linesize 120
set pagesize 1000

select   thread#, sequence#,       applied,
to_char(first_time,'DD-MON-YY:HH24:MI:SS') "First Time",
to_char(next_time,'DD-MON-YY:HH24:MI:SS') "Next Time"
from      v$archived_log
UNION
select   NULL,NULL,' ',NULL,null FROM DUAL
UNION
select   null,null,
db_unique_name,
database_role,
open_mode
from      v$database
/

and the output was (long list but necessary to see both threads). What baffled us why why there were in-memory logs and yet later logs  had been applied.

Read the rest of this entry »

Posted in Oracle, UKOUG | Tagged: , , , , | 6 Comments »

Managing dataguard – monitoring scripts

Posted by John Hallas on July 16, 2009

I am providing three scripts which I find useful in ensuring that the standby database is keeping up with the primary database in Dataguard setup.

@last

LOGS             TIME
---------------- ------------------
Last applied   :  16-JUL-09:09:24:16
Last received :  16-JUL-09:09:28:36
    select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
    where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
    union
    select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log);

@dg_stats

NAME                   VALUE                  UNIT
---------------------- ---------------------- -----------------------------------
apply finish time      +00 00:02:07.2         day(2) to second(1) interval
apply lag              +00 00:01:59           day(2) to second(0) interval
estimated startup time 16                     second
standby has been open  N
transport lag          +00 00:00:00           day(2) to second(0) interval

                                              Time Computed: 16-JUL-2009 09:33:16

   

select
    NAME Name,
    VALUE Value,
    UNIT Unit
    from v$dataguard_stats
    union
    select null,null,' ' from dual
    union
    select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
   from v$dataguard_stats;

A final option is to use v$standby_log

@last_redo

Redo onsite
--------------------
16-JUL-2009 09:42:44

   

select to_char(max(last_time),'DD-MON-YYYY HH24:MI:SS') "Redo onsite"
     from v$standby_log

None of them that clever, but all of them are useful.  Thanks to colleague Simon Ryan for pulling them together.

Posted in Oracle | Tagged: , , , , , | Leave a Comment »