The core Oracle GoldenGate product:

Captures transactional changes from a source database. For most databases this is accomplished by reading the database transaction log/redo logs.
Sends and queues these changes as a set of database-independent files called the Oracle GoldenGate trail file.
Optionally alters the source data using mapping parameters and functions.
Oracle GoldenGate performs this capture and apply in near real-time across heterogeneous databases and operating systems. The Oracle GoldenGate adapters integrate with installations of the
Oracle GoldenGate core product to read an Oracle GoldenGate trail and write transactions to a flat file that can be used by other applications.

Oracle GoldenGate for Flat File:

Oracle GoldenGate for Flat File is used to output transactional data captured by Oracle GoldenGate to rolling flat files to be consumed by a third party product.
Oracle GoldenGate for Flat File is implemented as a user exit provided as a shared library (.so or .dll) that integrates into the Oracle GoldenGate Extract process.

The user exit supports two modes of output:

● DSV – Delimiter Separated Values

● LDV – Length Delimited Values

It can output data:

● All to one file

● One file per table

● One file per operation code

The user exit can roll over based on time and/or size criteria and flushes files and maintains checkpoints whenever Oracle GoldenGate checkpoints to ensure recovery.
It writes a control file containing a list of rolled over files for synchronization with the supported data integration product and can also produce a summary file for use in auditing.
  Additional properties control formatting (delimiters, other values), directories, file extensions, metadata columns (such as table name, file position, etc.) and data options.

Installing on Source system ( Linux or Windows)

1.  Extract the download Oracle GoldenGate mediapack.zip file specific  to the UNIX or Linux system type and directory where you want Oracle GoldenGate to be installed.

2.  Run the command shell and change directories to the new Oracle GoldenGate directory. In Linux /goldapp/GGS , In windows  
D:\GGS  and Ensure that you have your Oracle environment variables set correctly, including LD_LIBRARY_PATH defined.  For example, if using bash or korn shell set the variable as follows:

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

3.  From the Oracle GoldenGate directory, run the GGSCI program.

./ggsci

4.  In GGSCI, issue the following command to create the Oracle GoldenGate working

directories.

GGSCI (ctlgold01.citagus.com) 1> create subdirs

5.  Issue the following command to exit GGSCI.

EXIT

Installing on Target system:

The Oracle GoldenGate for Flat File comes as  prebuilt for a particular platform. The core Oracle GoldenGate should be fully installed and tested prior to installation of the Oracle
GoldenGate for Flat Files user exit code. The user should have the necessary file permissions to write output data to the required output directories.

Installation is same as we done it for the source system and in addition to that we need to install Oracle GoldenGate for Flat Files user exit code as follows.

Oracle GoldenGate for Flat File is shipped:

● On Windows as a zip file

● On UNIX as a .tar.gz file

To install, unzip the flat file user exit code media file, then extract the tar file in the Oracle GoldenGate install directory. The file contains:

● Shared library flatfilewriter.so

● Sample user exit properties file (ffwriter.properties)

● Sample Extract parameter file (ffwriter.prm)

Architecture of the Goldengate installed for the Flat file output.





Configuring on source system: (Linux and Windows Servers)

The above figure shows a typical Oracle GoldenGate for Flat File configuration

1. Configure the Manager:



Configure the GoldenGate Manager process for the GoldenGate instance as below:

GGSCI (ctlgold01.citagus.com) 2> edit params mgr

PORT 7809

PURGEOLDEXTRACTS ./dirdat/sa* USECHECKPOINTS, MINKEEPHOURS 1

AUTOSTART extract *

GGSCI (ctlgold01.citagus.com) 3> start mgr

Manager started.

GGSCI (ctlgold01.citagus.com) 4> status mgr

Manager is running (IP port ctlgold01.citagus.com.7809).

In windows install Manager as a Windows service , run the command in cmd as below:

D:\GGS>install ADDSERVICE

2. Prepare source Database:

a. Enabling supplemental logging.

In order to extract the committed transactions from the source Oracle database’s online redo logs, as a minimum the database must be configured for supplemental logging on Primary Key columns. This can be enabled at database level using the following DDL executed as SYSDBA:

oracle@ctgdevdb ~]$ sqlplus ‘/as sysdba’

SQL> alter database add supplemental log data (primary key) columns;

Initiate a log file switch to start supplemental logging:

SQL> alter system switch logfile;

Check the status using below command:

SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;

Note : Every source table must have a Primary Key enabled else GoldenGate will define all viable columns to represent uniqueness. This will increase the volume of supplemental log data being written to the redo logs and subsequent trail files.

b. Create Database user for the GoldenGate with dba role, since GoldenGate Processes need to have read access to data dictionary tables and other admin access to database.

[oracle@ctgdevdb ~]$ sqlplus ‘/as sysdba’

SQL> create user ggs_admin identified by ggs_admin;

SQL> grant dba to ggs_admin;

