I've got two databases on the same (test) server which I've set up with Goldengate.
The databases are both 10.2.0.4 64-bit on RHEL5, and the Goldengate version is 11.2.1.0.1 for 10g.
Source=PROD
Target=PROD11G (in anticipation of being upgraded...)
I've successfully configured Golgengate so the database was replicating DML and DDL, but realised I forgot to install support for Oracle sequences.
Following the instructions on pg 32 of the Goldengate Installation Guide, I ran the sequence.sql script as sysdba on both databases and pointed it to my existing 'GGATE' schema owner.
On the source system I've also run: alter table sys.seq$ add supplemental log data (primary key) columns;
... but I'm confused about steps 8 and 9 which are:
8. on source
GRANT EXECUTE on DDLuser.updateSequence TO DBLOGINuser;
9. on target
GRANT EXECUTE on DDLuser.replicateSequence TO Replicatuser;
The DDLuser is my 'GGATE' schema owner, but who is DBLOGINuser and ReplicatUser. Is this the schema(s) referred to in my extract group? i.e. sure006 (btw,... I tried but still get the below error when starting replicat).
Anyway,... I now start the replicat process and it abends with the below error:
2012-07-06 14:03:44 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rep1.prm: REPLICAT REP1 started.
2012-07-06 14:03:44 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rep1.prm: Setting current schema for DDL operation to [SYS].
2012-07-06 14:03:44 ERROR OGG-00519 Oracle GoldenGate Delivery for Oracle, rep1.prm: Fatal error executing DDL replication: error [Error code [24344], ORA-24344: success with compilation error SQL CREATE OR REPLACE PROCEDURE "GGATE" .replicateSequence (
sourceHWM IN NUMBER,
maxDistance IN NUMBER,
seqFlush IN NUMBER,
seqOwner IN VARCHAR2,
seqUpdate IN NUMBER,
seqName IN VARCHAR2,
loggedUser IN VARCHAR2,
racTarget IN NUMBER,
isTrace IN NUMBER,
traceUser IN VARCHAR2
)
AUTHID current_user
IS
PRAGMA autonomous_transaction;
newSeqVal NUMBER;
HWMTarget NUMBER;
HWMTargetNext NUMBER;
maxVal NUMBER;
minVal NUMBER;
cnt NUMBER;
numberOfSimulatedHWTargetMoves NUMBER;
simulateHWTargetStart NUMBER;
simulateHWTargetEnd NUMBER;
HWMOnSource NUMBER;
isFound NUMBER;
distance NUMBER;
firstPass NUMBER;
isCycle NUMBER;
incBy NUMBER;
flushStmt VARCHAR2(1000);
objId NUMBER;
sCache NUMBER;
mDist NUMBER;
BEGIN
firstPass := 1;
IF isTrace = 1 THEN
"GGATE" .seqTrace ('BEGIN "' || traceUser || '".trace_put_line(''SEQUENCEREP'', ''################ STARTING SEQUENCE REPLICATION FOR '' || '''|| seqOwner || '.' || seqName || '''); END;', traceUser);
"GGATE" .seqTrace ('BEGIN "' || traceUser || '".trace_put_line(''SEQUENCEREP'', ''INPUT IS: sourceHWM '' || '''
|| to_char(sourceHWM) || ''' || '' maxDistance '' || ''' || to_char(maxDistance) || ''' || '' seqFlush '' || ''' || to_char(seqFlush) || ''' || '' seqOwner '' || ''' || seqOwner || ''' || '' seqUpdate '' || ''' || to_char(seqUpdate) || ''' || '' seqName '' || ''' || to_char(seqName) || ''' || '' loggedUser '' || ''' || to_char(loggedUser) || ''' || '' racTarget '' || ''' || racTarget || ''' || '' isTrace '' || ''' || to_char(isTrace) || ''' || '' traceUser '' || ''' || traceUser ||'''); END;', traceUser);
END IF;
-- set current schema to sequence owner. This is to avoid issues when there is a table name same as schema
-- name in which case sequence name is treated a column to table name and things fail
EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA="' || seqOwner || '"');
-- we d.
2012-07-06 14:03:44 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep1.prm: PROCESS ABENDING.
I'm obviously doing something stupid, but can't realise what it is.
I see from the second INFO line that my current schema is being set to SYS. Should I be granting the execute privileges to SYS? (if so, the doc isn't at all clear)
I've included my manager, extract and replicat parameter files if that helps.
GLOBALS
===========
GGSCHEMA ggate
CHECKPOINTTABLE ggate.chkptab
mgr.prm
===========
PORT 7809
USERID ggate, PASSWORD *****
PURGEOLDEXTRACTS /u04/app/oracle/goldengate/11.2.1.0.1/10g/extracts, USECHECKPOINTS
ext1.prm
===========
EXTRACT ext1
USERID ggate@prod, PASSWORD *****
RMTHOST prod2.ctru.auckland.ac.nz, MGRPORT 7809
RMTTRAIL /u04/app/oracle/goldengate/11.2.1.0.1/10g/dirdat/rt
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
TABLE sure006.*;
rep1.prm
===========
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggate@prod11g, PASSWORD *****
DISCARDFILE /u04/app/oracle/goldengate/11.2.1.0.1/10g/dirdat/discard, append
MAP sure006.*, TARGET sure006.*;
Edited by: stuartu on Jul 6, 2012 3:07 PM