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.

IMPDP Error ORA-39002: invalid operation ORA-39165:

Forgot to add this the other day when I came across this interesting feature ( :-/ ) oracle.
Import (impdp) was failing with:

ORA-39002: invalid operation
ORA-39165: Schema SCHEMA_NAME was not found.

Took me a while to figure out why I was getting that error.
Went thru EXPDP log and find that the similar error in there too:
ORA-39165: Schema SCHEMA_NAME was not found.

Interestingly, That schema has no objects and thus EXPDP was erroring with that error and thus the IMPDP too.

Removed these schema(s) from the IMPDP list and import went thru fine...

Fatal error executing DDL replication: error [Error code [24344], ORA-24344: success with compilation error SQL

One of my Golden Gate Replict group failed today with:

Fatal error executing DDL replication: error [Error code [24344], ORA-24344: success with compilation error SQL

Well, time to add exceptions kinda list to rpm file.

Added "ddlerror 24344 ignore" to that group rpm and am back in business.

Note: please pay attention to those objects are which are compiled with errors to make sure its valid not because some other dependencies in that object are missed in your replication environment...

ERROR OGG-01444 Oracle GoldenGate Delivery for Oracle, RPCS03.prm: Error in replicating sequence value [ORA-20783:

One of the group started abending with sequence failure as you see below:

2013-11-11 14:43:21  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  Aborted grouped transaction on 'SCHEMA.SEQUENCE_NAME', Database error 20783 ().
2013-11-11 14:43:21  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  Repositioning to rba 1850715874 in seqno 15.
2013-11-11 14:43:22  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  SQL error 20783 mapping SCHEMA.SEQUENCE_NAME to SCHEMA.SEQUENCE_NAME.
2013-11-11 14:43:22  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  Repositioning to rba 1850730331 in seqno 15.
2013-11-11 14:43:22  ERROR   OGG-01444  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  Error in replicating sequence value [ORA-20783:
ORA-08004: sequence SEQUENCE_NAME.NEXTVAL exceeds MAXVALUE and cannot be instantiated
ORA-06512: at "GGADMIN.REPLICATESEQUENCE", line 418
ORA-06512: at line 1 SQL BEGIN ggadmin .replicateSequence (TO_NUMBER(91900), TO_NUMBER(20), TO_NUMBER(1), 'SCHEMA', TO_NUMBER(10000), 'SEQUENCE_NAME', UPPER('ggadmin'), TO_NUMBER(2), TO_NUMBER (0), ''); END;].
2013-11-11 14:43:22  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  PROCESS ABENDING.

Compared this sequence at the source and target and I see the maxvalue is set to same in Source and Target.

BTW, flush sequence is not an issue as this was already taken care in the beginning of group setup.

Only thing I can find so far on this kind of issue is that it might be a bug that I am hitting (BUG:17300028 - REPLICAT ABENDS WITH ORA-20783 WHILE REPLICATING SEQUENCE). 

Anyways, I took a chance with a workaround that I thought will work to by pass this issue after making sure that this sequence is not used in the database anywhere and knowing very well that maxvalue for this Sequence is set at Source and even if it is used it wont be crossing that maxvalue.

So, what i did is recreate that sequence at Target with higher maxvalue than it is defined and started replicat and its running fine now.

ORA-01628: max # extents (32765) reached for rollback segment

OK, Finally after spending weeks of time to figure out what was going wrong with my import (IMPDP), it turns out to be a bug in 11gR2.

Was trying to import about 80G dumpfiles and impdp keeps failing on the last table with

ORA-39171: Job is experiencing a resumable wait.

ORA-01628: max # extents (32765) reached for rollback segment _SYSSMU499_3908321086$

Followed Metalink Doc: 1434643.1 (Data Pump (or other Oracle process) Reports ORA-01628: Max # Extents (32765) For Rollback Segment _SYSSMUx$) steps by shrinking and creating a new UNDO TS and etc., etc., but no use. That error kept poping up.

The thing is the table it was getting stuck is only about 9.5G with 93M records and UNDO still has about 30G left. It loads up 99% and then hangs with the wait event "wait for unread message on broadcast channel".
Note that this wait event is not just waiting on that continuously though. It only says waited a second or less than a second but never changes anything apart from that wait event.
Finally impdp will fail like 20-30 hours later with that above said error!!!!

Ok, enough blogging on this.

Reason for the error is, It's an Oracle Bug they recently identified and confirmed.
So, need to apply a patch "Patch 17306264: ORA-1628: MAX # EXTENTS (32765) REACHED FOR ROLLBACK SEGMENT - OFTEN ENCOUNTERE" and see how if the issue will be resolved or not.

Will know in couple of days.

ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01

May be I need to RTFM but found this in a hard way.

I was trying to initiate an EXPDP in our 10g (2 node RAC) database and I was getting this error.

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user EQT6TIW
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39147: cannot migrate Data Pump queue table ownership to this instance

Basically, there was another EXPDP already running on 2nd node instance and my EXPDP session load balanced and connected to 1st node instance and tried to initiate EXPDP and throwing this error.

Try making the connection to 2nd node where the first EXPDP is already running and initiate another session of EXPDP and you will be fine.

Run the below query to see which instance and who's running the EXPDP:

select * from gv$datapump_job;
OR

select * from gv$session

 where program like 'ude%';

ude - Expdp
udi - Impdp