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;
-----------------自己整理的
