ADD SCHEMATRANDATA failing with OGG-06522 or OGG-01783

This issue can happen when you are trying to run "ADD SCHEMATRANDATA" data from the golden gate command line interface.Most probably this is the new setup and you are trying to configure goldengate  for the first time on this server.

Following is the example of the error message.

OGG-06522  Cannot verify existence of table function that is required to ADD schema level supplemental logging, failed.

1. In this case the first thing that you want to check is if you have all the required patches to support the SCHEMATRANDATA in oracle golden gate.If you have the MOS access following note will help you to identify all the required patches

DB Patches Needed to Support SCHEMATRANDATA in OGG (Oracle GoldenGate) (Doc ID 1426440.1)

2. If you have all the required patches and still you are getting the error while running the "ADD SCHEMATRANDATE" command then you need to check if there are any invalid objects inside the database.

You can run the $ORACLE_HOME/rdbms/admin/utlrp.sql to compile any invalid objects in the database.

3. If the issue occurs again then you may want to run sql trace on the goldengate session .

If the problem reproduces then enable the sql trace on the GG session using the following commands from sqlplus (Run ADD SCHEMATRANDATA in the gg sesssion) and analyze the trace files.

Specify OS Process ID.
SQL> oradebug setospid
SQL> oradebug unlimit
Enable SQL trace on that OS Process ID.
SQL> oradebug event 10046 trace name context forever, level 12

In the trace file you will be able to find the offending sql and you can take action based on that .You can also contact oracle support with the trace files.

If this solution fixed your issue or you need any help please leave a comment below.

Thanks !!