VMCD.ORG » 主表缺少主键导致logic standby delay 一例

table缺少primary key 导致logic standby delay 一例

早上在主库做如下操作：

[oracle@racdg1 ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.5.0 – Production on Wed Dec 28 11:23:14 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> DECLARE
2 row_num NUMBER := 0;
BEGIN
FOR c_test IN (SELECT ROWID rid FROM MC_SITE_PRODUCT_PIC t ) LOOP
UPDATE MC_SITE_PRODUCT_PIC t SET t.UPDATE_FLAG = 0 WHERE ROWID = c_test.rid;
row_num := row_num + 1;
IF MOD(row_num,2000) =0 THEN
COMMIT;
dbms_lock.sleep(2);
END IF;
END LOOP;
COMMIT;
END; 3 4 5 6 7 8 9 10 11 12 13
14 /

PL/SQL procedure successfully completed.

SQL>

该表大约有150万行，选择update 2000条提交一次，导致逻辑备库出现delay

STATUS
————— —————————————————————————————————-
ORA-16116: no work available
ORA-16127: stalled waiting for additional transactions to be applied
ORA-16127: stalled waiting for additional transactions to be applied
ORA-16127: stalled waiting for additional transactions to be applied
ORA-16127: stalled waiting for additional transactions to be applied
ORA-16127: stalled waiting for additional transactions to be applied
ORA-16120: dependencies being computed for transaction at SCN 0x0007.1a92b5a4
ORA-16124: transaction 21 33 10269852 is waiting on another transaction
ORA-16113: applying change to table or sequence “PROD_DATA2″.”MC_SITE_PRODUCT_PIC”
ORA-16124: transaction 21 27 10269698 is waiting on another transaction
ORA-16124: transaction 21 6 10269799 is waiting on another transaction
ORA-16124: transaction 21 29 10269696 is waiting on another transaction
ORA-16124: transaction 10 37 2597623 is waiting on another transaction
ORA-16124: transaction 21 40 10269557 is waiting on another transaction
ORA-16124: transaction 33 35 358073 is waiting on another transaction
ORA-16124: transaction 33 16 357996 is waiting on another transaction
ORA-16124: transaction 21 46 10269647 is waiting on another transaction
ORA-16124: transaction 10 23 2598005 is waiting on another transaction
ORA-16124: transaction 21 13 10269728 is waiting on another transaction

—>5679 APPLIER ORA-16113: applying change to table or sequence “PROD_DATA2”.”MC_SITE_PRODUCT_PIC”更新出现延迟

APPLIED_SCN APPLIED_TIME RESTART_SCN RESTART_TIME LATEST_SCN LATEST_TIME MINING_SCN MINING_TIME
———– ——————- ———– ——————- ———- ——————- ———- ——————-
3.0511E+10 2011-12-28 09:12:06 3.0510E+10 2011-12-28 08:48:07 3.0511E+10 2011-12-28 09:27:28 3.0511E+10 2011-12-28 09:24:01

延迟超过了10分钟，并且有不断加大的趋势

查看5679 apply过程 :

SQL_ID CHILD outline/plan_hash_value Ex DISK_READS bg bg/exec rows LOAD_TIME
————- ———- —————————————————————- ———- ———- ————— ———— ———- ———–
8zy2mm8z8fvpw 0 745000887 109851 24 59862576 544.94 109851 12-28/00:44
TOTAL 109851 24 59862576 544.94 109851

发现主库已经更新完成150万，但是逻辑备库仅仅更新了10万条，执行计划走了 TABLE ACCESS FULL

查看主库的执行计划：

完全不同，查看该表的结构发现此表没有主键，导致logic standby的约束检查，导致logic standby 严重delay

解决方法:

SQL> create public database link xxx connect to xxx IDENTIFIED by xxx using ‘xxx’;

SQL> alter session db_file_multiblock_read_count = 128;

SQL> alter session set sort_area_size = 104857600;

SQL> alter session set workarea_size_policy=manual

SQL> alter database stop logical standby apply;

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE (‘xxx’, ‘xxx’, ‘xxx’);

SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP(‘DML’,’xx’,’xxx’);

SQL> alter database start logical standby apply immediate

使用 DBMS_LOGSTDBY.INSTANTIATE_TABLE 将表从pri端重新同步过来。注意上面的步骤需要在空闲时候做，不然可能导致大量的latch等待:
这次操作产生了大量以下事件，幸运的是table本身不是很大，同步大概花了几十秒而已，对于大表的同步切记小心：

latch: undo global data
latch: undo global data
latch: undo global data
latch: undo global data
latch: undo global data
latcg cache buffers chains
latch: cache buffers chains
latch: cache buffers chains
latch: cache buffers chains
latch: cache buffers chains

临时的解决方法可以先skip掉该表。最后强调一点,表结构最好添加主键，没有主键的表对类似ogg等同步工具的使用都会产生影响。