ORACLE GOLDENGATE

1)  GoldenGate Setup & Configuration


On Source & Target Hosts Perform the Following
On Source: Unzip the GG Software and Perform the installation & Configuration

[oracle@targethost]/data # unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@targethost]/data # tar -xvf ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@sourcehost]/data/ggate # ./ggsci


GGSCI (targethost) 1> create subdirs

1. Ensure The database is in archive log mode

2. Create Necessary tablespace and goldengate user in database
SQL> create tablespace ggs_data datafile '/data/GGS/Base/ggs_data01.dbf' size 200m;
SQL> create user ggs_owner identified by ggs_owner default tablespace ggs_data temporary tablespace temp;
SQL> grant connect,resource to ggs_owner;
SQL> grant select any dictionary, select any table to ggs_owner;
SQL> grant create table to ggs_owner;
SQL> grant flashback any table to ggs_owner;
SQL> grant execute on dbms_flashback to ggs_owner;
SQL> grant execute on utl_file to ggs_owner;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SQL> alter session set recyclebin=OFF;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('GGS_OWNER');
SQL> grant select any dictionary to GGS_OWNER;
SQL> grant insert any table to GGS_OWNER;
SQL> grant update any table to GGS_OWNER;
SQL> grant delete any table to GGS_OWNER;

Also Configure DDL Replication, Run the following scripts from goldengate software directory

@marker_setup
@ddl_setup
@role_setup
grant ggs_ggsuser_role to ggs_owner;
@ddl_enable
@ddl_pin GGS_OWNER



2)  Configuring Manager Process

On Source: Configure Manager Process
############################################################

cd /data/ggate
./ggsci
info all
EDIT PARAMS MGR
### Copy the below to param file
PORT 7809
USERID ggs_owner, PASSWORD ggs_owner
PURGEOLDEXTRACTS /data/ggate/dirdat/ex, USECHECKPOINTS
######start the manager process ########
start manager
info all

############################################################
On Target: Configure Manager Process
############################################################
cd /data/ggate
./ggsci
info all
EDIT PARAMS MGR
### Copy the below to param file
PORT 7809
USERID ggs_owner, PASSWORD ggs_owner
PURGEOLDEXTRACTS /u01/ggt_target/dirdat/rt, USECHECKPOINTS
######start the manager process ########
start manager

info all

3 : Configure the Extract / Replicat for Initial Load

4: Configuring Online Change Synchronization after initial load

In this example , we will configure a continuous synchronisation for the same myobj table.

• Create a GoldenGate Checkpoint table
• Create an Extract group
• Create a parameter file for the online Extract group
• Create a Trail
• Create a Replicat group
• Create a parameter file for the online Replicat group

 5: Configuring Secondary Extract on Source (datapump Extract)

In the previous example, we have configured the extract process which extracts the changes from source and write the trail files to target.

However, in large system this can lead to lag for extract process and may have chance of loosing data.

Hence configuring local extract apart from remote trail is also possible using datapump process (differs from database expdp/impdp datapump).

Datapump process create local trail files and send them to remote as required.

6: Configuring DDL Synchronization

In Previous examples we see Extract and Replicat process and dml synchronisation.

Where in if you want DDL also synchronised across databases then extract parameter file must be changed accordingly.

In addition to extract options, we must execute scripts (refer to Example 1) to configure the ddl setup.

Also, the object that need to be DDL synchronised option trandata should be enabled.

 7: Filtering the Data

In this example we see how to filter the data changes to target side. i.e we can exclude some changes to thetarget side and skip them applying.

 8: Using Defgen Utility

It may be possible that some changes happen to table structure on target side, but this affects the replicat process.

For example, if a column name is changed in the replicat side itself and the primary column name is different then the replicat process will abended with error "invalid identifier"

In those cases, defgen utility will help to handle the column name changes.

Not only the above case, when you have structural changes between tables defgen utility can be used to create a COLMAP information.

First lets create a DDL information using defgen utility on source side.

## Create a Defgen Parameter file

EDIT PARAMS defgen

DEFSFILE I:\ggs_source\dirsql\def.sql

USERID ggs_owner, PASSWORD ggs_owner

TABLE test.myobj;

## Using Defgen utitlty create the sql file

./defgen paramfile I:\ggs_source\dirprm\defgen.prm

## Copy the generated SQL file to target location

scp I:\ggs_source\dirsql\def.sql 192.168.56.109:/u01/ggt_target/dirsql/def.sql

On target side, Edit the Replicat parameter file to colmap the differences in column names

./ggsci

EDIT params rep2

##Edit and Add the Bold part of following lines to parameter file

REPLICAT rep2

SOURCEDEFS /u01/ggt_target/dirsql/def.sql

USERID ggs_owner, PASSWORD ggs_owner

MAP test.myobj, TARGET test.myobj, COLMAP (usedefaults,object_name=objname);

## Start the replicat

start replicat rep2

## Replication will proceed with out error

9: Conflict Resolution & Skipping Transaction

In replication, one of the most important issue is conflict data and whenever the conflicts happen the replication stops and it will not proceed. Common cases of conflicts can be,

• No data found (ora-1403)
• Unique constraint errors
• primary key violations

We need to either fix the data (not the dba job should checked by business analysts), or skip the transactions.

In order to skip the transactions in goldengate in such scenario, the following methods can be used. Usually and generally replicat process will be in abended state when conflict arises.

• Using logdump
• Skiptranaction
• Handle collisions
• Exceptional handling

 10: Reporting Commands in Goldengate

It is required to evaluate the extract status, and details and replicat status, lag details. Further also any long running transactions etc.

11: Missing Trail File

Sometimes there is likely hood that extract trail files are deleted by manually or by discard process.

