Tuesday, August 14, 2012

DBA_LOGSTDBY_LOG, APPLIED column is 'CURRENT'

Why does Logical Standby say an applied log is 'CURRENT' instead of 'YES' when the Standby is up to date? Reason is because there's at least one session on the Primary that hasn't yet committed or rolled back. Until a commit or rollback occurs on the Primary the Standby keeps the logs in a 'CURRENT' state and on the Standby server filesystem as they may still be required to apply transactions.

(output below formatted to fit)

column file_name heading 'Archive Log' format a80
column applied heading 'Applied' format a30

select file_name, sequence#, timestamp,
decode(applied,'NO',applied||' - Not Applied Logs!','YES',applied||' - OK',applied) applied
from dba_logstdby_log
order by sequence#;

Archive Log                                                   SEQUENCE# TIMESTAMP            Applied
-------------------------------------------------------------------------------- ---------- -------------------- ------------------------------
/oradata/MYDB/arch/logapplyarch/arch1_1247463_699468119.log    1247463 10.AUG.2012 16:16:33 CURRENT
/oradata/MYDB/arch/logapplyarch/arch1_1247464_699468119.log    1247464 10.AUG.2012 16:17:04 CURRENT
...
...

The 10.2 documentation doesn't give many details:

"APPLIED, Indicates whether each archive log has been applied (YES) or not (NO)"

  • 10.2 DBA_LOGSTDBY_LOG Data Dictionary View
  • The 11.2 documentation has more details:

    "APPLIED, CURRENT - SQL Apply is currently applying changes contained in the foreign archived log"

  • 11.2 DBA_LOGSTDBY_LOG Data Dictionary View
  • "Since transactions can span multiple archived log files, multiple archived log files may show the value CURRENT in the APPLIED column."

  • 11.2 Data Guard Concepts and Administration - DBA_LOGSTDBY_LOG View
  • To resolve: On the Primary look for a session that has a lock requested around the time when the first (oldest) 'CURRENT' archive log appeared (16:16:33 in the above).

    
    set linesize 500
    
    column program format a40
    column event format a30
    column username format a15
    column osuser format a15
    column lock_requested format a30 heading 'Lock Requested'
    column object_name format a30
    
    select
            s.sid,
            s.serial#,
            s.username,
            s.osuser,
            s.program,
            s.event,
            l.type,
            decode(l.lmode,
                    0, 'NONE',
                    1, 'NULL',
                    2, 'ROW SHARE',
                    3, 'ROW EXCLUSIVE',
                    4, 'SHARE',
                    5, 'SHARE ROW EXCLUSIVE',
                    6, 'EXCLUSIVE', '?') "Mode",
            decode(l.request,
                     0, 'NONE',
                     1, 'NULL',
                     2, 'ROW SHARE',
                     3, 'ROW EXCLUSIVE',
                     4, 'SHARE',
                     5, 'SHARE ROW EXCLUSIVE',
                     6, 'EXCLUSIVE', '?') "Request",
            o.object_name,
            to_char(sysdate-l.ctime / 86400,'DD-MON-YYYY HH24:MI:SS') lock_requested
    from
            v$lock l, dba_objects o, v$session s
    where l.id1 = o.object_id (+)
    and l.sid = s.sid
    order by l.sid, l.type
    /
    
           SID     SERIAL# USERNAME     OSUSER  PROGRAM                    EVENT                          TY Mode                Request     OBJECT_NAME      Lock Requested
    --------- ----------------------- ------ ------------------------- ------------------------------ -- ------------------- ------------- ------------------ -----------------------------
    ...
    
           113      34978 SCOTT         scott     prog@tigerserv (TNS V1-V3)    SQL*Net message from client    TM ROW EXCLUSIVE NONE         scott25X         10-AUG-2012 16:16:32
           113      34978 SCOTT         scott     prog@tigerserv (TNS V1-V3)    SQL*Net message from client    TM ROW EXCLUSIVE NONE         scott25J         10-AUG-2012 16:16:32
           113      34978 SCOTT         scott     prog@tigerserv (TNS V1-V3)    SQL*Net message from client    TM ROW EXCLUSIVE NONE         scott25I         10-AUG-2012 16:16:32
           113      34978 SCOTT         scott     prog@tigerserv (TNS V1-V3)    SQL*Net message from client    TX EXCLUSIVE     NONE    
    ...
    ...
    
    
    Above there was a DML lock at 16:16:32 on the Primary. Investigate why the session has uncommitted transactions. There is nothing necessarily wrong but if the Standby has been uncharacteristically building up 'CURRENT' applied logs for hours or the filesystem is starting to fill up with shipped archive logs then maybe there is an SQL performance problem or it could be something as simple as a user has not exited their session.

    No comments:

    Post a Comment