GoldenGate 21 for MySQL and Views

75 Views Asked by At

I am running GG Microservices v21 in a hub configuration. I want to migrate from MySQL 8 to Oracle 19. I am testing with the old employee table data in MySQL and everything works fine if I exclude either on the extract or the replicat the views. I have to make sure all the views are created manually before I do this. If i don't create the views I get this error.

Oracle GoldenGate Delivery for Oracle, RIEMP.prm.backup: Failed to lookup object ID for table 
MYPDB.MYSCHEMA.CURRENT_DEPT_EMP. No data found when executing SQL statement <SELECT o.obj# FROM sys.obj$ o, 
sys."_BASE_USER" u, sys.tab$ t WHERE o.name = :1 AND o.owner# = u.user# AND u.name = :2 AND o.obj# = t.obj#>.

If I create the views beforehand and include them in the extractr or replicat I get this error.

Oracle GoldenGate Delivery for Oracle, RIEMP.prm.backup: SQL error 1779 mapping source table myschema.current_dept_emp to target table MYPDB.MYSCHEMA.CURRENT_DEPT_EMP. 
Database error: OCI Error ORA-01779: cannot modify a column which maps to a non 
key-preserved table (status = 1779), SQL <INSERT INTO "MYSCHEMA"."CURRENT_DEPT_EMP" 
("EMP_NO","DEPT_NO","FROM_DATE","TO_DATE") VALUES (:a0,:a1,:a2,:a3)>.

The documentation states that views are able to be migrated but not sure what I am doing wrong here.

Here is my parameter file for the initial load extract.

EXTRACT ELEMP1
USERIDALIAS  mysqldev, DOMAIN OracleGoldenGate
setenv (TZ="GMT-6")
EXTFILE /u01/app/oracle/product/ggms/18_ma_deployments/mysqldev/migrate/ep ,  PURGE
TRANLOGOPTIONS ALTLOGDEST REMOTE
TABLE myschema.*;
TABLEEXCLUDE myschema.current_dept_emp
TABLEEXCLUDE myschema.dept_emp_latest_date

My replicat parameter file

REPLICAT RIEM1
USERIDALIAS ggadmin_dbainvd19 DOMAIN ogg
MAPEXCLUDE myschema.current_dept_emp
MAPEXCLUDE myschema.dept_emp_latest_date
MAP myschema.*, TARGET mypdb.myschema.*;

Thanks in advance.

0

There are 0 best solutions below