In those cases, replicat process will abend since it does not have a trail file. Not only the replicat, the extract process itself will abend if its the current extract file.

The following is the sample errors in those cases, if an extract file is deleted.

2015-04-16 08:52:08  ERROR   OGG-01496  Oracle GoldenGate Capture for Oracle, myload2.prm:  Failed to open target trail file /data/ggate/dirdat/bb000007, at RBA 12678462.

In case of missing trail file or files, you can create a extract for specific period and run the extract, in this case you must have archives present in the location

12: Missing Archive Log File

Sometimes , due to RMAN Deletion Policy archives may be deleted and if goldengate extract process needs it, then as such no archive is present , the extract process will be abended.

In those cases, the following error will be shown in GG Error log

2011-05-10 15:13:23  ERROR   OGG-00446  Could not find archived log for sequence 92 thread 1 under default destinations SQL <SELECT  name    FROM v$archived_log   WHERE sequence# = :ora_seq_no AND  thread# = :ora_thread AND         resetlogs_id = :ora_resetlog_id AND         archived = 'YES' AND         deleted = 'NO>, error retrieving redo file name for sequence 92, archived = 1, use_alternate = 0 Not able to establish

initial position for begin time 2011-05-10 14:38:39.

If you have an archive log in backup. Just restore it from rman and start the extract process.

If you do not have archive log at all, then you must skip the archive log to do that.

Reading the error saying , the archive log sequence 92 is missing, so if you want to read next archive then issue following command

./ggsci

alter extract ext3, extseqno 93

Monitor the error log or check extract status

info extract ext3, detail

Note: By Doing so, the target may miss the data that changed and recorded in that missing archive log file.

13: Purging Trail Files

As like archive log clean up, one should also think about purging golden gate trail files to purge them.

By default, Goldengate manager does not purge them unless the purgeoldextracts parameter is not specified in the mgr param file.

Syntax for purging trail files

PURGEOLDEXTRACTS trail [, USECHECKPOINTS | NOUSECHECKPOINT ] [, MINKEEP_rule MINKEEPHOURS|MINKEEPDAYS|MINKEEPFILES] 

So now question is how often the manager purges the trail records, by default it is 10 MINS as per CHECKMINUTES default value. Even you do not specify this 10 mins is applicable.

For example, in the following the manager process purge the old extracts. The USECHECKPOINTS parameter tells manager process to check trail file is already applied or not in Checkpoint table. Do NOT use NOUSECHECKPOINT, since it will delete all the trail files without considering the trail file

.ggsci

./ggsci

info all

EDIT PARAMS MGR

### Add the bold line to your manager Param file ###

PORT 7809

USERID ggs_owner, PASSWORD ggs_owner

PURGEOLDEXTRACTS /data/ggate/dirdat/ex, USECHECKPOINTS

For example, you want to retain trails for 10 Hours and rest to be deleted use MINKEEP_RULE

.ggsci

./ggsci

info all

EDIT PARAMS MGR

### Add the bold line to your manager Param file ###

PORT 7809

USERID ggs_owner, PASSWORD ggs_owner

PURGEOLDEXTRACTS /data/ggate/dirdat/ex, USECHECKPOINTS , MINKEEPHOURS 10

For example, if you want to delete the DDL History also, you can use PURGEDDLHISTORY Marker in the parameter file

.ggsci

./ggsci

info all

EDIT PARAMS MGR

### Add the bold line to your manager Param file ###

PORT 7809

USERID ggs_owner, PASSWORD ggs_owner

PURGEOLDEXTRACTS /data/ggate/dirdat/ex, USECHECKPOINTS , MINKEEPHOURS 10

PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

So this is all about configuration, what about when you hit a filesystem issue and you want to purge the trails and change the MINKEEPHOURS 10 reasonable number.

Lets check it. Check the extract or replicat trail file location by using

ggsci> info extract ext3 detail

cd /data/ggate/

[oracle@sourcehost]/data/ggate: du -sch *

4.3M    rt

15G     ex

15G     total

My extract folder showing 15G extracts are there. Now to purge this I will need to edit the manager parameter file, and you see below it keeps 10 Hours of trail files. By which using checkpoints , so its safe to delete those trail files even keeping for 5 hours. Lets do that.

.ggsci

./ggsci

info all

EDIT PARAMS MGR

PORT 7809

USERID ggs_owner, PASSWORD ggs_owner

PURGEOLDEXTRACTS /data/ggate/dirdat/ex, USECHECKPOINTS , MINKEEPHOURS 10

Change the Hours to 5, Ensure you have USECHECKPOINTS in parameterfile, this will make sure the trails files need for GG will not be deleted.

EDIT PARAMS MGR

PORT 7809

USERID ggs_owner, PASSWORD ggs_owner

PURGEOLDEXTRACTS /data/ggate/dirdat/ex, USECHECKPOINTS , MINKEEPHOURS 5

Once you changed , *** IMP *** Do not stop the manager, just refresh it. After 10 mins (maintainence activity default) it will start purging. If you stop/start the manager it will stop the extract and replicat process running on host.

GGSCI (source) 3> refresh mgr

Sending REFRESH request to MANAGER ...

Mgr Params Updated

After 10 mins you can see in error log file. The old purge files start purging.

If you want to purge the trail files immediately, you can use,

GGSCI(source) > send manager getpurgeoldextracts

GGSCI (source) > send manager purgeoldextracts

This command will immediately remove the trails from the location.

Also note, you must add each individual location for trails file to purge each extract trail files.

14: Auto Starting Extract & Replicat, More Manager Parameters

Manager parameter also can be used to define many other functions to manage.

