Saturday, November 9, 2024

What does “HANDLECOLLISIONS” do in GoldenGate?

 What does “HANDLECOLLISIONS” do in GoldenGate?

HANDLECOLLISIONS is a parameter in Oracle GoldenGate designed to help manage data conflicts that arise during data replication. It's especially useful during initial data loads or when a specific subset of tables needs to be reloaded on the target database.

How HANDLECOLLISIONS Works: This parameter directs GoldenGate on how to handle specific types of data conflicts that can occur:

  • Insert Collision: When an insert operation is attempted, but the row already exists on the target.
  • Update Collision: When an update is attempted, but the target row is missing.
  • Delete Collision: When a delete operation is attempted, but the row is not found on the target.

HANDLECOLLISIONS takes the following actions:

  • For insert collisions, GoldenGate will skip the error if the row already exists.
  • For update collisions, it will convert the update to an insert if no row is found.
  • For delete collisions, it will skip the error if the row doesn't exist.

This parameter should only be used temporarily during initial loads or first-time synchronization, as keeping it on long-term can mask real data consistency issues. Many users mistakenly leave it on and later face data integrity concerns.

While enabling this parameter can prevent the replicat process from stopping unexpectedly, it’s important to understand that it does not provide alerts about underlying issues in the system. This lack of visibility can mean that critical issues go unnoticed until they escalate. Therefore, you should carefully consider the trade-off between stability and the potential risk of unresolved issues.

Prerequisites for using HANDLECOLLISIONS:

  1. HANDLECOLLISIONS requires supplemental logging of all columns for each table, which can add significant overhead to the database. Once HANDLECOLLISIONS is disabled, revert supplemental logging to default settings (SCHEDULINGCOLS).
  2. It requires the full "before" image of every column in the trail file, which is enabled through LOGALLSUPCOLS in the Extract.
  3. HANDLECOLLISIONS only works with tables that have a primary or unique key, which GoldenGate can use. If used on tables without such a key, it may result in duplicate data during update or delete collisions.

Be sure to turn off HANDLECOLLISIONS once the initial load or synchronization is complete. Keeping it enabled beyond this can hide data consistency issues in production. Disabling HANDLECOLLISIONS after the initial synchronization helps detect and address data problems promptly, preserving system and data integrity and preventing performance and reliability issues in the long term.

GoldenGate : GGSCI CHEATSHEET

## INFO

INFO MANAGER         Provides details of the Manager process
INFO MGR         Also provides details of the Manager process
STATUS MANAGER         This command also display the info of manager
INFO ER *         To info all of the process
INFO EXTTRAIL /u01/app/OGGH/DIRDAT/SE         Display info for specific exttrails
INFO EXTTRAIL *         Display info for all exttrails
INFO RMTTRAIL /u01/app/OGGH/DIRDAT/TE         Display info for specific rmttrails
INFO RMTTRAIL *         Display info for all rmttrails

## REFRESH

REFRESH MANAGER         Reloads from the Manager Parameter file
REFRESH MGR         Reloads from the Manager Parameter file

# SEND

SEND MANAGER CHILDSTATUS         Displays status of processes, started by Manager.
SEND MANAGER CHILDSTATUS DEBUG         Return the ports numbers allocated by the Manager
SEND MANAGER GETPORTINFO         Displays the list of currently allocated ports by MGR process
SEND MANAGER GETPORTINFO DETAIL         Provides info on ports and process assigned to them.
SEND MANAGER GETPURGEOLDEXTRACTS         Retrieves trail purge retention info.

# START

START MANAGER         Starts the Manager Process
START MGR         Starts the Manager Process

# STOP

STOP MGR         Stops the Manager Process
STOP MANAGER !         Stops Manager without asking for user confirmation.
STOP MGR !         Stops Manager without asking for user confirmation.

# Add Command

ADD EXTRACT EXT_SR, Tranlog, Begin Now         Used to specify transaction logs as data source for extract.

ADD EXTRACT EXT_SR, Begin Now, Passive         Specifies the extract to be run in passive mode.

