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;

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