Find DML SQL statement literal values caused Replicate to Abend.

Generally in Goldengate replicat apply SQL using bind variable so when error occurs report file or error log will have SQL statement with bind
variable. This is hard to catch which literal values caused a problem at Target side. Below is example to see Which literal values caused a
problem.

Error log / Reporfile (Normal Mode)

2013-05-15 11:08:31  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rbmdr.prm:  
Aborted grouped transaction on 'DUMMY_ADMIN_6.DUMMYEVENT', Database error 1403 (OCI Error ORA-01403: no data found, SQL ).

2013-05-15 11:08:31  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rbmdr.prm:  
SQL error 1403 mapping DUMMY_ADMIN.DUMMYEVENT to DUMMY_ADMIN_6.DUMMYEVENT OCI Error ORA-01403: no data found, SQL .

2013-05-15 11:08:31  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rbmdr.prm:  
Error mapping from DUMMY_ADMIN.DUMMYEVENT to DUMMY_ADMIN_6.DUMMYEVENT.

Edit below parameters in replicat and start again.

NOBINARYCHARS
NODYNSQL
SHOWSYNTAX

Error log / Reporfile (Actual value)

2013-05-15 11:17:35  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rbmdr.prm:  
Aborted grouped transaction on 'DUMMY_ADMIN_6.DUMMYEVENT', Database error 1403 (OCI Error ORA-01403: no data found, SQL ).
2013-05-15 11:17:35  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rbmdr.prm:  

SQL error 1403 mapping DUMMY_ADMIN.DUMMYEVENT to DUMMY_ADMIN_6.DUMMYEVENT OCI Error ORA-01403: no data found, SQL .
2013-05-15 11:17:35  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rbmdr.prm:  
Error mapping from DUMMY_ADMIN.DUMMYEVENT to DUMMY_ADMIN_6.DUMMYEVENT.

Goldengate Filter data based on Before image and and after Value in Extract.

Below is example to filter data based on Existing value or before image in column and New updated value .

This is used in 11.2 OGG. In OGG 12C use @BEFORE to Get before image.

So X_COMMAND will appear as "REFRESH" If status change from Inactive to Active.

X_COMMAND=@IF (((@STRFIND(before.STATUS, "Inactive") > 0 ) and (@STRFIND(STATUS, "Active") >0 )) > 0 , "REFRESH", @CASE(@GETENV("GGHEADER", "OPTYPE"), "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE"))

You can also use this code for compare Exist and new value.

((@STRFIND(before.STATUS, "Inactive") > 0 ) and (@STRFIND(STATUS, "Active") >0 )) > 0 . 

Goldengate Filter data using Lookup Table and SQLEXEC .

This is true that You can use multiple SQLEXEC in Goldengate.

Below code shows How to filter data from SOURCE Table using Lookup table and get filter value dynamically .

This code check If Old value ORG_ID for source_table is exist in LOOKUP_PARAMETER_TAB.
to filter using lookup table ,I used FILTER and get lookup value from SQLEXEC.if value match it execute another SQLEXEC where it
execute stored procedure and Get dynamic parameters using @GETVAL function and pass to Procedure.

TABLE "INV"."SOURCE_TABLE",FILTER (ORG_ID = @GETVAL(lookup.XYZ)),SQLEXEC (ID lookup, QUERY "select distinct (MASTER_ORG_ID) XYZ from ABC.LOOKUP_PARAMETER_TAB mp  where mp.MASTER_ORG_ID= :p_organization_id_X", PARAMS (p_organization_id_X=ORG_ID),BEFOREFILTER , TRACE),SQLEXEC (ID ABC.PACKAGE_TO_EXEC.PROCEDURE_TO_EXEC,SPNAME ABC.PACKAGE_TO_EXEC.PROCEDURE_TO_EXEC,PARAMS (P_DBNAME=@GETENV("DBENVIRONMENT","DBNAME"),P_TRANSACTION_ID=@GETENV("TRANSACTION", "XID"),P_COMMAND=@CASE(@GETENV("GGHEADER", "OPTYPE"), "INSERT", "INSERT", "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE","DELETE","DELETE"),P_INVENT_ITEM_ID=INVENT_ITEM_ID,P_ORGANIZATION_ID=ORG_ID),AFTERFILTER, TRACE);

Goldengate Stored procedure parameter in SQLEXEC with GETENV

Here GG is extracting ABC.DUMMY table and while It does that, It also does following.

- check Where ORGANIZATION_ID =83,
- if true above execute XYZ.DUMMY_PKG (PACKAGE) to DUMMY_PROCEDURE_NAME (procedure),GG has bug for fully qualify Name of schema and Packagename as prefix.
-while executing above Procedure , It gets value run-time and pass value to procedure.

~database Name using GETENV("DBENVIRONMENT","DBNAME").
~Object Name
~Inventory_item_id (comes within procedure )
~Get Command type INSERT/UPDATE/DELETE ,again case function wrote to convert SQL COMPUPDATE / PKUPDATE to UPDATE string.
~Get Transaction XID of database.

TABLE ABC.DUMMY,WHERE (ORGANIZATION_ID = 83),SQLEXEC (ID XYZ.DUMMY_PKG.DUMMY_PROCEDURE_NAME,SPNAME XYZ.DUMMY_PKG.DUMMY_PROCEDURE_NAME,PARAMS (P_DBNAME=@
GETENV("DBENVIRONMENT","DBNAME"),P_OBJECT_NAME='DUMMY',P_INVENTORY_ITEM_ID=INVENTORY_ITEM_ID,P_ORGANIZATION_ID=ORGANIZATION_ID,P_COMMAND=@CASE(@GETENV("GGHEADER", "OPTYPE"), "INSERT", "INSERT", "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE","DELETE","DELETE"),P_TRANSACTION_ID=@GETENV("TRANSACTION", "XID")));

Goldengate Discard Monitoring and Alert.

This script is to monitor Discard , It should run Each Hour at 59 Min.

It will send email if Any discard found in Any Processes. Please customize it according to your Need.

It is still under Development and raw but it does the Job.

