Enabling audit trail for Oracle E-Business Suite Application in R12.1.x/R12.2.x

Audit is a very important feature of the oracle apps. We can track the last changes in the Oracle apps records but what about the second last change so well there is no track of this. So to see all the changes that happened for the table we can use the Audit feature of the oracle apps. We can audit some of the sensitive tables in oracle apps R12 using audit trail functionality. We don't need to audit the complete table. We can audit the Complete or we can audit some of the columns in the table and the Audit report will give us all the details regarding any changes in this table for that audit columns.

You can choose to store and retrieve a history of all changes users make on a given table. Auditing is accomplished using audit groups, which functionally registered Oracle IDs or group tables to be audited. For a table to be audited, it must be included in an enabled audit group.

Audit Trail Groups are groups of tables and columns. You do not necessarily need to include all the columns in a given table. You enable auditing for audit groups rather than for individual tables. You would typically group together those tables that belong to the same business process (for example, purchase order tables).

A given table can belong to more than one audit group. If so, the table is audited according to the highest level of enabling for any of its groups, where Enabled is the highest, followed by Disable Dump Data, Disable No Growth, and Disable Purge Table, in that order.

You can enable auditing for a maximum of 240 columns for a given table, and you can enable auditing for all types of table columns except LONG, RAW, or LONG RAW. Your audit group must include all columns that make up the primary key for a table; these columns are added to your audit group automatically. Once you have added a column to an audit group, you cannot remove it.

Turn on Audit Trail.

Turn on Oracle E-Business Suite Applications Audit Trail by setting the system profile Audit Trail: Activate to Yes

System Administrator ==> Profile ==>System

Find the Profile 'AuditTrail:Activate' and set this to Yes at Site Level.

Identify which table we want to audit and which column of this table. Then need to identify the Module of the Audit table to which that Table belongs.

 

Install the Audit Trail

System Administrator ==> Security ==>AuditTrail ==>Install

 

Select the Module to which we want to Install the Audit Trail.

Create the Table Audit Group.

 

System Administrator ==> Security ==>AuditTrail ==>Groups

Create the Audit Group for table 'AP_INVOICES_ALL'

 

Note: When you first create the audit group the group status will be 'Enable Requested' this will automatically be changed to 'Enabled' once the AuditTrail Update Tables concurrent request is run

Enable Audit Columns 

System Administrator ==> Security ==>AuditTrail ==>Tables

Now we need to set the Audit on the Table and the column to which we want to keep track.

Now we will run the concurrent Program in System Administrator responsibility "AuditTrail report for Audit Group Validation".

After the given above concurrent program run successfully then we will run the concurrent Program in System Administrator responsibility "AuditTrail Update Tables'.

Confirm the Audit tables would be created with (_A) name. These are also called shadow Tables

Error:

While running AuditTrail Update Tables concurrent request on e-business suite R12 as part of audittrail enable process the request completed with Fatal error in fdasql, quitting.... Fatal error in fdacv, quitting.... Fatal error in fdaupc, quitting error.

Metalink note

AuditTrail Update Tables fails on View AP_SYSTEM_PARAMETERS_ALL_AC1 (Doc ID 727208.1)

Solution

1. Navigate to the audit group tables, and query back the table as before

2. Set the Group state to "Disable - Purge Table" for AP_SYSTEM_PARAMETERS_ALL. This option

Disable - Purge Table Drops the auditing triggers and views and deletes all data from the shadow

table.

3. Run the "AuditTrail Update Tables" concurrent program

Setup ora2pg for Oracle to Postgres Migration

An important part of performing a migration from Oracle to PostgreSQL is selecting the right tool for helping with the conversion between systems. ora2pg a powerful open source utility.

ora2pg is a tool that migrates Oracle or MySQL databases to PostgreSQL by generating compatible SQL files As the documentation states, ora2pg “connects your Oracle database, scans it automatically and extracts its structure or data, it then generates SQL scripts that you can load into your PostgreSQL database.”

Below Steps Required for Setup ora2pg and Configuration:

1. Install necessary prerequisites
2. Build and install DBD::Oracle
3. Build and install ora2pg
4. Install the Oracle Instant Client SDK
5. Configure ora2pg
6. Test and use ora2pg

Prerequisite

1. Install wget, perl, DBI (database interface module), DBD::Oracle and DBD::Pg
2. Download the latest version of ora2pg

Step 1: Install Required Package and it's dependency

# yum install -y wget perl perl-DBI perl-DBD-Pg perl-Time-HiRes make gcc 

# yum install -y perl-core perl-devel

Step 2: Download the ora2pg

# wget https://github.com/darold/ora2pg/archive/refs/tags/v24.3.tar.gz

--2026-03-31 15:31:03--  https://github.com/darold/ora2pg/archive/refs/tags/v24.3.tar.gz

Resolving github.com (github.com)... 20.207.73.82

Connecting to github.com (github.com)|20.207.73.82|:443... connected.

HTTP request sent, awaiting response... 302 Found

Location: https://codeload.github.com/darold/ora2pg/tar.gz/refs/tags/v24.3 [following]

--2026-03-31 15:31:09--  https://codeload.github.com/darold/ora2pg/tar.gz/refs/tags/v24.3

Resolving codeload.github.com (codeload.github.com)... 20.207.73.88

Connecting to codeload.github.com (codeload.github.com)|20.207.73.88|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: unspecified [application/x-gzip]

Saving to: ‘v24.3.tar.gz’

[ <=> ] 576,965 1.34MB/s   in 0.4s

