ANBOB™

Oracle DataGuard是Oracle DATABASE的容灾方案，在负载不是极高的情况下，异步模式最大性能下确实像宣传的那样可以压秒级同步(async)， 也有一些金融客户对容灾要求极高配置DG为同步（sync）最大可用模式，会牺牲一些提交时的性能确认standby 反馈后给应用进程完成commit。 那常用的异步最大性能模式如何查询primary与standby之前的gap时延差异，oracle提供了一些view, 这类查询在MySQL主从最常见架构当前没有较准确的方法，直到当前MySQL8中binlog增强original_commit_timestamp功能在binlog中增加了original_commit_timestamp和immediate_commit_timestamp日志戳，跟同事讨论过确认也是需要
mysqlbinlog dump转储才可读，如下

#221028  8:58:55 server id 129  end_log_pos 276 CRC32 0x17f79afd        GTID    last_committed=0        sequence_number=1       rbr_
only=yes    original_committed_timestamp=1666918735086611   immediate_commit_timestamp=1666918735086611     transaction_length=390
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1666918735086611 (2022-10-28 08:58:55.086611 CST)
# immediate_commit_timestamp=1666918735086611 (2022-10-28 08:58:55.086611 CST)
/*!80001 SET @@session.original_commit_timestamp=1666918735086611*//*!*/;
/*!80014 SET @@session.original_server_version=80031*//*!*/;
/*!80014 SET @@session.immediate_server_version=80031*//*!*/;
SET @@SESSION.GTID_NEXT= '22228e8c-b0ee-11ec-a2d1-00163e23e2cc:70'/*!*/;

下面列一下，在Oracle中可用的查询当前Gap的方法

1. v$database
通过当前实际应用的SCN转换为时间戳

SQL> select scn_to_timestamp(current_scn) from v$database;

这里需要依赖SMON_SCN_TIME table转换SCN, 只有当数据库是Open时可以查询，也就是只有ADG时，否则会提示ORA-904 SMON_SCN_TIME无效表示符错误.也有一种情况:在存在GAP较大的情况下，SMON_SCN_TIME不是最新的，超出了映射范围，得到的ORA-08181:指定的数字不是有效的系统更改编号。在MOS中375401.1 记录了SMON_SCN_TIME保留策略

SMON wakes up every 5 minutes and checks how many on-disk mappings we have–the max is 144000.
The new mappings are then added for the last period (since SMON last updated), and if this is over 144000, SMON will then issue the delete statement:
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0)
There will be an execution of this each time SMON wakes to update smon_scn_time, and if one deletion does not free enough mappings, then there will be multiple executions.

如果没有Active Data Guard，需要去Primary端转换SCN为时间，或者从数据文件中读取SCN，但这不是最新的应用，而是最新的checkpoint,从V$DATABASE读取不依赖于Data Guard，当MRP未启动时也可用，并且在标准版非托管备用中也可用。

select max(checkpoint_time) from v$datafile_header;

2, gv$recovery_progress or V$SESSION_LONGOPS.
可以查

SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
Session altered.
select inst_id, max(timestamp) 
     from gv$recovery_progress group by inst_id;

当查看执行计划是是查询X$KSULOP的VIEW， 该X$ view也是V$SESSION_LONGOPS的源， 所以也可以使用查询V$SESSION_LONGOPS,当然这方法方法只能是session是ACTIVE，也就是MRP是启动时。

SQL> select inst_id,opname,timestamp from gv$session_longops 
where opname='Media Recovery' and target_desc='Last Applied Redo';

3, v$managed_standby
谈到MRP记录的内容在v$managed_standby中查看MRP状态的顺序和块#增加。因为我们可以比较剩余的工作，从RFS收到的:

SQL> select inst_id,process,status,client_process,thread#,sequence#,block#,blocks from gv$managed_standby;

4, v$archived_log
V$ARCHIVED_LOG有一个’ APPLIED ‘标志，但它在这里并没有真正的帮助，因为它没有考虑real-time apply,这个视图是关于归档日志的。但是在存档之前，redo流会被接收到standby log中。

select dest_id,thread#,sequence#,first_time,next_time,standby_dest,archived,registrar,applied,deleted,status 
from v$archived_log order by firest_change# desc fetch first 10 rows only;

-- or --
select t.*,arched-applied gap,sysdate etime  from (select dest_id, thread#,max(sequence#) arched,
 max(decode(applied,'YES',sequence#,1)) applied, 
max(decode(DELETED,'YES',sequence#,1)) DELETED 
from v$archived_log where resetlogs_change# in(select resetlogs_change# from v$database) 
-- and standby_dest='YES' # on primary 
group by thread#,dest_id ) t;	 

-- or --
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last in Sequence", APPL.SEQUENCE# "Last Applied Sequence", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

5, v$standby_log
使用实时应用(RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE)，一旦standby redo logs接收到redo，就立即应用redo,差距应该很小.可以在v$managed_standby中看到(来自LGWR和MRP的RFS之间的块数)。这查询读的是X$KCCSL需要读standby log,如果文件很大时，查询会很慢。

SQL> select max(last_time)      from gv$standby_log; 

6， v$dataguard_stats
DG Broker的show database查看gap也是来自该view.

SQL> select name||' '||value ||' '|| unit
     ||' computed at '||time_computed 
     from v$dataguard_stats;

另外对于Dataguard gap history,因为 V$ARCHIVED_LOG只有APPLIED没有APPLIED_TIME,同时有completion_time。对于非realtime apply应用的，可以使用如下脚本从alert log中获得applied_time计算应用GAP。

awk '
BEGIN {
print "set linesize 200 pagesize 1000"
print "column completion_time format a32"
print "column applied_time format a38"
print "column gap format a30"
s="with a as("
} 
/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9][.][0-9]*[+][0-9]*:[0-9]*/ {
sub(/T/," ");ts=$0
} 
/Media Recovery Log/{print s" select timestamp" q ts q "applied_time,"q $NF q "name from dual "; s=" union all"} 
END{
print ") select thread#,sequence#,cast(completion_time as timestamp) completion_time,applied_time,applied_timecompletion_time gap"
print " from a right outer join v$archived_log using(name) order by completion_time, applied_time;"
}
' q="'" $ORACLE_BASE/diag/rdbms/?*/?*/trace/alert_*.log |
sqlplus -s / as sysdba

— end —

打赏

微信扫一扫，打赏作者吧～