Golden Gate Setup 7 - Replicat Process on Target

Go to dirprm directory under golden gate home on target and create a file called <replicat name>.prm.
in our case the replicat name is RPATHDV.

I am going to explain the contents of the parameter file in a subsequent post.So please have patience with me
before scratching your head looking at the below parameter file.

Please refer to Metalink Note
Example How to Use DML Exception Handling and DDL Replication Together in GoldenGate Replicat [ID 1382092.1

I believe the support analyst wrote this based on my ticket :-).

***changed the below on 11/30/2012 to put a better exception handling syntax
as suggested by oracle support. The Part in red shows the exact place where it was changed.
Also read metalink note :Replicat Exceptions Mapping Is Not Working (Doc ID 1504267.1)*******
 +++++++++++++++++++++++++++++++++++++++
replicat rpathdv
SETENV (ORACLE_HOME="/oracle/software/rdbms/11.2.0.3")
SETENV (ORACLE_SID="xxx")
SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
userid ggddlusr, password <password>
Reportcount every 30 Minutes, Rate
Report at 15:00
ReportRollover at 15:15
discardfile /oracle/goldengatedata/dirdsc/rpathdv.dsc, megabytes 50, append
DDL INCLUDE MAPPED, &
EXCLUDE OBJNAME GGDDLUSR.EXCEPTIONS
DDLOPTIONS REPORT
AssumeTargetDefs
REPERROR (DEFAULT, EXCEPTION)
map scott.*, target scott.*, mapexception (TARGET ggadm.exceptions
, INSERTALLRECORDS
-- , EXCEPTIONSONLY
, colmap ( rep_name = 'rpathdv'
, table_name = @GETENV ('GGHEADER', 'TABLENAME')
, errno = @GETENV ('LASTERR', 'DBERRNUM')
, dberrmsg = @GETENV ( 'LASTERR', 'DBERRMSG')
, optype = @GETENV ( 'LASTERR', 'OPTYPE')
, errtype = @GETENV ( 'LASTERR', 'ERRTYPE')
, logrba = @GETENV ( 'GGHEADER', 'LOGRBA')
, logposition = @GETENV ( 'GGHEADER', 'LOGPOSITION')
, TRAILRBA = @GETENV('RECORD', 'FILERBA')
, TRAILSEQUENCE = @GETENV('RECORD', 'FILESEQNO')
, committimestamp = @GETENV ( 'GGHEADER', 'COMMITTIMESTAMP')
) );

 +++++++++++++++++++++++++++++++++++++++++++++


Login into ggsci on the target

[oracle@xxxx 11.1.1.1]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2_06 13694062 OGGCORE_11.1.1.1.3_PLATFORMS_120216.1755
Linux, x64, 64bit (optimized), Oracle 11g on Feb 19 2012 23:07:54

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI  1> dblogin userid ggddlusr,password <password>
Successfully logged into database.
GGSCI  2> add checkpointtable ggddlusr.ckptathdv

Successfully created checkpoint table GGDDLUSR.CKPTATHDV.

The above step adds a dedicated checkpoint table for our replicat. This is not mandatory but desirable.

GGSCI  3> add replicat rpathdv, exttrail /oracle/software/goldengate/11.1.1.1/dirdat/at, CHECKPOINTTABLE ggddlusr.ckptathdv
REPLICAT added.

GGSCI  4> start replicat rpathdv ,aftercsn 8297974408496

Sending START request to MANAGER ...
REPLICAT RPATHDV starting

Please note that the above is the flashback scn of the export dump on the source database. Since the export was done at the above flashback scn and the extract was started before that, we are telling GG replicat to apply only transactions after that SCN. This feature is really cool. This is the reason why the extract process should be started before you take the export of the source schema.

Now as soon as GG replicat encounters an exception, like "No data found" or Primary Key Violation
you should see an entry in GGDDLUSR.exceptions table.

A sample entry is like this ....

Unfortunatley the literals are replaced by bind variables.  Will soon right a blog to see the literal values.


SQL> select dberrmsg from exceptions where rownum<2;

DBERRMSG
--------------------------------------------------------------------------------
OCI Error ORA-01403: no data found, SQL <UPDATE "SCOTT"."EMPLOYEES" SET "EMP_
ETYP_CD" = :a1,"EMP_PPL_CD" = :a2,"EMP_ORGU_CD" = :a3,"EMP_SUPPORT_EXT_NO" = :a4
,"EMP_SRCH_NM" = :a5,"EMP_USERID" = :a6,"EMP_HDC_DEPT_CODE" = :a7,"EMP_HDC_SUPER
VISOR_EMP_CD" = :a8 WHERE "EMP_CD" = :b0>

