客户OGG抽取进程存在很大的延迟
查看抽取进程状态
EXTRACT E_TEST (PID 3178828) Current status: Recovery complete: Processing data with empty data queue Current read positions: Redo thread #: 1 Sequence #: 279152 RBA: 1062784468 Timestamp: 2015-01-28 16:34:14.000000 SCN: 3302.1032117830 Redo thread #: 2 Sequence #: 278094 RBA: 679934232 Timestamp: 2015-01-28 16:34:20.000000 SCN: 3302.1032123746 Current write position: Sequence #: 5 RBA: 65684322 Timestamp: 2015-01-28 17:01:01.945973 Extract Trail: /xxxx/ogg/dirdat/et |
GGSCI (racdb01) 18> send extract E_TEST,status Sending STATUS request to EXTRACT E_TEST ... EXTRACT E_TEST (PID 3178828) Current status: Recovery complete: Processing data with empty data queue Current read positions: Redo thread #: 1 Sequence #: 279152 RBA: 1062784468 Timestamp: 2015-01-28 16:34:14.000000 SCN: 3302.1032117830 Redo thread #: 2 Sequence #: 278094 RBA: 679934232 Timestamp: 2015-01-28 16:34:20.000000 SCN: 3302.1032123746 Current write position: Sequence #: 5 RBA: 65684322 Timestamp: 2015-01-28 17:01:52.455615 Extract Trail: /xxxx/ogg/dirdat/et |
查看当前日志状态
SQL> SET line 200 SQL> / GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- 1 1 279156 1072693248 2 NO CURRENT 1.4183E+13 2015-01-28 16:53:40 2 1 279154 1072693248 2 YES INACTIVE 1.4183E+13 2015-01-28 16:39:02 3 1 279155 1072693248 2 YES ACTIVE 1.4183E+13 2015-01-28 16:45:55 4 2 278097 1072693248 2 NO CURRENT 1.4183E+13 2015-01-28 16:48:37 5 2 278096 1072693248 2 YES INACTIVE 1.4183E+13 2015-01-28 16:34:20 6 2 278095 1072693248 2 YES INACTIVE 1.4183E+13 2015-01-28 16:25:41 6 ROWS selected. SQL> |
从以上信息来看ogg没有读取redo日志。由于暂时没有找到解决方法决定临时尝试增加参数让OGG只抽取归档日志
tranlogoptions ARCHIVEDLOGONLY |
在增加参数后抽取进程延迟一个当前redo的时间,但是还没有解决此问题。
数据库redo存放在裸设备上，Aix下的裸设备存在一个偏移量问题，怀疑是不是后期维护的时候增加redo日志，导致日志的偏移量不一样。
查看redo裸设备的偏移量
MOS文档：Oracle GoldenGate Supports Reading Redo And Archive Log files Stored On Raw Devices With Source Database On AIX OS And Also Requires TRANLOGOPTIONS RAWDEVICEOFFSET Parameter (文档 ID 1269855.1)
SQL> col member FOR a40 SQL> SELECT GROUP#,member FROM v$Logfile; GROUP# MEMBER ---------- ---------------------------------------- 3 /dev/xxxxxx3/lv_redo1_1g_21 3 /dev/xxxxxx1/lv_redo1_1g_22 2 /dev/xxxxxx2/lv_redo1_1g_11 2 /dev/xxxxxx3/lv_redo1_1g_12 1 /dev/xxxxxx1/lv_redo1_1g_01 1 /dev/xxxxxx2/lv_redo1_1g_02 4 /dev/xxxxxx1/lv_redo2_1g_01 4 /dev/xxxxxx2/lv_redo2_1g_11 5 /dev/xxxxxx2/lv_redo2_1g_02 5 /dev/xxxxxx3/lv_redo2_1g_12 6 /dev/xxxxxx3/lv_redo2_1g_21 GROUP# MEMBER ---------- ---------------------------------------- 6 /dev/xxxxxx1/lv_redo2_1g_22 |
racdb02$[/oracle]$ORACLE_HOME/bin/offset /dev/xxxxxx1/lv_redo2_1g_22 4096 racdb02$[/oracle]$ORACLE_HOME/bin/offset /dev/xxxxxx1/lv_redo1_1g_01 4096 racdb02$[/oracle] |
发现裸设备的offset为4096。配置文件里配置的为0.
tranlogoptions rawdeviceoffset 0 |
查看后发现redo文件偏移量为4K，但是OGG的配置文件里为0。
修改为 rawdeviceoffset 4096 ，重启进程后查看OGG抽取进程状态
GGSCI (racdb01) 251> !nfo info e_ab_1 EXTRACT E_AB_1 LAST Started 2015-01-30 12:19 STATUS RUNNING Checkpoint Lag 00:00:00 (updated 00:00:08 ago) Log READ Checkpoint Oracle Redo Logs 2015-01-30 13:29:25 Thread 1, Seqno 279890, RBA 365621600 SCN 3302.2129370429 (14184111381821) Log READ Checkpoint Oracle Redo Logs 2015-01-30 13:29:26 Thread 2, Seqno 278697, RBA 727540532 SCN 3302.2129371973 (14184111383365) |
OGG抽取进程正常。批量修改进程参数，重启全部进程，OGG抽取正常。
