GoldenGate enables us to extract and replicate data across a variety of topologies as shown the diagram below as well as the exchange and manipulation of data at the transactional level between a variety of database platforms like Oracle, DB2, SQL Server, Ingres, MySQL etc.
It can support a number of different business requirements like:
- Business Continuity and High Availability
- Data migrations and upgrades
- Decision Support Systems and Data Warehousing
- Data integration and consolidation
){kind=link}
The Manager process must be running on both the source as well as target systems before the Extract or Replicat process can be started and performs a number of functions including monitoring and starting other GoldenGate processes, managing the trail files and also reporting.
The Extract process runs on the source system and is the data capture mechanism of GoldenGate. It can be configured both for initial loading of the source data as well as to synchronize the changed data on the source with the target. This can be configured to also propagate any DDL changes on those databases where DDL change support is available.
The Replicat process runs on the target system and reads transactional data changes as well as DDL changes and replicates then to the target database. Like the Extract process, the Replicat process can also be configured for Initial Load as well as Change Synchronization.
The Collector is a background process which runs on the target system and is started automatically by the Manager (Dynamic Collector) or it can be configured to stsrt manually (Static Collector). It receives extracted data changes that are sent via TCP/IP and writes then to the trail files from where they are processed by the Replicat process.
Trails are series of files that GoldenGate temporarily stores on disks and these files are written to and read from by the Extract and Replicat processes as the case may be. Depending on the configuration chosen, these trail files can exist on the source as well as on the target systems. If it exists on the local system, it will be known an Extract Trail or as an Remote Trail if it exists on the target system.
Data Pumps are secondary extract mechanisms which exist in the source configuration. This is optional component and if Data Pump is not used then Extract sends data via TCP/IP to the remote trail on the target. When Data Pump is configured, the Primary Extract process will write to the Local Trail and then this trail is read by the Data Pump and data is sent over the network to Remote Trails on the target system.
In the absence of Data Pump, the data that the Extract process extracts resides in memory alone and there is no storage of this data anywhere on the source system. In case of network of target failures, there could be cases where the primary extract process can abort or abend. Data Pump can also be useful in those cases where we are doing complex filtering and transformation of data as well as when we are consolidating data from many sources to a central target.
When processing transactional data changes, the Extract process can obtain data directly from the database transaction logs (Oracle, DB2, SQL Server, MySQL etc) or from a GoldenGate Vendor Access Module (VAM) where the database vendor (for example Teradata) will provide the required components that will be used by Extract to extract the data changes.
To differentiate between the number of different Extract and Replicat groups which can potentially co-exist on a system, we can define processing groups. For instance, if we want to replicate different sets of data in parallel, we can create two Replicat groups.
A processing group consists of a process which could be either a Extract or Replicat process, a corresponding parameter file, checkpoint file or checkpoint table (for Replicat) and other files which could be associated with the process.
Steps :-
- Golden Gate Setup at Source and Target
- Configure Source :-
- Configure Schema
- Configure Manager
- Configure Extract
- Add Trandata
- Configure Pump
- Configure Target
- Configure Manager
- Create Check point table
- Configure Replicat
Database And Machine Info
SOURCE Database
Oracle11g Release 2 – (11.2.0.1.0)
VM1
Red Hat Linux 5
SRC
GGS_OWNER
TARGET Database |
Oracle11g Release 2 – (11.2.0.1.0)
VM2
Red Hat Linux 5
TARGET
GGS_OWNER
¨
- mkdir /u01/soft/
- $unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
Archive: ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar
- $ tar -xf fbo_ggs_Linux_x86_ora11g_32bit.tar
- export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/oradata/gg
- [oracle@VM1 soft]$ ls
bcpfmt.tpl ddl_ora10upCommon.sql defgen freeBSD.txt libxml2.txt role_setup.sql
bcrypt.txt ddl_ora11.sql demo_more_ora_create.sql ggcmd logdump sequence.sql
cfg ddl_ora9.sql demo_more_ora_insert.sql ggMessage.dat marker_remove.sql server
chkpt_ora_create.sql ddl_pin.sql demo_ora_create.sql ggsci marker_setup.sql sqlldr.tpl
cobgen ddl_purgeRecyclebin.sql demo_ora_insert.sql help.txt marker_status.sql tcperrs
convchk ddl_remove.sql demo_ora_lob_create.sql jagent.sh mgr ucharset.h
db2cntl.tpl ddl_session1.sql demo_ora_misc.sql keygen notices.txt ulg.sql
ddl_cleartrace.sql ddl_session.sql demo_ora_pk_befores_create.sql libantlr3c.so oggerr UserExitExamples
ddlcob ddl_setup.sql demo_ora_pk_befores_insert.sql libdb-5.2.so params.sql usrdecs.h
ddl_ddl2file.sql ddl_status.sql demo_ora_pk_befores_updates.sql libgglog.so prvtclkm.plb zlib.txt
ddl_disable.sql ddl_staymetadata_off.sql dirjar libggrepo.so pw_agent_util.sh
ddl_enable.sql ddl_staymetadata_on.sql dirprm libicudata.so.38 remove_seq.sql
ddl_filter.sql ddl_tracelevel.sql emsclnt libicui18n.so.38 replicat
ddl_nopurgeRecyclebin.sql ddl_trace_off.sql extract libicuuc.so.38 retrace
ddl_ora10.sql ddl_trace_on.sql fbo_ggs_Linux_x86_ora11g_32bit.tar libxerces-c.so.28 reverse
- [oracle@VM1 soft]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
—
- GGSCI (VM1) 1> create subdirs
Creating subdirectories under current directory /u01/soft
Parameter files /u01/soft/dirprm: already exists
Report files /u01/soft/dirrpt: created
Checkpoint files /u01/soft/dirchk: created
Process status files /u01/soft/dirpcs: created
SQL script files /u01/soft/dirsql: created
Database definitions files /u01/soft/dirdef: created
Extract data files /u01/soft/dirdat: created
Temporary files /u01/soft/dirtmp: created
Stdout files /u01/soft/dirout: created
- Issue the following command to exit GGSCI.
GGSCI (VM1) 2> exit
–
- create tablespace
- create user
- Give Grants
- grant connect, resource to ggs_owner;
- grant select any dictionary, select any table to ggs_owner;
- grant create table to ggs_owner;
- grant flashback any table to ggs_owner;
- grant execute on dbms_flashback to ggs_owner;
- grant execute on utl_file to ggs_owner;
- grant create any table to ggs_owner;
- grant insert any table to ggs_owner;
- grant update any table to ggs_owner;
- grant delete any table to ggs_owner;
- grant drop any table to ggs_owner;
- Change Parameter as per requirement
- UNDO_MANAGEMENT=AUTO
- UNDO_RETENTION=86400
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 247466368 bytes
Database Buffers 167772160 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
SQL>
SQL> alter system set recyclebin=off scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 247466368 bytes
Database Buffers 167772160 bytes
Redo Buffers 6094848 bytes
SQL> alter database archivelog;
Database altered.
SQL> alter database Open;
Database altered.
SQL> create tablespace ggs_data datafile ‘/u01/<DBNAME>ggs_data01.dbf’ size 200m;
tablespace created
SQL> create user ggs_owner identified by ggs_owner default tablespace ggs_data temporary tablespace temp;
User created.
SQL> grant connect, resource to ggs_owner;
grant select any dictionary, select any table to ggs_owner;
grant create table to ggs_owner;
grant flashback any table to ggs_owner;
grant execute on dbms_flashback to ggs_owner;
grant execute on utl_file to ggs_owner;
grant create any table to ggs_owner;
grant insert any table to ggs_owner;
grant update any table to ggs_owner;
grant delete any table to ggs_owner;
grant drop any table to ggs_owner;
grant drop any table to ggs_owner;
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set UNDO_RETENTION=86400 scope=both;
System altered.
sql> alter database add supplemental log data;
Database altered.
— bounce the database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 251660672 bytes
Database Buffers 163577856 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
EXPORT: @ SOURCE
$ expdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_gg.log schemas=ggtest Scp from SOURCE and TARGET
$scp –p schema_gg.dmp 172.168.10.108:/oradata
IMPORT: @ TARGET
$impdp directory=db_dir dumpfile=schema_gg.dmp logfile=schema_imp_gg.log schemas=ggtest
Run scripts for creating all necessary objects for support DDL replication
[oracle@VM1 soft]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 26 05:42:29 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:
Marker setup table script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_OWNER
MARKER TABLE
——————————-
OK
MARKER SEQUENCE
——————————-
OK
Script complete.
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication…
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:
Working, please wait …
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables …
Check complete.
Using GGS_OWNER as a Oracle GoldenGate schema name.
Working, please wait …
DDL replication setup script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_OWNER
CLEAR_TRACE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDL IGNORE TABLE
———————————–
OK
DDL IGNORE LOG TABLE
———————————–
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDL HISTORY TABLE
———————————–
OK
DDL HISTORY TABLE(1)
———————————–
OK
DDL DUMP TABLES
———————————–
OK
DDL DUMP COLUMNS
———————————–
OK
DDL DUMP LOG GROUPS
———————————–
OK
DDL DUMP PARTITIONS
———————————–
OK
DDL DUMP PRIMARY KEYS
———————————–
OK
DDL SEQUENCE
———————————–
OK
GGS_TEMP_COLS
———————————–
OK
GGS_TEMP_UK
———————————–
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
———- —————————————————————–
No errors No errors
DDL TRIGGER INSTALL STATUS
———————————–
OK
DDL TRIGGER RUNNING STATUS
———————————–
ENABLED
STAYMETADATA IN TRIGGER
———————————–
OFF
DDL TRIGGER SQL TRACING
———————————–
0
DDL TRIGGER TRACE LEVEL
———————————–
0
LOCATION OF DDL TRACE FILE
————————————————————————————————————————
/u01/app/oracle/diag/rdbms/src/SRC/trace/ggs_ddl_trace.log
Analyzing installation status…
STATUS OF DDL REPLICATION
————————————————————————————————————————
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
SQL>
SQL> grant ggs_ggsuser_role to ggs_owner;
Grant succeeded.
SQL> @ddl_enable
Trigger altered.
SQL> @ddl_pin GGS_OWNER
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
- Add Trandata
i. Create trandata statement and execute it on gg prompt
SQL> select ‘add trandata ‘||owner||’.’||object_name||’;’ from dba_objects where owner=’GGTEST’ and object_type=’TABLE’;
‘ADDTRANDATA’||OWNER||’.’||OBJECT_NAME||’;’
——————————————————————————–
add trandata GGS_OWNER.SCOTT;
add trandata GGS_OWNER.DEPT;
ii. Syntax for reference add trandata <>.<>
i. **
ii. **
iii. edit params mgr
— Created By :- Sachin Ichake
— Script :- Parameter file for MANAGER
— Manager Parameter file
PORT 7809
–Automatically start extract and pump as soon as manager starts
–AUTOSTART EXTRACT GGEC01G1
–If an abend occurs, try to restart with maximum of 20 times delaying 2 minutes between start attempts
AUTOSTART EXTRACT GGEC01G1
–,RETRIES 20 ,WAITMINUTES 2
–Delete GG trails if it is older than 3 days and there are no GG process with a checkpoint to it
PURGEOLDEXTRACTS ./DIRDAT/* USECHECKPOINTS MINKEEPHOURS 3
–Report any lag every 60 Minutes. anytimes lag exceeds 20 Minutes immediately generate a critical message
LAGREPORTMINUTES 60
LAGCRITICALMINUTES 20
iv. info all
v. dblogin USERID ggs_owner, PASSWORD ggs_owner vi. start manager
c. Configure Extract
— Created By :- Sachin Ichake
— Script :- To Add Extract
–Add extract for EMP
— TARGET DATABASE
DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
DELETE GGEC01G1
ADD EXTRACT GGEC01G1, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/soft/dirdat/g1, EXTRACT GGEC01G1
–, MEGABYTES 5
- Edit params GGEC01G1
— Created By :- Sachin Ichake
— Script :- Parameter file for Extract
— Parameter Syntax check ( Remove after syntax Validation )
— CHECKPARAMS
EXTRACT GGEC01G1
–Source Database
USERID ggs_owner, PASSWORD ggs_owner
–Generate SQL output
–FORMATSQL
–Discard file parameters
DISCARDFILE /u01/soft/dirrpt/GGEC01G1.dsc, PURGE
–Transaction log parameter
–TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
— Local Trail File Parameters
EXTTRAIL /u01/soft/dirdat/g1
–Output in SQL format
–To check the parameter syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC” parameter. start the group
–CHECKPARAMS
–NODYNAMICRESOLUTION
— Update and delete operation parameters capture full ops image
–NOCOMPRESSDELETES
–NOCOMPRESSUPDATES
–IGNORE DELETE, UPDATE, INSERT
— IGNOREDELETES
–IGNOREUPDATES
–IGNOREINSERTS
–Check parameter syntax also check the table syntax
–after correcting the syntax, remove these 2 parameters
–CHECKPARAMS
–NODYNAMICRESOLUTION
— Runtime parameters
STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 1 HOUR, RATE
–Wildcard table processing parameters
WILDCARDRESOLVE DYNAMIC
DDL INCLUDE MAPPED
— GG data capture table list: EMP
TABLE GGS_OWNER.EMP;
— Created By :- Sachin Ichake
— Script :- To Add Extract
–Add extract for EMP
— TARGET DATABASE
DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
— EXTRACT PUMP
DELETE GGPC01G1
ADD EXTRACT GGPC01G1, EXTTRAILSOURCE /u01/soft/dirdat/g1
ADD RMTTRAIL /u01/soft/dirdat/1g, EXTRACT GGPC01G1
- Edit params GGPC01G1
— Created By :- Sachin Ichake
— Script :- Parameter file for Pump
— Parameter Syntax check ( Remove after syntax Validation )
— CHECKPARAMS
EXTRACT GGPC01G1
–Data Pump mode (This should be commented)
PASSTHRU
— Control Parameters
RMTHOST 10.184.93.74, MGRPORT 7809, COMPRESS
–Remote Trail File parameter
RMTTRAIL /u01/soft/dirdat/1g
–To check the parameter syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC” parameter. start the group
–CHECKPARAMS
–NODYNAMICRESOLUTION
–Wildcard table processing parameters
WILDCARDRESOLVE DYNAMIC
— GG data pump table list: EMP
TABLE GGS_OWNER.EMP;
–TABLE EMP FILTER(ORANGE(1,2));
- Edit GLOBAL parameter file by connecting gg prompt
** i.
GGSCHEMA ggs_owner
CHECKPOINTTABLE ggs_owner. Checkpoint
Configure Manager
i. **
ii. info all
iii. edit params mgr
— Created By :- Sachin Ichake
— Script :- Parameter file for MANAGER
— Manager Parameter file
PORT 7809
–Automatically start extract and pump as soon as manager starts
–AUTOSTART EXTRACT GGPC01G1
–If an abend occurs, try to restart with maximum of 20 times delaying 2 minutes between start attempts
AUTOSTART EXTRACT GGPC01G1
–,RETRIES 20 ,WAITMINUTES 2
–Delete GG trails if it is older than 3 days and there are no GG process with a checkpoint to it
PURGEOLDEXTRACTS ./DIRDAT/* USECHECKPOINTS MINKEEPHOURS 3
–Report any lag every 60 Minutes. anytimes lag exceeds 20 Minutes immediately generate a critical message
LAGREPORTMINUTES 60
LAGCRITICALMINUTES 20
iv. **
**v.
vi. start manager
- Create Check point table
i. **
ii. add checkpointtable ggs_owner.checkpoint
Configure Replicat
i.
— Created By :- Sachin Ichake
— Script :- Replicat Obey File
— login to the database
DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
— Add replicat
DELETE GGRC01G1
ADD REPLICAT GGRC01G1, EXTTRAIL /u01/soft/dirdat/1g, checkpointtable ggs_owner.checkpoint
— Created By :- Sachin Ichake
— Script :- Parameter file for Replicat
–Following 3 options are to display SQL statements comment out BATCHSQL
–SHOWSYNTAX
–NODYNSQL
–NOBINARYCHARS
—
–NOLIST will not expand the macros in the report file
–INCLUDE the macro library
–LIST is end of NOLIST
—
REPLICAT GGRC01G1
— Handle the collisions after the initial load
HANDLECOLLISIONS
— TARGET DATABASE
–#DB_Connect()
USERID ggs_owner, PASSWORD ggs_owner
–Discard file parameters
DISCARDFILE /u01/soft/dirrpt/GGEC01G1.dsc, PURGE
—
–Source and Target table parameters
ASSUMETARGETDEFS
–SOURCEDEFS C:GGDIRSQLMYTABLES.SQL
—
–To check the parameter syntax, uncomment the below 2 lines and comment out “WILDCARDRESOLVE DYNAMIC” parameter.
–CHECKPARAMS
–NODYNAMICRESOLUTION
— Performance parameters for <5k bytes per row data change
— Replaced “BATCHSQL” with “BATCHSQL BATCHTRANSOPS … ”
BATCHSQL BATCHTRANSOPS 10000
–Running reports parameters
–#generate_stats()
REPORTCOUNT EVERY 1 HOUR, RATE
STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
— Wildcard table processing parameter
WILDCARDRESOLVE DYNAMIC
TRANSACTIONTIMEOUT 5 S
–ddl support
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
–Specify table mapping —
MAP GGS_OWNER.*, TARGET GGS_OWNER.*;
MAP GGS_OWNER.EMP, TARGET GGS_OWNER.EMP;
- START MGR
- START EXTRACT GG*
- START MGR
- START replicat GG*
Some more scripts
Golden gate script About these ads### Share this:
– – – .### Like this:
_Related_
Monitoring GoldenGate Performance (Source :- Metalink)In "Oracle Fundamental"
Oracle GoldenGate for Filtering and Transformation DataIn "Oracle Fundamental"
Post 1 : Data Replication and IntegrationIn "DB Replication and Advance Queuing"
[
View all posts by Sachin Ichake → ](https://sachinichake.wordpress.com/author/sachinichake/)[Oracle Core](https://sachinichake.wordpress.com/category/oracle-core/)[permalink](https://sachinichake.wordpress.com/2013/05/08/step-by-step-golden-gate-configuration/)