整理logical standby测试中一些经常用到的sql语句。

1. 1 1. 查看logical standby上当前正在apply的redo log

COLUMN DICT_BEGIN FORMAT A15;
COLUMN FILE_NAME FORMAT A30;
SET NUMF 9999999;
col FCHANGE# format 9999999999999;
col NCHANGE# for 999999999999999999999;
set line 200
SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#,NEXT_CHANGE# AS NCHANGE#, TIMESTAMP, DICT_BEGIN AS BEG, DICT_END AS END,THREAD# AS THR# ,applied FROM DBA_LOGSTDBY_LOG ORDER BY THREAD# ;

2. 查看logical standby节点apply进程状态
select sid,type,status_code,status from v$logstdby_process;

查看applyer进程的个数
SELECT COUNT(*) AS APPLIER_COUNT FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER';

查看空闲的applyer进程
SELECT COUNT(*) AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16166;

3. 调整logical standby的apply进程数

ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop SQL Apply

EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20); --- 调整apply进程数为20，默认为5个

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; --- Start real-time Apply

确认logical standby上的空闲APPLIER进程

SELECT COUNT(*) AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16166;

注：status_code = 16166 表示进程是空闲状态，可以看到"STATS"为"ORA-16116: no work available"

如何根据系统表现调整APPLIER进程的个数，统计transactions的apply状态，

SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'transactions%';

NAME VALUE

-------------------------- ----------------------------

transactions ready 159

transactions applied 159

SQL>

如果ready(等待apply)和applied(已经apply)的值基本同步，则设置的APPLIER进程合适或偏多。根据IDLE_APPLIER的进程数，可减少APPLIER进程数目.如果transactions ready - transactions applied的差比APPLIER进程数的2倍还多，则需要增加APPLIER进程数目了。

4. 调整PREPARER(调制机)的进程数

logical standby上有很多transactions等待apply, 但是还有空闲的applyer进程，且已经没有idle状态的PREPARER进程，这时需要增加PREPARER(调制机)的进程数。如下：

ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop SQL Apply

EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 4); --- 调整PREPARER进程数为4

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; --- Start real-time Apply

5. 计算logical standby的apply速度

可以通过v$logstdby_stats视图统计logical standby的apply速度，计算公式如下：

apply_rate = bytes of redo processed / (coordinator uptime - seconds system is idle)

比如：

SQL> select name,value from v$logstdby_stats where name in ('coordinator uptime' , 'seconds system is idle' , 'bytes of redo processed' );

NAME VALUE

--------------------------- ---------------------------------

coordinator uptime 78717

bytes of redo processed 7954813012

seconds system is idle 40

logical standby.apply_rate = 7954813012/(78717-40)/1024/1024

整理成1条sql语句计算出apply_rate如下：

select c.value / (a.value - b.value)/1024/1024 as "APPLY_RATE"

from v$logstdby_stats a, v$logstdby_stats b, v$logstdby_stats c

where a.name = 'coordinator uptime' and b.name = 'seconds system is idle' and c.name = 'bytes of redo processed';

qhswac希望大家健康平平安安幸福吉祥