首页 » GoldenGate » External Table On GoldenGate Discard Files

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

打赏

对不起,这篇文章暂时关闭评论。