ADD EXTRACT EXT_SR, Extseqno 3 Extrba 1056, Begin Now   Specifies the extract process starting position

ADD EXTRACT EXT_SR, SOURCEISTABLE         Extracts data from data tables for initial loading.

ADD REPLICAT INITLOAD, SPECIALRUN         It will create a special run replicat as task.

ADD REPLICAT REPL_TR1, EXTTRAIL         Create the replicat with the trail
/u01/app/OGGH/DIRDAT/AA

ADD REPLICAT REPL_TR1, EXTTRAIL                     Create the replicat with the trail and the
/u01/app/OGGH/DIRDAT/AA, CHECKPOINTTABLE GGSUSER.OGG_CHECKPOINT  Checkpoint info like the DB table used to save checkpoint info.

ADD REPLICAT REPL_TR1, EXTTRAIL           Create the replicat with the trail and specifying that this
/u01/app/OGGH/DIRDAT/AA, NODBCHECKPOINT                  replicat didn’t write info to DB table info to DB table

ADD EXTTRAIL It is used to create the local trail file for extract process on local system

ADD EXTTRAIL /u01/app/OGGH/DIRDAT/SE, EXTRACT EXT_SR, Create EXTTRAIL with the Prefix”SE”, and the size of 100 mb
MEGABYTES 100

ADD EXTTRAIL /u01/app/OGGH/DIRDAT/SE000008 To create the EXTTRAIL with specific sequence number

ADD RMTTRAIL It is used to create the remote trail files for the extract or pump processes on remote systems

ADD RMTTRAIL /u01/app/OGGH/DIRDAT/TE, EXTRACT PXT_SR, Create RMTTRAIL with the Prefix”TE”, and the size of 100 mb
MEGABYTES 100

ADD RMTTRAIL /u01/app/OGGH/DIRDAT/SE000010 To create the RMTTRAIL with specific sequence number

# ALTER Command

ALTER EXTRACT EXT_SR, BEGIN NOW Instructs extract to start processing
ALTER EXTRACT EXT_SR, BEGIN 20241009 Instructs extract to start processing from specific date
ALTER EXTRACT EXT_SR, ETROLLOVER Extract rolls over to next trail file
ALTER EXTRACT EXT_SR, EXTSEQNO 3, EXTRBA 1056 Alters extract to start from the specific locaton in the trail
ALTER EXTRACT EXT_SR, THREAD 4, BEGIN 20241009 Alters extract thread & start date for RAC
ALTER REPLICAT REPL_TR1, BEGIN NOW Alter replicat to start processing from now
ALTER REPLICAT REPL_TR1, BEGIN 20241009 Alter replicat to start processing from specific date
ALTER REPLICAT REPL_TR1, BEGIN 20241009 10:00:10 Alter replicat to start processing from specific date and time
ALTER REPLICAT REPL_TR1, EXTSEQNO 8 Alter replicat to start from the specific trail file
ALTER REPLICAT REPL_TR1, EXTRBA 254998 Alter replicat to start from the specific location in the trail
ALTER EXTTRAIL ./DIRDAT/SE, EXTRACT EXT_SR, MEGABYTES 50 It is used to change the options of the existing EXTTRAIL file for extract process on local system
ALTER RMTTRAIL ./DIRDAT/TE, EXTRACT PXT_SR, MEGABYTES 50 It is used to change the options of the existing RMTTRAIL file of extract or pump processes on remote systems

# CLEANUP Command

CLEANUP EXTRACT EXT_SR It purges all history of records except last
CLEANUP EXTRACT EXT_SR, SAVE 10 It saves last 10 records and deletes all other
CLEANUP REPLICAT REPL_TR1 It purges all history of records except last
CLEANUP REPLICAT REPL_TR1, SAVE 10 It saves last 10 records and deletes all other

# DELETE Command