You will notice that the DBA Role has been granted to the GGS_ADMIN database user. This is deliberate because of the high level of database access required. However, the GoldenGate installation and setup guide lists the minimum individual roles and privileges required against each process, which also command a high privilege.

c. We now need to configure GoldenGate to add supplemental log data to the source tables using GGSCI’s ADD TRANDATA command for all the tables which needs to be replicated. This is shown in the following for the SCOTT.DEPT and SCOTT.EMP tables, it has to be done for all the table required.

GGSCI (ctgdevdb) 1> DBLOGIN USERID ggs_admin, PASSWORD ggs_admin

GGSCI (ctgdevdb) 2> ADD TRANDATA scott.DEPT

Logging of supplemental redo data enabled for table SCOTT.DEPT.

GGSCI (ctgdevdb) 3> ADD TRANDATA scott.EMP

Logging of supplemental redo data enabled for table SCOTT.EMP.

You can use SQL to generate the ADD TRANDATA statements dynamically as below:

SQL> set pages 0

SQL> select ‘ADD TRANDATA SRC.’||tname from tab;

3. The DEFGEN utility

The DEFGEN utility creates a data definitions file for the tables in your source or target  database schema.

GGSCI (ctgdevdb) 2> EDIT PARAMS DEFGEN

defsfile /opt/GGS/dirdef/scottsis1.def

USERID ggs_admin, PASSWORD ggs_admin

table scott.*;

table sis.*;

Exit GGSCI  and run below command in OS prompt:

[oracle@ ctgdevdb]$ ./defgen paramfile dirprm/defgen.prm

Then Copy/FTP  the file  /opt/GGS/dirdef/scottsis1.def to target GoldenGate system.

4. Extract process for Change Data Capture

Create extract process parameters to capture the Data changes in the source database:

GGSCI (ctgdevdb) 1> edit params eoltp01

EXTRACT EOLTP01

SETENV (ORACLE_SID=SISTSCM)

USERID ggs_admin, PASSWORD ggs_admin

EXTTRAIL ./dirdat/sa

TABLE SCOTT.*;

TABLE SIS.*;

The EXTRACT parameter defines the group name of the Extract process.

The EXTTRAIL parameter defines the directory and file prefix for the trail files.

Add the Extract process EOLTP01 using GGSCI as below:

GGSCI (ctgdevdb) 2> add extract EOLTP01, tranlog, begin now

GGSCI (ctgdevdb) 3> add exttrail ./dirdat/sa, extract EOLTP01,megabytes 10

Specifying TRANLOG tells the Extract process to read the database’s online redologs. We have also requested that the CDC should begin now for the single instance source database.

The above GGSCI command will create a local trail, each trail file having a maximum size of 10 Megabytes and file prefix “sa”.

Configure a Data Pump Extract process named EPUMP01, to send the data to the  remote target system ctlgold01.

The PASSTHRU parameter specifies that no transformations are to be made to the extracted data. Therefore, no table definition lookup in the database is necessary and the data is allowed to pass through from source to target.

Data Pump is an Extract process and therefore references source objects. Be sure to include the source schema tables in its parameter file, else Data Pump will not send the extracted data to the Replicate process.

GGSCI (ctgdevdb) 4> edit params epump01

EXTRACT EPUMP01

PASSTHRU

RMTHOST ctlgold01, MGRPORT 7809

RMTTRAIL ./dirdat/ta

TABLE SCOTT.*;

TABLE SIS.*;

Add the Data Pump process EPMP01 and remote trail from the source system  using GGSCI, associating it with the newly created local source trail.

GGSCI (ctgdevdb) 5> add extract EPUMP01, exttrailsource ./dirdat/sa

GGSCI (ctgdevdb) 6> add rmttrail ./dirdat/ta, EXTRACT EPUMP01,  megabytes 10

Finally, we can now start our Extract processes as follows:

GGSCI (ctgdevdb) 7> start EXTRACT *

Sending START request to MANAGER …

EXTRACT EOLTP01 starting

Sending START request to MANAGER …

EXTRACT EPMP01 starting

Check the processes are running using the GGSCI command INFO ALL.

GGSCI (ctgdevdb) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EOLTP01     00:00:00      00:00:04

EXTRACT     RUNNING     EPUMP01     00:00:00      00:00:08

Should a processes abend, a report file will automatically be generated. The following example would display the error report for the EOLTP Extract process:

GGSCI (ctgdevdb) 9> view REPORT EOLTP01

With our data extract and propagation working, it’s time to configure the delivery. The next section walks through the necessary steps to create, configure, and start the Replicat process.

To check the Transaction status you can use the below command

GGSCI (ctgdevdb) 9> stats extract EOLTP01

The sample process names and trail names used above can be replaced with any valid name. Process names need to be 8 characters or less, trail names need to be two characters.



Target GoldenGate for Flat File Server configuration:

1. Configuring Change Delivery to flat files.

Create the user exit Extract parameters to extract the data from source trail files and write to the Flat files in DSV format.

GGSCI (ctlgold01.citagus.com) 2> edit params SISCOTT1