#!/usr/bin/ksh
##############################################################
# To get Accurate Result schedule it at each Hour like 59 * * * * from Cron
##############################################################
# Export variables
##############################################################
export ORACLE_HOME=/usr/local/opt/oracle/11r2
export GG_HOME=/usr/local/opt/oracle/ggs/112106
export PATH=$ORACLE_HOME/bin:$GG_HOME:$PATH
##############################################################
# Customized "info all" command to get Replicat Processes
##############################################################
X=`${GG_HOME}/ggsci< /tmp/info.log
stats $i,Hourly
exit
!
echo "______________________________"
##############################################################
# Check and send Email if any discard Found. 
##############################################################
Y=`grep discards /tmp/info.log |tr "." " "|awk '{print $3}'`
echo $Y
for c in $Y
do
if [ $c -gt 0 ];then
echo "send email for process $i"
mailx -s "Discards found in $i" emailadd@comapny.com < /tmp/info.log
cat /tmp/info.log
else
echo "dont send email"
fi
done
done
###############################################################

OGG-01028 Formatting error

Extract can be abend if Parameters are not Set correctly.
Extract was abend due to below Error , after searching on Metalink I found that it is considered as DB bug which fixed in 11.2.0.4.
But there is workaround.

Error

2013-09-24 04:08:30  ERROR   OGG-01028  Formatting error on: table name DUMMY.DUMMY_INQ, rowid AADYhHAHVAAJLIcAAA, XID 8.26.543865, 
position (Seqno 7314, RBA 212298768). Error converting timestamp with timezone from Oracle to ASCII format for column DUMMY_UPDATE.

Metalink Suggestion

GoldenGate extract abends: OGG-01028 Formatting error on: table name xxxx.xxxx rowid , XID nnnn.nn.nnnn, position (Seqno n, RBA n). (Doc ID 1558791.1)

Oracle GoldenGate - Version 11.2.1.0.1 and later
Information in this document applies to any platform.

This is related to database bug 15947884 (base bug 14053498).

In House WorkAround

--Add SETENV parameter before userid 
SETENV (NLS_LANG="AMERICAN_AMERICA.US7ASCII")
--Add SETENV in Extract
TRANLOGOPTIONS INCLUDEREGIONID

Find Transaction SQL from RBA

logdump> open 
logdump> GHDR ON
logdump> DETAIL DATA
logdump> HEADERTOKEN
logdump> GGSTOKEN detail
logdump> pos  
TokenID x47 'G' Record Header    Info x01  Length 1180
TokenID x48 'H' GHDR             Info x00  Length   55
 4504 0041 0417 0fff 02f2 052c 5ca7 4670 0000 0000 | E..A.......,\.Fp....
 05c2 b45c 0001 83f6 0352 0000 0001 4e53 5044 4241 | ...\.....R....dummy
 2e4e 5350 5f49 4e56 454e 544f 5259 00             | .DUMMY_INVENTORY.
TokenID x44 'D' Data             Info x00  Length 1047
TokenID x54 'T' GGS Tokens       Info x00  Length   58
TokenID x5a 'Z' Record Trailer   Info x01  Length 1180
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :  1047  (x0417)   IO Time    : 2013/06/12 14:08:51.000.944
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
---------------------------------------------------------------
---------------------------------------------------------------
--AuditRBA is Redo sequence Number , AuditPos is SCN
AuditRBA   :      99318       AuditPos   : 96646236   
---------------------------------------------------------------
---------------------------------------------------------------
Continued  :     N  (x00)     RecCount   :     1  (x01)

2013/06/12 14:08:51.000.944 FieldComp            Len  1047 RBA 433498317
Name: DUMMYDBA.DUMMY_INVENTORY
After  Image:                                             Partition 4   G  s
 0000 000e 0000 000a 4d37 3266 3833 6464 3034 0001 | ........M72f83dd04..
 0003 0000 4e00 0200 0c00 0000 0847 5244 4e43 4130 | ....N........GRDNCA0
 3100 0300 0900 0000 0531 3256 5450 0004 0009 0000 | 1........12VTP......
 0005 3131 5437 5600 0500 0a00 0000 0633 3130 3231 | ..11T7V........31021
 3700 0600 0a00 0000 0634 3130 3630 3600 0700 0300 | 7........410606.....
 004e 0008 0003 0000 4e00 0900 0300 004e 000a 0008 | .N......N......N....
 0000 0004 4654 544e 000b 0015 ffff 3139 3030 2d30 | ....FTTN......1900-0
Column     0 (x0000), Len    14 (x000e)
 0000 000a 4d37 3266 3833 6464 3034                | ....M72f83dd04
Column     1 (x0001), Len     3 (x0003)
 0000 4e                                           | ..N
Column     2 (x0002), Len    12 (x000c)
 0000 0008 4752 444e 4341 3031                     | ....GRDNCA01
Column     3 (x0003), Len     9 (x0009)
 0000 0005 3132 5654 50                            | ....12VTP
Column     4 (x0004), Len     9 (x0009)
 0000 0005 3131 5437 56                            | ....11T7V
Column     5 (x0005), Len    10 (x000a)
 0000 0006 3331 3032 3137                          | ....310217
Column     6 (x0006), Len    10 (x000a)
 0000 0006 3431 3036 3036                          | ....410606
Column    57 (x0039), Len     4 (x0004)
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0
 30                                                | 0
Column    78 (x004e), Len    21 (x0015)
 ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0
 30                                                | 0
Column    79 (x004f), Len     4 (x0004)
 ffff 0000                                         | ....
Column    80 (x0050), Len     5 (x0005)
 0000 0001 59                                      | ....Y

GGS tokens:
TokenID x52 'R' ORAROWID         Info x00  Length   20
 4141 4338 6c39 4143 6d41 4141 7a68 3141 4148 0001 | AAC8l9ACmAAAzh1AAH..
TokenID x4c 'L' LOGCSN           Info x00  Length   14
 3133 3332 3539 3536 3332 3432 3337                | 13325956324237
------XID Transactions. 
TokenID x36 '6' TRANID           Info x00  Length   12
 3332 2e32 382e 3831 3139 3133                     | 32.28.811913 

Transaction XID will be at end of Logdump record format.


This way we can find XID,redo log sequence and SCN of Record.

Now how to find Original transaction.

Go to source database.

Find Location of Archivelog sequence which is in AuditRBA in our case it is 99318.

spool output.txt
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
set numformat 9999999999999999999999
set long 999999
set lines 190
set pages 555
exec dbms_logmnr.add_logfile(' complete path of arc seq 99318 '); 
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
select username, os_username, DATA_OBJ#, rbasqn, RBABLK, rbabyte, RS_ID, row_id, rollback, seg_name,seg_owner,operation, timestamp, sql_redo, scn, cscn from v$logmnr_contents where xidusn || '.' || xidslt || '.' || xidsqn='32.28.811913';
EXECUTE DBMS_LOGMNR.END_LOGMNR;
spool off;

Goldengate User Creation.

This is SQL script to Create GG Admin User. Script Run DBMS_PACKAGE and grants according to Oracle database Version.

It also created Dedicated Tablespace for Goldengate User.

prompt "Enter Goldengate Tablespace Name"
def GGATE_ADMIN_TABLESPACE_NAME=&1
prompt "Enter Goldengate Tablespace Location"
def GG_ADMIN_TABLESPACE_LOCATION=&2
prompt "Enter Goldengate Admin UserName"
def GOLDENGATE_ADMIN_USER=&3
prompt "Enter Goldengate username password"
def GOLDENGATE_ADMIN_USER_PWD=&4
set serveroutput on;
prompt "-------------------------------------------------------------------------"
prompt "This script first drop Old tablespace and Goldengate user if same name exist"
prompt "Provide DISKGROUP NAME LIKE +DATA if Tablespace location is on ASM  "
PROMPT "OR"
prompt "provide filesystem with datafile name like /u01/app/datafile/GGADMIN.dbf"
prompt "-------------------------------------------------------------------------"
spool GG_ADMIN_USER_CREATION.LOG
prompt "Goldengate User must have its own Tablespace it can not be shared with"
Prompt "shared with any other user or ddl_setup script will fail"
--prompt "=======first read above instruction clearly============"
prompt "-------------------------------------------------------------------------"
prompt "------------------------Disable DDL--------------------------------------"
@ddl_disable.sql
SET VERIFY OFF;
prompt "-----------------------Droping any existing Tablespace-------------------"
DROP TABLESPACE &GGATE_ADMIN_TABLESPACE_NAME INCLUDING CONTENTS AND DATAFILES;
prompt "-----------------------Droping any existing User-------------------------"
DROP USER &GOLDENGATE_ADMIN_USER CASCADE;
Prompt "-----------------------creating new Tablespace --------------------------"
CREATE TABLESPACE &GGATE_ADMIN_TABLESPACE_NAME  datafile '&GG_ADMIN_TABLESPACE_LOCATION' SIZE 1500M autoextend on;
--PROMPT "ENTER PASSWORD FOR NEW GOLDENGATE ADMIN USER"
prompt "-----------------------creating new Goldengate Admin User----------------"
CREATE USER &GOLDENGATE_ADMIN_USER IDENTIFIED BY &GOLDENGATE_ADMIN_USER_PWD DEFAULT TABLESPACE &GGATE_ADMIN_TABLESPACE_NAME TEMPORARY TABLESPACE TEMP;
GRANT CONNECT,alter session,CREATE SESSION,RESOURCE,CREATE TABLE TO &GOLDENGATE_ADMIN_USER;
GRANT FLASHBACK ANY TABLE TO &GOLDENGATE_ADMIN_USER;
grant SELECT ANY TRANSACTION to &GOLDENGATE_ADMIN_USER;
grant SELECT ANY TABLE to &GOLDENGATE_ADMIN_USER;
grant SELECT ANY DICTIONARY to &GOLDENGATE_ADMIN_USER;
grant CREATE TABLE to &GOLDENGATE_ADMIN_USER;
grant ALTER SESSION to &GOLDENGATE_ADMIN_USER;
grant UNLIMITED TABLESPACE  to &GOLDENGATE_ADMIN_USER;
grant EXECUTE ON utl_file to &GOLDENGATE_ADMIN_USER;
grant select on v_$instance to &GOLDENGATE_ADMIN_USER;
grant select on v_$database to &GOLDENGATE_ADMIN_USER;
grant LOCK ANY TABLE to &GOLDENGATE_ADMIN_USER;
PROMPT "----------DBA PRIVILEGES WILL BE GRANTED TO GOLDENGATE_ADMIN_USER--------"
GRANT DBA TO &GOLDENGATE_ADMIN_USER;
Prompt "-------------------------------------------------------------------------"
Prompt "-------------------------------------------------------------------------"
Prompt " Running PLSQL BLOCK to execute procedure and grants according to Oracle version"
Prompt "-------------------------------------------------------------------------"
Prompt "-------------------------------------------------------------------------"
Prompt
Prompt


declare
v1 VARCHAR2(200);
v2 varchar2(200);
V3 varchar2(1000);
l_sql varchar2(1000);
begin
select version into v1 from DBA_REGISTRY WHERE COMP_NAME LIKE '%Catalog Views%';
 CASE
      WHEN v1 like '10.2%' THEN
DBMS_OUTPUT.PUT_LINE('it is Oracle version 10.2');
execute immediate 'BEGIN dbms_streams_auth.grant_admin_privilege(''&GOLDENGATE_ADMIN_USER''); END;';
EXECUTE IMMEDIATE 'grant insert on system.logmnr_restart_ckpt$ to &GOLDENGATE_ADMIN_USER';
EXECUTE IMMEDIATE 'grant update on sys.streams$_capture_process to &GOLDENGATE_ADMIN_USER';
EXECUTE IMMEDIATE 'grant become user to &GOLDENGATE_ADMIN_USER';
dbms_output.put_line('Executing dbms_streams_auth.grant_admin_privilege');
WHEN v1 like '11.1%' THEN
DBMS_OUTPUT.PUT_LINE('it is Oracle version 11.1');
execute immediate 'BEGIN dbms_streams_auth.grant_admin_privilege(''&GOLDENGATE_ADMIN_USER''); END;';
EXECUTE IMMEDIATE 'grant become user to &GOLDENGATE_ADMIN_USER';
dbms_output.put_line('Executing dbms_streams_auth.grant_admin_privilege');
WHEN v1 like '11.2.0.3%' THEN
DBMS_OUTPUT.PUT_LINE('it is Oracle version 11.2.0.3');
EXECUTE IMMEDIATE 'begin dbms_goldengate_auth.grant_admin_privilege(''&GOLDENGATE_ADMIN_USER''); END;';
dbms_output.put_line('Executing dbms_goldengate_auth.grant_admin_privilege');
    END CASE;
exception when others
then
dbms_output.put_line('ERROR IS ' || SQLCODE || SQLERRM);
end;
/
prompt "---------------------script end-------------------------------------------"
prompt
prompt
prompt "~~~~~~~~please verify GG_ADMIN_USER_CREATION.LOG for Errors ~~~~~~~~~~~~~~"
prompt
prompt
prompt
spool OFF;

OGG-01930 Errors In Datastore

Few days back we got below error on few instance of GG.

2013-06-07 01:25:06 WARNING OGG-01930 Oracle GoldenGate Delivery for Oracle, dummy1.prm: Datastore error in 'dirbdb': BDB0113 Thread/process 17080/1125251392 failed: BDB1507 Thread died in Berkeley DB library.


Later found that one of the Teammate is trying to configure Monitoring using OEM plugin for OGG.

If your version of GoldenGate from Version 11.2.0.0.0 to 11.2.1.0.4 then You must upgrade till OGG 11.2.1.0.5_02 and re-create datastore.

you may found more reference document on.
GoldenGate Monitor: BDB0060 BDB1581 BDB1582 BDB1507 BDB0118 BDB2027 BDB0113 BDB0087 Errors In Datastore [ID 1495998.1]

Goldengate Implementation Automated - I

THis is part of First script to Install DDL/SEQUENCES and Create HeartBeat Tables.

It also create GoldenGate parameter file for Hearbeat tables and Update GLOBALS file for GG schema.

Parden my editing because HTML does not like ">" .

###########################################################################
#!/bin/ksh


######################################

###functions.

checksid ()

{

ORACLE_SID=""

ORACLE_HOME=""

TNS_ADMIN=""

cat /etc/oratab
cut -d ":" -f1

#echo "SID must be in oratab file"

echo "Enter ORACLE_SID of Database from Above:\c "

read ORACLE_SID

while :

do

if [[ $( grep -wc "$ORACLE_SID" /etc/oratab ) -eq 1 ]]

then

echo "ORACLE SID Found"

echo "$ORACLE_SID is instance"

ORACLE_HOME=`grep -w $ORACLE_SID /etc/oratab
cut -d ":" -f2`

TNS_ADMIN=$ORACLE_HOME/network/admin

PATH=$ORACLE_HOME/bin:$PATH

GGHOME=$PWD

echo "Current Oracle Instance :-------------: $ORACLE_SID"

echo "Current Oracle Home :-----------------: $ORACLE_HOME"

echo "Current TNS admin :-------------------: $TNS_ADMIN"

echo "Current Goldengate Home :-------------: $GGHOME"

break;

#exit 1

else

echo "Enter valid ORACLE_SID or press ctl+c to cancel script:\c"

read ORACLE_SID

continue;

fi

done

}

###############################################################



checksid

echo "Enter Goldengate Home directory:\c"

read GGHOME

echo "$GGHOME"

echo "----Create Install directory-----"

mkdir -p $GGHOME/admin/etc

mkdir -p $GGHOME/admin/logs

mkdir -p $GGHOME/admin/scripts

mkdir -p $GGHOME/dirprm/parameterfiles

mkdir -p $GGHOME/admin/install

echo "----copy sqls to Install dir-----"

cp $GGHOME/*.sql $GGHOME/admin/install

export GGINSTALL=$GGHOME/admin/install

echo $GGINSTALL

echo "------start processing ----------"

for i in $GGINSTALL/ddl_remove.sql $GGINSTALL/marker_setup.sql $GGINSTALL/ddl_setup.sql

do

echo "----- $i file Processing Now --------"

sed -e '/new_value gg_user/d' $i > $GGINSTALL/temp1.sql

sed -e '/accept/d' $GGINSTALL/temp1.sql > $GGINSTALL/temp2.sql

mv $GGINSTALL/temp2.sql $i

done

echo "verify files"

echo "---------File processing is Done -------"

echo ""

echo ""

echo "----------------------------------------"

echo "Enter Goldengate Admin User for DDL and sequence Installation :\c"

read GOLDENGATE_ADMIN_USER

echo $GOLDENGATE_ADMIN_USER

echo "DISABLE DDL and remove if any Existing Installation"

$ORACLE_HOME/bin/sqlplus -s / as sysdba <
PROMPT "=========================DISABLE DDL TRIGGER =========================================="

@$GGINSTALL/ddl_disable.sql

PROMPT "===========================REMOVING DDL ==============================================="

@$GGINSTALL/ddl_remove.sql $GOLDENGATE_ADMIN_USER

EOF

echo "Now Installing Sequence and DDL"

$ORACLE_HOME/bin/sqlplus -s / as sysdba <
PROMPT "============================INSTALL SEQUENCE==========================================="

@$GGINSTALL/sequence.sql $GOLDENGATE_ADMIN_USER

PROMPT "============================MARKER SCRIPT=============================================="

@$GGINSTALL/marker_setup.sql $GOLDENGATE_ADMIN_USER

PROMPT "============================INSTALL DDL================================================"

@$GGINSTALL/ddl_setup.sql $GOLDENGATE_ADMIN_USER

PROMPT "============================ENABLE DDL================================================="

@$GGINSTALL/ddl_enable.sql

PROMPT "============================RUN DDL PIN ==============================================="

@$GGINSTALL/ddl_pin.sql $GOLDENGATE_ADMIN_USER

EOF

echo "-----------DDL and sequence installation completed for user $GOLDENGATE_ADMIN_USER--------"

echo ""

echo ""

sleep 2

echo "--------------------creating Heartbeat Table for $GOLDENGATE_ADMIN_USER-------------------"

$ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF

spool GG_HEARTBEAT_SETUP.log

set VERIFY OFF;

drop table "$GOLDENGATE_ADMIN_USER".heartbeat;

-- Create table statement

prompt "--creating HEARTBEAT TABLE "

CREATE TABLE "$GOLDENGATE_ADMIN_USER".HEARTBEAT

( ID NUMBER ,

SRC_DB VARCHAR2(30),

EXTRACT_NAME varchar2(8),

SOURCE_COMMIT TIMESTAMP,

TARGET_COMMIT TIMESTAMP,

CAPTIME TIMESTAMP,

CAPLAG NUMBER,

PMPTIME TIMESTAMP,

PMPGROUP VARCHAR2(8 BYTE),

PMPLAG NUMBER,

DELTIME TIMESTAMP,

DELGROUP VARCHAR2(8 BYTE),

DELLAG NUMBER,

TOTALLAG NUMBER,

thread number,

update_timestamp timestamp,

EDDLDELTASTATS number,

EDMLDELTASTATS number,

RDDLDELTASTATS number,

RDMLDELTASTATS number,

CONSTRAINT HEARTBEAT_PK PRIMARY KEY (thread) ENABLE

)

/

prompt "--DROPING SEQUENCE "

DROP SEQUENCE "$GOLDENGATE_ADMIN_USER".SEQ_GGS_HEARTBEAT_ID ;

CREATE SEQUENCE "$GOLDENGATE_ADMIN_USER".SEQ_GGS_HEARTBEAT_ID INCREMENT BY 1 START WITH 1 ORDER ;

DROP TABLE "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT;

prompt "--Creating GGS_HEARTBEAT TABLE "

CREATE TABLE "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT

(ID NUMBER ,

SRC_DB VARCHAR2(30),

EXTRACT_NAME varchar2(8),

SOURCE_COMMIT TIMESTAMP,

TARGET_COMMIT TIMESTAMP,

CAPTIME TIMESTAMP,

CAPLAG NUMBER,

PMPTIME TIMESTAMP,

PMPGROUP VARCHAR2(8 BYTE),

PMPLAG NUMBER,

DELTIME TIMESTAMP,

DELGROUP VARCHAR2(8 BYTE),

DELLAG NUMBER,

TOTALLAG NUMBER,

thread number,

update_timestamp timestamp,

EDDLDELTASTATS number,

EDMLDELTASTATS number,

RDDLDELTASTATS number,

RDMLDELTASTATS number,

CONSTRAINT GGS_HEARTBEAT_PK PRIMARY KEY (DELGROUP) ENABLE

);



prompt "--Creating GGS_HEARTBEAT_TRIG TRIGGER "



CREATE OR REPLACE TRIGGER "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_TRIG

BEFORE INSERT OR UPDATE ON "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT

FOR EACH ROW

BEGIN

select seq_ggs_HEARTBEAT_id.nextval

into :NEW.ID

from dual;

select systimestamp

into :NEW.target_COMMIT

from dual;

select trunc(to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT ),1, instr(:NEW.CAPTIME - :NEW.SOURCE_COMMIT,' ')))) * 86400

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+1,2)) * 3600

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+4,2) ) * 60

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+7,2))

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+10,6)) / 1000000

into :NEW.CAPLAG

from dual;

select trunc(to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME),1, instr(:NEW.PMPTIME - :NEW.CAPTIME,' ')))) * 86400

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+1,2)) * 3600

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+4,2) ) * 60

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+7,2))

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+10,6)) / 1000000

into :NEW.PMPLAG

from dual;

select trunc(to_number(substr((:NEW.DELTIME - :NEW.PMPTIME),1, instr(:NEW.DELTIME - :NEW.PMPTIME,' ')))) * 86400

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+1,2)) * 3600

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+4,2) ) * 60

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+7,2))

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+10,6)) / 1000000

into :NEW.DELLAG

from dual;

select trunc(to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),1, instr(:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT,' ')))) * 86400

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+1,2)) * 3600

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+4,2) ) * 60

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+7,2))

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+10,6)) / 1000000

into :NEW.TOTALLAG

from dual;

end ;

/



prompt "--Enable GGS_HEARTBEAT TABLE "

ALTER TRIGGER "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_TRIG ENABLE;



--

-- This is for the History heartbeat table

--

prompt "--Creating GGS_HEARTBEAT_HIST sequence "



DROP SEQUENCE "$GOLDENGATE_ADMIN_USER".SEQ_GGS_HEARTBEAT_HIST ;

CREATE SEQUENCE "$GOLDENGATE_ADMIN_USER".SEQ_GGS_HEARTBEAT_HIST INCREMENT BY 1 START WITH 1 ORDER ;

DROP TABLE "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_HISTORY;



prompt "--Creating GGS_HEARTBEAT_HIST TABLE "



CREATE TABLE "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_HISTORY

( ID NUMBER ,

SRC_DB VARCHAR2(30),

EXTRACT_NAME varchar2(8),

SOURCE_COMMIT TIMESTAMP,

TARGET_COMMIT TIMESTAMP,

CAPTIME TIMESTAMP,

CAPLAG NUMBER,

PMPTIME TIMESTAMP,

PMPGROUP VARCHAR2(8 BYTE),

PMPLAG NUMBER,

DELTIME TIMESTAMP,

DELGROUP VARCHAR2(8 BYTE),

DELLAG NUMBER,

TOTALLAG NUMBER,

thread number,

update_timestamp timestamp,

EDDLDELTASTATS number,

EDMLDELTASTATS number,

RDDLDELTASTATS number,

RDMLDELTASTATS number,

CONSTRAINT GGS_HEARTBEAT_HIST_PK PRIMARY KEY (ID) ENABLE

);



prompt "--Creating GGS_HEARTBEAT_TRIG_HIST Trigger. "



CREATE OR REPLACE TRIGGER "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_TRIG_HIST

BEFORE INSERT OR UPDATE ON "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_HISTORY

FOR EACH ROW

BEGIN

select seq_ggs_HEARTBEAT_HIST.nextval

into :NEW.ID

from dual;

select systimestamp

into :NEW.target_COMMIT

from dual;

select trunc(to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT ),1, instr(:NEW.CAPTIME - :NEW.SOURCE_COMMIT,' ')))) * 86400

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+1,2)) * 3600

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+4,2) ) * 60

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+7,2))

+ to_number(substr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT), instr((:NEW.CAPTIME - :NEW.SOURCE_COMMIT),' ')+10,6)) / 1000000

into :NEW.CAPLAG

from dual;

select trunc(to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME),1, instr(:NEW.PMPTIME - :NEW.CAPTIME,' ')))) * 86400

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+1,2)) * 3600

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+4,2) ) * 60

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+7,2))

+ to_number(substr((:NEW.PMPTIME - :NEW.CAPTIME), instr((:NEW.PMPTIME - :NEW.CAPTIME),' ')+10,6)) / 1000000

into :NEW.PMPLAG

from dual;

select trunc(to_number(substr((:NEW.DELTIME - :NEW.PMPTIME),1, instr(:NEW.DELTIME - :NEW.PMPTIME,' ')))) * 86400

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+1,2)) * 3600

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+4,2) ) * 60

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+7,2))

+ to_number(substr((:NEW.DELTIME - :NEW.PMPTIME), instr((:NEW.DELTIME - :NEW.PMPTIME),' ')+10,6)) / 1000000

into :NEW.DELLAG

from dual;

select trunc(to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),1, instr(:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT,' ')))) * 86400

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+1,2)) * 3600

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+4,2) ) * 60

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+7,2))

+ to_number(substr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT), instr((:NEW.TARGET_COMMIT - :NEW.SOURCE_COMMIT),' ')+10,6)) / 1000000

into :NEW.TOTALLAG

from dual;

end ;

/



prompt "--Enable GGS_HEARTBEAT_HIST TABLE"



ALTER TRIGGER "$GOLDENGATE_ADMIN_USER".GGS_HEARTBEAT_TRIG_HIST ENABLE;





Prompt "--Setup DBMS_SCHEDULER for Heart Beat"



prompt "--grant select on v_$instance to GOLDENGATE_ADMIN_USER is already given. "

prompt "--grant select on v_$database to GOLDENGATE_ADMIN_USER is already given. "



PROMPT "--CREEATE PROCEDURE TO UPDATE HEARTBEAT TABLE"

CREATE OR REPLACE PROCEDURE "$GOLDENGATE_ADMIN_USER".gg_update_hb_tab IS

v_thread_num NUMBER;

v_db_unique_name VARCHAR2 (128);

BEGIN

SELECT thread#, db_unique_name

INTO v_thread_num, v_db_unique_name

FROM v\$instance, v\$database;

UPDATE "$GOLDENGATE_ADMIN_USER".heartbeat

SET update_timestamp = SYSTIMESTAMP

,src_db = v_db_unique_name

WHERE thread = v_thread_num;

COMMIT;

END;

/

PROMPT "INSERT INTO HEARTBEAT TABLE"

truncate table "$GOLDENGATE_ADMIN_USER".HEARTBEAT;



INSERT INTO "$GOLDENGATE_ADMIN_USER".HEARTBEAT (thread) select thread# from GV\$instance;

commit;



---FOR 10G USE THIS



PROMPT "CREATE GOLDENGATE JOBS"



BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => '"$GOLDENGATE_ADMIN_USER".OGG_HB',

job_type => 'STORED_PROCEDURE',

job_action => '"$GOLDENGATE_ADMIN_USER".GG_UPDATE_HB_TAB',

number_of_arguments => 0,

repeat_interval => 'FREQ=MINUTELY',

job_class => 'SYS.DEFAULT_JOB_CLASS',

start_date => NULL,

end_date => NULL,

enabled => FALSE,

auto_drop => FALSE,

comments => 'GoldenGate'

);

END;

/





BEGIN

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

name => '"$GOLDENGATE_ADMIN_USER"."OGG_HB"',

attribute => 'restartable', value => TRUE);

END;

/



BEGIN

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(

name => '"$GOLDENGATE_ADMIN_USER"."OGG_HB"',

attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

END;

/



BEGIN

SYS.DBMS_SCHEDULER.enable(

name => '"$GOLDENGATE_ADMIN_USER"."OGG_HB"');

END;

/



SPOOL OFF;

EOF

echo "-------------------------Hearbeat setup completed --------------------------------"

echo ""

echo ""



echo "----------creating parameterfile for $GOLDENGATE_ADMIN_USER FOR EXTRACT---------"

echo "TABLE GGADMIN.HEARTBEAT," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "TOKENS (" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "CAPGROUP = @GETENV (\"GGENVIRONMENT\", \"GROUPNAME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "CAPTIME = @DATE (\"YYYY-MM-DD HH:MI:SS.FFFFFF\",\"JTS\",@GETENV (\"JULIANTIMESTAMP\"))," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "EDDLDELTASTATS = @GETENV (\"DELTASTATS\", \"DDL\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "EDMLDELTASTATS = @GETENV (\"DELTASTATS\", \"DML\")" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo ");" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo ""

echo ""

echo "----EXTRACT Hearbeat mapping parameterfile for $GOLDENGATE_ADMIN_USER is ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm--"

echo ""

echo ""

cat ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_EXTRACT.prm

echo "----------creating parameterfile for $GOLDENGATE_ADMIN_USER FOR PUMP---------"

echo "TABLE GGADMIN.heartbeat," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm

echo "TOKENS (" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm

echo "PMPGROUP = @GETENV (\"GGENVIRONMENT\",\"GROUPNAME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm

echo "PMPTIME = @DATE (\"YYYY-MM-DD HH:MI:SS.FFFFFF\",\"JTS\",@GETENV (\"JULIANTIMESTAMP\")));" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm

echo ""

echo ""

echo "----PUMP Hearbeat mapping parameterfile for $GOLDENGATE_ADMIN_USER is ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm--"

echo ""

echo ""

cat ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_PUMP.prm

echo "----------creating parameterfile for $GOLDENGATE_ADMIN_USER FOR REPLICAT---------"

echo "MAP GGADMIN.HEARTBEAT, TARGET GGADMIN.GGS_HEARTBEAT," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "KEYCOLS (DELGROUP)," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "INSERTMISSINGUPDATES," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "COLMAP (USEDEFAULTS," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "ID = 0," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "SOURCE_COMMIT = @GETENV (\"GGHEADER\", \"COMMITTIMESTAMP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EXTRACT_NAME = @TOKEN (\"CAPGROUP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "CAPTIME = @TOKEN (\"CAPTIME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "PMPGROUP = @TOKEN (\"PMPGROUP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "PMPTIME = @TOKEN (\"PMPTIME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "DELGROUP = @GETENV (\"GGENVIRONMENT\", \"GROUPNAME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "DELTIME = @DATE (\"YYYY-MM-DD HH:MI:SS.FFFFFF\",\"JTS\",@GETENV (\"JULIANTIMESTAMP\"))," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EDDLDELTASTATS = @TOKEN (\"EDDLDELTASTATS\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EDMLDELTASTATS = @TOKEN (\"EDMLDELTASTATS\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "RDDLDELTASTATS = @GETENV (\"DELTASTATS\", \"DDL\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "RDMLDELTASTATS = @GETENV (\"DELTASTATS\", \"DML\")" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo ");" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "----------Mapping into history table------------------" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "MAP GGADMIN.HEARTBEAT, TARGET GGADMIN.GGS_HEARTBEAT_HISTORY," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "KEYCOLS (ID)," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "INSERTALLRECORDS," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "COLMAP (USEDEFAULTS," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "ID = 0," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "SOURCE_COMMIT = @GETENV (\"GGHEADER\", \"COMMITTIMESTAMP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EXTRACT_NAME = @TOKEN (\"CAPGROUP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "CAPTIME = @TOKEN (\"CAPTIME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "PMPGROUP = @TOKEN (\"PMPGROUP\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "PMPTIME = @TOKEN (\"PMPTIME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "DELGROUP = @GETENV (\"GGENVIRONMENT\", \"GROUPNAME\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "DELTIME = @DATE (\"YYYY-MM-DD HH:MI:SS.FFFFFF\",\"JTS\",@GETENV (\"JULIANTIMESTAMP\"))," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EDDLDELTASTATS = @TOKEN (\"EDDLDELTASTATS\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "EDMLDELTASTATS = @TOKEN (\"EDMLDELTASTATS\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "RDDLDELTASTATS = @GETENV (\"DELTASTATS\", \"DDL\")," >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo "RDMLDELTASTATS = @GETENV (\"DELTASTATS\", \"DML\")" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo ");" >> ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

echo ""

echo ""

echo "----REPLICAT Hearbeat mapping parameterfile for $GOLDENGATE_ADMIN_USER is ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm--"

echo ""

echo ""

cat ${GGHOME}/dirprm/${GOLDENGATE_ADMIN_USER}_HB_REPLICAT.prm

#########################################################################################################

echo "------------Adding Goldengate Admin schema $GOLDENGATE_ADMIN_USER to GLOBALS file ----------------"

echo "GGSCHEMA $GOLDENGATE_ADMIN_USER" >> ${GGHOME}/GLOBALS

echo "------------Creating Checkpoint Table for $GOLDENGATE_ADMIN_USER ---------------------------------"

echo "Enter New checkpoint table for $GOLDENGATE_ADMIN_USER :\c"

read CKPTABLE

echo "Enter Goldengate admin password :\c"

read GOLDENGATE_ADMIN_USER_PWD

$GGHOME/ggsci << EOF

DBLOGIN USERID $GOLDENGATE_ADMIN_USER,PASSWORD $GOLDENGATE_ADMIN_USER_PWD

ADD CHECKPOINTTABLE $GOLDENGATE_ADMIN_USER.$CKPTABLE

exit

EOF

echo ""

echo ""

echo "------------Apend $GOLDENGATE_ADMIN_USER.$CKPTABLE to Globals file -------------------------------"

echo "CHECKPOINTTABLE $GOLDENGATE_ADMIN_USER.$CKPTABLE" >> ${GGHOME}/GLOBALS

cat ${GGHOME}/GLOBALS

###############################################################################

Goldengate ArchiveLog delete issue when Extract is registered.

When you register extract on source database using Log retention. It marks scn from that point.

2013-02-07 21:53:51 INFO OGG-01749 Oracle GoldenGate Command Interpreter for Oracle: Successfully registered EXTRACT XCOMTE to start managing log retention at SCN 10855583972257.

After it registered , when you try to delete Archivelog using RMAN you get below errors.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file

This mostly occurs on 10.2.0.4 to 11.2.0.2, it also happens due to Orphan Logminer sessions.

You may refer below links.
Why is RMAN Not Purging Archive Logs After Backup Since OGG was Configured? [ID 1351352.1]

It is also recommend to apply Patch on database.

Important: To support RMAN log retention on Oracle RAC, you must download and install the database patch that is provided in BUGFIX 11879974, before you add the Extract groups.

Goldengate Mapping and Transformation for ETL

There are situation when you want to take advantage of GG. it can map and transform data in shape which you want on Target.
we had same one situation,when Source table have 90+ rows and 5 million rows gets Replicated/day and Target table is not partitioned.

so we did partition Target table not just partitioned but we sub-partition it.

Table will be first Partitioned based on date column by New 11g Interval partition and
then subpartitioned by site id which is again subset of account number, Account number column is Varchar2 with some 20 digits Number.so site id will be parsed from first 3 digits from Account Number.
In nutshell we are decomposing account Number on Target.

In addition to this ,we added three extra column.

R_TGT_CREATED_DT = Whenever record inserted first time on target it will have target current timestamp.
R_TGT_UPDATED_DT = Whenever record inserted & updated on target it will have target timestamp.
R_ACTION_CD = type of DML will be captured in this column, (insert/update/delete)

Again addition to this, we want to preserve rows which will be deleted in separate partitioned archived table.
so we had again archive table called ${TABLE_NAME}_DEL , Sorry using variable of Unix.

Note: As source and Target DDL is not identical , You must use defgen utility to generate source DDL. First time You must load data using Initial Load,so rows will fall into right partitions and Extra mapping columns will also populated as same time.

Note: When you want use INSERTDELETES on Targetside, You must use NOCOMPRESSDELETES on Source.
DDL of Source table.

CREATE TABLE DUMMYEVENT
(
  ACC_NUM                    VARCHAR2(20 BYTE) NOT NULL,
  ORIGINAL_ACC_NUM           VARCHAR2(20 BYTE),
  RULE_NUMBER                    NUMBER(1),
  -----few columns Trim. 
  PREVIOUS_EVENT_REF             VARCHAR2(16 BYTE),
  PREVIOUS_EVENT_SEQ             NUMBER(9),
  PRODUCT_SEQ                    NUMBER(9),
  TWIN_EVENT_BOO                 VARCHAR2(1 BYTE)
  )

DDL of Target table.

CREATE TABLE DUMMYEVENT
( SITE_ID                        NUMBER(3),
  ACC_NUM                    VARCHAR2(20 BYTE) NOT NULL,
  ORIGINAL_ACC_NUM           VARCHAR2(20 BYTE),
  RULE_NUMBER                    NUMBER(1),
 -----few columns Trim. 
  PREVIOUS_EVENT_REF             VARCHAR2(16 BYTE),
  PREVIOUS_EVENT_SEQ             NUMBER(9),
  PRODUCT_SEQ                    NUMBER(9),
  TWIN_EVENT_BOO                 VARCHAR2(1 BYTE)
  R_TGT_CREATED_DT  DATE,
  R_TGT_UPDATED_DT  DATE,
  R_ACTION_CD       VARCHAR2(100 BYTE)
)
TABLESPACE DUMMY_ADMIN
PARTITION BY RANGE (ACC_NUM) 
INTERVAL(NUMTODSINTERVAL(1, 'DAY')) 
subpartition by list (SITE_ID)
(PARTITION DUMMYEVENT1 VALUES LESS THAN (TO_DATE('20-02-2013', 'DD-MM-YYYY')) 
(
SUBPARTITION CO_1 VALUES (1),
SUBPARTITION CO_126 VALUES (126),
SUBPARTITION CO_131 VALUES (131),
SUBPARTITION CO_132 VALUES (132),
SUBPARTITION CO_135 VALUES (135),
SUBPARTITION CO_182 VALUES (182),
SUBPARTITION CO_186 VALUES (186),
SUBPARTITION CO_214 VALUES (214),
SUBPARTITION CO_215 VALUES (215),
SUBPARTITION CO_216 VALUES (216),
SUBPARTITION CO_238 VALUES (238),
SUBPARTITION CO_239 VALUES (239),
SUBPARTITION CO_333 VALUES (333),
SUBPARTITION CO_334 VALUES (334),
SUBPARTITION CO_342 VALUES (342),
SUBPARTITION CO_436 VALUES (436),
SUBPARTITION CO_476 VALUES (476),
SUBPARTITION CO_477 VALUES (477),
SUBPARTITION CO_541 VALUES (541),
SUBPARTITION CO_580 VALUES (580),
SUBPARTITION CO_609 VALUES (609),
SUBPARTITION CO_UK VALUES (DEFAULT)
  )
);

---ARCHIVED DELETE TABLE TO PRESERVE DELETED ROWS.

CREATE TABLE IDENTITY.DUMMYEVENT_DEL
( SITE_ID                        NUMBER(3),
  ACC_NUM                    VARCHAR2(20 BYTE) NOT NULL,
  ORIGINAL_ACC_NUM           VARCHAR2(20 BYTE),
  RULE_NUMBER                    NUMBER(1),
  -----few columns Trim. 
  PREVIOUS_EVENT_REF             VARCHAR2(16 BYTE),
  PREVIOUS_EVENT_SEQ             NUMBER(9),
  PRODUCT_SEQ                    NUMBER(9),
  TWIN_EVENT_BOO                 VARCHAR2(1 BYTE)
  R_TGT_CREATED_DT  DATE,
  R_TGT_UPDATED_DT  DATE,
  R_ACTION_CD       VARCHAR2(100 BYTE)
)
TABLESPACE IDM_DATA
PARTITION BY RANGE (R_TGT_UPDATED_DT) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(
PARTITION costed1 VALUES LESS THAN (TO_DATE('20-02-2013', 'DD-MM-YYYY')) TABLESPACE IDM_DATA
);

---Goldengate Mapping On source

Please keep in mind that you put NOCOMPRESSDELETES in Source parameter file.

TABLE ICBS_ADMIN.DUMMYEVENT,
KEYCOLS(ACC_NUM);

---Goldengate Mapping On Target
Please keep in mind that you need to put ALLOWDUPTARGETMAP in Target Mapping file.

---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
--THIS IS FOR INSERT AND DELETE RECORD ONLY. 
ALLOWDUPTARGETMAP
GETINSERTS
GETDELETES
IGNOREUPDATES
MAP ICBS_ADMIN.DUMMYEVENT, TARGET ICBS_ADMIN.DUMMYEVENT,
KEYCOLS(ACC_NUM),
INSERTMISSINGUPDATES,
COLMAP (USEDEFAULTS,
R_TGT_CREATED_DT= @DATENOW (),
R_TGT_UPDATED_DT= @DATENOW (),
R_ACTION_CD= @GETENV ("GGHEADER","OPTYPE")
);
--THIS IS FOR UPDATE RECORD ONLY.
GETUPDATES
IGNOREINSERTS
IGNOREDELETES
MAP ICBS_ADMIN.DUMMYEVENT, TARGET ICBS_ADMIN.DUMMYEVENT,
KEYCOLS(ACC_NUM),
INSERTMISSINGUPDATES,
COLMAP (USEDEFAULTS,
R_TGT_UPDATED_DT= @DATENOW (),
R_ACTION_CD= @GETENV ("GGHEADER","OPTYPE")
);
---------------------------------------------------------------------------------
--To preserve deletes. 
---------------------------------------------------------------------------------
GETDELETES
IGNOREINSERTS
IGNOREUPDATES
INSERTDELETES
MAP ICBS_ADMIN.DUMMYEVENT, TARGET ICBS_ADMIN.DUMMYEVENT_DEL,
KEYCOLS(ACC_NUM),
INSERTMISSINGUPDATES,
COLMAP (USEDEFAULTS,
SITE_ID= @STREXT (ACC_NUM, 1, 3),
R_TGT_CREATED_DT= @DATENOW (),
R_TGT_UPDATED_DT= @DATENOW (),
R_ACTION_CD= @GETENV ("GGHEADER","OPTYPE")
);

GoldenGate performance Tuning - 1

Problem : Replicat was doing Full table scan on Updating 804000 rows.

Source. = 11.2.0.3 /TARGG

Below is update statement which was issued to update 804000 rows.
This completed in 15 seconds on SOURCE side DB.

sql_id :ghsutzgq0m8j9 

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID ghsutzgq0m8j9
--------------------
UPDATE dummy11.OS_HISTORYSTEP_BLD SET ENTRY_ID=5555, STEP_ID=100000,
ACTION_ID=20000, OWNER='JIGNESHKANKRECHA',
START_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000
PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
FINISH_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000
PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
DUE_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000 PM','fmMMfm/fmDDfm/YYYY
fmHH12fm:MI:SS.FF AM'), STATUS='QC Validation failed', CALLER='KALIYA'
where ID  BETWEEN 1220000 AND 5999999

Plan hash value: 3578452229

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                    |       |       |   376 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |    47 |  4277 |   376   (1)| 00:00:05 |
-----------------------------------------------------------------------------------------

------------------------------------------------------------------
Target. = 10.2.0.4 / SOUGG

Below statment Replicat executed ,
Because i had BATCHSQL replicat made BATCH of few rows and send for update.
that is the reason we see COUNT STOPKEY in execution plan.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID b4t7hv6p53165
--------------------
UPDATE "dummy11"."OS_HISTORYSTEP_BLD" SET "ENTRY_ID" = :a1,"STEP_ID" =
:a2,"ACTION_ID" = :a3,"OWNER" = :a4,"START_DATE" = :a5,"FINISH_DATE" =
:a6,"DUE_DATE" = :a7,"STATUS" = :a8,"CALLER" = :a9 WHERE "ID" = :b0 AND ROWNUM = 1

Plan hash value: 185372276

------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |                    |       |       |   304 (100)|          |
|   1 |  UPDATE             | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   COUNT STOPKEY     |                    |       |       |            |          |
|   3 |    TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |     1 |    91 |   304   (1)| 00:00:04 |
------------------------------------------------------------------------------------------

--Excerpt from Report file.

BATCHSQL BATCHESPERQUEUE 100, BATCHTRANSOPS 10000, OPSPERBATCH 10000, OPSPERQUEUE 100000

MAP resolved (entry dummy11.OS_HISTORYSTEP_BLD):
  MAP "dummy11"."OS_HISTORYSTEP_BLD", TARGET dummy11.OS_HISTORYSTEP_BLD, KEYCOLS(ID);

2012-12-12 12:00:30  WARNING OGG-00869  No unique key is defined for table 'OS_HISTORYSTEP_BLD'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
Using following columns in default map by name:
  ID, ENTRY_ID, STEP_ID, ACTION_ID, OWNER, START_DATE, FINISH_DATE,
  DUE_DATE, STATUS, CALLER
Using the following key columns for target table dummy11.OS_HISTORYSTEP_BLD: ID.

--------some statistic of replicat. 
2012-12-12 12:18:22  INFO    OGG-01408  Restoring current schema for DDL operation to [GGADMIN].
              104578 records processed as of 2012-12-12 12:21:05 (rate 84,delta 236)
              457309 records processed as of 2012-12-12 13:35:07 (rate 80,delta 79)
              913119 records processed as of 2012-12-12 13:39:47 (rate 153,delta 1628)
              943118 records processed as of 2012-12-12 13:41:25 (rate 155,delta 305)
              953118 records processed as of 2012-12-12 13:42:43 (rate 155,delta 127)
              963118 records processed as of 2012-12-12 13:44:25 (rate 154,delta 98)
              973118 records processed as of 2012-12-12 13:46:29 (rate 153,delta 80)
              983118 records processed as of 2012-12-12 13:48:57 (rate 151,delta 67)
              993118 records processed as of 2012-12-12 13:51:47 (rate 148,delta 58)
             1003118 records processed as of 2012-12-12 13:54:59 (rate 146,delta 51)
             1013118 records processed as of 2012-12-12 13:58:35 (rate 143,delta 46)

SOLUTION:

1) kill session of replicat which was running update statement ,,This will abend replicat.
2) Add Index on ID column which was in KEYCOLS Of MAP statement.
3) start replicat .

And we are on Road again....

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:40

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:42

GGSCI (catlmsxt205) 6> INFO ALL
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:33:06

GGSCI (catlmsxt205) 7> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      00:00:03

Execution plan of Update after Index.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b4t7hv6p53165, child number 0
-------------------------------------
UPDATE "dummy11"."OS_HISTORYSTEP_BLD" SET "ENTRY_ID" = :a1,"STEP_ID" =
:a2,"ACTION_ID" = :a3,"OWNER" = :a4,"START_DATE" = :a5,"FINISH_DATE" =
:a6,"DUE_DATE" = :a7,"STATUS" = :a8,"CALLER" = :a9 WHERE "ID" = :b0 AND ROWNUM =
1

Plan hash value: 2252287618

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                    |       |       |     5 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|*  2 |   COUNT STOPKEY    |                    |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| OS_HIST_BLDIDX     |     1 |    91 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)
   3 - access("ID"=TO_NUMBER(:B0))

GoldenGate High availability using Clusterware

This post is to setup High Availability for Goldengate.

Clusterware will manage Goldengate resources to start/stop/relocate of Goldengate Processes.

For 10g & 11gR1 Clusterware.

--Add VIP for Goldengate application

--As Oracle - Create resource profile.  Use IP address which was pre-allocated 1.2.2 section. 
/u00/app/oracle/product/10.2.0/CRS/bin/crs_profile -create ggatevip -t application -a /u00/app/oracle/product/10.2.0/CRS/bin/usrvip -p balanced -h dummyxt200,dummyxt205 -o oi=eth0,ov=10.10.10.10,on=255.255.255.0

--Next, Register the VIP as oracle:
/u00/app/oracle/product/10.2.0/CRS/bin/crs_register ggatevip

--Because the assignment of an IP address is done by the root user, you have to set the ownership of the VIP to the root user. 

--Connect as root and execute:
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm ggatevip -o root

--As root, allow oracle to run the script to start the VIP.
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm ggatevip -u user:oracle:r-x

--Then, as oracle, start the VIP:
/u00/app/oracle/product/10.2.0/CRS/bin/crs_start ggatevip

--To validate whether the VIP is running and on which node it is running, execute:
/u00/app/oracle/product/10.2.0/CRS/bin/crs_stat ggatevip -t

Try to Ping Goldengate VIP. 
ping -c4 [Goldengate VIP]

Copy & Test GoldenGate action script

Place Goldengate Action script Which is in Addendum of This Post to $CRS_HOME/crs/public on each Node.  

$CRS_HOME/crs/public/ggaction.scr
/u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr

Give full permission on it.
chmod 777 ggaction.scr

Once copied please test it using below argument. 

/u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr  [start|stop|check]
/u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr stop

Add Goldengate application resource to cluster

--As Oracle create profile for Goldengate Application 
/u00/app/oracle/product/10.2.0/CRS/bin/crs_profile -create goldengate_app -t application -r ggatevip -a /u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr -o ci=10

--As oracle
/u00/app/oracle/product/10.2.0/CRS/bin/crs_register goldengate_app

--As root
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm goldengate_app -o root

--As root
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm goldengate_app -u user:oracle:r-x

--As oracle
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_start goldengate_app

--As oracle.
/u00/app/oracle/product/10.2.0/CRS/bin/crs_stat goldengate_app -t

Name           Type           Target    State     Host
------------------------------------------------------------
ggatevip       application    ONLINE    ONLINE    dummyxt200
goldengate_app application    ONLINE    ONLINE    dummyxt200

Manage Application.

--To Relocate Goldengate on different Node. 
dummyxt200@:/u00/app/oracle/product/10.2.0/CRS/crs/public :CRS $crs_relocate -f goldengate_app
Attempting to stop `goldengate_app` on member `dummyxt200`
Stop of `goldengate_app` on member `dummyxt200` succeeded.
Attempting to stop `ggatevip` on member `dummyxt200`
Stop of `ggatevip` on member `dummyxt200` succeeded.
Attempting to start `ggatevip` on member `dummyxt208`
Start of `ggatevip` on member `dummyxt208` succeeded.
Attempting to start `goldengate_app` on member `dummyxt208`
Start of `goldengate_app` on member `dummyxt208` succeeded.

--Test relocation of resource on All Node in cluster. 

crs_relocate -f goldengate_app -n 

--Confirm Goldengate process has been started on relocated Node.

--Issue below command on Node where Goldengate application resource is relocated. 
> ps -ef | grep mgr
root     23942     1  0 15:41 ?        00:00:00 ./mgr PARAMFILE /ORAGG/product/11.2.1/gghome10/dirprm/mgr.prm REPORTFILE /ORAGG/product/11.2.1/gghome10/dirrpt/MGR.rpt PROCESSID MGR PORT 7809
> ps -ef | grep extract
root     23958 23942  2 15:41 ?        00:00:01 /ORAGG/product/11.2.1/gghome10/extract PARAMFILE /ORAGG/product/11.2.1/gghome10/dirprm/identde.prm REPORTFILE /ORAGG/product/11.2.1/gghome10/dirrpt/IDENTDE.rpt PROCESSID IDENTDE USESUBDIRS
root     23959 23942  0 15:41 ?        00:00:00 /ORAGG/product/11.2.1/gghome10/extract PARAMFILE /ORAGG/product/11.2.1/gghome10/dirprm/identdp.prm REPORTFILE /ORAGG/product/11.2.1/gghome10/dirrpt/IDENTDP.rpt PROCESSID IDENTDP USESUBDIRS

--To stop Goldengate using clusterware. 
> crs_stop goldengate_app
Attempting to stop `goldengate_app` on member `dummyxt208`
Stop of `goldengate_app` on member `dummyxt208` succeeded.
dummyxt208 | CRS | /ORAGG/product/11.2.1/gghome10
> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ggatevip       application    ONLINE    ONLINE    dummyxt208
goldengate_app application    OFFLINE   OFFLINE

--To start Goldengate using clusterware. 
dummyxt200@:/ORAGG/product/11.2.1/gghome10 :CRS $crs_start goldengate_app
Attempting to start `ggatevip` on member `dummyxt200`
Start of `ggatevip` on member `dummyxt200` succeeded.
Attempting to start `goldengate_app` on member `dummyxt200`
Start of `goldengate_app` on member `dummyxt200` succeeded.

For 11gR2 Clusterware

--Add VIP Goldengate Application

. oraenv
ORACLE_SID = [TARGG1] ? GRID
The Oracle base for ORACLE_HOME=/u00/app/11.2.0/GRID is /u00/app/oracle

--As root 
sudo /u00/app/11.2.0/GRID/bin/appvipcfg create -network=1 -ip=10.10.10.10 -vipname=mvggatevip -user=root

-----------------Output---------------
Production Copyright 2007, 2008, Oracle.All rights reserved
2012-11-04 10:24:30: Creating Resource Type
2012-11-04 10:24:30: Executing cmd: /u00/app/11.2.0/GRID/bin/crsctl add type app.appvip.type -basetype cluster_resource -file /u00/app/11.2.0/GRID/crs/template/appvip.type
2012-11-04 10:24:30: Create the Resource
2012-11-04 10:24:30: Executing cmd: /u00/app/11.2.0/GRID/bin/crsctl add resource mvggatevip -type app.appvip.type -attr USR_ORA_VIP=10.10.10.10,START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network),STOP_DEPENDENCIES=hard(ora.net1.network),ACL='owner:root:rwx,pgrp:root:r-x,other::r--,user:root:r-x'

dummyxt271 | GRID | /export/home/oracle
--As root,  Allow the Oracle Grid infrastructure software owner (e.g. oracle) to run the script to start the VIP.
sudo /u00/app/11.2.0/GRID/bin/crsctl setperm resource mvggatevip -u user:oracle:r-x

--As oracle, start the VIP:
/u00/app/11.2.0/GRID/bin/crsctl start resource mvggatevip
CRS-2672: Attempting to start 'mvggatevip' on 'dummyxt274'
CRS-2676: Start of 'mvggatevip' on 'dummyxt274' succeeded


--To validate whether the VIP is running and on which node it is running, execute:
/u00/app/11.2.0/GRID/bin/crsctl status resource mvggatevip
NAME=mvggatevip
TYPE=app.appvip.type
TARGET=ONLINE
STATE=ONLINE on dummyxt274

--At this point you can also connect to another server in the subnet and ping the VIP's IP address. You should get a reply from this IP address.

ping -c4 mvggatevip

Copy & Test Goldengate Action script.

--Place Goldengate Action script from Addendum of this Post to $GRID_HOME/crs/public  on each Node.  
/u00/app/11.2.0/GRID/crs/public/ggaction.scr [start|stop|check]
/u00/app/11.2.0/GRID/crs/public/ggaction.scr start

Add goldengate application resource.

--As Oracle
/u00/app/11.2.0/GRID/bin/crsctl add resource ggateapp -type cluster_resource -attr  "ACTION_SCRIPT=/u00/app/11.2.0/GRID/crs/public/ggaction.scr, CHECK_INTERVAL=30, START_DEPENDENCIES='hard(mvggatevip) pullup(mvggatevip)', STOP_DEPENDENCIES='hard(mvggatevip)'"

--As Oracle 
dummyxt271 | GRID | /u00/app/11.2.0/GRID/crs/public
crsctl start resource ggateapp
CRS-2672: Attempting to start 'ggateapp' on 'dummyxt274'
CRS-2676: Start of 'ggateapp' on 'dummyxt274' succeeded

--As Oracle 
crsctl status resource ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on dummyxt274

--confirm of Goldengate has started on perticular Node. 
dummyxt274 | ORA102 | /export/home/oracle
ps -ef | grep mgr
oracle   12582     1  0 10:46 ?        00:00:00 ./mgr PARAMFILE /ORAGG/product/11.2.1/gghome11/dirprm/mgr.prm REPORTFILE /ORAGG/product/11.2.1/gghome11/dirrpt/MGR.rpt PROCESSID MGR PORT 7809

--Relocate Goldengate Application. 
crsctl relocate resource ggateapp -f
CRS-2673: Attempting to stop 'ggateapp' on 'dummyxt274'
CRS-2677: Stop of 'ggateapp' on 'dummyxt274' succeeded
CRS-2673: Attempting to stop 'mvggatevip' on 'dummyxt274'
CRS-2677: Stop of 'mvggatevip' on 'dummyxt274' succeeded
CRS-2672: Attempting to start 'mvggatevip' on 'dummyxt271'
CRS-2676: Start of 'mvggatevip' on 'dummyxt271' succeeded
CRS-2672: Attempting to start 'ggateapp' on 'dummyxt271'
CRS-2676: Start of 'ggateapp' on 'dummyxt271' succeeded

--Test Relocate on each node. 
crsctl relocate resource ggateapp -n Node_name -f

Goldengate Action script.

#!/bin/sh
#############################################################################
#@(#) Clusterware script to manage Golden Gate v1.0
# Script to Manage Golden Gate from Clusterware
# change required Environment. 
#############################################################################
GGS_HOME=/ORAGG/product/11.2.1/gghome11
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${GGS_HOME}
ORACLE_HOME=/u00/app/oracle/product/11.2.0/DB
export GGS_HOME LD_LIBRARY_PATH ORACLE_HOME
# Function runCmd to run the Golden Gate Script Execution
runCmd()
{
ggsciCmd=$1
result=`${GGS_HOME}/ggsci << EOF
${ggsciCmd}
exit
EOF`
}
# Function CheckMgr to check the Golden Gate Manager process
checkMgr()
{
if ( [ -f "${GGS_HOME}/dirpcs/MGR.pcm" ] )
then
pid=`cut -f8 "${GGS_HOME}/dirpcs/MGR.pcm"`
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |cut -d " " -f2` ]
then
exit 0
else
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |cut -d " " -f1` ]
then
exit 0
else
exit 1
fi
fi
else
exit 1
fi
}
# Main Code to get the input and run it
case $1 in
'start') runCmd 'start manager'
runCmd 'start er *'
sleep 5
checkMgr
;;
'stop') runCmd 'stop er *'
runCmd 'stop er *!'
runCmd 'stop manager!'
exit 0
;;
'check') checkMgr
;;
'clean') runCmd 'stop er *'
runCmd 'stop er *!'
runCmd 'kill er *'
runCmd 'stop manager!'
exit 0
;;
'abort') runCmd 'stop er *!'
runCmd 'kill er *'
runCmd 'stop manager!'
exit 0
;;
esac
# End of Script
#############################################################################

Reference: Oracle GoldenGate Best Practices: Oracle GoldenGate high availability using Oracle Clusterware [ID 1313703.1]

OGG-01496 Failed to open target trail file

My Datapump was abending after start, because It was looking for MISSING remote trailfile at perticular RBA , that trailfile was deleted.

If you want to reposition extract please visit. Re-position Extract at particular RBA

here is from source side.

GGSCI (RACD1.localdomain.com) 6> INFO RMTTRAIL *

       Extract Trail: ./dirdat/DE
             Extract: RACDE
               Seqno: 1
                 RBA: 1049
           File Size: 10M

       Extract Trail: ./dirdat/DP
             Extract: RACDP
               Seqno: 0
                 RBA: 4499
           File Size: 10M

some output from ggserr.log

2012-10-17 19:47:58  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT RACDP starting.
2012-10-17 19:47:58  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP starting.
2012-10-17 19:47:59  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP started.
2012-10-17 19:48:04  INFO    OGG-01226  Oracle GoldenGate Capture for Oracle, racdp.prm:  Socket buffer size set to 27985 (flush size 27985).
--This is the error....
2012-10-17 19:48:04  ERROR   OGG-01496  Oracle GoldenGate Capture for Oracle, racdp.prm:  Failed to open target trail file ./dirdat/DP000000, at RBA 4499.
2012-10-17 19:48:04  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, racdp.prm:  PROCESS ABENDING.

I had trailfile missing on Target side, which DATAPUMP was looking for.

TEST:/u00/app/gghome/dirdat :TEST$ll
total 12
-rw-rw-rw-  1 oracle oinstall 1167 Aug  2 09:49 XP000000
drwxr-xr-x 14 oracle oinstall 4096 Oct 17 16:10 ..
drwxrwxr-x  2 oracle oinstall 4096 Oct 17 16:52 .

So i did this below on Source side and start datapump successfully.

GGSCI (RACD1.localdomain.com) 11> ALTER EXTRACT RACDP ETROLLOVER

2012-10-17 19:54:30  INFO    OGG-01520  Rollover performed.  For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file;  it will not happen automatically.
EXTRACT altered.


GGSCI (RACD1.localdomain.com) 12> INFO RMTTRAIL *

       Extract Trail: ./dirdat/DE
             Extract: RACDE
               Seqno: 1
                 RBA: 1049
           File Size: 10M

       Extract Trail: ./dirdat/DP
             Extract: RACDP
               Seqno: 1
                 RBA: 0
           File Size: 10M



GGSCI (RACD1.localdomain.com) 13> START RACDP

Sending START request to MANAGER ...
EXTRACT RACDP starting


GGSCI (RACD1.localdomain.com) 14> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     RACDE       00:00:00      00:00:01
EXTRACT     RUNNING     RACDP       00:00:00      00:00:02


2012-10-17 19:55:20  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT RACDP starting.
2012-10-17 19:55:20  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP starting.
2012-10-17 19:55:31  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP started.
2012-10-17 19:55:39  INFO    OGG-01226  Oracle GoldenGate Capture for Oracle, racdp.prm:  Socket buffer size set to 27985 (flush size 27985).
2012-10-17 19:55:40  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, racdp.prm:  No recovery is required for target file ./dirdat/DP000001, at RBA 0 (file not opened).
2012-10-17 19:55:40  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, racdp.prm:  Output file ./dirdat/DP is using format RELEASE 10.4/11.1.

Active Dataguard vs Goldengate.

Few days back i have provided Recommendation on what should use for DR. 

Active Data Guard or Oracle Goldengate. 

Primary Functional Comparison .

Oprations	Active Data Guard	GoldenGate V11.2
Suitable	Disaster recovery /  Read-only Reporting.	Fine-grain Replication / Object Transformation
Security	User  grant/privileges are same on Target & Source	User access can restricted as per requirement.
Data type support	All data-type supported by Primary	*  (Attached separately)
Replication	·      Physical block-to-block copy of whole DB on Target.  (Carried out by Redo logs).  ·      Target will be read only  (Can be Open temporary for read/write) ·      Support Parallel DML on RAC span on Multiple instances. ·      No object-Transformation allowed ·      Support compression.	·      Only Required Table are subject for Replication  (More controlled). (carried out by Trailfiles)  ·      Target will be in read/write all Time. ·      Does Not support Parallel DML on RAC  spawn from Multiple instances ·      Object-Transformation Allowed. ·      Not-support Compression.
Maintenance.	Automatic Outage Gap resolution. Low-Maintenance	Manual Invention needed for Troubleshoot. Moderate - Maintenance.
Clustered DB	Only One instance will Perform recovery  on Target.	All instance will perform recovery on Target side.  (Load balance)

Other things to look for . 

·         Do you need Copy of source database or  Set of tables required to be Replicate

·         SLA for lag/latency.

·         Amount of Archive log Generated / day by database  ( Per Instance for RAC )

·         Is source & target are in different Time-zone.

·         No of tables with LOB/CLOB data to be replicate.

·         Network bandwidth.

Goldengate on ASM using DBLOGREADER

This is demostration of CLASSIC CAPTURE on ASM using New ASM API ,
Which reading redo log on ASM Using GoldenGate user provided by USERID in Extract

Source Side : 3 Node RAC / Grid Infrastructure 11.2.0.2
Target Side : Stand-alone 11.2.0.1

DBLOGREADER

(Oracle) Valid for Extract in classic capture mode.Causes Extract to use a newer ASM API that is available as
of Oracle 10.2.0.5 and later 10g R2 versions, and Oracle 11.2.0.2 and later 11g R2 versions (but not in Oracle 11g R1
versions). This API uses the database server to access the redo and archive logs, instead of connecting directly to the
Oracle ASM instance. The database must contain the libraries that contain the API modules and must be
running. To use this feature, the Extract database user must have SELECT ANY TRANSACTION privilege.
When used, DBLOGREADER enables Extract to use a read size of up to 4 MB in size. This is controlled with the
DBLOGREADERBUFSIZE option The maximum read size when using the default OCI buffer is 28672 bytes.
This is controlled by the ASMBUFSIZE option. A larger buffer may improve the performance of Extract
when redo rate is high.When using DBLOGREADER, do not use the ASMUSER and ASMPASSWORD options of TRANLOGOPTIONS.
The API uses the user and password specified with the USERID parameter.

DBLOGREADERBUFSIZE

(Oracle) Valid for Extract in classic capture mode.Controls the maximum size, in bytes, of a read operation
into the internal buffer that holds the results of each read of the transaction log in ASM. Higher values increase
extraction speed but cause Extract to consume more memory. Low values reduce memory usage but increase I/O
because Extract must store data that exceeds the cache size to disk.
Use DBLOGREADERBUFSIZE together with the DBLOGREADER option if the source ASM instance is Oracle 10.2.0.5 or
later10g R2 versions, or Oracle 11.2.0.2 and later 11g R2 versions (but not Oracle 11g R1 versions). The newer ASM
API in those versions provides better performance than the older one. If the Oracle version is not one of those versions,
then ASMBUFSIZE must be used.



--SOURCE SIDE

Add supplement log at database level.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Database altered.

--ON SOURCE Add checkpoint Table if it is bi-directional replication,and if you dont have one.

GGSCI (PNETN1.localdomain.com) 19>DBLOGIN, USERID GGADMIN, PASSWORD Summer2011
GGSCI (PNETN1.localdomain.com) 19>INFO CHECKPOINTTABLE

--if you dont have one , create it. 

GGSCI (PNETN1.localdomain.com) 19>ADD CHECKPOINTTABLE GGADMIN.CKPT_TABLE

GGSCI (PNETN1.localdomain.com) 19>EDIT PARAMS ./GLOBALS
GGSCHEMA GGADMIN
CHECKPOINTTABLE GGADMIN.CKPT_TABLE

--Add Extract as Below , REGISTER extract using LOGRETENTION

Use DBLOGIN to REGISTER extract

GGSCI (PNETN1.localdomain.com)>DBLOGIN USERID GGADMIN,PASSWORD Summer2011
GGSCI (PNETN1.localdomain.com)>REGISTER EXTRACT DW_EX LOGRETENTION

--Add extract 

GGSCI (PNETN1.localdomain.com)>ADD EXTRACT DW_EX TRANLOG,BEGIN NOW,THREADS 3
GGSCI (PNETN1.localdomain.com)>ADD EXTTRAIL ./dirdat/EX, EXTRACT DW_EX

--Create parameter file for Extract 
GGSCI (PNETN1.localdomain.com) 19>EDIT PARAMS DW_EX
EXTRACT DW_EX
---ORACLE ENVIRONMET
SETENV (ORACLE_HOME = "/u01/app/oracle/11.2.0/db_1")
SETENV (ORACLE_SID = "EDWP1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252")
USERID GGADMIN, PASSWORD Summer2011

--TRANLOGOPTIONS ASMUSER sys@+ASM, ASMPASSWORD Summer69
--This is ASM API that is available as of
--Oracle 10.2.0.5 and later 10g R2 versions AND
--Oracle 11.2.0.2 and later 11g R2 versions
--BUT NOT in Oracle 11g R1 versions

TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE  2597152,ASMBUFSIZE 28000
DYNAMICRESOLUTION
DISCARDFILE ./dirrpt/edwp.dsc,PURGE, MEGABYTES 100
EXTTRAIL ./dirdat/EX

--DDL REPLICATION
DDL INCLUDE MAPPED OBJNAME TEST.*

--DML replication for SCHEMA level.
TABLE TEST.*;
--end

--Add DATAPUMP

GGSCI (PNETN1.localdomain.com) 19>ADD EXTRACT DW_EP, EXTTRAILSOURCE ./dirdat/EX,begin now
GGSCI (PNETN1.localdomain.com) 19>ADD RMTTRAIL ./dirdat/EP, EXTRACT DW_EP, MEGABYTES 100
GGSCI (PNETN1.localdomain.com) 19>edit params DW_EP
EXTRACT DW_EP
SETENV (ORACLE_HOME = "/u01/app/oracle/11.2.0/db_1")
SETENV (ORACLE_SID = "EDWP1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252")
USERID GGADMIN, PASSWORD Summer2011
PASSTHRU
RMTHOST 192.168.100.101, MGRPORT 7809
RMTTRAIL ./dirdat/EP
TABLE TEST.*;
--end

--START extract / Pump.

GGSCI (PNETN1.localdomain.com) 17> start DW_EX

Sending START request to MANAGER ...
EXTRACT DW_EX starting


GGSCI (PNETN1.localdomain.com) 18> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DW_EP       00:00:00      00:20:02
EXTRACT     RUNNING     DW_EX       00:33:56      00:00:06


GGSCI (PNETN1.localdomain.com) 19> start DW_EP

Sending START request to MANAGER ...
EXTRACT DW_EP starting


GGSCI (PNETN1.localdomain.com) 20> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DW_EP       00:00:00      00:00:08
EXTRACT     RUNNING     DW_EX       00:00:02      00:00:07

--Output from ggserr.log

 
[[A2012-07-31 12:34:15  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start DW_EX.
2012-07-31 12:34:15  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host PNETN1.localdomain.com (START EXTRACT DW_EX ).
2012-07-31 12:34:15  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT DW_EX starting.
2012-07-31 12:34:15  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  EXTRACT DW_EX starting.
2012-07-31 12:34:15  INFO    OGG-03035  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2012-07-31 12:34:15  INFO    OGG-01635  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  BOUNDED RECOVERY: reset to initial or altered checkpoint.
2012-07-31 12:34:15  INFO    OGG-01815  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Virtual Memory Facilities for: BR
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u02/gghome/BR/DW_EX.
2012-07-31 12:34:15  INFO    OGG-01815  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u02/gghome/dirtmp.
2012-07-31 12:34:17  INFO    OGG-00546  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Default thread stack size: 33554432.
2012-07-31 12:34:17  INFO    OGG-01515  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioning to begin time Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:18  INFO    OGG-01516  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioned to (Thread 1) Sequence 4, RBA 5810192, SCN 0.0, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:18  INFO    OGG-01515  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioning to begin time Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:18  INFO    OGG-01516  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioned to (Thread 2) Sequence 2, RBA 5019152, SCN 0.0, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:18  INFO    OGG-01515  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioning to begin time Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:19  INFO    OGG-01516  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Positioned to (Thread 3) Sequence 2, RBA 4712464, SCN 0.0, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:19  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Position of first record processed for Thread 2, Sequence 2, RBA 5019152, SCN 0.1098626, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:19  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Position of first record processed for Thread 3, Sequence 2, RBA 4712464, SCN 0.1098627, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:19  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  EXTRACT DW_EX started.
2012-07-31 12:34:19  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  No recovery is required for target file ./dirdat/EX000000, at RBA 0 (file not opened).
2012-07-31 12:34:19  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Output file ./dirdat/EX is using format RELEASE 11.2.
2012-07-31 12:34:19  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, dw_ex.prm:  Position of first record processed for Thread 1, Sequence 4, RBA 5810192, SCN 0.1098585, Jul 31, 2012 12:00:23 PM.
2012-07-31 12:34:32  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start DW_EP.
2012-07-31 12:34:32  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host PNETN1.localdomain.com (START EXTRACT DW_EP ).
2012-07-31 12:34:32  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT DW_EP starting.
2012-07-31 12:34:33  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  EXTRACT DW_EP starting.
2012-07-31 12:34:33  INFO    OGG-03035  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2012-07-31 12:34:33  INFO    OGG-01815  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u02/gghome/dirtmp.
2012-07-31 12:34:33  WARNING OGG-01015  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  Positioning with begin time: Jul 31, 2012 12:02:01 PM, waiting for data: at extseqno 0, extrba 0.
2012-07-31 12:34:33  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  EXTRACT DW_EP started.
2012-07-31 12:34:38  INFO    OGG-01226  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  Socket buffer size set to 27985 (flush size 27985).
2012-07-31 12:34:38  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  No recovery is required for target file ./dirdat/EP000000, at RBA 0 (file not opened).
2012-07-31 12:34:38  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, dw_ep.prm:  Output file ./dirdat/EP is using format RELEASE 11.2.

---Also you will see below output on Target ggserr.log

2012-07-31 09:32:36  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from EXTRACT on host 192.168.100.126 (START SERVER CPU -1 PRI -1  TIMEOUT 300 PARAMS ).
2012-07-31 09:32:36  INFO    OGG-00974  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started collector process (Port 7840).
2012-07-31 09:32:36  INFO    OGG-01677  Oracle GoldenGate Collector:  Waiting for connection (started dynamically).
2012-07-31 09:32:36  INFO    OGG-01228  Oracle GoldenGate Collector:  Timeout in 300 seconds.
2012-07-31 09:32:41  INFO    OGG-01229  Oracle GoldenGate Collector:  Connected to 192.168.100.126:23270.
2012-07-31 09:32:41  WARNING OGG-01223  Oracle GoldenGate Collector:  did not recognize command (n).
2012-07-31 09:32:41  INFO    OGG-01669  Oracle GoldenGate Collector:  Opening ./dirdat/EP000000 (byte -1, current EOF 0).
2012-07-31 09:32:41  INFO    OGG-01670  Oracle GoldenGate Collector:  Closing ./dirdat/EP000000.
2012-07-31 09:32:41  INFO    OGG-01669  Oracle GoldenGate Collector:  Opening ./dirdat/EP000000 (byte -1, current EOF 0).

=============================================================
=============================================================

--TARGET SIDE

--Add checkpoint Table into GLOBALS, if you dont have any.

GGSCI (TEST.localdomain.com) 4> view PARAMS ./GLOBALS
CHECKPOINTTABLE GGSUSER.CKPT
GGSCHEMA GGSUSER

--Add checkpoint Table into Database. 

GGSCI (TEST.localdomain.com) 6>DBLOGIN USERID GGSUSER,PASSWORD Summer2011
Successfully logged into database.

--confirm if any checkpoint Table is already exist. 
GGSCI (TEST.localdomain.com) 8>INFO CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (GGSUSER.CKPT)...
Checkpoint table GGSUSER.CKPT created 2012-05-31 13:32:57.

--Add Replicat on Target SIDE.

GGSCI (TEST.localdomain.com) 8>ADD REPLICAT DW_ER, EXTTRAIL ./dirdat/EP,checkpointtable GGSUSER.CKPT

--Create parameter file for Replicat

GGSCI (TEST.localdomain.com) 8>edit params DW_ER
REPLICAT DW_ER
SETENV (ORACLE_HOME = "/u00/app/oracle/product/11.2.0/db_1")
SETENV (ORACLE_SID = "TEST")
--Assume DDL of Source.
ASSUMETARGETDEFS
USERID ggsuser, PASSWORD Summer2011
DISCARDFILE ./dirrpt/EDWP.dsc, append, megabytes 100
--DLL replication. 
DDL INCLUDE ALL
--DML replication from TEST schema to TEST schema. 
MAP TEST.*, TARGET TEST.*;
--end

--start Replicat

GGSCI (TEST.localdomain.com) 6> start DW_ER

Sending START request to MANAGER ...
REPLICAT DW_ER starting

--Some Output from Replicat ggserr.log

2012-07-31 09:33:08  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start DW_ER.
2012-07-31 09:33:08  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 192.168.100.101 (START REPLICAT DW_ER ).
2012-07-31 09:33:08  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT DW_ER starting.
2012-07-31 09:33:09  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, dw_er.prm:  REPLICAT DW_ER starting.
2012-07-31 09:33:09  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, dw_er.prm:  REPLICAT DW_ER started.