Tuesday, June 12, 2018

GoldenGate : ADD SCHEMATRANDATA failing with OGG-01790 + ORA-06550

Tried to enable supplemental logging at schema level on 11.2.0.4 database triggered with following error  


GGSCI (sev1) 1> dblogin USERID g_user, PASSWORD oracle
Successfully logged into database.

GGSCI (sev1) 2> add schematrandata SCOTT

2018-06-11 09:54:07  ERROR   OGG-01790  Failed to ADD SCHEMATRANDATA on schema SCOTT because of the following SQL error: ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_CAPTURE_ADM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('SCOTT','ALLKEYS_ON'); END;.



The grants are missing for the GoldenGate user. Please run below to open up grants 


SQL> exec dbms_streams_auth.grant_admin_privilege('G_USER');

PL/SQL procedure successfully completed.


It failed again because of missing parameter enable_goldengate_replication, we need this set to TRUE to support GoldenGate Replication


GGSCI (sev1) 3> add schematrandata SCOTT

2018-06-11 10:51:21  ERROR   OGG-01790  Failed to ADD SCHEMATRANDATA on schema SCOTT because of the following SQL error: ORA-26947: Oracle GoldenGate replication is not enabled.
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 1577
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 1086
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 722
ORA-06512: at line 1 SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('SCOTT','ALLKEYS_ON'); END;.

Change the parameter on DB level


SQL> alter system set enable_goldengate_replication=true scope=both;

System altered.

Supplemental Logging got enabled now. 


GGSCI (sev1) 4> add schematrandata SCOTT

2018-06-11 10:52:35  INFO    OGG-01788  SCHEMATRANDATA has been added on schema SCOTT.

To check if its enabled, please run below


GGSCI (sev1) 5> info schematrandata SCOTT

2018-06-11 10:57:23  INFO    OGG-01785  Schema level supplemental logging is enabled on schema SCOTT.


Hope this resolves your issue. 

No comments:

Post a Comment