As usual with questions,
- I have my source and target schema names different, do we need to check something during replication
- Should i worry about schema name being referenced while issuing DDL at source
Yes its about derived objects and its impact during replication using Oracle Goldengate. So what do you mean by derived objects,
Lets see how does the inclusion of schema name impact Oracle Goldengate replication.
Details of setup
Source DB - OMS
Schema Name - hari
Target DB - OGG
Schema Name - puthranv
Before getting into Derive Objects Replication, Lets start with
- OGG process setup
- DML Replication
- DDL Replication and then
- Derived Object Replication
At Source - Basic info
SQL>
SQL>
SQL> show user
USER is "HARI"
SQL>
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
OMS ARCHIVELOG
SQL>
Creating a simple table
SQL>
SQL> REM-'SOURCE'
SQL>
SQL> desc OGG_1to1map;
ERROR:
ORA-04043: object OGG_1to1map does not exist
SQL> create table ogg_1to1map (id number primary key,id_val varchar2(10));
Table created.
SQL>
SQL> alter system switch logfile;
System altered.
SQL>
OGG - Source - Setting up the Env and process
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Windows (optimized), Oracle 11g on Oct 5 2011 00:50:57
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (HARI-PC) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (HARI-PC) 2> add extract sr1_ext,tranlog,begin now
EXTRACT added.
GGSCI (HARI-PC) 3> add exttrail H:\OGG\SOURCE\dirdat\ex,extract sr1_ext
EXTTRAIL added.
GGSCI (HARI-PC) 4> dblogin userid hari,password ******
Successfully logged into database.
GGSCI (HARI-PC) 5> add trandata ogg_1to1map
Logging of supplemental redo data enabled for table HARI.OGG_1TO1MAP.
GGSCI (HARI-PC) 6> add extract sr1_pump,exttrailsource H:\OGG\SOURCE\dirdat\ex
EXTRACT added.
GGSCI (HARI-PC) 7> add rmttrail H:\OGG\TARGET\dirdat\rt,extract sr1_pump
RMTTRAIL added.
GGSCI (HARI-PC) 8> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED SR1_EXT 00:00:00 00:07:36
EXTRACT STOPPED SR1_PUMP 00:00:00 00:06:41
AT Target - Setting up the process
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Windows (optimized), Oracle 11g on Oct 5 2011 00:50:57
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (HARI-PC) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (HARI-PC) 2> dblogin userid puthranv@ogg,password *******
Successfully logged into database.
GGSCI (HARI-PC) 3>
GGSCI (HARI-PC) 3> add checkpointtable puthranv.chkp_ogg
Successfully created checkpoint table PUTHRANV.CHKP_OGG.
GGSCI (HARI-PC) 4> add replicat sr1_rep,exttrail H:\OGG\TARGET\dirdat\rt,checkpointtable puthranv.chkp_ogg
REPLICAT added.
GGSCI (HARI-PC) 5> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
REPLICAT STOPPED SR1_REP 00:00:00 00:04:53
Lets start the Oracle Goldengate Process and check run time messages
Manager :-
GGSCI (HARI-PC) 9> start mgr
Manager started.
"========================================================================"
***********************************************************************
Oracle GoldenGate Manager for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Windows (optimized), Oracle 11g on Oct 5 2011 00:28:27
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-06-09 13:08:37
***********************************************************************
Operating System Version:
Microsoft Windows 7 , on x86
Version 6.1 (Build 7600: )
Process id: 4136
Parameters...
PORT 1350
DYNAMICPORTLIST 12010-12030,1350,1230
***********************************************************************
** Run Time Messages **
***********************************************************************
2013-06-09 13:08:37 INFO OGG-00983 Manager started (port 1350).
"========================================================================"
Starting Data Pump :-
GGSCI (HARI-PC) 10> start extract sr1_pump
Sending START request to MANAGER ...
EXTRACT SR1_PUMP starting
"========================================================================"
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 1G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 1.75G
CACHESIZEMAX (strict force to disk): 1.55G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"
2013-06-09 13:09:14 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).
2013-06-09 13:09:14 INFO OGG-01052 No recovery is required for target file H:\OGG\TARGET\dirdat\rt000000, at RBA 0 (file not opened).
2013-06-09 13:09:14 INFO OGG-01478 Output file H:\OGG\TARGET\dirdat\rt is using format RELEASE 10.4/11.1.
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file H:\OGG\SOURCE\dirdat\ex000000 at 2013-06-09 13:09:18
TABLE resolved (entry HARI.OGG_1TO1MAP):
TABLE HARI.OGG_1TO1MAP;
PASSTHRU mapping resolved for source table HARI.OGG_1TO1MAP
10 records processed as of 2013-06-09 13:45:58 (rate 0,delta 0)
2013-06-09 13:46:29 INFO OGG-01021 Command received from GGSCI: STATS.
2013-06-09 14:00:41 INFO OGG-01021 Command received from GGSCI: STOP.
***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************
Report at 2013-06-09 14:00:41 (activity since 2013-06-09 13:13:41)
Output to H:\OGG\TARGET\dirdat\rt:
"========================================================================"
Starting Extract :-
GGSCI (HARI-PC) 11> start extract sr1_ext
Sending START request to MANAGER ...
EXTRACT SR1_EXT starting
"========================================================================"
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 1G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 1.75G
CACHESIZEMAX (strict force to disk): 1.55G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"
2013-06-09 13:09:18 WARNING OGG-01423 No valid default archive log destination directory found for thread 1.
2013-06-09 13:09:18 INFO OGG-01515 Positioning to begin time Jun 9, 2013 1:00:43 PM.
2013-06-09 13:09:18 INFO OGG-01052 No recovery is required for target file H:\OGG\SOURCE\dirdat\ex000000, at RBA 0 (file not opened).
2013-06-09 13:09:18 INFO OGG-01478 Output file H:\OGG\SOURCE\dirdat\ex is using format RELEASE 10.4/11.1.
***********************************************************************
** Run Time Messages **
***********************************************************************
2013-06-09 13:09:18 INFO OGG-01515 Positioning to begin time Jun 9, 2013 1:00:43 PM.
2013-06-09 13:09:18 INFO OGG-01516 Positioned to Sequence 37, RBA 153104, Jun 9, 2013 1:00:43 PM.
2013-06-09 13:09:18 INFO OGG-01517 Position of first record processed Sequence 37, RBA 153104, SCN 0.1773802, Jun 9, 2013 1:00:53 PM.
TABLE resolved (entry HARI.GGS_MARKER):
TABLE HARI.GGS_MARKER;
Using the following key columns for source table HARI.GGS_MARKER: SEQNO, FRAGMENTNO, OPTIME.
TABLE resolved (entry HARI.OGG_1TO1MAP):
TABLE HARI.OGG_1TO1MAP;
Using the following key columns for source table HARI.OGG_1TO1MAP: ID.
"========================================================================"
At the Target side
Starting Replicat:-
GGSCI (HARI-PC) 7> start replicat sr1_rep
Sending START request to MANAGER ...
REPLICAT SR1_REP starting
"========================================================================"
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 512M
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 1G
CACHESIZEMAX (strict force to disk): 881M
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"
For further information on character set settings, please refer to user manual.
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file H:\OGG\TARGET\dirdat\rt000000 at 2013-06-09 13:09:37
MAP resolved (entry HARI.OGG_1TO1MAP):
MAP HARI.OGG_1TO1MAP, TARGET puthranv.OGG_1to1map;
Using following columns in default map by name:
ID, ID_VAL
Using the following key columns for target table PUTHRANV.OGG_1TO1MAP: ID.
"========================================================================"
Lets do some DML to check OGG replication
DML at Source
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 01.07.56.034000 PM +05:30
SQL>
SQL> insert into ogg_1to1map select level,'ORACLE'||level from dual connect by level < 10;
9 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from ogg_1to1map;
COUNT(*)
----------
9
SQL>
Validating the DML at Target
DML - Replicated to Target
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 01.08.03.101000 PM +05:30
SQL>
SQL>
SQL> select count(*) from ogg_1to1map;
COUNT(*)
----------
9
lets see check the DDL replication too
DDL - At Source
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 01.16.35.561000 PM +05:30
SQL>
SQL>
SQL> alter table ogg_1to1map add id_comp varchar2(10);
Table altered.
SQL> desc ogg_1to1map;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
ID_VAL VARCHAR2(10)
ID_COMP VARCHAR2(10)
DDL - Validation at Target
At Target
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 01.16.40.738000 PM +05:30
SQL>
SQL> desc ogg_1to1map
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
ID_VAL VARCHAR2(10)
ID_COMP VARCHAR2(10)
So far so good, lets see how Oracle treats Derived Objects,
Derived Objects Replication
At Source
SQL>
SQL> create unique index hari.index_comp on hari.ogg_1to1map(id_comp);
Index created.
SQL>
SQL> select index_name,table_name from user_indexes where index_name='INDEX_COMP';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
INDEX_COMP OGG_1TO1MAP
SQL>
Lets validate the replication of the Derived Object at Target
Derived Objects Replication At Target
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 01.41.29.483000 PM +05:30
SQL>
SQL> select index_name,table_name from user_indexes where index_name='INDEX_COMP';
no rows selected
SQL> select index_name,table_name from user_indexes where index_name='INDEX_COMP';
no rows selected
SQL> select index_name,table_name from user_indexes where index_name='INDEX_COMP';
no rows selected
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 01.45.59.396000 PM +05:30
SQL>
Oh, what went wrong, my error log also doesn't say any thing too,
2013-06-09 13:40:42 INFO OGG-00482 Oracle GoldenGate Delivery for Oracle,
SR1_REP.prm: DDL found, operation [create unique index hari.index_comp on hari.ogg_1to1map(id_comp) (size 65)].
2013-06-09 13:40:42 INFO OGG-00489 Oracle GoldenGate Delivery for Oracle,
SR1_REP.prm: DDL is of mapped scope, after mapping new operation [create unique index hari.index_comp on "PUTHRANV"."OGG_1TO1MAP"(id_comp) (size 73)].
2013-06-09 13:40:42 INFO OGG-00487 Oracle GoldenGate Delivery for Oracle,
SR1_REP.prm: DDL operation included [INCLUDE MAPPED], optype [CREATE], objtype [INDEX], objowner [PUTHRANV], objname [OGG_1TO1MAP].
2013-06-09 13:40:42 INFO OGG-00484 Oracle GoldenGate Delivery for Oracle,
SR1_REP.prm: Executing DDL operation.
2013-06-09 13:40:42 INFO OGG-00483 Oracle GoldenGate Delivery for Oracle,
SR1_REP.prm: DDL operation successful.
Take a look at the transformation OGG is doing in processing the derived objects, object owner is modified as per the target environment. Ok all these looks nice, what is the issue, The ggserr shows DDL operation as success, but it is not replicated in the target DB. Isn't this a bug, have raised the same to Oracle Support. Lets see what happens to this.
As an another option, Along with the one to one map i.,e MAP hari.OGG_1to1map, TARGET puthranv.OGG_1to1map in the Replicat parameter file, Include the wildcard search like - MAP hari.*, TARGET puthranv.*;
Now let me re-test the derived objects replication on a new table.
Derived Objects - With Wildcard Search@Replicat Param.
At Source
SQL> show user
USER is "HARI"
SQL>
SQL> create table ogg_wildcard(id number primary key,id_val varchar2(10));
Table created.
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 03.23.30.103000 PM +05:30
SQL>
At Target
SQL> show user
USER is "PUTHRANV"
SQL>
SQL> create table ogg_wildcard(id number primary key,id_val varchar2(10));
Table created.
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 03.23.34.525000 PM +05:30
SQL>
Lets create the Derived and Non-Derived Object
At Source
SQL> create unique index hari.index_comp1 on hari.ogg_wildcard(id_comp);
Index created.
SQL> select index_name,table_name from user_indexes where index_name='INDEX_COMP1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
INDEX_COMP1 OGG_WILDCARD
SQL>
SQL>
SQL> REM - Check non-derived objects creation
SQL>
SQL> show user
USER is "HARI"
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 03.33.17.254000 PM +05:30
SQL>
SQL> create index index_val1 on ogg_wildcard(id_val);
Index created.
SQL> select index_name,table_name from user_indexes where index_name='INDEX_VAL1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
INDEX_VAL1 OGG_WILDCARD
SQL>
At Target - Validating the Derived and Non-Derived Object replication
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 03.31.10.720000 PM +05:30
SQL>
SQL> select index_name,table_name from user_indexes where index_name='INDEX_COMP1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
INDEX_COMP1 OGG_WILDCARD
SQL> REM - The Derived Index is created above which is not happening in 1to1map
SQL>
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
09-JUN-13 03.34.36.039000 PM +05:30
SQL>
SQL> select index_name,table_name from user_indexes where index_name='INDEX_VAL1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
INDEX_VAL1 OGG_WILDCARD
SQL>
SQL> REM - Non-derived index is created above
SQL>
Yes the derived objects are replicated when wildcard search ( MAP hari.*, TARGET puthranv.*;)are included in replicat parameter.
Why derived objects are replicated only with wildcard search and not with one to one mapping, OGG doc's has given examples using wildcard search but has never pointed that derived objects wok only with wildcard search. Have raised the same to Oracle Support. Lets wait :)