EXTRACT siscott1

SOURCEDEFS ./dirdef/scottsis1.def

CUSEREXIT ./flatfilewriter.so CUSEREXIT PASSTHRU, INCLUDEUPDATEBEFORES, PARAMS “/goldapp/GGS/ffwriter.properties”

REPORTCOUNT EVERY 5 seconds, RATE

TABLE SCOTT.*;

TABLE SIS.*;

The EXTRACT parameter defines the name of the Extract process

GGSCI (ctlgold01.citagus.com) 3> add extract siscott1, exttrailsource  ./dirdat/ta

We can now start our Extract processes in the GoldenGate server as follows:

GGSCI (ctlgold01.citagus.com) 3>start extract siscott1

Check the extract process got started as follows:

GGSCI (ctlgold01.citagus.com) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     SISCOTT1    00:00:00      00:00:01

EXTRACT     RUNNING     SISCOTT2    00:00:00      00:00:08

EXTRACT     RUNNING     SQLFF1      00:00:00      30:48:06

Now the system is ready to generate the Flat file, as soon as  the transactions done on the source databases, Falt files will be generated at /golddata/DSV_OUT in GoldenGate server – ctlgold01

ETL tool can use these DSV files to load the data to Data Warehousing Database.
问题：
1.ogg 版本不一致
  源端添加 format release 12.1
2.sh.def文件 version 4 调整为 version5,注意调整权限

3../GLOBALS 添加限制trail后缀数字长度参数 reference 12.2 research 6D

4.alter extract eoltp01 etrollover
  alter extract eoltp01 seqno netseqno
  alter extract epump01 etrollover
  alter extract epump01 seqno next seqno


源端版本：
[oracle@node1 goldengate]$ ggsci -v

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38

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

  

目标端版本：
[ogg@node2 ogg]$ ggsci -v

Oracle GoldenGate Command Interpreter
Version 12.1.2.1.4 20470586 OGGCORE_12.1.2.1.0OGGBP_PLATFORMS_150303.1209
Linux, x64, 64bit (optimized), Generic on Mar  3 2015 13:49:31

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









源端抽取参数：

GGSCI (node1) 2> view params eoltp01

EXTRACT EOLTP01
SETENV (ORACLE_SID=orcc1)
USERIDALIAS ogg
EXTTRAIL ./dirdat/sa,format release 12.1
TABLE SH.test;



GGSCI (node1) 3> viea params epump01
ERROR: Invalid command.

GGSCI (node1) 4> view params epump01

EXTRACT EPUMP01
PASSTHRU
RMTHOST node2, MGRPORT 7888
RMTTRAIL ./dirdat/ta,format release 12.1
TABLE SH.test;

目标端抽取参数：

GGSCI (node2) 1> info all   

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                          
JAGENT      STOPPED                                          
EXTRACT     STOPPED     FLATSH      00:00:00      12:42:38   


GGSCI (node2) 2> view params flatsh

EXTRACT flatsh
SOURCEDEFS ./dirdef/sh.def
CUSEREXIT ./flatfilewriter.so CUSEREXIT PASSTHRU, INCLUDEUPDATEBEFORES, PARAMS "./AdapterExamples/file-writer/ffwriter.properties"
REPORTCOUNT EVERY 5 seconds, RATE
TABLE SH.test;




ffwriter 配置文件：

[ogg@node2 file-writer]$ cat ffwriter.properties.dsv
cat: ffwriter.properties.dsv: No such file or directory
[ogg@node2 file-writer]$ cat ffwriter.properties.dsvbak
#------------------------
#LOGGING OPTIONS
#------------------------
goldengate.log.logname=ffwriter
goldengate.log.level=INFO
goldengate.log.modules=LOGMALLOC
goldengate.log.level.LOGMALLOC=ERROR
goldengate.log.tostdout=false
goldengate.log.tofile=true

#------------------------
#FLAT FILE WRITER OPTIONS
#------------------------
goldengate.flatfilewriter.writers=dsvwriter
goldengate.userexit.chkptprefix=ffwriter_

