Oracle客户端数据库向中央库迁移咨询：重设PK/FK并保障数据一致性

Great question! Handling distributed data migration to a central Oracle database while reworking primary and foreign keys with sequence-driven consistency is a common challenge, and Oracle offers both built-in tools and flexible custom approaches to solve it. Let’s break down the most practical options:

Oracle Data Pump (Best for Batch Migrations) #

Data Pump is Oracle’s go-to tool for bulk data movement, perfect for one-time migrations from your client databases to the central repository. Here’s how to adapt it for your PK/FK needs:

• Export Data Only: When exporting from each client, use CONTENT=DATA_ONLY to avoid bringing over the client’s existing constraints—we’ll rebuild those in the central database with our new sequence-based PKs.
• Prepare the Central Schema: First, create your target tables in the central database, plus a global sequence for generating unique PKs (e.g., CREATE SEQUENCE central_global_seq START WITH 1 INCREMENT BY 1;). You can set the PK column’s default value to central_global_seq.NEXTVAL or handle it during import.
• Remap PK Values During Import: Use Data Pump’s REMAP_DATA parameter to replace the client’s old PK values with new ones from the central sequence. You’ll need a simple PL/SQL function to fetch the next sequence value, then reference it in your import command:

  impdp system/password@centraldb schemas=CLIENT_DATA REMAP_SCHEMA=CLIENT_DATA:CENTRAL_DATA CONTENT=DATA_ONLY REMAP_DATA=CENTRAL_DATA.EMP.EMP_ID:CENTRAL_UTILS.SEQ_GENERATOR.GET_NEW_PK
  

• Handle Foreign Keys: After importing all parent tables and generating their new PKs, update the child tables’ FK columns to reference the new PK values (you might need a mapping table to track old vs. new PKs), then create the FK constraints in the central database.

Oracle GoldenGate (Best for Real-Time Sync) #

If you need continuous, near-real-time synchronization (instead of a one-time migration) to keep the central database updated as clients add data, GoldenGate is Oracle’s built-in real-time data integration tool:

• Capture and Replicate Changes: Configure an Extract process on each client database to capture data changes, then a Replicat process on the central database to apply those changes.
• Transform PKs During Replication: In the Replicat configuration, define mapping logic to replace the client’s PK with the next value from the central sequence. GoldenGate supports custom PL/SQL transformations to handle this seamlessly.
• Sync FKs Automatically: Since GoldenGate replicates changes in order, you can ensure child table changes use the newly generated PK values from the parent tables, then enable FK constraints in the central database once initial sync is complete.

If you need more flexibility (e.g., complex business logic during migration), a custom PL/SQL solution gives you complete control over PK/FK handling:

1. Set Up Database Links: Create database links from the central database to each client, so you can query client data directly:

   CREATE DATABASE LINK client1_db CONNECT TO client_user IDENTIFIED BY secure_password USING 'CLIENT1_TNS';
   

2. Create Mapping Tables: Add a helper table to track old PK values (from clients) to new PK values (from the central sequence)—this is critical for updating FKs in child tables:

   CREATE TABLE pk_mapping (
     table_name VARCHAR2(30),
     old_pk NUMBER,
     new_pk NUMBER,
     PRIMARY KEY (table_name, old_pk)
   );
   

3. Migrate Parent Tables First: Write a PL/SQL block to pull data from the client, generate new PKs via the central sequence, insert into the central table, and log the mapping:

   DECLARE
     v_new_pk NUMBER;
   BEGIN
     FOR emp_rec IN (SELECT * FROM emp@client1_db) LOOP
       SELECT central_global_seq.NEXTVAL INTO v_new_pk FROM DUAL;
       INSERT INTO central_emp (emp_id, name, hire_date, ...)
       VALUES (v_new_pk, emp_rec.name, emp_rec.hire_date, ...);
       INSERT INTO pk_mapping (table_name, old_pk, new_pk)
       VALUES ('EMP', emp_rec.emp_id, v_new_pk);
     END LOOP;
     COMMIT;
   END;
   /
   

4. Migrate Child Tables: Use the mapping table to update FK values in child tables before inserting them into the central database:

   DECLARE
     v_new_emp_id NUMBER;
   BEGIN
     FOR sal_rec IN (SELECT * FROM emp_salary@client1_db) LOOP
       SELECT new_pk INTO v_new_emp_id FROM pk_mapping WHERE table_name='EMP' AND old_pk=sal_rec.emp_id;
       INSERT INTO central_emp_salary (sal_id, emp_id, salary, ...)
       VALUES (central_sal_seq.NEXTVAL, v_new_emp_id, sal_rec.salary, ...);
     END LOOP;
     COMMIT;
   END;
   /
   

5. Finalize Constraints: Once all data is migrated, create FK constraints in the central database to enforce referential integrity.

• Sequence Uniqueness: Stick to a single global sequence in the central database to guarantee no duplicate PKs across all client datasets. Avoid splitting sequences into client-specific ranges unless you’re absolutely confident in managing overlaps.
• Dependency Order: Always migrate parent tables first, then child tables—this ensures you have the PK mapping in place to update FKs correctly.
• Validation: After migration, run checks to verify data integrity: compare row counts between client and central tables, validate that all FKs reference existing PKs, and spot-check random records for accuracy.
• Performance: For large datasets, use Data Pump or batch operations (like FORALL in PL/SQL) to avoid slow row-by-row processing.

内容的提问来源于stack exchange，提问作者Basel