Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

GoldenGate INSERTALLRECORDS and OGG-01154 SQL error 1400

  • Posted by Gavin Soorma
  • On June 26, 2016
  • 1 Comments
  • awr history, cdc, GoldenGate, insertallrecords, supplemental logging, transaction history

The Goldengate INSERTALLRECORDS commands can be used in cases where the requirement is to have on the target database a transaction history or change data capture (CDC) tables which will keep a track of changes a table undergoes at the row level.

So every INSERT, UPDATE or DELETE statement on the source tables is captured as INSERT statements on the target database

But in certain cases update statements issued on the source database can cause the replicat process to abend with an error:

“ORA-01400: cannot insert NULL”.

This can happen when the table has not null columns that have not been updated and when the update is converted to an insert, the trail file will not have values for those columns so the insert will use nulls and consequently fail with the ORA-1400 error.


Test Case

We create two tables – SYSTEM.MYTABLES in the source database and SYSTEM.MYTABLES_CDC in the target database.

The SYSTEM.MYTABLES_CDC table in the target will have two additional columns for maintaining the CDC or Transaction history – OPER_TYPE capture the type of DML operation on the table and CHANGE_DATE which will capture the timestamp information of when the change took place.

We create a Primary Key constraint on the source table – note, the target table will have no similar constraints as rows will be inserted all the time into the CDC table regardless of whether the DML statement on the source was an INSERT, UPDATE or DELETE.

SQL> create table system.mytables
  2  (owner VARCHAR2(30) NOT NULL,
  3   table_name VARCHAR2(30) NOT NULL,
  4  tablespace_name VARCHAR2(30) NOT NULL,
  5  logging VARCHAR2(3) NOT NULL);

Table created.

SQL> alter table system.mytables add constraint pk_mytables primary key (owner,table_name);

Table altered.


SQL SYS@euro> create table system.mytables_cdc
  2  (owner VARCHAR2(30) NOT NULL,
  3    table_name VARCHAR2(30) NOT NULL,
  4  tablespace_name VARCHAR2(30) NOT NULL,
  5  logging VARCHAR2(3) NOT NULL,
  6  oper_type VARCHAR2(20),
  7  change_date TIMESTAMP);

Table created.

We now issue the ADD TRANDATA GGSCI command.

Note issuing the ADD TRANDATA command will enable supplemental logging at the table level for PK columns, UK columns and FK columns – not ALL columns.



GGSCI (ogg2.localdomain as oggsuser@sourcedb) 64> dblogin useridalias oggsuser_sourcedb
Successfully logged into database.

GGSCI (ogg2.localdomain as oggsuser@sourcedb) 65> add trandata system.mytables

Logging of supplemental redo data enabled for table SYSTEM.MYTABLES.
TRANDATA for scheduling columns has been added on table 'SYSTEM.MYTABLES'.
GGSCI (ogg2.localdomain as oggsuser@sourcedb) 66> info trandata system.mytables

Logging of supplemental redo log data is enabled for table SYSTEM.MYTABLES.

Columns supplementally logged for table SYSTEM.MYTABLES: OWNER, TABLE_NAME.


We can query the DBA_LOG_GROUPS view to get information about the supplemental logging added for the the table MYTABLES.

The ADD TRANDATA command has created a supplmental log group called GGS_729809 and we can see that supplemental logging is enabled for all columns part of a primary key, unique key or foreign key constraint.


SQL> SELECT
  2  LOG_GROUP_NAME,
  3   TABLE_NAME,
  4  DECODE(ALWAYS, 'ALWAYS', 'Unconditional','CONDITIONAL', 'Conditional') ALWAYS,
  5  LOG_GROUP_TYPE
  6  FROM DBA_LOG_GROUPS
  7   WHERE TABLE_NAME='MYTABLES' AND OWNER='SYSTEM';

no rows selected

SQL> /

                                     Conditional or
