OGG常见问题处理

1403： ORA-01403: No data found 在执行PL/SQL块、存储过程、函数、触发器等，如果需要进行操作的记录没有查询到，则会返回1403的错误 Goldengate中的1403 在目标段复制进程中，godengate从日志中解析出sql语句，根据sql语句的where条件，在目标段的库中匹配不出相应的记录
1403错误原因: 1. 附加日志相关
? 数据相关 ? 表结构不一致/索引重建
常见问题： 1.生产端I/O过大、内存占用过大 配置了多个Extract进程导致 2.进程“假死” 1>参数配置错误： TRANLOGOPTIONS rawdeviceoffset 0 此参数只在AIX使用裸设备时配置，其他环境不能配置 2>查看进程处理状态： GGSCI>send repxxx, status send repxxx status Sending STATUS request to REPLICAT REPXXX ... Current status: Processing data Sequence #: 1 RBA: 3861681 21157 records in current transaction 3、RAC节点时钟不同步（OGG-01028） 1> 配置参数： THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 90000 IOLATENCY 90000 2> 配置NTP时钟同步服务器
4、队列文件不自动删除 1>实施时，执行了多次： ADD EXTTRAIL 。。。 ADD RMTTRAIL 。。。 2>处理细节参照运维文档进行处理
5、参数文件中用户名处理 1> TRANLOGOPTIONS EXCLUDEUSER USERNAME 这里的USERNAME是需要替换为实际的GoldenGate用户名
6、参数文件中字符集处理 1> setenv (NLS_LANG=“AMERICAN_AMERICA.ZHS16GBK”) 这里的AMERICAN_AMERICA.ZHS16GBK是需要替换为实际数据库的字符集设置
7、参数文件中压缩参数 1> RMTHOST *.*.*.*, MGRPORT 7839, compress 这里compress一定要配置，加快传输速度，减少datapump的延迟
9、容灾端入库性能优化 1> 无主键表建议增加主键或唯一索引 2> 将大表单独拆分为独立的进程，增加参数： BATCHSQL

OGG如何跳过长事务？ GGSCI (cdla6702.netjets.com) 13> send extract EXT1I2D4, showtrans
Sending SHOWTRANS request to EXTRACT EXT1I2D4 ...
Oldest redo log file necessary to restart Extract is:

Redo Log Sequence Number 4762, RBA 77272080
-----------------------------------------------------------
XID: 3.9.209025
Items: 0
Extract: EXT1I2D4
Redo Thread: 1
Start Time: 2014-03-22:00:11:06
SCN: 14.1966161432 (62095703576)
Redo Seq: 4762
Redo RBA: 77272080
Status: Running
send extract EXT1I2D4, skiptrans 3.9.209025 ---->>>此处应该为XID，不是SCN

Question
Can I turn on the Oracle Supplemental Log at the DB Level only, without doing it at the Table Level?


Answer
GoldenGate requires adding Supplemental Logging at the Table Level, regardless of the Database Supplemental setting, due to issues with Multiple Unique Keys and/or lack of Keys.

It is highly recommended to use "ADD TRANDATA" under the GGSCI interface. If using the SQL command to add Supplemental Logging at the Table Level, ALL the keys should be included (ex. 2 separate Unique Keys are all required).

如何打开附加日志： To turn on supplemental logging at the database level, GoldenGate requires this command to be executed:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

This change to add supplemental logging will not take effect until the current redo log is switched, so the following command must also be executed:

ALTER SYSTEM SWITCH LOGFILE.

(Note: You must have the ALTER DATABASE and ALTER SYSTEM privileges to execute the above sql statements.)

To turn on supplemental logging at the table level, you can execute this command:

alter table <table_name> add supplemental log group ggs_mytab (<column_name>, <column_name>) always;

(Note: You must have the ALTER TABLE privilege to execute the above sql statement.)

Or you can turn on supplemental logging through GGSCI with this command:

GGSCI> dblogin userid <user>, password <pw>

GGSCI> add trandata <schema>.<table>
如何确定附加日志是否打开： 1> 数据库级别 select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, FORCE_LOGGING from v$database;
2> 表级别 select * from dba_log_groups where OWNER='<schema_name_in_upper_case>' and TABLE_NAME='<table_name_in_upper_case>'; ---如果有数据返回，则说明附加日志已经打开，反之没有打开

For a particular table, you can find which columns are part of the Supplemental Log group with the query below:

select LOG_GROUP_NAME, COLUMN_NAME, POSITION from
dba_log_group_columns
where OWNER='<schema_name_in_upper_case>' and
TABLE_NAME='<table_name_in_upper_case>'
order by position;

For a particular table, you can find out if Supplemental Logging is turned on through GGSCI with the commands below:

GGSCI> dblogin userid <user>, password <pw>
GGSCI> info trandata <schema>.<table>
select distinct a.sql_id,a.event,b.sql_text from v$session a,v$sql b where a.username='GGUSER' and a.program like 'replicat%' and a.status='ACTIVE' and a.sql_id=b.sql_id;

-----------------自己整理的