For example if you want to start the Extract and Replicat Process automatically when a manager process start , keep the following line

AUTOSTART ER *

There are other options also available , retries and wait before retry and reset the minutes along with your restart.

AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60

There are other certain options which can help you to manage goldengate effectively and important are,

BOOTDELAYMINUTES	When ever a database host restarts , the goldengate manager wait until this many minutes to ensure OS completes start up and start GG then, Applicable only for windows.
DOWNREPORTMINUTES	Displays the Status of Extract/Replicat process in Error Log, the frequency of showing such status can be regulated with this option. If you have very large error log file it can be difficult to read every and then so you can specify how frequent you want to see such status.
DOWNCRITICAL	In the report , it shows the process status abended/normal shutdown
LAGCRITICALSECONDS	How many seconds of LAG threshold before it publish to error log.
LAGCRITICAL	Specifies a lag threshold that is considered critical and generates a warning to the error log.

Sample parameter file

.ggsci

edit params MGR

AUTOSTART ER *

AUTORESTART ER *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60

DOWNREPORTMINUTES 15

DOWNCRITICAL

LAGCRITICALSECONDS 10

LAGINFOMINUTES 0

LAGREPORTMINUTES 15

15: Security in Goldengate

As you aware, the parameter files for Extract and replicat contains database username and password which can be security concern.

Hence goldengate provide a keygen utility to create a encryption key and can be used to generate a encrypted password and use that encrypted password in parameter file.

Let's look at, first create a encryption keys using keygen utility in goldengate software location

oracle@sourcehost:/data/ggate  $ ./keygen  128 4 >> ENCKEYS

0x022A972B7CF6EF537DBCF35792BEH321

0x03CC8167B516426D9CA3A70B5B1FDW12

0x066E6C23EF369406BA8A5B3F2580DF54

0x0A10575F2857E61FD8710F73EFE08H11

### Copy the Enckeys file to target goldengate home

scp ENCKEYS target:/data/ggate

### Create a Encryption password using Encryption of above

[oracle@sourcehost]/data/ggate # ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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

GGSCI (source) 2> encrypt password gguser123 ENCRYPTKEY key1

Encrypted password:  AADAAAAAAAAAAAKAVHWAGJIGWBGHDBRAGJMIPEQEXBYEMDYIRBICFHSCTDHHEJHANCKAUDDGQJPBHRDT

Algorithm used:  AES128

GGSCI (source) 3> dblogin USERID gguser@MYDB, PASSWORD AADAAAAAAAAAAAKAVHWAGJIGWBGHDBRAGJMIPEQEXBYEMDYIRBICFHSCTDHHEJHANCKAUDDGQJPBHRDT, encryptkey key1

Successfully logged into database.

GGSCI (source) 4> dblogin USERID gguser@MYDB, PASSWORD gguser123

Successfully logged into database.

So you can use this encrypted password in your extract param file

GGSCI> edit params ext3

### Look at below bold part and remove old password and keep the encrypted password.

EXTRACT ext1

USERID ggs_owner, PASSWORD AADAAAAAAAAAAAKAVHWAGJIGWBGHDBRAGJMIPEQEXBYEMDYIRBICFHSCTDHHEJHANCKAUDDGQJPBHRDT, encryptkey  key1

RMTHOST 192.168.56.109, MGRPORT 7809

RMTTASK replicat, GROUP rep1

TABLE test.myobj;

As we removed the clear text password and reset the password with new above encrypted password.

16: Different Versions of Goldengate Replication

Is it possible to have goldengate replicate between 10g database and 11g database or viceversa.

Answer, Yes.

Its not the databases versions , since Since goldengate trail files are independent to database and they contain their own format of trail files by reading redo/archive log files. And this trail files are only means for replication.

But if the target version of goldengate is lower than source version then this could be an issue, due to header format maintain the version in extract trail files.

Consider this scenario

Source	Target
11gR2 Database	10G/11gR1 Database
11gR2 GG Software	11GR1 GG Software
Extract will generate trails in 11gR2 Format	Replicat will try to read those extract trail and file with ERROR OGG-01389 File header failed to parse tokens. File /data/ggate/ex10919, last offset 100, data: 0x

To overcome this, The extract parameter FORMAT must be added to target version so that extract maintains the trail file headers in lower version format as like target. As you see the format release is matched with target gg version above.

./ggsci

edit params ext3

extract ext3
userid ggs_owner, password ggs_owner
rmthost 192.168.56.109, mgrport 7809
rmttrail /data/ggate/ex00, format release 11.1
table test.myobj6;

Once added, alter the extract to start over from next trail file using etrollover. (new sequence will be generated)

ggsci> alter extract ext3, etrollover

On Target side, alter the replication process to start read from new extrail sequence

ggsci&gt; <span style="font-family: Helvetica, Arial, sans-serif;">alter replicat rep1 extseqno 1

Should now the replicate process proceed without issue.

17: Start, Stop, Report, Altering Extract – Regenerating, Rolling Over etc

There are numerous options for extract process and changes from version to version and many new introduced.

Out of them , The following will be useful in the cases as mentioned below.