Log Group            Table           Unconditional  Type of Log Group
-------------------- --------------- -------------- --------------------
GGS_72909            MYTABLES        Unconditional  USER LOG GROUP
SYS_C009814          MYTABLES        Unconditional  PRIMARY KEY LOGGING
SYS_C009815          MYTABLES        Conditional    UNIQUE KEY LOGGING
SYS_C009816          MYTABLES        Conditional    FOREIGN KEY LOGGING



SQL> select LOG_GROUP_NAME,COLUMN_NAME from DBA_LOG_GROUP_COLUMNS
  2  where OWNER='SYSTEM' and TABLE_NAME='MYTABLES'
  3  order by 1,2;


Log Group            COLUMN_NAME
-------------------- ------------------------------
GGS_72909            OWNER
GGS_72909            TABLE_NAME


Let us now test the case.

We insert some rows into the source table MYTABLES – these rows are replicated fine to the target table MYTABLES_CDC.


SQL> insert into system.mytables
  2  select OWNER,TABLE_NAME,TABLESPACE_NAME,LOGGING
  3   from DBA_TABLES
  4   where OWNER='SYSTEM' and TABLESPACE_NAME is NOT NULL;

110 rows created.

SQL> commit;

Commit complete.



SQL SYS@euro> select count(*) from system.mytables_cdc;

  COUNT(*)
----------
       110


Let us now see what happens when we run an UPDATE statement on the source database. Note the columns involved in the UPDATE are not PK or UK columns.


SQL> update system.mytables set tablespace_name='USERS' where tablespace_name='SYSTEM';

89 rows updated.

SQL> commit;

Commit complete.


Immediately we will see that the Replicat process on the target has ABENDED an d if we examine the Replicat report log we can see the error message as shown below.

