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.
-
defgenhelps you generate those definitions (metadata) automatically from your Oracle source database (via queries on DBA_TABLES) into a.deffile. -
This approach helps ensure consistency, reduce manual errors, and support dynamic table changes.
Script: def_gen.sh
Sample Output: .def File
When you run the script successfully, your .def output file will look something like this:
* 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)
-
Dynamic Schema Lists
Instead of hardcodingOWNER 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. -
Filter by Table Name or Last DDL Time
Optionally filter yourSELECTbyTABLE_NAMEor by recentLAST_DDL_TIMEto regenerate definitions only for recently modified tables. -
Parameterize Passwords Securely
Avoid putting plain-text passwords in the script. Use encrypted wallets, environment variables, or Oracle OS authentication for security. -
Dry-Run Mode
Introduce a-nor--dry-runflag to echo commands instead of executingdefgen, for validation before production runs. -
Logging & Alerts
After running the script, capture return codes and send alerts (email) ifdefgenfails or if the.deffile is empty. -
Backup Existing Definition Files
Before overwriting, archive prior.deffiles or maintain a version control system (e.g. Git) to track changes. -
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. -
Validation Post-Run
After generating.def, run a validation check: -
Ensure non-empty
.deffile. -
Validate that it includes all expected tables.
-
Optionally execute a test replicat process in a dev/test cluster.