Starting & Stopping Extract	stop extract ext3 start extract ext3	Starting and stopping extracts
Killing Extract	kill extract ext3	Some times extract process does not respond to stop and you need to kill it.
Report Lag of Extract from source	lag extract ext3	Reports how much lag extract is from the database, it will provide more information than INFO command
Begin the extract again	ALTER EXTRACT EXT3, BEGIN NOW	Caution, This will make extract to restart from current SCN, so when you do this you are actually making extract to start fresh replication from current point. When you are trying to fix and restarting extract, you should use start extract ext3
Restart extract from specific date and time	ALTER EXTRACT ext3, BEGIN 2015-07-13	Useful when you missed some extract files and replication is abended but ensure this will reset extract trail
Restart extract from specific date and time & rollover	ALTER EXTRACT ext3, BEGIN 2015-07-13, etrollover	This will restart the extract to read specific time and also start new sequence number. The replicat must be modified to read from this new sequence on target alter replicat rep3 , extseqno 1
Extract to start processing at a specific location in the trail.	ALTER EXTRACT ext3, EXTSEQNO 26, EXTRBA 338	Useful when extract abended for any reasons and you want to restart the extract to regenerate those redo records from that particular sequence at particular RBS You can find the RBA and SEQNO in extract info, where its abended. or replicat info extract ext3, detail info replicat rep3, detail
For RAC, read for specific thread	ALTER EXTRACT ext3, THREAD 4, BEGIN 2015-07-13	For rac environments if you want extract process reads from thread 4 for the said date.
Start new sequence for Extract	ALTER EXTRACT ext3, ETROLLOVER	This will create new extract sequence no. and replicat must ensure to start from new sequence alter replicat rep3, extseqno 1
For Oracle , Particular SCN	ALTER EXTRACT ext3, SCN 778899 For particular log sequence ALTER EXTRACT ext3, logseqno 18	For oracle , to read from specific scn & specific log file sequence, This is differ from extseqno
For SQL Server, Particular LSN	ALTER EXTRACT ext3, LSN 3454:875:445	For MS-SQL Server, to start over from particular LSN number
For DB2, particular LRI	ALTER EXTRACT ext3, TRANLOG LRI 8066.322711	For DB2 LUW, to restart the extract from particular LRI
Unregister	Unregister extract ext3	Unregister extract from database
Delete Extract	delete extract ext3	Deletes the extract
cleanup	cleanup extract ext3	this will just clean the records from checkpoint table , not deletes the extract

18: Start, Stop, Report , Altering Replicat – Repositioning etc.

As like extract altering, you can also alter the replicat process to reposition to start over replication process from a trail file, Along with reposition with specific Extseqno , you can also reposition to specific date and time especially useful when you regenerate extract trail files or replication abended for some reasons.

Start Stop Replicat	start replicat rep3 stop replicat rep3 stop replicat r* start replicat r*	starting and stopping replicat process stop/start replicat process or group that start with r*
Kill Replicat	kill replicat rep3	Kill the replicat process when the stop command does not respond
Lag Replicate	lag replicat rep3	Shows how much lag replicat is from the extract.
From paritcular extract sequence	ALTER REPLICAT rep3, EXTSEQNO 53	Useful when you know which sequence the replicat stop and restart from that point
From particular RBA address	ALTER REPLICAT rep3, EXTRBA 0 ALTER REPLICAT rep3, EXTRBA 1001	Useful when the extrail is available and you want to read from particular place to avoid duplicacy of data 0 - means from header n - means from particular that point.
From Particular date	ALTER REPLICAT rep3, BEGIN 2011-01-07 08:00:00	Reads and start replicate from particular date and time
Unregister	Unregister replicat rep3	Rather delete if you want to unregister the replicat from database, so ti removes the information from the goldengate metadata.
Delete	delete replicat rep3	deletes the replicat from process
cleanup	cleanup replicate rep3	cleansup records from checkpoint table , does not delete the replicat

20: Managing Extracts for abase Homes

If there is one instance of Oracle Database on the system, set the ORACLE_HOME and ORACLE_SID environment variables at the system level. If you cannot set them that way, use the following SETENV statements in the parameter file of every Extract and Replicat group that will be connecting to the instance. The SETENV parameters override the system settings and allow the Oracle GoldenGate process to set the variables at the session level when it connects to the database.

SETENV (ORACLE_HOME = "path to Oracle home location")

SETENV (ORACLE_SID = "SID")

If there are multiple Oracle instances on the system with Extract and Replicat processes connecting to them, you will need to use a SETENV statement in the parameter file of each process group. As input to the SETENV parameter, use the ORACLE_HOME and ORACLE_SID environment variables to point Oracle GoldenGate to the correct Oracle instance. For example, the following shows parameter files for two Extract groups, each capturing from a different Oracle instance.

In one extract parameter ora9a:

EXTRACT ora9a

SETENV (ORACLE_HOME = "/home/oracle/ora/product")

SETENV (ORACLE_SID = "oraa")

USERIDALIAS tiger1

RMTHOST sysb

RMTTRAIL /home/ggs/dirdat/rt

TABLE hr.emp;

TABLE hr.salary;

In Another extract parameter orab:

EXTRACT orab

SETENV (ORACLE_HOME = "/home/oracle/ora/product")

SETENV (ORACLE_SID = "orab")

USERIDALIAS tiger1

RMTHOST sysb

RMTTRAIL /home/ggs/dirdat/st

TABLE fin.sales;

TABLE fin.cust;

Like this:

                                                  GOLDEN-GATE  IN  ORACLE

Q. What type of Topology does Goldengate support?

Q. What are the main components of the Goldengate replication?                                                                            1. Manager
2. Extract
3. Pump
4. Replicate

Q. What transaction types does Goldengate support for Replication?
Goldengate supports both DML and DDL Replication from the source to target.

Q. What are the supplemental logging pre-requisites?
The following supplemental logging is required.
1. Database supplemental logging
2. Object level logging

Q. Why is Supplemental logging required for Replication?
Integrated Capture (IC):
1. In the Integrated Capture mode, GoldenGate works directly with the database log mining server to receive the data changes in the form of logical change records (LCRs).
2. IC mode does not require any special setup for the databases using ASM, transparent data encryption, or Oracle RAC.
3. This feature is only available for oracle databases in Version 11.2.0.3 or higher.
4. It also supports various object types which were previously not supported by Classic Capture.
5. This Capture mode supports extracting data from source databases using compression.
Integrated Capture can be configured in an online or downstream mode.

 Q. List the minimum parameters that can be used to create the extract process?
