Golden Gate: Cleanup and restart for the failed Initial load direct load

When performing an initial load using Direct Load, you may need to restart the process due to errors or a need to reset the target tables. Below are the steps to restart the initial load, including handling foreign key constraints, reference-partitioned tables, and recreating the extract/replicat processes.

Steps to Restart Initial Load

1. Remove the Extract and Replicat Processes

Execute the following commands in GGSCI to delete the existing extract and replicat processes:

GGSCI> DELETE EXTRACT EXTINIT;
GGSCI> DELETE REPLICAT REPINIT;

2. Truncate All Target Tables

Before reloading data, all target tables must be truncated. However, if foreign key constraints exist, truncating the tables may result in ORA-02266 (unique/primary keys referenced by enabled foreign keys).

To handle foreign key constraints, execute the following PL/SQL block:

DECLARE
   v_schema_name VARCHAR2(30) := 'APP_SCHEMA'; -- Change this to your schema
BEGIN
   -- Disable all foreign key constraints
   FOR rec IN (SELECT constraint_name, table_name 
               FROM all_constraints 
               WHERE constraint_type = 'R' 
                 AND owner = v_schema_name) LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE ' || v_schema_name || '.' || rec.table_name || ' DISABLE CONSTRAINT ' || rec.constraint_name;
   END LOOP;

   -- Truncate all tables
   FOR rec IN (SELECT table_name 
               FROM all_tables 
               WHERE owner = v_schema_name) LOOP
      EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || v_schema_name || '.' || rec.table_name || ' CASCADE CONSTRAINTS';
   END LOOP;

   -- Enable all foreign key constraints
   FOR rec IN (SELECT constraint_name, table_name 
               FROM all_constraints 
               WHERE constraint_type = 'R' 
                 AND owner = v_schema_name) LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE ' || v_schema_name || '.' || rec.table_name || ' ENABLE CONSTRAINT ' || rec.constraint_name;
   END LOOP;
END;
/

If you encounter ORA-14650 (operation not supported for reference-partitioned tables), you need to drop and recreate the affected tables.

3. Identify Reference-Partitioned Tables

Reference-partitioned tables cannot be truncated directly. Identify them using:

SELECT table_name, reference_partitioned, parent_table 
FROM all_part_tables 
WHERE owner = 'APP_SCHEMA' AND reference_partitioned = 'YES';

To handle these tables, create temporary copies, drop the originals, and then recreate them:

CREATE TABLE temp_TRANS_TBL AS SELECT * FROM ecmstg.USR_TBL;

Then drop the problematic tables:

DROP TABLE APP_SCHEMA.TRANS_PAYMENTS_TBL;
DROP TABLE APP_SCHEMA.SKU_ITEMS_TBL;
DROP TABLE APP_SCHEMA.CARTS_TBL;
DROP TABLE APP_SCHEMA.GIFTCARDS_TBL;
DROP TABLE APP_SCHEMA.TRANS_ORDERS_TBL;
DROP TABLE APP_SCHEMA.ADDRESSES_TBL;

4. Recreate Tables Using DBMS_METADATA

After dropping tables, generate DDL scripts to recreate them:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'ADDRESSES_TBL', 'APP_SCHEMA') FROM dual;

Run the output to recreate the tables.

5. Re-add the Extract Process

Recreate the extract process in GGSCI:

GGSCI> ADD EXTRACT EXTINIT, SOURCEISTABLE;
GGSCI> INFO EXTRACT EXTINIT;

Expected output:

EXTRACT    EXTINIT   Initialized   2024-06-07 20:56   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

6. Re-add the Replicat Process

Recreate the replicat process in GGSCI:

GGSCI> ADD REPLICAT REPINIT, SPECIALRUN;
GGSCI> INFO REPLICAT REPINIT;
REPLICAT   REPINIT   Initialized   2024-06-07 20:56   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

Final Verification

• Ensure extract and replicat processes are correctly configured.
• Run INFO EXTRACT EXTINIT and INFO REPLICAT REPINIT to confirm their status.
• Start the processes as needed.

By following these steps, you can safely restart an initial load using Direct Load in Oracle GoldenGate, ensuring a clean data migration