Cool, right!!!!

Golden Gate Setup 6 - Exceptions Table on Target

This post is optional to follow in the sense that I always like to trap exceptions on the target database for sql's which could not be applied. Some common errors are primary key violations and no data found errors.
Usually in 1 way replication , this should never happen but being a creature of habit i just put this everywhere.
I will show you later how cool this feature is for trapping exceptions.

At the target database, you should have a golden gate admin schema called GGDDLUSR(same as in source database too).

CREATE TABLE EXCEPTIONS
(
REP_NAME VARCHAR2(8 BYTE),
TABLE_NAME VARCHAR2(61 BYTE),
ERRNO NUMBER,
DBERRMSG VARCHAR2(4000 BYTE),
OPTYPE VARCHAR2(20 BYTE),
ERRTYPE VARCHAR2(20 BYTE),
LOGRBA NUMBER,
LOGPOSITION NUMBER,
TRAILRBA NUMBER,
TRAILSEQUENCE NUMBER,
COMMITTIMESTAMP TIMESTAMP(6)
);

ALTER TABLE EXCEPTIONS ADD CONSTRAINT PK_EXCEPTIONS
PRIMARY KEY (TRAILRBA,TRAILSEQUENCE,COMMITTIMESTAMP)
/

Golden Gate Setup 5- Source Export and Target Import

Please give special attention to this section as we use SCN based export and import.

At this stage, please ensure that both extract and datapump extract are running before you do this. I repeat please do NOT perform this step unless both of them are running (or at the minimum the extract should be running).

SOURCE DATABASE
======================

SQL> create directory ATHDP as '/oracle/goldengatedata/exp';

Directory created.

SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)
----------------------------------------
8297974408496

Go back to unix prompt,
export DATA_PUMP_DIR="ATHDP"

expdp \'/ as sysdba\' schemas=scott dumpfile=scott_exp.dmp include=table,index logfile=scott_exp.log flashback_scn=8297974408496 content=all

My requirement is that our app dev team needs only table data. Therefore the above way of using include makes sure that all other objects like procedure,package,views are omitted.Still triggers come across as they are attached to tables.Therefore I use excluse=trigger during the impdp process.

(Please note that since I precreate an empty schema on destination with all privs and grants , I exclude them in the expdp statement.please feel free to change as per your liking. Please make sure that you use FLASHBACK_SCN parameter in the expdp which is plugged in from the current_scn sql statement).
FTP the file to the remote server and import it to the destination schema.

TARGET DATABASE
++++++++++++++++++

I usually pre-create the schema on the target database with all the system roles and grants.

impdp \'/ as sysdba\' dumpfile=scott_exp.dmp   exclude=trigger,grant,ref_constraint logfile=impdp.log


To summarize, we did a flashback_scn based export on source and imported this into target.

Golden Gate Setup 4 (Source Database) Data Pump Extract Process

This is a relatively easier step and is done at the source database.
The extract (in the previous post) and the Data Pump extract should be running on the source.
The role of the DP extract is to transfer the files from the local location (where extract is writing to) and push it to the remote server. Therefore it is only logical that it should have 2 directories and a remote server name to work on.

First and foremost, decide a name for the DP extract process. In my example I have named it as DPATHDV.

Please go to dirprm directory under the folden gate home and create a file called dpathdv.prm with the following content.
+++++++++++++++++++++++++++++++++++++++++++
extract dpathdv
passthru
rmthost <remote server name>,mgrport 7809
rmttrail /oracle/software/goldengate/11.1.1.1/dirdat/at
table scott.*;
+++++++++++++++++++++++++++++++++++++++++++++++

Port 7809 is the default port where the manager process is listening on the remote server.Please ensure that the manager process is running to receive the files.

Also  /oracle/software/goldengate/11.1.1.1/dirdat is the directory in the remote server and files will be pushed with a name starting with "at" .

Now login , into ggsci
1.
add extract dpathdv,exttrailsource /oracle/goldengatedata/dirdat/at

The above directory is the local directory

2. add rmttrail /oracle/software/goldengate/11.1.1.1/dirdat/at,extract dpathdv,megabytes 250
The above directory is remote directory (which is also mentioned in the prm file).

3. Start the DP extract

 start ext dpathdv


GGSCI (jupiter) 12> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     DPATHDV     00:00:00      00:00:04   
EXTRACT     RUNNING     XTATHDV     00:00:00      00:00:01   


GGSCI (jupiter) 13>

The above shows that both extract and DP extract is running on the source database.

Golden Gate Setup 3 (Source Database) Extract Process

This summary is not available. Please click here to view the post.