DELETE EXTRACT EXT_SR Deletes the extract process
DELETE EXTRACT e* Deletes all extract process whose name starts with e
DELETE EXTRACT e* ! Deletes all extract process whose name starts with e without prompting
DELETE REPLICAT REPL_TR1 Deletes the replicat process
DELETE EXTRACT r* Deletes all replicat process whose name starts with r
DELETE EXTRACT r* ! Deletes all replicat process whose name starts with r without prompting
DELETE EXTTRAIL /u01/app/OGGH/DIRDAT/SE It is used to delete the exttrail assigned to the extract on local system
DELETE RMTTRAIL /u01/app/OGGH/DIRDAT/TE It is used to delete the exttrail for the extract or pump on remote system

# INFO Command

INFO EXTRACT EXT_SR, SHOWCH Display checkpoint info of extract
INFO EXTRACT EXT_SR, DETAIL Display trail info, run history
INFO EXTRACT EXT_SR, TASKS Display extract tasks
INFO REPLICAT *, TASKS It is used to display replicat tasks only.
INFO REPLICAT REPL_TR1 It is used to display the information of replicat.
INFO REPLICAT REPL_TR1, DETAIL It display the detailed information of replicat.
INFO REPLICAT REPL_TR1, SHOWCH It displays the checkpoint table information, from checkpoint file and checkpoint table.

# KILL Command

KILL EXTRACT EXT_SR
KILL REPLICAT REPL_TR1
KILL ER * To kill all of the process


# LAG Command

LAG EXTRACT EXT_SR To find lag for extract EXT_SR”
LAG EXTRACT * To find lag for all of the extract processes
LAG REPLICAT REPL_TR1 To find lag for replicat “REPL_TR1”
LAG REPLICAT * To find lag for all of the replicat processes
LAG ER * To get lag info of all the process

# REGISTER Command

REGISTER EXTRACT EXT_SR LOGRETENTION To register extract EXT_SR”

# UNREGISTER Command

UNREGISTER EXTRACT EXT_SR LOGRETENTION To unregister the extract EXT_SR””

# SEND Command

SEND EXTRACT SRC_EXT1, ROLLOVER         To increment the extract to next file in trail
SEND EXTRACT SRC_EXT1, STOP To stop the extract process
SEND EXTRACT SRC_EXT1, TRANLOGOPTIONS TRANSCLEANUPFREQUENCY 20 For the Oracle RAC, specify the time after which the OGG scan and delete the orphan transactions
SEND EXTRACT SRC_EXT1, SKIPTRANS 1.11.1111 THREAD 2 For skipping the transaction in the Oracle RAC environment
SEND EXTRACT EXT_SR, SHOWTRANS Display the info about the open transactions like checkpoint, extract group name, SCN, Redo log and RB, status etc
SEND EXTRACT EXT_SR, SHOWTRANS COUNT 2 Display the info for two transactions only
SEND REPLICAT REPL_TR1, HANDLECOLLISIONS Enable the handlecollisions option of OGG used for error handling
SEND REPLICAT REPL_TR1, REPORT HANDLECOLLISIONS r_* Generate statical report to replicat report file
SEND REPLICAT REPL_TR1, GETLAG Get the lag info in seconds
SEND ER * To use send command on all of the process
SEND EXTRACT SRC_EXT1, BR BRCHECKPOINT IMMEDIATE This command forces a BR checkpoint after skipping any old running transactions

# START Command

START EXTRACT EXT_SR
START REPLICAT REPL_TR1
START REPLICAT REPL_TR1, ATCSN 2549980 To start the replicat process from the oraclespecific CSN number including the CSN no transaction
START REPLICAT REPL_TR1, AFTERCSN 25472359 To start the replicat process from the oraclespecific CSN number, but ignoring that CSN no transaction
START ER * To start all of the process

# STATS Command

STATS EXT_SR
STATS EXTRACT EXT_SR Stats will be displayed for the extract EXT_SR””
STATS EXTRACT EXT_SR REPORTRATE SEC Display the stats for the fetch operations per sec
STATS EXTRACT EXT_SR, TOTAL, DAILY The total stats is shown since the start of the day
STATS EXTRACT EXT_SR, TOTAL, HOURLY, REPORTRATE MIN, By using comma between the keywords multiple options can be used for the stats command
RESET, REPORTFETCH
STATS REPL_TR1
STATS REPLICAT REPL_TR1 Stats will be displayed for the REPLICAT REPL_TR1””
STATS REPLICAT REPL_TR1 REPORTDETAIL SEC Display the stats for the opertaion that were not replicated due to errors
STATS REPLICAT REPL_TR1, TOTALSONLY *.* Total of all transactions since start
STATS REPLICAT REPL_TR1, TOTAL, DAILY The total stats is shown since the start of the day
STATS REPLICAT REPL_TR1, TOTAL, HOURLY, REPORTRATE MIN, By using comma between the keywords multiple options can be used for the stats command
RESET, NOREPORTDETAIL
STATS ER * To check the stats all of the process