#------------------------
# dsvwriter options
#------------------------
dsvwriter.mode=DSV
dsvwriter.rawchars=false
dsvwriter.includebefores=false
dsvwriter.includecolnames=false
dsvwriter.omitvalues=false
dsvwriter.diffsonly=false
dsvwriter.omitplaceholders=false
#dsvwriter.files.onepertable=false
dsvwriter.files.prefix=csv
dsvwriter.files.data.rootdir=./dirout
dsvwriter.files.data.ext=_data.dsv
dsvwriter.files.data.tmpext=_data.dsv.temp
dsvwriter.files.data.rollover.time=10
#dsvwriter.files.data.rollover.size=
dsvwriter.files.data.norecords.timeout=10
dsvwriter.files.control.use=true
dsvwriter.files.control.ext=_data.control
dsvwriter.files.control.rootdir=./dirout
dsvwriter.dsv.nullindicator.chars=<NULL>
dsvwriter.dsv.fielddelim.chars=,
dsvwriter.dsv.linedelim.chars=\n
dsvwriter.dsv.quotes.chars="
dsvwriter.dsv.quotes.escaped.chars=""
dsvwriter.metacols=position,txind,opcode,timestamp,schema,table
dsvwriter.metacols.txind.fixedlen=1
dsvwriter.metacols.txind.begin.chars=B
dsvwriter.metacols.txind.middle.chars=M
dsvwriter.metacols.txind.end.chars=E
dsvwriter.files.formatstring=pump_%s_%t_%d_%05n
[ogg@node2 file-writer]$ cat ffwriter.properties.
ffwriter.properties.bak     ffwriter.properties.dsvbak  ffwriter.properties.ldbbak  
[ogg@node2 file-writer]$ cat ffwriter.properties.ldbbak
#------------------------
#LOGGING OPTIONS
#------------------------
goldengate.log.logname=ffwriter
goldengate.log.level=INFO
goldengate.log.modules=LOGMALLOC
goldengate.log.level.LOGMALLOC=ERROR
goldengate.log.tostdout=false
goldengate.log.tofile=true

#------------------------
#FLAT FILE WRITER OPTIONS
#------------------------
goldengate.flatfilewriter.writers=ldvwriter
goldengate.userexit.chkptprefix=ffwriter_
ldvwriter.mode=LDV
ldvwriter.rawchars=true
ldvwriter.includebefores=false
ldvwriter.includecolnames=false
ldvwriter.files.onepertable=false
ldvwriter.files.data.rootdir=./dirout
ldvwriter.files.data.ext=.data
ldvwriter.files.data.tmpext=.temp
ldvwriter.files.data.rollover.time=10
ldvwriter.files.data.norecords.timeout=10
ldvwriter.files.control.use=true
ldvwriter.files.control.ext=.ctrl
ldvwriter.files.control.rootdir=./dirout
ldvwriter.metacols=position,timestamp,@TOKEN-RBA,@TOKEN-POS,opcode,txind,schema,table
ldvwriter.metacols.TOKEN-RBA.fixedlen=10
ldvwriter.metacols.TOKEN-POS.fixedlen=10
ldvwriter.metacols.timestamp.fixedlen=26
ldvwriter.metacols.schema.fixedjustify=right
ldvwriter.metacols.schema.fixedpadchar.chars=Y
ldvwriter.metacols.opcode.fixedlen=1
ldvwriter.metacols.opcode.insert.chars=I
ldvwriter.metacols.opcode.update.chars=U
ldvwriter.metacols.opcode.delete.chars=D
ldvwriter.metacols.txind.fixedlen=1
ldvwriter.metacols.txind.begin.chars=B
ldvwriter.metacols.txind.middle.chars=M
ldvwriter.metacols.txind.end.chars=E
ldvwriter.metacols.txind.whole.chars=W
ldvwriter.ldv.vals.missing.chars=M
ldvwriter.ldv.vals.present.chars=P
ldvwriter.ldv.vals.null.chars=N
ldvwriter.ldv.lengths.record.mode=binary
ldvwriter.ldv.lengths.record.length=4
ldvwriter.ldv.lengths.field.mode=binary
ldvwriter.ldv.lengths.field.length=2
ldvwriter.files.rolloveronshutdown=false
ldvwriter.statistics.toreportfile=false
ldvwriter.statistics.period=onrollover
ldvwriter.statistics.tosummaryfile=true
ldvwriter.statistics.overall=true
ldvwriter.statistics.summary.fileformat=schema,table,schemaandtable,total,gctimestamp,ctimestamp
ldvwriter.statistics.summary.delimiter.chars=|
ldvwriter.statistics.summary.eol.chars=\n
ldvwriter.metacols.position.format=dec
ldvwriter.writebuffer.size=36863
[ogg@node2 file-writer]$ cat ffwriter.properties
#------------------------
#LOGGING OPTIONS
#------------------------
goldengate.log.logname=ffwriter
goldengate.log.level=INFO
goldengate.log.modules=LOGMALLOC
goldengate.log.level.LOGMALLOC=ERROR
goldengate.log.tostdout=false
goldengate.log.tofile=true

#------------------------
#FLAT FILE WRITER OPTIONS
#------------------------
goldengate.flatfilewriter.writers=dsvwriter
dsvwriter.mode=DSV
dsvwriter.rawchars=false
dsvwriter.includebefores=false
dsvwriter.includecolnames=false
dsvwriter.omitvalues=false
dsvwriter.diffsonly=false
dsvwriter.omitplaceholders=false
dsvwriter.files.onepertable=true
dsvwriter.files.data.ext=_data.dsv
dsvwriter.files.data.tmpext=_data.dsv.temp
dsvwriter.dsv.nullindicator.chars=
dsvwriter.dsv.fielddelim.chars=;
dsvwriter.dsv.fielddelim.escaped.chars=
dsvwriter.files.data.rootdir=./dirout
dsvwriter.files.data.rollover.size=10000

