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.