The following are the minimum required parameters which must be defined in the extract parameter file.
1. extract name
2. userid
3. exttrail
4. Table

Q. I want to configure multiple extracts to write to the same exttrail file? Is this possible?
Only one Extract process can write to one exttrail at a time. So you can’t configure multiple extracts to write to the same exttrail

Q. What type of Encryption is supported in Goldengate?
Oracle Goldengate provides 3 types of Encryption.
1. Data Encryption using Blow fish.
2. Password Encryption.
3. Network Encryption

Q. What are the different password encryption options available with OGG?
You can encrypt a password in OGG using
1. Blowfish algorithm and
2. Advance Encryption Standard (AES) algorithm             

Q. What are the different encryption levels in AES?
You can encrypt the password/data using the AES in three different keys
a) 128 bit
b) 192 bit and
c) 256 bit

Q. What are some of the key features of GoldenGate 12c?
The following are some of the more interesting features of Oracle GoldenGate 12c:
1. Support for Multitenant Database
2. Coordinated Replicat
3. Integrated Replicat Mode
4. Use of Credential store
5. Use of Wallet and master key
6. Trigger-less DDL replication
7. Automatically adjusts threads when RAC node failure/start
8. Supports RAC PDML Distributed transaction
9. RMAN Support for mined archive logs

Q. What are the installation options available in OGG 12c?
You can install Oracle GoldenGate 12c using in 2 ways:
1) Interactive Installation with OUI – Graphical interface
2) Silent Installation with OUI – Command Interface

Q. What is a Credential Store in OGG 12c?
OGG Credential Store manages Encrypted Passwords and USERIDs that are used to interact with the local database and Associate them with an Alias. Instead of specifying actual USERID and Password in a command or a parameter file, you can use an alias. The Credential Store is implemented as an auto login wallet within the Oracle Credential Store Framework (CSF)

Q. What command is used to create the credential store?
ADD CREDENTIALSTORE

Q. How do you enable Oracle GoldenGate for Oracle database 11.2.0.4?
The database services required to support Oracle GoldenGate capture and apply must be enabled explicitly for an Oracle 11.2.0.4 database. This is required for all modes of Extract and Replicat.
To enable Oracle GoldenGate, set the following database initialization parameter. All instances in Oracle RAC must have the same setting.
ENABLE_GOLDENGATE_REPLICATION=true

Q. How does the Replicat works in a Coordinated Mode?
In a Coordinated Mode Replicat operates as follows:
1. Reads the Oracle GoldenGate trail.
2. Performs data filtering, mapping, and conversion.
3. Constructs SQL statements that represent source database DML or DDL transactions (in committed order).
4. Applies the SQL to the target through the SQL interface that is supported for the given target database, such as ODBC or the native database interface.

Q. What is the difference between Classic and Coordinated Replicat?
The difference between classic mode and coordinated mode is that Replicat is multi-threaded in coordinated mode. Within a single Replicat instance, multiple threads read the trail independently and apply transactions in parallel. Each thread handles all of the filtering, mapping, conversion, SQL construction, and error handling for its assigned workload. A coordinator thread coordinates the transactions across threads to account for dependencies among the threads

Q. If have created a Replicat process in OGG 12c and forgot to specify DISCARDFILE parameter. What will happen?
Starting with OGG 12c, if you don’t specify a DISCARDFILE OGG process now generates a dicard file with default values whenever a process is started with START command through GGSCI.

Q. Is it possible to start OGG EXTRACT at a specific CSN?
Yes, Starting with OGG 12c you can now start Extract at a specific CSN in the transaction log or trail.
Example:
START EXTRACT fin ATCSN 12345
START EXTRACT finance AFTERCSN 67890

Q. List a few parameters which may help improve the replicat performance?
he parameters below can be used to improve the replicat performance:
1. BATCHSQL
2. GROUPTRANSOPS
3. INSERTAPPEND

Q. What are the areas to monitor in Goldengate Replication?
The lag and checkpoint latency of the Extract, pump and Replicat processes are normally monitored

Q. What is the PASSTHRU mode used for?
In pass-through mode, the Extract process does not look up the table definitions, either from the database or from a data definitions file. This increases the throughput of the data pump, as the object definition look-up is bypassed

Q. What are the most common reasons of an Extract process slowing down?
Some of the possible reasons are:
1. Long running batch transactions on a table.
2. Insufficient memory on the Extract side. Uncommitted, long running transactions can cause writing of a transaction to a temporary area (dirtmp) on disk. Once the transaction is committed it is read from the temporary location on the file system and converted to trail files.
3. Slow or overburdened Network.

Q. What are the most common reasons of the Replicat process slowing down?
Some of the possible reasons are:
1. Large amount of transactions on a particular table.
2. Blocking sessions on the destination database where non-Goldengate transactions are also taking place on the same table as the replicat processing.
3. If using DBFS, writing & reading of trail files may be slow if SGA parameters are not tuned.
4. For slow Replicat’s, latency may be due to missing indexes on target.
5. Replicat having to process Update, delete of rows in very large tables

Q. My extract was running fine for a long time. All of a sudden it went down. I started the extract processes after 1 hour. What will happen to my committed transactions that occurred in the database during last 1 hour?
OGG checkpoint provides the fault tolerance and make sure that the transaction marked for committed is capture and captured only once. Even if the extract went down abnormally, when you start the process again it reads the checkpoint file to provide the read consistency and transaction recovery.

