逻辑备库的sql apply

其中只能preparer/applier进程可以有多个，可以通过dbms_logstdby.apply_set来设定，之前需要先停止逻辑备库

execute dbms_logstdby.apply_set('PREPARE_SERVERS', 2);

execute dbms_logstdby.apply_set('APPLY_SERVERS', 8);

一般来说，一个preparer进程可以满足20个applier进程的需要；通过v$logstdby_stats查看preparer/applier的当前状态

SQL> select name,value from v$logstdby_stats where name like 'transactions%';

NAME                           VALUE

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

transactions ready             154100

transactions applied           154058

SQL> select name,value from v$logstdby_stats where name like 'number%';

NAME                           VALUE

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

number of preparers            2

number of appliers              8

各个sql apply进程的状态可以通过v$logstdby_process来获取

SQL> select sid,type,status from v$logstdby_process;

  SID TYPE         STATUS

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

 1080 COORDINATOR  ORA-16116: no work available

 1085 READER       ORA-16127: stalled waiting for additional transactions to be applied

 1023 BUILDER      ORA-16127: stalled waiting for additional transactions to be applied

 1025 PREPARER     ORA-16127: stalled waiting for additional transactions to be applied

 1083 PREPARER     ORA-16127: stalled waiting for additional transactions to be applied

 1084 ANALYZER     ORA-16120: dependencies being computed for transaction at SCN 0x0582.1294c01c

 1073 APPLIER      ORA-16124: transaction 57 17 2322872 is waiting on another transaction

 1049 APPLIER      ORA-16124: transaction 44 7 4445763 is waiting on another transaction

 1059 APPLIER      ORA-16124: transaction 59 5 2277932 is waiting on another transaction

 1092 APPLIER      ORA-16124: transaction 13 8 12274153 is waiting on another transaction

 1036 APPLIER      ORA-16124: transaction 2 18 24555358 is waiting on another transaction

 1051 APPLIER      ORA-16124: transaction 45 0 4338376 is waiting on another transaction

 1064 APPLIER      ORA-16124: transaction 56 11 2362783 is waiting on another transaction

 1071 APPLIER      ORA-16113: applying change to table or sequence "NINGOO"."TEST"

LCR

逻辑备库需要将redo记录解析为LCR，LCR cache从shared pool中分配；如果分配太小，则会像OS的虚拟内存一样做page out;

比如：一个事务跨越了5个logfile，逻辑备库重启后需要从第一个没有commit的事务开始解析日志，LCR cache太小导致分析logfile产生大量page out；调大LCR Cache，很快解决；

默认情况为shared pool的四分之一；

设置前先停止逻辑备库

EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SGA', 1000);

查看当前系统中的page out来调整LCR Cache

SQL> select name,value from v$logstdby_stats where name like '%page%';

NAME                           VALUE

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

bytes paged out                0

seconds spent in pageout       0

查看LCR Cache利用率

select name,(least(max_sga,bytes)/max_sga) * 100 pct_utilization

from ( select * from v$sgastat where name = 'Logminer LCR c'

),

(select value*(1024*1024) max_sga from dba_logstdby_parameters

where name = 'MAX_SGA'

);

NAME                           PCT_UTILIZATION

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

Logminer LCR c                             100

事务一致性 10gR2

参数Preserve_commit_order

True: 默认级别，事务应用顺序很主库的完全一致

False: 不管事务顺序，性能提升，但可能会读取不一致的数据

自动删除已经应用过的日志

先停止备库

execute dbms_logstdby.apply_set('LOG_AUTO_DELETE', FALSE);

更改logminer的默认表空间(sysaux)

exec DBMS_LOGMNR_D.set_tablespace('USERS');

10.2.0.4逻辑备库不支持in memory undo

主库执行exec dbms_logstdby.build的时候会自动配置supplement logging，而开启supplement logging会自动禁用IMU

逻辑备库做failover

停止备库应用，激活备库

alter database activate logical standby database finish apply;

若还有其他逻辑备库，则需要在剩余备库创建指向新主库的dblink

alter session disable guard;
create database link newprimary connect to user identified by password using ‘newprimary’;
alter session enable guard;

然后重新指向新的主库

alter database start logical standby apply new primary newprimary;

ITPub博客

logical standby日常概念