The core Oracle GoldenGate product:

Captures transactional changes from a source database. For most databases this is accomplished by reading the database transaction log/redo logs.
Sends and queues these changes as a set of database-independent files called the Oracle GoldenGate trail file.
Optionally alters the source data using mapping parameters and functions.
Oracle GoldenGate performs this capture and apply in near real-time across heterogeneous databases and operating systems. The Oracle GoldenGate adapters integrate with installations of the
Oracle GoldenGate core product to read an Oracle GoldenGate trail and write transactions to a flat file that can be used by other applications.

Oracle GoldenGate for Flat File:

Oracle GoldenGate for Flat File is used to output transactional data captured by Oracle GoldenGate to rolling flat files to be consumed by a third party product.
Oracle GoldenGate for Flat File is implemented as a user exit provided as a shared library (.so or .dll) that integrates into the Oracle GoldenGate Extract process.

The user exit supports two modes of output:

● DSV – Delimiter Separated Values

● LDV – Length Delimited Values

It can output data:

● All to one file

● One file per table

● One file per operation code

The user exit can roll over based on time and/or size criteria and flushes files and maintains checkpoints whenever Oracle GoldenGate checkpoints to ensure recovery.
It writes a control file containing a list of rolled over files for synchronization with the supported data integration product and can also produce a summary file for use in auditing.
  Additional properties control formatting (delimiters, other values), directories, file extensions, metadata columns (such as table name, file position, etc.) and data options.

Installing on Source system ( Linux or Windows)

1.  Extract the download Oracle GoldenGate mediapack.zip file specific  to the UNIX or Linux system type and directory where you want Oracle GoldenGate to be installed.

2.  Run the command shell and change directories to the new Oracle GoldenGate directory. In Linux /goldapp/GGS , In windows  
D:\GGS  and Ensure that you have your Oracle environment variables set correctly, including LD_LIBRARY_PATH defined.  For example, if using bash or korn shell set the variable as follows:

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

3.  From the Oracle GoldenGate directory, run the GGSCI program.

./ggsci

4.  In GGSCI, issue the following command to create the Oracle GoldenGate working

directories.

GGSCI (ctlgold01.citagus.com) 1> create subdirs

5.  Issue the following command to exit GGSCI.

EXIT

Installing on Target system:

The Oracle GoldenGate for Flat File comes as  prebuilt for a particular platform. The core Oracle GoldenGate should be fully installed and tested prior to installation of the Oracle
GoldenGate for Flat Files user exit code. The user should have the necessary file permissions to write output data to the required output directories.

Installation is same as we done it for the source system and in addition to that we need to install Oracle GoldenGate for Flat Files user exit code as follows.

Oracle GoldenGate for Flat File is shipped:

● On Windows as a zip file

● On UNIX as a .tar.gz file

To install, unzip the flat file user exit code media file, then extract the tar file in the Oracle GoldenGate install directory. The file contains:

● Shared library flatfilewriter.so

● Sample user exit properties file (ffwriter.properties)

● Sample Extract parameter file (ffwriter.prm)

Architecture of the Goldengate installed for the Flat file output.





Configuring on source system: (Linux and Windows Servers)

The above figure shows a typical Oracle GoldenGate for Flat File configuration

1. Configure the Manager:



Configure the GoldenGate Manager process for the GoldenGate instance as below:

GGSCI (ctlgold01.citagus.com) 2> edit params mgr

PORT 7809

PURGEOLDEXTRACTS ./dirdat/sa* USECHECKPOINTS, MINKEEPHOURS 1

AUTOSTART extract *

GGSCI (ctlgold01.citagus.com) 3> start mgr

Manager started.

GGSCI (ctlgold01.citagus.com) 4> status mgr

Manager is running (IP port ctlgold01.citagus.com.7809).

In windows install Manager as a Windows service , run the command in cmd as below:

D:\GGS>install ADDSERVICE

2. Prepare source Database:

a. Enabling supplemental logging.

In order to extract the committed transactions from the source Oracle database’s online redo logs, as a minimum the database must be configured for supplemental logging on Primary Key columns. This can be enabled at database level using the following DDL executed as SYSDBA:

oracle@ctgdevdb ~]$ sqlplus ‘/as sysdba’

SQL> alter database add supplemental log data (primary key) columns;

Initiate a log file switch to start supplemental logging:

SQL> alter system switch logfile;

Check the status using below command:

SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from v$database;

Note : Every source table must have a Primary Key enabled else GoldenGate will define all viable columns to represent uniqueness. This will increase the volume of supplemental log data being written to the redo logs and subsequent trail files.

b. Create Database user for the GoldenGate with dba role, since GoldenGate Processes need to have read access to data dictionary tables and other admin access to database.

[oracle@ctgdevdb ~]$ sqlplus ‘/as sysdba’

SQL> create user ggs_admin identified by ggs_admin;

SQL> grant dba to ggs_admin;

