Configured Extract process with Parameter for replication in Golden Gate

Golden Gate basic Replication Steps:

1. Extract process
2. Start data pump process
3. Performing initial Load
4. Start replicate process

1. Check the Level Supplemental Logging Enabled or Disabled.

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

For Enable :
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2. Enabling Table-Level Supplemental Logging
Use the GoldenGate ADD TRANDATA command to force the database to log primary-key columns for all updates on your source database.You don’t need to enable supplemental logging on the
target database for one-way replication. To add supplemental log data for key columns, issue the following commands from the GoldenGate Command Interface (GGSCI).

GGSCI (sourceserver) 1> dblogin userid gger password userpw
Successfully logged into database.

For SQL Server login
GGSCI (sourceserver) 24> dblogin sourcedb sqlserver, userid sa, password userpw
Successfully logged into database.
SOURCEDB specifies the SQL Server ODBC data source
USERID specifies the database user ID

ADD TRANDATA command to enable the supplemental logging required by GoldenGate:
GGSCI (sourceserver) 2> add trandata hr.employees
Logging of supplemental redo data enabled for table HR.EMPLOYEES.

The ADD TRANDATA command is equivalent to the following command in an Oracle database:

ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG GROUP GGS_EMPLOYEES_19387 (EMPLOYEE_ID) ALWAYS;

select owner, log_group_name, table_name from dba_log_groups where owner = 'HR';

Script to add all table into transit data of particular schema:

set echo off
set verify off
set pagesize 2000
set linesize 250
set trim on
set heading off
set feedback off
spool &&SCHEMA..add_trandata.obey
select 'add trandata &SCHEMA..'||table_name
from dba_tables where owner = '&SCHEMA' ;
spool off

The preceding script generates an Oracle spool file, which you can then process in GGSCI using an OBEY command as follows:
GGSCI (sourceserver) 1> dblogin userid gger password userpw
Successfully logged into database.

GGSCI (sourceserver) 2> obey diroby/HR.add_trandata.obey

when the ADD TRANDATA commands complete, you should verify in SQLPLUS that the supplemental logging has been successfully enabled.
SQL> select owner, log_group_name, table_name from dba_log_groups where owner = 'HR';

For SQL Server 2008, you can query the Change Data Capture tables to determine if changed datacapture has been enabled, as follows:
select * from cdc.change_tables

3. Disabling Triggers and Cascade-Delete Constraints
Need to disable triggers or cascade-delete referential integrity constraints on your target tables.
Starting with GoldenGate version 11, a new SUPPRESSTRIGGERS option is available as part of the Replicat DBOPTIONS parameter, to automatically suppress the triggers from firing on the target. You can
use it to avoid having to manually disable the triggers. This option is available for Oracle 10.2.0.5 databases and later, and for Oracle 11.2.0.2 databases and later. To disable constraints, if you have
Oracle 9.2.0.7 database and later, you can use the Replicat parameter DBOPTIONS with the DEFERREFCONST option to delay checking and enforcement of integrity constraints until the Replicat transaction commits.

SQL script such as the following Oracle database

set echo off
set verify off
set pagesize 2000
set linesize 250
set trim on
set heading off
set feedback off
spool &&SCHEMA..disable_cascade_delete_constraints.sql
select 'alter table '||owner||'.'||table_name||
' disable constraint '||constraint_name||';'
from all_constraints
where delete_rule = 'CASCADE'
and owner = '&SCHEMA';
spool off
spool &SCHEMA..disable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||
' disable ;'
from all_triggers
where owner = '&SCHEMA';
spool off
spool &SCHEMA..enable_cascade_delete_constraints.sql
select 'alter table '||owner||'.'||table_name||
' enable constraint '||constraint_name||';'
from all_constraints
where delete_rule = 'CASCADE'
and owner = '&SCHEMA';
spool off
spool &SCHEMA..enable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||
' enable;'
from all_triggers
where owner = '&SCHEMA';
spool off

4. Verify the Manager Process
GoldenGate Manager process,which manages all of the GoldenGate processes and resources

GGSCI (server) 1> edit params MGR
port 7840

--Check status of manager process
GGSCI (sourceserver) 1> info manager
Manager is running (IP port sourceserver.7840).

--GoldenGate Manager isn’t running, you can start with command:
GGSCI (sourceserver) 1> start manager

5. Configuring the Local Extract
Create a parameter file for the Extract
Note:
-You can change the default editor using the SET EDITOR command in GGSCI
-Configuring the local Extract to capture all the SQL DML changes from the sample HR schema

Parameter file for extract process:

GGSCI (sourceserver) 1> edit params LHREMD1

Extract LHREMD1

SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
USERID GGER@SourceDB, PASSWORD userpw
ExtTrail dirdat/l1
Table HR.*;

Check the Character set of Database:
select * from v$nls_parameters where parameter like ‘%NLS_CHARACTERSET%’;
PARAMETER VALUE
—————– ———-
NLS_CHARACTERSET US7ASCII