Q. I have configured Oracle GoldenGate integrated capture process using the default values. As the data load increases I see that extract starts lagging behind by an hour (or more) and database performance degrades. How you will resolve this performance issue?
When operating in integrated capture mode, you must make sure that you have assigned sufficient memory to STREAMS_POOL_SIZE. An undersized STREAMS_POOL_SIZE or limiting the streams pool to use a specific amount of memory can cause troubles.
The best practice is to allocate STREAMS_POOL_SIZE at the instance level and allocate the MAX. SGA at GG process level as below:
SQL> alter system set STREAMS_POOL_SIZE=3G
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2048, PARALLELISM 4)

Q. What command can be used to view the checkpoint information for the extract process?
Use the following command to view the Extract checkpoint information.
GGSCI> info extract , showch
GGSCI> info extract ext_fin, showch

Q. How do you view the data which has been extracted from the redo logs?
The logdump utility is used to open the trail files and look at the actual records that have been extracted from the redo or the archive log files.

Q. Why should I upgrade my GoldenGate Extract processes to Integrated Extract?
Oracle is able to provide faster integration of the new database features by moving the GoldenGate Extraction processes into the database. Due to this, the GoldenGate Integrated Extract has a number of features like Compression which are not supported in the traditional Extract. You can read more about how to upgrade to Integrated Extract and more about Integrated Delivery. Going forward preference should be give to create new extracts as Integrated Extracts and also to upgrade existing traditional Extracts

Q. What is the minimum Database version which supports Integrated Delivery?
Oracle 11.2.0.4 is the the minimum required database version which supports both Integrated extract and Integrated Reaplicat.

Q. What databases supports GoldenGate Integrated Delivery?
Oracle Integrated Delivery is only available for Oracle Databases.

Q. With Integrated Delivery, where can we look for the performance stats?
Yes with 12c, performance statistics are collected in the AWR repository and the data is available via the normal AWR reports.

Q. What are the steps required to add a new table to an existing replication setup?
The steps to be executed would be the following:
1. Include the new table to the Extract & pump process.
2. Obtain starting database SCN and Copy the source table data to the target database
3. Start Replicat on target at the source SCN database point

Q. What does the GoldenGate CSN equate to, in the Oracle Database?
It is equivalent of the Oracle database SCN transaction number.

Q. How do you create a CSV file from the Extracted data?
You will have to use the CSV Flat File Adaptor to create CSV files. The source would be the extract trail files which use the configuration of the adaptor settings to generate CSV files

Q. What is the purpose of the DEFGEN utility?
When the source and the target schema objects are not the same (different DDL’s) the Replicat process needs to know the source definition of the objects. The output from the DEFGEN utility is used in conjunction with the trail data to determine which column value in the trail belongs to which column.

Q. We want to setup one-way data replication for my online transaction processing application. However there are compressed tables in the environment. Please suggest how I can achieve it.
You must use OGG 11.2 and configure GoldenGate Integrated Capture process to extract data from compressed tables.
Note: Pre OGG 11.2 doesn’t support extracting data from compressed tables

Q. We want to setup one-way data replication using Integrated Extract for my Oracle database running 10.2.0.4. Please suggest the best architecture to achieve it.
Oracle GoldenGate Integrated Capture process supports Oracle databases 10.2 and higher. But if you are running Oracle database 10.2 and want to you Oracle GoldenGate Integrated Capture process then you must configure downstream topology.

Q. I have a table called ‘TEST’ on source and target with same name, structure and data type but in a different column order. How can you setup replication for this table?
OGG by default assumes that the sources and target tables are identical. A table is said to be identical if and only if the table structure, data type and column order are the same on both the source and the target.
If the tables are not identical you must use the parameter ‘SOURCEDEFS’ pointing to the source table definition and ‘COLMAP’ parameter to map the columns from source to target.

Q. What is the best practice to delete the extract files in OGG?
Use the manager process to delete the extract files after they are consumed by the extract/replicat process
PURGEOLDEXTRACTS /u01/app/oracle/dirdat/et*, USECHECKPOINTS, MINKEEPHOURS 2

Q. What parameters can be used to configure Oracle GoldenGate to extract data from Archived Redo log files only?
Use the TRANLOGOPTIONS ARCHIVEDLOGONLY option in the parameter file.

Q. What are the basic resources required to configure Oracle GoldenGate high availability solution with Oracle Clusterware?
There are 3 basic resources required:
1. Virtual IP
2. Shared storage
3. Action script

Q. What command can be applied to switch writing the trial data to a new trail file?

You can use the following command to write the trail data to a new trail file.
SEND EXTRACT ext_name, ROLLOVER

What information can you require when there us data in the discard file?
When data is discarded, the discard file can contain:

1.       Discard row details
2. Database Errors
3. Trail file number

How can you raise the highest size of the read operation into the buffer that holds the results of the reads from the transaction log?

If you are using the Standard Extract, you may use the TRANSLOCATION ASMBUFSIZE parameter to control the read size for ASM Databases

What Is the Best Practice to Delete the Extract Files in Ogg?                                                                           Use the manager process to delete the extract files after they are consumed by the extract/replicat process                                                                                                                                                           PURGEOLDEXTRACTS /u01/app/oracle/dirdat/et*, USECHECKPOINTS, MINKEEPHOURS 

Q. How can you determine if the parameters for a process was recently changed?
Whenever a process is started, the parameters in the.prm file for the process is written to the process REPORT. You can look at the older process reports to view the parameters which were used to start up the process. By comparing the older and the current reports you can identify the changes in the parameters.

Q .what is the use of SOURCEISTABLE & SPECIALRUN keyword in GG?

