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
如果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)
比如：
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';
6.统计logical standby上是否有报错信息
SELECT xidusn, xidslt, xidsqn, status, status_code
FROM dba_logstdby_events
WHERE event_time = (SELECT MAX (event_time)
FROM dba_logstdby_events);
7.调整MAX_SGA -防止Pageouts
SQL> select value bytes from v$logstdby_stats where name='bytes paged out';
注：如果以上查询结果在增长,则查到当前MAX_SGA的大小:
SQL> select value from v$logstdby_stats where name = 'maximum SGA for LCR cache';
VALUE
------------------------
30
增大MAX_SGA，备注:10gR2,MAX_SGA可以增大到4095 MB.
SQL> alter database stop logical standby apply;
Database altered.
SQL> execute dbms_logstdby.apply_set('MAX_SGA',1000);
PL/SQL procedure successfully completed.
SQL> alter database start logical standby apply immediate;
Database altered.
逻辑备库需要将redo记录解析成LCR，会在shared pool里分配一部分空间来作为LCR Cache，如果cache太小，就会像OS的虚拟内存管理一样，需要做page out，这会严重影响应用日志的性能。默认情况下，LCR Cache为Shared pool的四分之一，最少不少于30M，否则SQL Apply不能启动。如果机器的内存足够，建议将LCR Cache尽量设大一点，当然，同时share pool也要足够大。如果机器内存有限，那可以考虑将buffer cache减少一点来给LCR Cache腾出空间。
8.跳过特定表或Schema的DML或DDL事务
alter database stop logical standby apply;
execute dbms_logstdby.skip (stmt => 'DML', schema_name => 'EYGLE',
object_name => 'SALES', proc_name => null);
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL', schema_name => 'EYGLE',
object_name => 'SALES', proc_name => null);
execute dbms_logstdby.skip (stmt => 'DML',
schema_name => 'EYGLE', object_name => '%', proc_name => null);
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL',
schema_name => 'EYGLE', object_name => '%', proc_name => null);
alter database start logical standby apply;
9.如何重新初始化数据表
通过以下查询确认当前的skip规则:
select * from dba_logstdby_skip;
建议取消Skip之后，再重新初始化:
alter database stop logical standby apply;
execute dbms_logstdby.unskip('DML','EYGLE','SALES');
exec dbms_logstdby.instantiate_table('EYGLE','SALES','dblink_name');
alter database start logical standby apply;
Oracle文档参考：
INSTANTIATE_TABLE Procedure
This procedure creates and populates a table in the standby database from a corresponding table in the primary database. The table requires the name of the database link (dblink) as an input parameter.
Use the INSTANTIATE_TABLE procedure to:
- Add a table to a standby database
- Re-create a table in a standby database
Syntax
DBMS_LOGSTDBY.INSTANTIATE_TABLE (
table_name IN VARCHAR2,
schema_name IN VARCHAR2,
dblink IN VARCHAR2);
Parameters
Table 29-4 describes the parameters for the INSTANTIATE_TABLE procedure.
Table 29-4 DBMS_LOGSTDBY.INSTANTIATE_TABLE Procedure Parameters
|
Parameter
|
Description
|
|
table_name
|
Name of the table to be created or re-created in the standby database.
|
|
schema_name
|
Name of the schema.
|
|
dblink
|
Name of the database link account that has privileges to read and lock the table in the primary database.
|
Exceptions
None.
Usage Notes
· Use this procedure to create and populate a table in a way that keeps the data on the standby database transactionally consistent with the primary database.
· This procedure assumes that the metadata has been maintained correctly.
· This table is not safe until the redo log that was current on the primary database at the time of execution is applied to the standby database.
Example
Enter this statement to create and populate a new table on the standby database.
SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE ('myschema', 'mytable', 'mydblink');