GoldenGate

Hi all,

I'm in the middle of an online migration for a SAP system and I'm facing again problems, which I can't find an explanation for.

Within SAP there is often the "create table as select" being used. During replication the mapping for the base object (create table) is perfectly done, but there is no mapping being done for the derived object. Here is an extract of a replicat logfile:


2012-02-23 11:48:19 INFO OGG-00487 DDL operation included [INCLUDE MAPPED], optype [CREATE], objtype [TABLE], objowner [SAPSCM]
, objname [ORA_COCKPIT_HELP20120222072705].

2012-02-23 11:48:19 INFO OGG-01407 Setting current schema for DDL operation to [SYS].

2012-02-23 11:48:19 INFO OGG-00484 Executing DDL operation.

Source Context :
SourceModule : [ggapp.ddl]
SourceID : [scratch/aime1/adestore/views/aime1_staoi06/oggcore/OpenSys/src/gglib/ggapp/ddlrep.c]
SourceFunction : [DDLREP_handleDDLError]
SourceLine : [500]

2012-02-23 11:48:19 ERROR OGG-00519 Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view doe
s not exist, SQL create global temporary table "SAPSCM"."ORA_COCKPIT_HELP20120222072705" a

What I can't understand is, why OGG is setting the current schema to SYS? What is leading to that operation and where does SYS come from?

OGG is executed as SYSTEM on source and on target, the customer did not allow to create a specific OGG user. But we are facing the same problems even if we are using an OGG user.

If we find situations like this:
create table SAPSCM.XXX as select * from YYY
we can circumvent this failure by:

create public synonym YYY for SAPSCM.YYY;

and we restart the replicat.

But this time I'm not able to figure out, which table(s) are meant, because the SQL is much more complex. It is so long, that the complete SQL statement is not given in the logfile.

Here is the complete extract from log of my current problem:

2012-02-23 11:48:19 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [create global temporary table "SAPSCM".
"ORA_COCKPIT_HELP20120222072705" as select * from (SELECT NULL date_time, NULL week, NULL day, NULL date_, NULL time, N
ULL hour, NULL event, NULL waited_seconds, NULL waited_minutes, NULL waited_hours, NULL active_sessions, NULL waits, N
ULL avg_ms, NULL inst_id, NULL object_names FROM DUAL WHERE 1 = 0 UNION ALL ( SELECT NULL date_time, NULL week, NULL day,
NULL date_, NULL time, NULL hour, NULL event, NULL waits, NULL waited_seconds, NULL waited_minutes, NULL waited_hour
s, NULL active_sessions, NULL avg_ms, NULL inst_id, NULL object_names FROM DUAL WHERE 1 = 0 ) UNION ALL ( SELECT * FROM ( WI
TH BASIS_INFO AS ( SELECT DECODE(DBID, -1, OWN_DBID, DBID) DBID, DECODE(INSTANCE_NUMBER, -1, USERENV('INSTANCE'), INSTANCE_
NUMBER) INSTANCE_NUMBER, BEGIN_DATE, END_DATE, TO_TIMESTAMP(TO_CHAR(BEGIN_DATE, 'dd.mm.yyyy hh24:mi:ss'), 'dd.mm.y
yyy hh24:mi:ss') BEGIN_TIME, TO_TIMESTAMP(TO_CHAR(END_DATE, 'dd.mm.yyyy hh24:mi:ss'), 'dd.mm.yyyy hh24:mi:ss') END_TIME,
RTT_MICRO, MIN_AVG_DIRECT_PATH_TIME_MS, DECODE(AGGREGATE_BY, 'SNAPSHOT', 'YYYY-MM-DD HH24:MI:SS', 'DAY',
'YYYY-MM-DD (DY)', 'HOUR_OF_DAY', 'HH24', AGGREGATE_BY ) AGGREGATE_BY, CONSIDER_TIMEOUTS, EXCLUDE_WEEKEND
S, EXCLUDE_SYSTEM, EXCLUDE_ADMINISTRATIVE, MIN_TIME_WAITED_S, min_object_percentage/100 min_object_percentage, m
in_event_percentage FROM ( SELECT to_number(nvl('','-1')) DBID, to_number(nvl('','-2')) INSTANCE_NUMBER,
TO_DATE(rpad(nvl('', '1980-01-01 00:00:00'),19,':00'),'YYYY-MM-DD HH24:MI:SS') BEGIN_DATE, TO_DATE(rpad(nvl('',
'9999-12-31 00:00:00'),19,':00'),'YYYY-MM-DD HH24:MI:SS') END_DATE, to_number(nvl('','300')) RTT_MICRO, t
o_number(nvl('','1')) MIN_AVG_DIRECT_PATH_TIME_MS, decode(upper(substr('',1,1)),'N',' ',' ',' ','X') CONSIDER_TIMEOUTS, (si
ze 19339)].

2012-02-23 11:48:19 INFO OGG-00487 DDL operation included [INCLUDE MAPPED], optype [CREATE], objtype [TABLE], objowner [SAPSCM]
, objname [ORA_COCKPIT_HELP20120222072705].

2012-02-23 11:48:19 INFO OGG-01407 Setting current schema for DDL operation to [SYS].

2012-02-23 11:48:19 INFO OGG-00484 Executing DDL operation.

Source Context :
SourceModule : [ggapp.ddl]
SourceID : [scratch/aime1/adestore/views/aime1_staoi06/oggcore/OpenSys/src/gglib/ggapp/ddlrep.c]
SourceFunction : [DDLREP_handleDDLError]
SourceLine : [500]

2012-02-23 11:48:19 ERROR OGG-00519 Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view doe
s not exist, SQL create global temporary table "SAPSCM"."ORA_COCKPIT_HELP20120222072705" as select * from (SELECT NULL date_time,
NULL week, NULL day, NULL date_, NULL time, NULL hour, NULL event, NUL], no error handler present.


Does anybody have an explanation, why OGG is switching to SYS schema instead of SAPSCM? Is there anything in the setup on extract or replicat, which can have any influence on this?

Many thanks so far.
Elmar