2026-03-31 15:31:10 (1.34 MB/s) - ‘v24.3.tar.gz’ saved [576965]

Step 3: Check file Downloaded or not and after that untar 

# ll

total 7408

-rwxrwxrwx 1 oracle oracle   12668 Jun 28  2025 crontabs-1.11-6.20121102git.el7.noarch.rpm

-rwxrwxrwx 1 oracle oracle    3169 Sep  8  2025 RPM-GPG-KEY-oracle-ol8

-rwxrwxrwx 1 oracle oracle  576965 Mar 31 15:31 v24.3.tar.gz

# tar -xvf v24.3.tar.gz

Step 4: Install Postgres Software

# cd ora2pg-24.3/

# ll

total 644

-rwxrwxrwx 1 oracle oracle 374342 Mar 29  2024 changelog

drwxrwxrwx 1 oracle oracle    512 Mar 29  2024 doc

-rwxrwxrwx 1 oracle oracle     21 Mar 29  2024 INSTALL

drwxrwxrwx 1 oracle oracle    512 Mar 29  2024 lib

-rwxrwxrwx 1 oracle oracle  32472 Mar 29  2024 LICENSE

-rwxrwxrwx 1 oracle oracle  75191 Mar 29  2024 Makefile.PL

-rwxrwxrwx 1 oracle oracle    180 Mar 29  2024 MANIFEST

drwxrwxrwx 1 oracle oracle    512 Mar 29  2024 packaging

-rwxrwxrwx 1 oracle oracle 171271 Mar 29  2024 README

drwxrwxrwx 1 oracle oracle    512 Mar 29  2024 scripts

# perl Makefile.PL

which: no bzip2 in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:..........)

Checking if your kit is complete...

Looks good

Writing Makefile for Ora2Pg

Writing MYMETA.yml and MYMETA.json

Done...

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

Please read documentation at http://ora2pg.darold.net/ before asking for help

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

Now type: make && make install

# make && make install

# make install

                                    OR

# make && make install

Step 5: Check Postgres Software version

# ora2pg -v

Ora2Pg v24.3

Step 6: Download & Install Oracle Instant Client SDK

# yum install -y /path/to/oracle-instantclient*.rpm

You will need to include the below in your ~/.bash_profile:

$ vi ~/.bash_profile:

# InstantClient

export ORACLE_HOME=/opt/oracle/instantclient_23_26

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PAT

export PATH=$ORACLE_HOME/bin:$PATH

Then source the new env vars by running

$ source ~/.bash_profile

You will then test the connection to your Oracle server (if you've installed the sqlplus instantclient package simply run the below):

$ sqlplus user/pwd@//IP:1521/ORCLPDB1

 

Configure ora2pg

After you have ora2pg built and installed you can now configure the ora2pg.conf to work with your Oracle server. The settings that you see below are the settings in my environment. The ora2pg.conf file has many configuration settings that you can change. Below are the configuration changes that you will need to apply to your ora2pg.conf in order to be able to use ora2pg. Please keep in mind that you will need to change the settings to match your environment.

Edit config:

$vi /etc/ora2pg/ora2pg.conf

# Set the Oracle home directory

ORACLE_HOME     /opt/oracle/instantclient_23_26

#Connection Details (Oracle Source)

ORACLE_DSN      dbi:Oracle:host=hostname;port=1521;sid=ORCL

ORACLE_USER     user

ORACLE_PWD      password

#Migration Scope

#Oracle schema/owner to use

SCHEMA          APPS

#EXPORT SECTION (Export type and filters)

TYPE            TABLE

ALLOW           XX_.*

#Performance Settings

PARALLEL_TABLES 2

JOBS            2

ROWS            50000

PREFETCH        10000

CASE_SENSITIVE  0

USE_DBA         0

DATA_LIMIT 1

FILE_PER_TABLE 1

SPLIT_LIMIT 10M

# Export Oracle schema to PostgreSQL schema

EXPORT_SCHEMA   1

EXPORT_TABLES   1

EXPORT_INDEXES  0

EXPORT_CONSTRAINTS 0

EXPORT_TRIGGERS 0

#Data Type Handling

#TYPE SECTION (Control type behaviors and redefinitions)

PG_VERSION      11

PG_NUMERIC_TYPE 1

DEFAULT_NUMERIC numeric

#Disabled Objects

#OBJECT MODIFICATION SECTION (Control objects structure or name modifications)

DISABLE_PARTITION 1

DISABLE_TRIGGERS 1

DISABLE_SEQUENCE 1

DISABLE_PACKAGE   1

DISABLE_FUNCTION  1

DISABLE_PROCEDURE 1

#Parsing & Metadata

PLSQL_PARSING   0

ORACLE_METADATA 0

#Other Flags

DIRECT          1

LOOK_FORWARD_FUNCTION 0

NO_FULL_SCHEMA  1

Testing ora2pg

Now that ora2pg has been configured you can finally test connectivity. By testing the connectivity you will see that you have configured ora2pg correctly, if you didn’t you will receive errors. You can test by running the below command:

$ ora2pg -t SHOW_VERSION -c config/ora2pg.conf

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

  

Note: Here we used 'estimate_cost' is to activate the migration cost evaluation with SHOW_REPORT

 

$ ora2pg -t show_report  --estimate_cost -c config/ora2pg.conf --dump_as_html > /tmp/ora2pg/ora2pgtestreport.html

 

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

Ora2Pg v24.3 - Database Migration Report

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

Version  Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

Schema   APPS

Size     90.36 MB