You will notice that the DBA Role has been granted to the GGS_ADMIN database user. This is deliberate because of the high level of database access required. However, the GoldenGate installation and setup guide lists the minimum individual roles and privileges required against each process, which also command a high privilege.

c. We now need to configure GoldenGate to add supplemental log data to the source tables using GGSCI’s ADD TRANDATA command for all the tables which needs to be replicated. This is shown in the following for the SCOTT.DEPT and SCOTT.EMP tables, it has to be done for all the table required.

GGSCI (ctgdevdb) 1> DBLOGIN USERID ggs_admin, PASSWORD ggs_admin

GGSCI (ctgdevdb) 2> ADD TRANDATA scott.DEPT

Logging of supplemental redo data enabled for table SCOTT.DEPT.

GGSCI (ctgdevdb) 3> ADD TRANDATA scott.EMP

Logging of supplemental redo data enabled for table SCOTT.EMP.

You can use SQL to generate the ADD TRANDATA statements dynamically as below:

SQL> set pages 0

SQL> select ‘ADD TRANDATA SRC.’||tname from tab;

3. The DEFGEN utility

The DEFGEN utility creates a data definitions file for the tables in your source or target  database schema.

GGSCI (ctgdevdb) 2> EDIT PARAMS DEFGEN

defsfile /opt/GGS/dirdef/scottsis1.def

USERID ggs_admin, PASSWORD ggs_admin

table scott.*;

table sis.*;

Exit GGSCI  and run below command in OS prompt:

[oracle@ ctgdevdb]$ ./defgen paramfile dirprm/defgen.prm

Then Copy/FTP  the file  /opt/GGS/dirdef/scottsis1.def to target GoldenGate system.

4. Extract process for Change Data Capture

Create extract process parameters to capture the Data changes in the source database:

GGSCI (ctgdevdb) 1> edit params eoltp01

EXTRACT EOLTP01

SETENV (ORACLE_SID=SISTSCM)

USERID ggs_admin, PASSWORD ggs_admin

EXTTRAIL ./dirdat/sa

TABLE SCOTT.*;

TABLE SIS.*;

The EXTRACT parameter defines the group name of the Extract process.

The EXTTRAIL parameter defines the directory and file prefix for the trail files.

Add the Extract process EOLTP01 using GGSCI as below:

GGSCI (ctgdevdb) 2> add extract EOLTP01, tranlog, begin now

GGSCI (ctgdevdb) 3> add exttrail ./dirdat/sa, extract EOLTP01,megabytes 10

Specifying TRANLOG tells the Extract process to read the database’s online redologs. We have also requested that the CDC should begin now for the single instance source database.

The above GGSCI command will create a local trail, each trail file having a maximum size of 10 Megabytes and file prefix “sa”.

Configure a Data Pump Extract process named EPUMP01, to send the data to the  remote target system ctlgold01.

The PASSTHRU parameter specifies that no transformations are to be made to the extracted data. Therefore, no table definition lookup in the database is necessary and the data is allowed to pass through from source to target.

Data Pump is an Extract process and therefore references source objects. Be sure to include the source schema tables in its parameter file, else Data Pump will not send the extracted data to the Replicate process.

GGSCI (ctgdevdb) 4> edit params epump01

EXTRACT EPUMP01

PASSTHRU

RMTHOST ctlgold01, MGRPORT 7809

RMTTRAIL ./dirdat/ta

TABLE SCOTT.*;

TABLE SIS.*;

Add the Data Pump process EPMP01 and remote trail from the source system  using GGSCI, associating it with the newly created local source trail.

GGSCI (ctgdevdb) 5> add extract EPUMP01, exttrailsource ./dirdat/sa

GGSCI (ctgdevdb) 6> add rmttrail ./dirdat/ta, EXTRACT EPUMP01,  megabytes 10

Finally, we can now start our Extract processes as follows:

GGSCI (ctgdevdb) 7> start EXTRACT *

Sending START request to MANAGER …

EXTRACT EOLTP01 starting

Sending START request to MANAGER …

EXTRACT EPMP01 starting

Check the processes are running using the GGSCI command INFO ALL.

GGSCI (ctgdevdb) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EOLTP01     00:00:00      00:00:04

EXTRACT     RUNNING     EPUMP01     00:00:00      00:00:08

Should a processes abend, a report file will automatically be generated. The following example would display the error report for the EOLTP Extract process:

GGSCI (ctgdevdb) 9> view REPORT EOLTP01

With our data extract and propagation working, it’s time to configure the delivery. The next section walks through the necessary steps to create, configure, and start the Replicat process.

To check the Transaction status you can use the below command

GGSCI (ctgdevdb) 9> stats extract EOLTP01

The sample process names and trail names used above can be replaced with any valid name. Process names need to be 8 characters or less, trail names need to be two characters.



Target GoldenGate for Flat File Server configuration:

1. Configuring Change Delivery to flat files.

Create the user exit Extract parameters to extract the data from source trail files and write to the Flat files in DSV format.

GGSCI (ctlgold01.citagus.com) 2> edit params SISCOTT1