EXTTRAIL parameter to specify the two-character local Extract trail-file name. Trail files are staging files used to store the committed transactions.
trail file named l1. GoldenGate automatically appends six characters to the trail-file name for aging purposes.
For example, the first trail file is named l1000000 in the dirdat directory.
As this trail file fills up, GoldenGate will create the next new trail file, named l1000001, then l1000002, and so on.
TABLE parameter to specify from which source database tables you wish GoldenGate to extract changes. The TABLE parameter is a complex parameter; it has many options that allow you to filter rows,map columns, transform data, and so on

Note:
For SQL Server Connectivity:
SOURCEDB sqlserver, userid sa, PASSWORD userpw
SOURCEDB specifies the SQL Server ODBC data source, and USERID specifies the database user ID. If you’re using Windows authentication, you can leave off the USERID and PASSWORD options.
For SQL Server, you should specify an additional parameter, as shown in the following example, to
tell GoldenGate how to manage the secondary truncation points:
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
You can specify MANAGESECONDARYTRUNCATIONPOINT If you want GoldenGate to maintain a secondary truncation point. You would specify this parameter if SQL Server replication wasn’t running. If SQL Server replication was running concurrently with GoldenGate, then you would specify NOMANAGESECONDARYTRUNCATIONPOINT to allow SQL Server to manage the secondary truncation point.
TABLE

5. Adding the Extract

GGSCI (sourceserver) > ADD EXTRACT LHREMD1, TRANLOG, BEGIN NOW
GGSCI (sourceserver) > ADD EXTTRAIL dirdat/l1, EXTRACT LHREMD1, MEGABYTES 100

ADD EXTRACT, according to parameter file, it establishes checkpoints in the source trail file and on the database transaction log to keep track of processing. The TRANLOG parameter of the ADD EXTRACT
command tells GoldenGate to use the database transaction log as its source. In the Oracle example, the redo logs are the source. BEGIN NOW tells Extract to begin processing changes from the source database as soon as the Extract is started. Optionally, you can also instruct Extract to begin capturing changes at a
specific timestamp or using a specific trail-file number.
The ADD EXTTRAIL command adds the local Extract trail file, assigns it to Extract LHREMD1, and gives it a size of 100MB. The default size of trail files is 10MB.

Note: ADD EXTRACT in an obey file in the diroby directory. You can execute these from the GGSCI command prompt using the obey filename command

6. Starting and Stopping the Extract

GGSCI (sourceserver) > START EXTRACT LHREMD1

GGSCI (sourceserver) > STOP EXTRACT LHREMD1

If you see a status of STOPPED or ABENDED ,there may be a problem

7. Verify the extrace operation
GGSCI (sourceserver) 2> info extract LHREMD1
EXTRACT LHREMD1 Last Started 2011-01-15 13:53 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint Oracle Redo Logs
2011-01-17 22:55:08 Seqno 3261, RBA 7135232

The error file is named ggserr.log and is located in the GoldenGate software installation directory location.
In the example on Linux, the error log is located in /gger/ggs/ggserr.log. On Windows, the GoldenGate error log based on the example is located in c:\gger\ggs\ggserr.log.

GGSCI (sourceserver) 3> info ext LHREMD1, detail

EXTRACT LHREMD1 Last Started 2011-01-15 13:53 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint Oracle Redo Logs
2011-01-17 22:57:50 Seqno 3262, RBA 138240
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
dirdat/l1 3 1489 100
Extract Source Begin End
/data/SourceDB/database/redo01_01.rdo 2011-01-15 13:53 2011-01-17 22:57
/data/SourceDB/database/redo01_01.rdo 2011-01-13 22:29 2011-01-15 13:53
/data/SourceDB/database/redo01_01.rdo 2011-01-05 02:13 2011-01-13 22:29
/data/SourceDB/database/redo02_01.rdo 2010-12-16 15:28 2011-01-05 02:13
Not Available * Initialized * 2010-12-16 15:28

Current directory /gger/ggs
Report file /gger/ggs/dirrpt/LHREMD1.rpt
Parameter file /gger/ggs/dirprm/LHREMD1.prm
Checkpoint file /gger/ggs/dirchk/LHREMD1.cpe
Process file /gger/ggs/dirpcs/LHREMD1.pce
Stdout file /gger/ggs/dirout/LHREMD1.out
Error log /gger/ggs/ggserr.log

GGSCI (sourceserver) 2> stats ext LHREMD1
Sending STATS request to EXTRACT LHREMD1 …
Start of Statistics at 2011-01-18 18:50:38.
Output to /gger/ggs/dirdat/l1:
Extracting from HR.EMPLOYEES to HR.EMPLOYEES:
*** Total statistics since 2011-01-15 13:54:52 ***
Total inserts 4.00
Total updates 0.00
Total deletes 2.00
Total discards 0.00
Total operations 6.00

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.