一、调整APPLIER进程数:
查看一下当前的空闲APPLIER进程情况:
v$logstdby_process显示standby端的进程使用情况
SQL> select * from v$logstdby_process where type='APPLIER' and status_code=16166;
no rows selected
其中status_code=16166表示该进程是空闲进程
没有返回结果,有两种可能:一方面,可能是因为当前没有需要应用的日志,没有启动应用进程;另一方面就是当前的应用非常繁忙,需要DBA去增减APPLIER进程。
检查事物的应用情况:
SQL> select name,value from v$logstdby_stats where name like 'transactions%';
NAME VALUE
-------------------- --------------------
transactions ready 25
transactions applied 25
将两个值想减(ready-applied),如果该值超出空闲APPLIER的进程两倍,则说明APPLIER的值太少,需要天剑APPLIER的进程数; 反之如果该值比空闲APPLIER的进程数小的多,则证明APPLIER的进程数过多,可以适量的减少APPLIER的进程数
查看当前启动的APPLIER的进程数:
SQL> select count(*) from v$logstdby_process where type='APPLIER';
COUNT(*)
----------
5
设置APPILER的进程数:
SQL> alter database stop logical standby apply;
Database altered.
SQL> exec dbms_logstdby.apply_set('APPLY_SERVERS',10)
PL/SQL procedure successfully completed.
启动SQL应用,并查看进程的设置:
SQL> alter database start logical standby apply immediate;
Database altered.
SQL> select count(*) from v$logstdby_process where type='APPLIER';
COUNT(*)
----------
10
SQL> select * from v$logstdby_stats where name='number of appliers';
NAME VALUE
-------------------- --------------------
number of appliers 10
二、调整PREPAPER的进程数目
PREPAPER进程将接受到的REDO数据中的块修改转换成LCRs(Logical Change Records),当APPLIER进程有空闲进程,而Transactions Ready还很多,简单的说就是“供不应求”的时候,需要加大供给,适当的增大PREPAPER进程数。
检查空闲的PREPARER进程数
SQL> select count(*) from v$logstdby_process
2 where type='PREPARER' and status_code=16166
3 /
COUNT(*)
----------
0
与APPLIER进程相似,该查询结果为0并不代表没有空闲的PREPARER进程,也可能是当前没有REDO数据需要处理
调整PREPARER进程数的方法:
查看当前的PERPARER进程数
SQL> select * from v$Logstdby_stats where name='number of preparers';
NAME VALUE
-------------------- --------------------
number of preparers 1
或者:
SQL> select count(*) from v$logstdby_process where type='PREPARER';
COUNT(*)
----------
1
停止SQL应用并设置PREPARER进程数:
SQL> alter database stop logical standby apply;
Database altered.
SQL> exec dbms_logstdby.apply_set('PREPARE_SERVERS',3);
PL/SQL procedure successfully completed.
启动SQL应用,并查看新的PREPARER进程数目:
SQL> alter database start logical standby apply immediate;
Database altered.
SQL> select * from v$logstdby_stats where name='number of preparers';
NAME VALUE
-------------------- --------------------
number of preparers 3
SQL> select count(*) from v$logstdby_process where type='PREPARER';
COUNT(*)
----------
3
三、调戏LCR使用的内存:
LCR中保存的是转换后的块修改的记录,这部分数据保存在SGA中
查看当前LCR可用的最大内存:(显示单位为MB)
SQL> select * from v$logstdby_stats where name like '%SGA%LCR%';
NAME VALUE
------------------------------ --------------------
maximum SGA for LCR cache 30
调整LCR大小的步骤:
SQL> alter database stop logical standby apply;
Database altered.
SQL> exec dbms_logstdby.apply_set('MAX_SGA',50)
PL/SQL procedure successfully completed.
SQL> alter database start logical standby apply immediate;
Database altered.
查看调整后的LCR最大内存:
SQL> select * from v$logstdby_stats where name like '%SGA%LCR%';
NAME VALUE
------------------------------ --------------------
maximum SGA for LCR cache 50
四、调整事物应用方式:
默认情况下逻辑Standby端的事物顺序与primary数据库提交的事物顺序是一样的;
设置逻辑Standby的书屋顺序不按primary数据库顺序执行:
SQL> alter database stop logical standby apply;
Database altered.
SQL> exec dbms_logstdby.apply_set('PRESERVE_COMMIT_ORDER', 'FALSE');
PL/SQL procedure successfully completed.
SQL> alter database start logical standby apply immediate;
Database altered.
恢复逻辑Standby数据库的事物执行顺序:
SQL> exec dbms_logstdby.apply_unset('PRESERVE_COMMIT_ORDER')
PL/SQL procedure successfully completed.
SQL> alter database start logical standby apply immediate;
Database altered.
-----EDN-----
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29320885/viewspace-1107828/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29320885/viewspace-1107828/