Inaccurate GoldenGate Error Message ERROR: OCI Error ORA (status = 1031-ORA-01031: insufficient privileges

I recently ran into an issue a GoldenGate error message in an Oracle 12.2 PDB environment that was complicated by the fact that the GoldenGate error message did not accurately reflect the actual error that was happening.  In a PDB environment the integrated extract runs against the Container Database (CDB$ROOT).  This is necessary because there is only one set of redo log files for all the PDBs.  This has been the case since Pluggable Databases were introduced in Oracle 12.  The extract must also run as a common user in a PDB environment.

It is a best practice of mine to always use the least amount of privileges that are necessary to avoid granting DBA to the GoldenGate user.  So, following these rules I created my GoldenGate common user c##ggadmin and granted the necessary privileges using the container=all qualifier.  This included running the following:

exec dbms_goldengate_auth.grant_admin_privilege(‘C##GGADMIN’,container=>’ALL’);

In keeping with my own best practice, I setup the credentialstore and created an alias.  The problem arose when I went to add trandata.  I used the catalog option by using the syntax ..table.  When I ran it I got the following error message:

GGSCI (ora1-ed.perftuning.com) 4> dblogin useridalias ggadmc01

Successfully logged into database CDB$ROOT.

GGSCI (ora1-ed.perftuning.com as c##ggadmin@gg01c01/CDB$ROOT) 5> add trandata gg01p01.soe.*

ERROR: OCI Error ORA (status = 1031-ORA-01031: insufficient privileges

).

I subsequently tried adding permissions with no success.  I finally granted DBA and it worked, however this violated my best practices.  After trying various options, I went back to my original configuration and remembered that add trandata is supposed to be run at the PDB level.  The results were successful:

GGSCI (ora1-ed.perftuning.com) 20> dblogin useridalias ggadmp01

Successfully logged into database GG01P01.

GGSCI (ora1-ed.perftuning.com as c##ggadmin@gg01c01/GG01P01) 21> add trandata soe.*

2018-02-12 09:27:02  WARNING OGG-06439  No unique key is defined for table ADDRESSES. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

…..

So, even though this was my own mistake, it cost me valuable time by following an inaccurate GoldenGate error message error message.  Whether or not this actually was a permissions problem, the actual underlying problem was trying to run trandata at the CDB level rather than at the PDB level.

If the GoldenGate error message was more accurate it would have saved me a lot of time.

By | 2018-07-05T14:28:55+00:00 July 5th, 2018|Uncategorized|Comments Off on Inaccurate GoldenGate Error Message ERROR: OCI Error ORA (status = 1031-ORA-01031: insufficient privileges