External Table On GoldenGate Discard Files
goldengate 做为数据同步的利器，越来越普遍，往往会因为某些原因replicat进程在应用trail文件时会失败，进程要么Abending也可以也忽略记录到 Discard Files中，默认在OGG_HOME/dirrpt/ *.dsc,可以用文本工具查看内容，当然也可以更方便的用pl/sql function 读取文件，做为一张外部表（external table）查询，比如查询记录发生了多少个错误。(db alert日志有时也可以建外部表)
下面是一种方法
1，创建一个想查看的信息条目的TYPE
CREATE OR REPLACE TYPE DiscardRecord
AS OBJECT
(
message_type VARCHAR2(7)
, message VARCHAR2(120)
, message_date DATE
, description VARCHAR2(500)
, line_number NUMBER
, oracle_error VARCHAR2(10)
, error_number NUMBER(5)
, source_object_owner VARCHAR2(30)
, source_object_name VARCHAR2(30)
, target_object_owner VARCHAR2(30)
, target_object_name VARCHAR2(30)
, error_operation VARCHAR2(20)
, error_object_owner VARCHAR2(30)
, error_object_name VARCHAR2(30)
, error_action VARCHAR2(30)
, error_column VARCHAR2(120)
, error_value VARCHAR2(200)
, pk_table_name VARCHAR2(30)
, operation_seqno NUMBER
, operation_rba NUMBER
)
/
CREATE OR REPLACE TYPE DiscardTable
AS TABLE OF DiscardRecord
/
2, 创建一个disc文件路径的directory
system@GGS>CREATE OR REPLACE DIRECTORY GGDiscard AS ‘/oracle/ogg11r2/dirrpt’;
3， 创建一个pipelined function读disc文件，我的文件扩展表是.dsc,如果不是请修改下面代码的红色字体标志处
/* Formatted on 2012-12-11 11:07:31 (QP5 v5.185.11230.41888) */
CREATE OR REPLACE FUNCTION read_discard (DiscardName VARCHAR2)
RETURN DiscardTable
PIPELINED
IS
FileHandle UTL_FILE.FILE_TYPE;
DiscardRec DISCARDRECORD;
DiscardExtra DISCARDRECORD;
FileBuffer VARCHAR2 (32767);
PartLine VARCHAR2 (32767);
OutputRow BOOLEAN;
ErrorRow BOOLEAN;
FindingColumn BOOLEAN;
LineNumber NUMBER := 0;
/* Cursors */
CURSOR get_cons_cols_curs (
ConstraintOwner VARCHAR2,
ConstraintName VARCHAR2)
IS
SELECT column_name
FROM dba_cons_columns
WHERE owner = ConstraintOwner
AND constraint_name = ConstraintName
AND position = 1;
CURSOR get_pk_table_curs (
ConstraintOwner VARCHAR2,
ConstraintName VARCHAR2)
IS
SELECT pk.table_name
FROM dba_constraints fk, dba_constraints pk
WHERE pk.owner = fk.r_owner
AND pk.constraint_name = fk.r_constraint_name
AND fk.owner = ConstraintOwner
AND fk.constraint_name = ConstraintName;
CURSOR get_pk_col_curs (
TableOwner VARCHAR2,
TableName VARCHAR2)
IS
SELECT col.column_name
FROM dba_cons_columns col, dba_constraints pk
WHERE pk.owner = TableOwner
AND pk.table_name = TableName
AND pk.owner = col.owner
AND pk.constraint_name = col.constraint_name
AND pk.constraint_type = 'P'
AND col.position = 1;
/* Procedures */
PROCEDURE OpenDiscard
IS
BEGIN
FileHandle := UTL_FILE.FOPEN ('GGDISCARD', DiscardName || '.dsc', 'R');
END OpenDiscard;
PROCEDURE ReadDiscard
IS
BEGIN
UTL_FILE.GET_LINE (FileHandle, FileBuffer);
LineNumber := LineNumber + 1;
END ReadDiscard;
PROCEDURE CloseDiscard
IS
BEGIN
UTL_FILE.FCLOSE (FileHandle);
END CloseDiscard;
PROCEDURE InitialiseRecord (Discard_INOUT IN OUT DISCARDRECORD)
IS
BEGIN
Discard_INOUT :=
DISCARDRECORD (NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL);
END InitialiseRecord;
BEGIN
OpenDiscard;
ErrorRow := FALSE;
FindingColumn := FALSE;
InitialiseRecord (DiscardRec);
LOOP
ReadDiscard;
WHILE FileBuffer IS NULL
LOOP
ReadDiscard;
END LOOP;
/* Read first 2 words */
IF FileBuffer = '*'
THEN
PartLine := '*';
ELSE
PartLine :=
SUBSTR (FileBuffer,
1,
INSTR (FileBuffer,
' ',
2,
2)
- 1);
END IF;
OutputRow := FALSE;
CASE PartLine
WHEN 'Oracle GoldenGate'
THEN
IF ErrorRow
THEN
PIPE ROW (DiscardRec);
InitialiseRecord (DiscardRec);
ErrorRow := FALSE;
END IF;
DiscardRec.MESSAGE_TYPE := 'INFO';
DiscardRec.MESSAGE :=
SUBSTR (FileBuffer,
19,
INSTR (FileBuffer,
' ',
19,
1)
- 19)
|| ' Process '
|| SUBSTR (
FileBuffer,
INSTR (FileBuffer, 'for Oracle process') + 19,
INSTR (FileBuffer, ',')
- (INSTR (FileBuffer, 'for Oracle process') + 19));
-- DiscardRec.message := 'Capture Process '||SUBSTR(FileBuffer,47,INSTR(FileBuffer,',')-47);
DiscardRec.description :=
SUBSTR (
FileBuffer,
INSTR (FileBuffer, ',') + 2,
INSTR (FileBuffer, ':') - (INSTR (FileBuffer, ',') + 2));
DiscardRec.message_date :=
TO_DATE (SUBSTR (FileBuffer, INSTR (FileBuffer, ':') + 2),
'YYYY-MM-DD HH24:MI:SS');
DiscardRec.line_number := LineNumber;
OutputRow := TRUE;
WHEN 'Process Abending'
THEN
IF ErrorRow
THEN
PIPE ROW (DiscardRec);
InitialiseRecord (DiscardRec);
ErrorRow := FALSE;
END IF;
DiscardRec.MESSAGE_TYPE := 'WARNING';
DiscardRec.MESSAGE := PartLine;
DiscardRec.message_date :=
TO_DATE (SUBSTR (FileBuffer, INSTR (FileBuffer, ':') + 2),
'YYYY-MM-DD HH24:MI:SS');
DiscardRec.line_number := LineNumber;
OutputRow := TRUE;
WHEN 'Current time:'
THEN
IF ErrorRow
THEN
PIPE ROW (DiscardRec);
InitialiseRecord (DiscardRec);
END IF;
DiscardRec.MESSAGE_TYPE := 'ERROR';
DiscardRec.message_date :=
TO_DATE (SUBSTR (FileBuffer, INSTR (FileBuffer, ':') + 2),
'YYYY-MM-DD HH24:MI:SS');
DiscardRec.line_number := LineNumber;
ErrorRow := TRUE;
OutputRow := FALSE;
WHEN ' Error text'
THEN
DiscardRec.oracle_error :=
SUBSTR (
FileBuffer,
INSTR (FileBuffer, ', ') + 2,
INSTR (FileBuffer, ':') - (INSTR (FileBuffer, ', ') + 2));
DiscardRec.error_number :=
TO_NUMBER (
SUBSTR (DiscardRec.oracle_error,
INSTR (DiscardRec.oracle_error, '-') + 1));
DiscardRec.MESSAGE :=
SUBSTR (FileBuffer,
INSTR (FileBuffer, ':') + 2,
INSTR (FileBuffer,
',',
1,
2)
- (INSTR (FileBuffer, ':') + 2));
DiscardRec.description :=
SUBSTR (FileBuffer, INSTR (FileBuffer, 'SQL '));
OutputRow := FALSE;
WHEN 'Operation failed'
THEN
DiscardRec.operation_seqno :=
SUBSTR (
FileBuffer,
INSTR (FileBuffer, 'seqno ') + 6,
INSTR (FileBuffer, ' rba ')
- (INSTR (FileBuffer, 'seqno ') + 6));
DiscardRec.operation_rba :=
SUBSTR (FileBuffer, INSTR (FileBuffer, ' rba ') + 5);
OutputRow := FALSE;
WHEN 'OCI Error'
THEN
DiscardRec.oracle_error :=
SUBSTR (FileBuffer,
INSTR (FileBuffer,
' ',
1,
2)
+ 1,
INSTR (FileBuffer, ':')
- ( INSTR (FileBuffer,
' ',
1,
2)
+ 1));
DiscardRec.error_number :=
TO_NUMBER (
SUBSTR (DiscardRec.oracle_error,
INSTR (DiscardRec.oracle_error, '-') + 1));
DiscardRec.MESSAGE :=
SUBSTR (
FileBuffer,
INSTR (FileBuffer, ':') + 2,
INSTR (FileBuffer, ',') - (INSTR (FileBuffer, ':') + 2));
DiscardRec.description :=
SUBSTR (FileBuffer, INSTR (FileBuffer, 'SQL '));
CASE DiscardRec.error_number
WHEN 1
THEN
PartLine :=
SUBSTR (
DiscardRec.MESSAGE,
INSTR (DiscardRec.MESSAGE, '(') + 1,
INSTR (DiscardRec.MESSAGE, ')')
- (INSTR (DiscardRec.MESSAGE, '(') + 1));
DiscardRec.error_object_owner :=
SUBSTR (PartLine, 1, INSTR (PartLine, '.') - 1);
DiscardRec.error_object_name :=
SUBSTR (PartLine, INSTR (PartLine, '.') + 1);
WHEN 2291
THEN
PartLine :=
SUBSTR (
DiscardRec.MESSAGE,
INSTR (DiscardRec.MESSAGE, '(') + 1,
INSTR (DiscardRec.MESSAGE, ')')
- (INSTR (DiscardRec.MESSAGE, '(') + 1));
DiscardRec.error_object_owner :=
SUBSTR (PartLine, 1, INSTR (PartLine, '.') - 1);
DiscardRec.error_object_name :=
SUBSTR (PartLine, INSTR (PartLine, '.') + 1);
ELSE
NULL;
END CASE;
OutputRow := FALSE;
WHEN 'Discarding record'
THEN
DiscardRec.error_action :=
SUBSTR (FileBuffer,
INSTR (FileBuffer,
' ',
1,
4)
+ 1,
INSTR (FileBuffer,
' ',
1,
5)
- ( INSTR (FileBuffer,
' ',
1,
4)
+ 1));
DiscardRec.error_number :=
TO_NUMBER (SUBSTR (FileBuffer, INSTR (FileBuffer, ' ', -1)));
DiscardRec.oracle_error :=
'ORA-' || TO_CHAR (DiscardRec.error_number, 'FM09999');
OutputRow := FALSE;
WHEN 'Problem replicating'
THEN
PartLine :=
SUBSTR (FileBuffer,
INSTR (FileBuffer,
' ',
1,
2)
+ 1,
INSTR (FileBuffer, ' to ')
- ( INSTR (FileBuffer,
' ',
1,
2)
+ 1));
DiscardRec.source_object_owner :=
SUBSTR (PartLine, 1, INSTR (PartLine, '.') - 1);
DiscardRec.source_object_name :=
SUBSTR (PartLine, INSTR (PartLine, '.') + 1);
PartLine := SUBSTR (FileBuffer, INSTR (FileBuffer, ' to ') + 4);
DiscardRec.target_object_owner :=
SUBSTR (PartLine, 1, INSTR (PartLine, '.') - 1);
DiscardRec.target_object_name :=
SUBSTR (PartLine, INSTR (PartLine, '.') + 1);
OutputRow := FALSE;
WHEN 'Mapping problem'
THEN
DiscardRec.error_operation :=
UPPER (SUBSTR (FileBuffer,
INSTR (FileBuffer,
' ',
-1,
4)
+ 1,
INSTR (FileBuffer,
' ',
-1,
3)
- ( INSTR (FileBuffer,
' ',
-1,
4)
+ 1)));
OutputRow := FALSE;
WHEN 'Record not'
THEN
DiscardRec.MESSAGE := FileBuffer;
OutputRow := FALSE;
WHEN '*'
THEN
IF FindingColumn
THEN
FindingColumn := FALSE;
OutputRow := TRUE;
ELSE
/* Gather some information for specific messages */
CASE DiscardRec.error_number
WHEN 1 /* Unique constraint violation */
THEN
/* Find the first column for the key and then be able to find it's value in the text */
OPEN get_cons_cols_curs (DiscardRec.error_object_owner,
DiscardRec.error_object_name);
FETCH get_cons_cols_curs INTO DiscardRec.error_column;
CLOSE get_cons_cols_curs;
WHEN 1403 /* Row not found */
THEN
/* Find the PK of the table and then find it's value - if possible */
DiscardRec.error_object_owner :=
DiscardRec.source_object_owner;
DiscardRec.error_object_name :=
DiscardRec.source_object_name;
OPEN get_pk_col_curs (DiscardRec.error_object_owner,
DiscardRec.error_object_name);
FETCH get_pk_col_curs INTO DiscardRec.error_column;
CLOSE get_pk_col_curs;
WHEN 2291 /* Integrity constraint voilation */
THEN
/* Find the fk table name , column name and then be able to find the value of the offending row */
/* Get the column name */
OPEN get_cons_cols_curs (DiscardRec.error_object_owner,
DiscardRec.error_object_name);
FETCH get_cons_cols_curs INTO DiscardRec.error_column;
CLOSE get_cons_cols_curs;
/* Get primary key table name */
OPEN get_pk_table_curs (DiscardRec.error_object_owner,
DiscardRec.error_object_name);
FETCH get_pk_table_curs INTO DiscardRec.pk_table_name;
CLOSE get_pk_table_curs;
ELSE
NULL;
END CASE;
FindingColumn := TRUE;
END IF;
ELSE
IF FindingColumn
THEN
IF DiscardRec.error_column IS NOT NULL
THEN
IF DiscardRec.error_column =
SUBSTR (FileBuffer, 1, INSTR (FileBuffer, ' = ') - 1)
THEN
DiscardRec.error_value :=
SUBSTR (FileBuffer, INSTR (FileBuffer, ' = ') + 3);
END IF;
END IF;
ELSE
/* Found something unusual so let's output an extra record */
InitialiseRecord (DiscardExtra);
DiscardExtra.MESSAGE_TYPE := 'UNKNOWN';
DiscardExtra.MESSAGE := SUBSTR (FileBuffer, 1, 120);
DiscardExtra.description := SUBSTR (FileBuffer, 120);
DiscardExtra.line_number := LineNumber;
PIPE ROW (DiscardExtra);
END IF;
END CASE;
IF OutputRow
THEN
PIPE ROW (DiscardRec);
InitialiseRecord (DiscardRec);
ErrorRow := FALSE;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
/* Output last row if not output */
IF DiscardRec.MESSAGE_TYPE IS NOT NULL
THEN
PIPE ROW (DiscardRec);
END IF;
CloseDiscard;
END read_discard;
/
--SELECT * FROM user_errors
注意在建procedure时注意授权对依赖对象的查询权限如dba_constraints,注意pl/sql中需要显示授权
4, 查询 OGG discard file
select LINE_NUMBER
, ORACLE_ERROR
, SOURCE_OBJECT_NAME
, ERROR_OPERATION
, ERROR_COLUMN
, ERROR_VALUE
, PK_TABLE_NAME
from table(read_discard('ricme')) order by 1;
LINE_NUMBER ORACLE_ERR SOURCE_OBJECT_NAME ERROR_OPERATION ERROR_COLUMN ERROR_VALUE PK_TABLE_NAME
----------- ---------- -------------------- -------------------- -------------------- -------------------- ---------------
1 ORA-01403 IC_SIGNUP UPDATE SIGNUP_ID
33 ORA-01403 IC_SIGNUP UPDATE SIGNUP_ID
68 ORA-01403 IC_SIGNUP UPDATE SIGNUP_ID
99 ORA-01403 IC_SIGNUP UPDATE SIGNUP_ID
134 ORA-24344
thanks for Luke Davies show your knowledge
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。