# STATUS Command

STATUS EXTRACT EXT_SR To check the status for extract EXT_SR”
STATUS EXTRACT e* To check the status for all extracts starting with “e”
STATUS REPLICAT REPL_TR1 To check the status for replicat “REPL_TR1”
STATUS REPLICAT r* To check the status for all replicat starting with “r”
STATUS ER * To find status of all the process

# STOP Command

STOP EXTRACT EXT_SR To stop the extract EXT_SR”
STOP EXTRACT e* To stop all of the running extract process whose name start with “e”
STOP EXTRACT * To stop all of the running extract processes
STOP REPLICAT REPL_TR1 To stop the replicat “REPL_TR1”
STOP REPLICAT r* To stop all of the running replicat process whose name start with “r”
STOP REPLICAT * To stop all of the running replicat processes
STOP ER * To stop all of the process

Wednesday, March 2, 2022

OGG-00868 Oracle GoldenGate Capture for Oracle

 Issue :

GoldenGate Extract was getting abended with below error


OGG-00868 Oracle GoldenGate Capture for Oracle, extract01.prm: No data found retrieving if to ignore object


Fix :

Permissions on the Table were changed, Granting the permissions helped fixing this issue


Hope this helps.. 

Monday, October 26, 2020

Oracle : ORA-01186,ORA-01111, ORA-01110, ORA-01157 - MRP Terminated with error 1111 - SOLVED

Datafiles Named "UNNAMED%" 

The datafiles are incorrectly stored under $ORACLE_HOME/dbs location with name 'UNNAMED%' for different reasons. No space available to create the datafiles under the allotted datafile location or if the parameter "STANDBY_FILE_MANAGEMENT" is not set to AUTO

As per Oracle 

STANDBY_FILE_MANAGEMENT : enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. 

Precaution should be taken while using this parameter, Make sure the FILE NAME CONVERT parameter is set correctly. 


1st Case : If the allotted datafiles location is running short on space and a new datafile is added on Primary Database, then the datafile is stored under $ORACLE_HOME/dbs location. 

Fix : Get more space added to the mountpoint and perform the below steps to rename the datafile 


2nd Case : If a new datafile is added on Primary database and the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL, then Oracle Recovery Process(MRP) will get terminated due to wrong parameter setting and create the datafile under $ORACLE_HOME/dbs location.

Fix : Rename the Datafile to correct name and change the parameter STANDBY_FILE_MANAGEMENT


I had the 2nd Case on my Database

Errors From Alert Log file:-

Mon Oct 26 14:02:03 2020

Managed Standby Recovery starting Real Time Apply

Mon Oct 26 14:02:03 2020

Errors in file /usr/app/oracle/diag/rdbms/SDB1/SDB11/trace/SDB11_dbw0_2960.trc:

ORA-01186: file 997 failed verification tests

ORA-01157: cannot identify/lock data file 997 - see DBWR trace file

ORA-01111: name for data file 997 is unknown - rename to correct file

ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'

File 997 not verified due to error ORA-01157

Mon Oct 26 14:02:04 2020

MRP0: Background Media Recovery terminated with error 1111

Errors in file /usr/app/oracle/diag/rdbms/SDB1/SDB11/trace/SDB11_pr00_342871.trc:

ORA-01111: name for data file 997 is unknown - rename to correct file

ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'

ORA-01157: cannot identify/lock data file 997 - see DBWR trace file

ORA-01111: name for data file 997 is unknown - rename to correct file

ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'

Managed Standby Recovery not using Real Time Apply