Use the SOURCEISTABLE parameter to extract complete records directly from source tables in preparation

for loading them into another table or file.SOURCEISTABLE extracts all column data specified within a TABLE statement.

This parameter applies to the following initial load methods:

·         Loading data from file to Replicat.

·         Loading data from file to database utility.

·         Use the SPECIALRUN parameter in a Replicat parameter file for a one-time processing run to direct Replicat not to create checkpoints. A one-time run has a beginning and an end, so checkpoints are not needed. Use SPECIALRUN for certain initial data load methods.

·         When Replicat is in SPECIALRUN mode, do not start it with the START REPLICAT command in GGSCI. It is started automatically during the initial load.

·         SPECIALRUN requires the use of the END parameter. Either REPLICAT or SPECIALRUN is required in the Replicat parameter file. See "REPLICAT" for more information.

       What is checkpoint table
Create the GoldenGate Checkpoint table
GoldenGate maintains its own Checkpoints which is a known position in the trail file from where the Replicat process will start processing after any kind of error or shutdown.This ensures data integrity and a record of these checkpoints is either maintained in files stored on disk or table in the database which is the preferred option.

How is the RESTARTCOLLISION parameter different from HANDLECOLLISIONS?

The RESTARTCOLLISION parameter is used to skip ONE transaction only in a situation when the GoldenGate process crashed and performed an operation (INSERT, UPDATE & DELETE) in the database but could not checkpoint the process information to the checkpoint file/table. On recovery it will skip the transaction and AUTOMATICALLY continue to the next operation in the trail file.When using HANDLECOLLISION GoldenGate will continue to overwritten and process transactions until the parameter is removed from the parameter files and the processes restarted.

2.       What is difference between CSN, SCN?

3.       What is extract, replicat?

4.       What are the GG types or topologies?

5.       How to get log from source & target?

6.       Difference between classic & integral extr  act process?

7.       What are the pre-request for GG?

8.       Types of replication in GG?

9.       What are the areas monitoring required in GG?

10.   What is the difference between local trail / Remote Trail?

11.   What are the GG utilities used?

12.   How to define size for extract file?

13.   How to change GG writing extract file?

14.   How to purge extract file automatically?

15.   How to check how many extract file created per day?

16.   What to check, when replication steps in primary & target?

17.   How to start/stop, extract/replicat for source & target?

18.   How to sync target with source if we missed the trail file?

19.   What is the format for trail file?

20.   What is the default location for the trail file & can we change the trail file location?

21.   What is the datapump in GG?

22.   Is there any limit in extract groups / Replicat numbers?

23.   What are the GG views?

24.   What is GLOBAL file in GG & location?

25.   What are the directory in GG?

26.   What is credential store?

27.   What are the types of encryption supported by GG?

28.   What is obey usage?

29.   What is discard file & usage? Can we change from default location?

30.   How to switch writing to new trail file?

31.   What is the proper steps to shutdown GG database?

32.   How to automatically remove old trail file?

33.   How to enable automatic for replicat/ extract?

34.   What are the parameter file used by GG?

35.   What are the limitation for bidirectional GG?

36.   What is the use of REPERROR in GG?

37.   How to check latency between source & GG?

38.   What is utility used to open trail file in GG?

39.   How to check particular trail file needed or not?

40.   What are the various GG operational types?

41.   What is reverse utility?

42.   In unidirectional GG setup, steps to add a table for replication?

43.   Can we change the GG oracle DB without setting up in operating system level?

44.   What are the types extract check point positions?

45.   How to change the trail file size in GG?

46.   How to check all records processed in replicat? Info replicat group , it should return <EOF>

47.   How to calculate & allocate swap space for GG server?

48.   How to check last 5 recent extract checkpoints?

49.   How to control the checkpoint in extract & replicat?

50.   How to confirm extract in working file in GG?

51.   What are the recovery types in extract process?

52.   How to setup lag process time in GG?

53.   How to manager process to check extract/replicat lag?

54.   How to view GG error log & process report?

55.   How to check how many records processed in replication /extraction?

56.   What is discard file? How to check default location?

57. How to check ,how many updates/deletes happened on GG in particular time?

58. what is archive log only mode in GG?

Q .what is the use of SOURCEISTABLE & SPECIALRUN keyword in GG?

Use the SOURCEISTABLE parameter to extract complete records directly from source tables in preparation

for loading them into another table or file.SOURCEISTABLE extracts all column data specified within a TABLE statement.

This parameter applies to the following initial load methods:

·         Loading data from file to Replicat.

·         Loading data from file to database utility.

·         Use the SPECIALRUN parameter in a Replicat parameter file for a one-time processing run to direct Replicat not to create checkpoints. A one-time run has a beginning and an end, so checkpoints are not needed. Use SPECIALRUN for certain initial data load methods.

·         When Replicat is in SPECIALRUN mode, do not start it with the START REPLICAT command in GGSCI. It is started automatically during the initial load.

·         SPECIALRUN requires the use of the END parameter. Either REPLICAT or SPECIALRUN is required in the Replicat parameter file. See "REPLICAT" for more information.

       What is checkpoint table
Create the GoldenGate Checkpoint table
GoldenGate maintains its own Checkpoints which is a known position in the trail file from where the Replicat process will start processing after any kind of error or shutdown.This ensures data integrity and a record of these checkpoints is either maintained in files stored on disk or table in the database which is the preferred option.

How is the RESTARTCOLLISION parameter different from HANDLECOLLISIONS?

