Definition of a Table Using defgen Utility (Oracle GoldenGate)

Definition of a table using defgen Utility (oracle golden gate) - simple script to automatically get your table definitions if you are using UserExits or if you are relying on the definition file rather than the replicat parameter file.. Do not forget to update your table definitions when the source table has been modified.

Important Note: Always update your definition files (DEFSFILE) when the source table structure changes, to avoid replicat failures. Test in non-production environments first.
Btw, edit the home, golden gate locations accordingly to suit your environment..

Why Use defgen?

• In certain GoldenGate replication setups, especially when using DEFSFILE or UserExits, the replicat process expects table definitions externally instead of deriving them from the parameter file.

• defgen helps you generate those definitions (metadata) automatically from your Oracle source database (via queries on DBA_TABLES) into a .def file.

• This approach helps ensure consistency, reduce manual errors, and support dynamic table changes.

Script: def_gen.sh

#!/bin/bash

# ---------------------------------------------------------------------

# def_gen.sh — Generate GoldenGate DEFSFILE (table definitions) automatically

# Usage: ./def_gen.sh

# Author: Raj 

# Adjust below variables to your environment (Oracle home, GoldenGate paths, owners)

# ---------------------------------------------------------------------

# Adjust these to suit your system

ORACLE_BASE="/u01/orcl"

GG_HOME="/u01/orcl/goldengate/111"

# Source your Oracle environment (ORACLE_HOME, PATH, etc.)

. ${ORACLE_BASE}/.profile

cd ${ORACLE_BASE}

cd ${ORACLE_BASE}/gg

# Move old .obey files to logs directory (backup/archive)

if [ ! -d logs ]; then

  mkdir logs

fi

mv *.obey logs/ 2>/dev/null

TIMESTAMP=$(date +"%m-%d-%y-%H-%M-%S")

OBEY_FILE="${ORACLE_BASE}/gg/defgen_${TIMESTAMP}.obey"

DEFINITION_FILE="${ORACLE_BASE}/gg/defgen_${TIMESTAMP}.def"

# Use SQL*Plus to generate the .obey file (instructions for defgen)

sqlplus -s / as sysdba <<EOF

SET ECHO OFF

SET HEAD OFF

SET TRIMSPOOL ON

SET FEEDBACK OFF

SPOOL ${OBEY_FILE}

PROMPT --DEFSFILE

PROMPT DEFSFILE ${DEFINITION_FILE},PURGE

PROMPT USERID ggs, PASSWORD <your_password_here>

SELECT 'TABLE ' || OWNER || '.' || TABLE_NAME || ';'

  FROM DBA_TABLES

 WHERE OWNER IN (

   'SCOTT',

   'HR'

   -- Add your schemas here; you can also filter by TABLE_NAME if desired

 );

SPOOL OFF

EOF

# Change directory to GoldenGate home

cd ${GG_HOME}

# Execute defgen with the generated obey file

# Alternatively: echo "obey ${OBEY_FILE}" | ./defgen

./defgen PARAMFILE ${OBEY_FILE}

# List the new definition files

ls -ltr ${ORACLE_BASE}/gg

Sample Output: .def File

When you run the script successfully, your .def output file will look something like this:

raj@localhost > cat defgen_05-18-13-02-24-59.def


* Definitions created/modified  2013-05-18 02:25
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*
*
Definition for table SCOTT.VALUES_TAB
Record length: 430
Syskey: 0
Columns: 5
ID       64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
NAME     64    200       56  0  0 1 0    200    200      0 0 0 0 0 1    0 0 0
VALUE1   64     50      262  0  0 1 0     50     50     50 0 0 0 0 1    0 0 2
VALUE2   64     50      318  0  0 1 0     50     50     50 0 0 0 0 1    0 0 2
VALUE3   64     50      374  0  0 1 0     50     50     50 0 0 0 0 1    0 0 2
End of definition

Each line in the table describes a column’s metadata, with positional fields (Name, Data Type, Nullability, etc.) required by GoldenGate.

Best Practices (Based on Experience)

1. Dynamic Schema Lists
   Instead of hardcoding OWNER IN ( … ), you can generate the list of schemas dynamically (or via a control table) so you don’t need to edit the script when schemas change.

2. Filter by Table Name or Last DDL Time
   Optionally filter your SELECT by TABLE_NAME or by recent LAST_DDL_TIME to regenerate definitions only for recently modified tables.

3. Parameterize Passwords Securely
   Avoid putting plain-text passwords in the script. Use encrypted wallets, environment variables, or Oracle OS authentication for security.

4. Dry-Run Mode
   Introduce a -n or --dry-run flag to echo commands instead of executing defgen, for validation before production runs.

5. Logging & Alerts
   After running the script, capture return codes and send alerts (email) if defgen fails or if the .def file is empty.

6. Backup Existing Definition Files
   Before overwriting, archive prior .def files or maintain a version control system (e.g. Git) to track changes.

7. Schedule via Cron / Orchestration Engine
   Automate this script (e.g. once daily or before batch runs) using cron or orchestration tools like Ansible, making sure only one instance runs at a time.

8. Validation Post-Run
   After generating .def, run a validation check:

• Ensure non-empty .def file.

• Validate that it includes all expected tables.

• Optionally execute a test replicat process in a dev/test cluster.