2016-06-25 14:40:26  INFO    OGG-06505  MAP resolved (entry SYSTEM.MYTABLES): MAP "SYSTEM"."MYTABLES", TARGET SYSTEM.MYTABLES_CDC, COLMAP (USEDEFAULTS, CHANGE_DATE=@GETENV ('GGHEADER', 'COM
MITTIMESTAMP'), OPER_TYPE=@GETENV ('GGHEADER', 'OPTYPE')).

2016-06-25 14:40:46  WARNING OGG-06439  No unique key is defined for table MYTABLES_CDC. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may
be used to define the key.
Using the following default columns with matching names:
  OWNER=OWNER, TABLE_NAME=TABLE_NAME, TABLESPACE_NAME=TABLESPACE_NAME, LOGGING=LOGGING

2016-06-25 14:40:46  INFO    OGG-06510  Using the following key columns for target table SYSTEM.MYTABLES_CDC: OWNER, TABLE_NAME, TABLESPACE_NAME, LOGGING, OPER_TYPE, CHANGE_DATE.


2016-06-25 14:45:18  WARNING OGG-02544  Unhandled error (ORA-26688: missing key in LCR) while processing the record at SEQNO 7, RBA 19037 in Integrated mode. REPLICAT will retry in Direct m
ode.

2016-06-25 14:45:18  WARNING OGG-01154  SQL error 1400 mapping SYSTEM.MYTABLES to SYSTEM.MYTABLES_CDC OCI Error ORA-01400: cannot insert NULL into ("SYSTEM"."MYTABLES_CDC"."LOGGING") (statu
s = 1400), SQL .


There is a column called LOGGING which is a NOT NULL column – the GoldenGate trail file has information about the other columns – OWNER, TABLE_NAME and TABLESPACE_NAME.

But there is no data captured in the trail file for the LOGGING column.

Using the LOGDUMP utility we can see this.

Logdump 103 >open ./dirdat/rt000007
Current LogTrail is /ogg/euro/dirdat/rt000007
Logdump 104 >ghdr on
Logdump 105 >detail on
Logdump 106 >detail data
Logdump 107 >pos 32008
Reading forward from RBA 32008
Logdump 108 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    52  (x0034)   IO Time    : 2016/06/25 14:45:02.999.764
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x02)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         67       AuditPos   : 8056764
Continued  :     N  (x00)     RecCount   :     1  (x01)

2016/06/25 14:45:02.999.764 FieldComp            Len    52 RBA 32008
Name: SYSTEM.MYTABLES
After  Image:                                             Partition 4   G  e
 0000 000a 0000 0006 5359 5354 454d 0001 0015 0000 | ........SYSTEM......
 0011 4c4f 474d 4e52 5f50 4152 414d 4554 4552 2400 | ..LOGMNR_PARAMETER$.
 0200 0900 0000 0555 5345 5253                     | .......USERS
Column     0 (x0000), Len    10 (x000a)
 0000 0006 5359 5354 454d                          | ....SYSTEM
Column     1 (x0001), Len    21 (x0015)
 0000 0011 4c4f 474d 4e52 5f50 4152 414d 4554 4552 | ....LOGMNR_PARAMETER
 24                                                | $
Column     2 (x0002), Len     9 (x0009)
 0000 0005 5553 4552 53                            | ....USERS


The table has not null columns that have not been updated (column LOGGING was not part of the update statement).

If the column was not updated on the update statement, when the update is converted to an insert, the trail file will not have values for that column and so the insert will use nulls and consequently fail with the ORA-1400, so this is an expected behavior.

We can see that the update on source database is converted into an insert statement on the target – this is because of the INSERTALLRECORDS parameter we are using in the Replicat parameter file.

.

So the solution is that we need to enable supplemental logging for ALL columns at the source database table.

We will now add supplemental log data to all columns

SQL> alter table system.mytables add supplemental log data (ALL) columns;

Table altered.

Note the DBA_LOG_GROUPS view as well as the ADD TRANDATA command now shows all the columns have supplemental logging enabled.


SELECT
 LOG_GROUP_NAME,
  TABLE_NAME,
 DECODE(ALWAYS, 'ALWAYS', 'Unconditional','CONDITIONAL', 'Conditional') ALWAYS,
 LOG_GROUP_TYPE
  FROM DBA_LOG_GROUPS
  WHERE TABLE_NAME='MYTABLES' AND OWNER='SYSTEM';
SQL>   2    3    4    5    6    7
                                     Conditional or
Log Group            Table           Unconditional  Type of Log Group
-------------------- --------------- -------------- --------------------
GGS_72909            MYTABLES        Unconditional  USER LOG GROUP
SYS_C009814          MYTABLES        Unconditional  PRIMARY KEY LOGGING
SYS_C009815          MYTABLES        Conditional    UNIQUE KEY LOGGING
SYS_C009816          MYTABLES        Conditional    FOREIGN KEY LOGGING
SYS_C009817          MYTABLES        Unconditional  ALL COLUMN LOGGING


GGSCI (ogg2.localdomain as oggsuser@sourcedb) 12> info trandata system.mytables

Logging of supplemental redo log data is enabled for table SYSTEM.MYTABLES.

Columns supplementally logged for table SYSTEM.MYTABLES: ALL.


SQL> alter system switch logfile;

System altered.

Note: STOP and RESTART the Extract and Pump

Note the position where the Extract pump was writing to.

GGSCI (ogg2.localdomain as oggsuser@sourcedb) 28> info pext1 detail

EXTRACT    PEXT1     Last Started 2016-06-25 15:04   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
Process ID           31081
Log Read Checkpoint  File ./dirdat/lt000012
                     2016-06-25 15:05:16.927851  RBA 1476

  Target Extract Trails:

  Trail Name                                       Seqno        RBA     Max MB Trail Type

  ./dirdat/rt                                          9       1522        100 RMTTRAIL


Delete and recreate the Integrated Replicat

GGSCI (ogg1.localdomain as oggsuser@euro) 2> delete replicat rep2

2016-06-25 15:07:11  WARNING OGG-02541  Replicat could not process some SQL errors before being dropped or unregistered. This may cause the data to be out of sync.

2016-06-25 15:07:14  INFO    OGG-02529  Successfully unregistered REPLICAT REP2 inbound server OGG$REP2 from database.
Deleted REPLICAT REP2.


GGSCI (ogg1.localdomain as oggsuser@euro) 3> add replicat rep2 integrated exttrail ./dirdat/rt
REPLICAT (Integrated) added.

Restart the replicat from the point where it had abended

GGSCI (ogg1.localdomain as oggsuser@euro) 4> alter rep2 extseqno 9 extrba 1522

2016-06-25 15:07:55  INFO    OGG-06594  Replicat REP2 has been altered through GGSCI. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REP2 with NOFILTERDUPTRANSACTION option.

REPLICAT (Integrated) altered.

Now run a similar update statement which earlier had caused the replicat to abend


SQL> update system.mytables set tablespace_name='SYSTEM'  where tablespace_name='USERS';

89 rows updated.

SQL> commit;

Commit complete.

We can see that this time the replicat has successfully applied the changes on the target table – 89 rows which were updated on the source table have now been transformed into 89 INSERT statements in the CDC table on the target database.

GGSCI (ogg1.localdomain as oggsuser@euro) 14> stats replicat rep2 table SYSTEM.MYTABLES_CDC latest

Sending STATS request to REPLICAT REP2 ...

Start of Statistics at 2016-06-25 15:11:59.

.....
......


Replicating from SYSTEM.MYTABLES to SYSTEM.MYTABLES_CDC:

*** Latest statistics since 2016-06-25 15:11:09 ***
        Total inserts                                     89.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                  89.00

End of Statistics.

If we now examine the trail file on the target, we can see that this time all the table columns including the LOGGING column (which was missing earlier) has been captured in the trail file

Logdump 109 >open ./dirdat/rt000009
Current LogTrail is /ogg/euro/dirdat/rt000009
Logdump 110 >ghdr on
Logdump 111 >detail on
Logdump 112 >detail data
Logdump 113 >pos 1522
Reading forward from RBA 1522
Logdump 114 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    56  (x0038)   IO Time    : 2016/06/25 15:10:52.999.941
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x00)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         68       AuditPos   : 186384
Continued  :     N  (x00)     RecCount   :     1  (x01)