The RESTARTCOLLISION parameter is used to skip ONE transaction only in a situation when the GoldenGate process crashed and performed an operation (INSERT, UPDATE & DELETE) in the database but could not checkpoint the process information to the checkpoint file/table. On recovery it will skip the transaction and AUTOMATICALLY continue to the next operation in the trail file.When using HANDLECOLLISION GoldenGate will continue to overwritten and process transactions until the parameter is removed from the parameter files and the processes restarted.

GOLDENGATE  REBULID PROCESS USING  FLASHBACK SCN

How to do it...

The steps to re-instantiate a broken GoldenGate replication configuration are as follows:

1. Stop the Replicat process in the target environment if it is running by using the following command:

   GGSCI> STOP REPLICAT RGGTEST1
   

2. Truncate the tables in the target database that you are replicating using the following commands:

   SQL> TRUNCATE TABLE SCOTT.EMP;
   SQL> TRUNCATE TABLE SCOTT.DEPT;
   SQL> TRUNCATE TABLE SCOTT.SALGRADE;
   SQL> TRUNCATE TABLE SCOTT.BONUS;
   

3. Note down the current timestamp from the source database:

   SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') from dual;
   TO_CHAR(SYSDATE,'YY
   -------------------
   2013-02-26 15:50:02
   

4. Note down the current_scn from the source database as follows:

   SQL> SELECT CURRENT_SCN FROM V$DATABASE;
   CURRENT_SCN
   -----------
     11547294
   

5. Create a /u01/app/ggate/expdp directory for taking a Datapump Export on both the servers, as follows:

   mkdir /u01/app/ggate/expdp
   

6. Create a directory object in the source database as follows:

   SQL> CREATE DIRECTORY EXPIMP as '/u01/app/ggate/expdp';
   Directory created.
   SQL> GRANT READ, WRITE, EXECUTE on Directory EXPIMP to system;
   Grant succeeded.
   

7. Run Datapump Export to backup the tables that we are replicating:

   [oracle@prim1-ol6-112 ggate]$ expdp userid=system schemas=SCOTT FLASHBACK_SCN=11547294 DIRECTORY=EXPIMP DUMPFILE=EXPDP.dmp LOGFILE=EXPDP.log
   Export: Release 11.2.0.3.0 - Production on Tue Feb 26 16:03:39 2013
   Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
   Password: 
   Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
   With the Partitioning, OLAP, Data Mining and Real Application Testing options
   FLASHBACK automatically enabled to preserve database integrity.
   Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  userid=system/******** schemas=SCOTT FLASHBACK_SCN=11547294 DIRECTORY=EXPIMP DUMPFILE=EXPDP.dmp LOGFILE=EXPDP.log 
   Estimate in progress using BLOCKS method...
   Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
   Total estimation using BLOCKS method: 192 KB
   Processing object type SCHEMA_EXPORT/USER
   Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
   Processing object type SCHEMA_EXPORT/ROLE_GRANT
   Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
   Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
   Processing object type SCHEMA_EXPORT/TABLE/TABLE
   Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
   Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
   Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     exported "SCOTT"."DEPT"   5.929 KB       4 rows
     exported "SCOTT"."EMP"      8.648 KB      16 rows
     exported "SCOTT"."SALGRADE" 5.859 KB       5 rows
     exported "SCOTT"."BONUS"    0 KB       0 rows
   Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
   *********************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
     /u01/app/ggate/expdp/EXPDP.dmp
   Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:05:08
   

8. Create a directory object in the database in the target database:

   SQL> CREATE DIRECTORY EXPIMP AS '/u01/app/ggate/expdp';
   Directory created.
   SQL> GRANT READ,WRITE,EXECUTE ON DIRECTORY EXPIMP TO SYSTEM;
   Grant succeeded.
   

9. Copy the dump file from the source to the target server as follows:

   scp /u01/app/ggate/expdp/expdp.dmp stdby1-ol6-112:/u01/app/ggate/expdp/
   

10. Import the dump file into the target database as follows:

    [oracle@stdby1-ol6-112 expdp]$ impdp userid=system directory=expimp dumpfile=EXPDP.dmp logfile=IMPDP.dmp full=y table_exists_action=REPLACE
    Import: Release 11.2.0.3.0 - Production on Tue Feb 26 16:20:51 2013
    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
    Password: 
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "SYSTEM"."SYS_IMPORT_FULL_01":  userid=system/******** directory=expimp dumpfile=EXPDP.dmp logfile=IMPDP.dmp full=y table_exists_action=REPLACE 
    Processing object type SCHEMA_EXPORT/USER
    ORA-31684: Object type USER:"SCOTT" already exists
    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    Processing object type SCHEMA_EXPORT/ROLE_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
      imported "SCOTT"."DEPT" 5.929 KB       4 rows
      imported "SCOTT"."EMP"  8.648 KB      16 rows
      imported "SCOTT"."SALGRADE" 5.859 KB  5 rows
      imported "SCOTT"."BONUS"    0 KB       0 rows
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 16:21:05
    

11. Alter the Extract and Datapump on the source server as follows:

    GGSCI> ALTER EXTRACT EGGTEST1, BEGIN 2013-02-26 15:50:02
    GGSCI> ALTER EXTRACT PGGTEST1, BEGIN 2013-02-26 15:50:02
    

12. Remove all the trail files from the dirdat directory on the source server except the last generated trail file.
13. Start Extract and Datapump on server A as follows:

    GGSCI> START EXTRACT EGGTEST1
    GGSCI> START EXTRACT PGGTEST1
    

14. Start the Replicat process on server B from the SCN noted in step 4 as follows:

    GGSCI> START REPLICAT RGGTEST1, AFTERCSN 11547294
    

15. Check whether the data is getting replicated on both sides by running stats or not as follows:

    GGSCI (prim1-ol6-112.localdomain) 3> STATS EGGTEST1, TOTAL, TABLE SCOTT.*