EXTRACT siscott1

SOURCEDEFS ./dirdef/scottsis1.def

CUSEREXIT ./flatfilewriter.so CUSEREXIT PASSTHRU, INCLUDEUPDATEBEFORES, PARAMS “/goldapp/GGS/ffwriter.properties”

REPORTCOUNT EVERY 5 seconds, RATE

TABLE SCOTT.*;

TABLE SIS.*;

The EXTRACT parameter defines the name of the Extract process

GGSCI (ctlgold01.citagus.com) 3> add extract siscott1, exttrailsource  ./dirdat/ta

We can now start our Extract processes in the GoldenGate server as follows:

GGSCI (ctlgold01.citagus.com) 3>start extract siscott1

Check the extract process got started as follows:

GGSCI (ctlgold01.citagus.com) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     SISCOTT1    00:00:00      00:00:01

EXTRACT     RUNNING     SISCOTT2    00:00:00      00:00:08

EXTRACT     RUNNING     SQLFF1      00:00:00      30:48:06

Now the system is ready to generate the Flat file, as soon as  the transactions done on the source databases, Falt files will be generated at /golddata/DSV_OUT in GoldenGate server – ctlgold01

ETL tool can use these DSV files to load the data to Data Warehousing Database.
问题：
1.ogg 版本不一致
  源端添加 format release 12.1
2.sh.def文件 version 4 调整为 version5,注意调整权限

3../GLOBALS 添加限制trail后缀数字长度参数 reference 12.2 research 6D

4.alter extract eoltp01 etrollover
  alter extract eoltp01 seqno netseqno
  alter extract epump01 etrollover
  alter extract epump01 seqno next seqno


源端版本：
[oracle@node1 goldengate]$ ggsci -v

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38

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

  

目标端版本：
[ogg@node2 ogg]$ ggsci -v

Oracle GoldenGate Command Interpreter
Version 12.1.2.1.4 20470586 OGGCORE_12.1.2.1.0OGGBP_PLATFORMS_150303.1209
Linux, x64, 64bit (optimized), Generic on Mar  3 2015 13:49:31

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









源端抽取参数：

GGSCI (node1) 2> view params eoltp01

EXTRACT EOLTP01
SETENV (ORACLE_SID=orcc1)
USERIDALIAS ogg
EXTTRAIL ./dirdat/sa,format release 12.1
TABLE SH.test;



GGSCI (node1) 3> viea params epump01
ERROR: Invalid command.

GGSCI (node1) 4> view params epump01

EXTRACT EPUMP01
PASSTHRU
RMTHOST node2, MGRPORT 7888
RMTTRAIL ./dirdat/ta,format release 12.1
TABLE SH.test;

目标端抽取参数：

GGSCI (node2) 1> info all   

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                          
JAGENT      STOPPED                                          
EXTRACT     STOPPED     FLATSH      00:00:00      12:42:38   


GGSCI (node2) 2> view params flatsh

EXTRACT flatsh
SOURCEDEFS ./dirdef/sh.def
CUSEREXIT ./flatfilewriter.so CUSEREXIT PASSTHRU, INCLUDEUPDATEBEFORES, PARAMS "./AdapterExamples/file-writer/ffwriter.properties"
REPORTCOUNT EVERY 5 seconds, RATE
TABLE SH.test;




ffwriter 配置文件：

[ogg@node2 file-writer]$ cat ffwriter.properties.dsv
cat: ffwriter.properties.dsv: No such file or directory
[ogg@node2 file-writer]$ cat ffwriter.properties.dsvbak
#------------------------
#LOGGING OPTIONS
#------------------------
goldengate.log.logname=ffwriter
goldengate.log.level=INFO
goldengate.log.modules=LOGMALLOC
goldengate.log.level.LOGMALLOC=ERROR
goldengate.log.tostdout=false
goldengate.log.tofile=true

#------------------------
#FLAT FILE WRITER OPTIONS
#------------------------
goldengate.flatfilewriter.writers=dsvwriter
goldengate.userexit.chkptprefix=ffwriter_