Fix:-

Check for the files needs to be recovered.

SQL> select file#,name,status from v$datafile where status not in ('ONLINE','SYSTEM');

     FILE# NAME                                                    STATUS

---------- ------------------------------------------------------- -------

       997 /usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997    RECOVER


Get the datafile from Primary Database

     FILE# NAME                                       STATUS

---------- --------------------------------------------------

       997 +DATA/PDB/datafile/tbs1.4268.1054563669    ONLINE


- Stop Recovery on Standby Database ( Most Likely it wont be running as Oracle will be terminating it)

SQL > RECOVER MANAGED STANDBY DATABASE CANCEL;


- Make sure STANDBY_FILE_MANAGEMENT is set to MANUAL, if not then run below

SQL> alter system set standby_file_management=MANUAL scope=both sid='*';

System altered.


SQL> alter database create datafile '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997' as '+DATA/SDB1/datafile/tbs1.4268.1054563669';

 *

 ERROR at line 1:

 ORA-01276: Cannot add file

 +DATA/SDB1/datafile/tbs1.4268.1054563669. File has an Oracle Managed Files file name.

 

This Failed for me as we were managing the ASM with OMF. With Oracle Managed Files, you specify file system directories in which the database automatically creates, names, and manages files at the database object level. For example, you need only specify that you want to create a tablespace; you do not need to specify the name and path of the tablespace's data file with the DATAFILE clause. This feature works well with a logical volume manager (LVM).

 

With OMF in place, its easier to rename the dummy datafile

SQL> alter database create datafile '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997' as new;

Database altered.


-- Set the Parameter back to AUTO

SQL> alter system set standby_file_management=AUTO scope=both;

System altered.


-- Start Recovery Process 

SQL> alter database recover managed standby database disconnect from session;

Database altered.


-- This fixed my issue and got the MRP started back and all Datafiles ONLINE.  Hope it resolves your Issue... 

Tuesday, September 22, 2020

ORA-12537: TNS:connection closed - Oracle RAC 11g and above

 Client was getting Below error while connecting to the RAC database


sqlplus tester@RACDB_1

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 22 11:34:07 2020

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:

ORA-12537: TNS:connection closed



Issue : 

The Oracle Binary permissions got changed and was not allowing the connections

Current Permissions under Oracle Home


[oracle@host1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwxr-sr-x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle


It should be set to 6751 and should look like "-rwsr-s--x"

But doing chmod 6751 on oracle binary was not setting the correct permissions


[oracle@host1 bin]$ chmod 6751 oracle

[oracle@host1 bin]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-x--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle


Running below as RDBMS database Owner user helped , in this case "oracle" user


1) Stop the database instance where the permissions got changed

 srvctl stop instance -d RACDB -i RACDB1

 2) Run as Oracle Database owner, in this case its Oracle OS user.  

[oracle@host1 ~]$ $GRID_HOME/bin/setasmgidwrap o=$ORACLE_HOME/bin/oracle

3) The permissions got changed and resolved the connection issues

[oracle@host1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-s--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle



Hope this resolves your issue.. 

Monday, July 6, 2020

Oracle GoldenGate Drop Capture : ORA-01338: Other process is attached to LogMiner session

While Dropping the GG capture Process, it was giving below errors


SQL> exec dbms_capture_adm.drop_capture('OGG$CAPTURE');
BEGIN dbms_capture_adm.drop_capture('OGG$CAPTURE'); END;

*
ERROR at line 1:
ORA-01338: Other process is attached to LogMiner session
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 829
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 800
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 566
ORA-06512: at line 1


Cause :

An execution of the DROP_CAPTURE procedure drops a logminer session.  The problem was caused by an attempt to drop a logminer session while processes are still attached to it.  The processes are still attached, because the capture has not been stopped.



Solution : 

Stop the GG Capture process and then drop 


SQL> exec dbms_capture_adm.stop_capture('OGG$CAPTURE');

PL/SQL procedure successfully completed.

SQL> exec dbms_capture_adm.drop_capture('OGG$CAPTURE');


PL/SQL procedure successfully completed.


Hope this helps you ..