OGG-00516 - ORA-01031: insufficient privileges SQL alter synonym

OK, this one took me a while to understand what's going on with one of my Golden Gate Replicat process.

Its abending with the above error. It seems to be pretty simple statement and not sure how this can happen.

Oracle says (check metalink id:1435898.1) alter synonym compile is "un-documented" command and therefore they wont recommend running that sql.
Actually, you can run alter synonym compile with in your schema synonyms with no issue.
If you want to compile other synonyms which dont belong in your schema then you will get that error. In this case you need to log in as SYSDBA. Makes perfect sense.

But wait, why is Golden Gate fails though?

Check below:
19:39:45 SQL> connect UserDBA@SOURCE
Enter password:
Connected.
19:39:59 SQL> alter session set current_schema=User2;

Session altered.

19:40:14 SQL> alter synonym User2.pkg_gg_test compile;
alter synonym User2.pkg_gg_test compile
*
ERROR at line 1:
ORA-01031: insufficient privileges

If you notice above, even though you set your current session to the actual user where the synonym exists it wont work. This is exactly what is happening with in Golden Gate.

Golden Gate starts with GGADMIN user and whenever there is a DDL operation, it will set the current session to that particular user to execute the DDL and this alter synonym fails due to that.

Only way that I can see to by pass this error is to include exception "ddlerror 1031 ignore" at replicat param and continue with these errors (will have to restart the replicat without this exception once DDL passes).

Here is the complete Test Case to reproduce that error in Golden Gate:

C:\Users\eqt6tiw>sqlplus User1@SOURCE

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Nov 27 18:53:01 2013

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> grant connect,resource,dba to UserDBA identified by UserDBAPW;

Grant succeeded.

SQL> grant connect,resource to User2 identified by User2PW;

Grant succeeded.

Creating UserDBA and User2 at TARGET (User1 already exists and setup in GG for REPLICAT)

SQL> connect User1@TARGET

Enter password:

Connected.

SQL> grant connect,resource,dba to UserDBA identified by UserDBAPW;

Grant succeeded.

SQL> grant connect,resource to User2 identified by User2PW;

Grant succeeded.

Create synonym at SOURCE

SQL> connect UserDBA@SOURCE

Enter password:

Connected.

SQL> create synonym User2.pkg_gg_test for User1.pkg_gg_test;

Synonym created.

Verified at target that synonym got created on this package under User1 schema

Now running that alter synonym by login as User2

SQL> connect User2@SOURCE

Enter password:

Connected.

19:17:18 SQL>

19:17:45 SQL>

19:17:45 SQL> alter synonym User2.pkg_gg_test compile;

Synonym altered.

19:17:46 SQL>

GG Error:

2013-11-27 18:17:35  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  REPLICAT RPCS99 started.

2013-11-27 18:17:52  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  DDL found, operation [alter synonym User2.pkg_gg_test compile  (size 43)].

2013-11-27 18:17:52  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  DDL is of mapped scope, after mapping new operation [alter synonym User2.pkg_gg_test compile  (size 43)].

2013-11-27 18:17:52  INFO    OGG-00487  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  DDL operation included [INCLUDE MAPPED], optype [ALTER], objtype [SYNONYM], objowner [User1], objname [PKG_GG_TEST].

2013-11-27 18:17:52  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  Setting current schema for DDL operation to [USER2].

2013-11-27 18:17:52  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  Executing DDL operation.

2013-11-27 18:17:52  ERROR   OGG-00516  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  Fatal error executing DDL replication: error [Error code [1031], ORA-01031: insufficient privileges SQL alter synonym user2.pkg_gg_test compile  /* GOLDENGATE_DDL_REPLICATION */], due to explicit ABEND error handling and filter [include all (default)].

2013-11-27 18:17:52  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  PROCESS ABENDING.