#------------------------
# dsvwriter options
#------------------------
dsvwriter.mode=DSV
dsvwriter.rawchars=false
dsvwriter.includebefores=false
dsvwriter.includecolnames=false
dsvwriter.omitvalues=false
dsvwriter.diffsonly=false
dsvwriter.omitplaceholders=false
#dsvwriter.files.onepertable=false
dsvwriter.files.prefix=csv
dsvwriter.files.data.rootdir=./dirout
dsvwriter.files.data.ext=_data.dsv
dsvwriter.files.data.tmpext=_data.dsv.temp
dsvwriter.files.data.rollover.time=10
#dsvwriter.files.data.rollover.size=
dsvwriter.files.data.norecords.timeout=10
dsvwriter.files.control.use=true
dsvwriter.files.control.ext=_data.control
dsvwriter.files.control.rootdir=./dirout
dsvwriter.dsv.nullindicator.chars=<NULL>
dsvwriter.dsv.fielddelim.chars=,
dsvwriter.dsv.linedelim.chars=\n
dsvwriter.dsv.quotes.chars="
dsvwriter.dsv.quotes.escaped.chars=""
dsvwriter.metacols=position,txind,opcode,timestamp,schema,table
dsvwriter.metacols.txind.fixedlen=1
dsvwriter.metacols.txind.begin.chars=B
dsvwriter.metacols.txind.middle.chars=M
dsvwriter.metacols.txind.end.chars=E
dsvwriter.files.formatstring=pump_%s_%t_%d_%05n
[ogg@node2 file-writer]$ cat ffwriter.properties.
ffwriter.properties.bak     ffwriter.properties.dsvbak  ffwriter.properties.ldbbak  
[ogg@node2 file-writer]$ cat ffwriter.properties.ldbbak
#------------------------
#LOGGING OPTIONS
#------------------------
goldengate.log.logname=ffwriter
goldengate.log.level=INFO
goldengate.log.modules=LOGMALLOC
goldengate.log.level.LOGMALLOC=ERROR
goldengate.log.tostdout=false
goldengate.log.tofile=true

#------------------------
#FLAT FILE WRITER OPTIONS
#------------------------
goldengate.flatfilewriter.writers=ldvwriter
goldengate.userexit.chkptprefix=ffwriter_
ldvwriter.mode=LDV
ldvwriter.rawchars=true
ldvwriter.includebefores=false
ldvwriter.includecolnames=false
ldvwriter.files.onepertable=false
ldvwriter.files.data.rootdir=./dirout
ldvwriter.files.data.ext=.data
ldvwriter.files.data.tmpext=.temp
ldvwriter.files.data.rollover.time=10
ldvwriter.files.data.norecords.timeout=10
ldvwriter.files.control.use=true
ldvwriter.files.control.ext=.ctrl
ldvwriter.files.control.rootdir=./dirout
ldvwriter.metacols=position,timestamp,@TOKEN-RBA,@TOKEN-POS,opcode,txind,schema,table
ldvwriter.metacols.TOKEN-RBA.fixedlen=10
ldvwriter.metacols.TOKEN-POS.fixedlen=10
ldvwriter.metacols.timestamp.fixedlen=26
ldvwriter.metacols.schema.fixedjustify=right
ldvwriter.metacols.schema.fixedpadchar.chars=Y
ldvwriter.metacols.opcode.fixedlen=1
ldvwriter.metacols.opcode.insert.chars=I
ldvwriter.metacols.opcode.update.chars=U
ldvwriter.metacols.opcode.delete.chars=D
ldvwriter.metacols.txind.fixedlen=1
ldvwriter.metacols.txind.begin.chars=B
ldvwriter.metacols.txind.middle.chars=M
ldvwriter.metacols.txind.end.chars=E
ldvwriter.metacols.txind.whole.chars=W
ldvwriter.ldv.vals.missing.chars=M
ldvwriter.ldv.vals.present.chars=P
ldvwriter.ldv.vals.null.chars=N
ldvwriter.ldv.lengths.record.mode=binary
ldvwriter.ldv.lengths.record.length=4
ldvwriter.ldv.lengths.field.mode=binary
ldvwriter.ldv.lengths.field.length=2
ldvwriter.files.rolloveronshutdown=false
ldvwriter.statistics.toreportfile=false
ldvwriter.statistics.period=onrollover
ldvwriter.statistics.tosummaryfile=true
ldvwriter.statistics.overall=true
ldvwriter.statistics.summary.fileformat=schema,table,schemaandtable,total,gctimestamp,ctimestamp
ldvwriter.statistics.summary.delimiter.chars=|
ldvwriter.statistics.summary.eol.chars=\n
ldvwriter.metacols.position.format=dec
ldvwriter.writebuffer.size=36863
[ogg@node2 file-writer]$ cat ffwriter.properties
#------------------------
#LOGGING OPTIONS
#------------------------
goldengate.log.logname=ffwriter
goldengate.log.level=INFO
goldengate.log.modules=LOGMALLOC
goldengate.log.level.LOGMALLOC=ERROR
goldengate.log.tostdout=false
goldengate.log.tofile=true

#------------------------
#FLAT FILE WRITER OPTIONS
#------------------------
goldengate.flatfilewriter.writers=dsvwriter
dsvwriter.mode=DSV
dsvwriter.rawchars=false
dsvwriter.includebefores=false
dsvwriter.includecolnames=false
dsvwriter.omitvalues=false
dsvwriter.diffsonly=false
dsvwriter.omitplaceholders=false
dsvwriter.files.onepertable=true
dsvwriter.files.data.ext=_data.dsv
dsvwriter.files.data.tmpext=_data.dsv.temp
dsvwriter.dsv.nullindicator.chars=
dsvwriter.dsv.fielddelim.chars=;
dsvwriter.dsv.fielddelim.escaped.chars=
dsvwriter.files.data.rootdir=./dirout
dsvwriter.files.data.rollover.size=10000