2016/06/25 15:10:52.999.941 FieldComp            Len    56 RBA 1522
Name: SYSTEM.MYTABLES
After  Image:                                             Partition 4   G  b
 0000 000a 0000 0006 5359 5354 454d 0001 000d 0000 | ........SYSTEM......
 0009 4d59 4f42 4a45 4354 5300 0200 0a00 0000 0653 | ..MYOBJECTS........S
 5953 5445 4d00 0300 0700 0000 0359 4553           | YSTEM........YES
Column     0 (x0000), Len    10 (x000a)
 0000 0006 5359 5354 454d                          | ....SYSTEM
Column     1 (x0001), Len    13 (x000d)
 0000 0009 4d59 4f42 4a45 4354 53                  | ....MYOBJECTS
Column     2 (x0002), Len    10 (x000a)
 0000 0006 5359 5354 454d                          | ....SYSTEM
Column     3 (x0003), Len     7 (x0007)
 0000 0003 5945 53                                 | ....YES

Note the data in the CDC table on the target

SQL SYS@euro>  select tablespace_name,oper_type from system.mytables_cdc
  2   where TABLE_NAME ='MYTABLES';

TABLESPACE_NAME                OPER_TYPE
------------------------------ --------------------
SYSTEM                         INSERT
USERS                          SQL COMPUPDATE
SYSTEM                         SQL COMPUPDATE

 2

1 Comments

C R RAVI
  • Jan 9 2017
Hi, Thanks for the Info , we regularly facing these 1400 issue. Please post what other relative errors commonly we will face in OGG 11g/12c

Leave Reply

Your email address will not be published. Required fields are marked *